Hi guys,
I wonder would someone be able to help me out with a little dynamic coding issue. I preface this by saying I am not a PLSQL developer...
I have hacked together a procedure which will (when invoked) initiate a DataPump export. The aim behind this is that we will allow app schema users in development to initiate DataPump exports of schemas as neccessary.
The DataPump export works successfully, the script however also looks to create an external table based on the Datapump log file in order to allow the app end users query the table and review the DataPump log output.
The below is what I have thus far. The issue is solely with the create external table statement in my opinion.
create or replace procedure DPUMP_AUTO_SCHEMA123 IS
-- Variables
hand1 NUMBER;
cnt NUMBER;
l_ext_table_name varchar2(2000);
l_create_table varchar2(1000);
BEGIN
-- Start by naming the external table LOG
l_ext_table_name:='''expdp_plsql_Schema_SCHEMA123.' || TO_CHAR(SYSDATE,'YYMMDDHH24MI') || '.log''';
-- Create a (user-named) Data Pump job to do a schema export
hand1 := Dbms_DataPump.Open(operation => 'EXPORT',
job_mode => 'SCHEMA',
job_name => 'EXP_SCHEMA123_' || TO_CHAR(SYSDATE,'YYMMDDHH24MI'),
version => 'COMPATIBLE');
-- Specify a single dump file for the job (using the handle just returned)
Dbms_DataPump.Add_File(handle => hand1,
filename => l_ext_table_name,
directory => 'AUTODPUMP_DIR',
filetype => 3);
Dbms_DataPump.Add_File(handle => hand1,
filename => 'expdp_plsql_Schema_SCHEMA123.' || TO_CHAR(SYSDATE,'YYMMDDHH24MI') || '.dmp',
directory => 'AUTODPUMP_DIR',
filetype => 1);
-- A metadata filter is used to specify the schema that will be exported.
DBMS_DATAPUMP.METADATA_FILTER(hand1,'SCHEMA_EXPR','IN (''SCHEMA123'')');
DBMS_DATAPUMP.Start_Job(hand1);
-- check if we need to drop table before proceeding
SELECT COUNT(*) INTO CNT FROM ALL_TABLES WHERE TABLE_NAME = 'AUTODPUMP_LOG' ;
BEGIN
IF CNT = 1 THEN
execute immediate 'DROP TABLE AUTODPUMP.AUTODPUMP_LOG';
END IF;
l_create_table := 'CREATE TABLE AUTODPUMP_LOG (line VARCHAR2(4000)) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY AUTODPUMP_DIR ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ''~''
MISSING FIELD VALUES ARE NULL (line CHAR(5000))) LOCATION (' || l_ext_table_name || ') ) PARALLEL 1 REJECT LIMIT UNLIMITED';
-- Check the create external table statement
insert into test values (l_create_table);
-- Perform the create external table statement
execute immediate l_create_table ;
END;
END DPUMP_AUTO_SCHEMA123;
/
If I comment out line 44 [--execute immediate l_create_table ;] and do not actually invoke the create table statement - everything succeeds. I have included a line on line 41 to spit out the "create external table..." statement prior to execution and this statement attempt when inserted into the test table looks correct. If I take that actual statement out of the test table - I can actually rerun the statement and it works successfully as the owner of the proc above.
It just appears to me that the execution of the actual "create table... external.." will not succeed when invoked from within the 'execute immediate' statement...
Thanks
Ruan