In my this procedure I am getting one error about DBMS_LOCK.sleep (10);.How to declare it?
CREATE OR REPLACE PROCEDURE SYSTEM.SCHEMA_IMPORT(dumpfilename IN VARCHAR2)
IS
h1 NUMBER; -- data pump job handle
job_state VARCHAR2 (30);
status ku$_Status; -- data pump status
job_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT (job_not_exist, -31626);
BEGIN
h1 :=
DBMS_DATAPUMP.open (operation => 'IMPORT',
job_mode => 'SCHEMA',
job_name => NULL);
DBMS_DATAPUMP.set_parameter (h1, 'TABLE_EXISTS_ACTION', 'TRUNCATE');
DBMS_DATAPUMP.add_file (h1, dumpfilename, 'SOURCE');
DBMS_DATAPUMP.add_file (h1,
dumpfilename || '.log',
'SOURCE',
NULL,
DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
DBMS_DATAPUMP.metadata_remap (h1,
'REMAP_SCHEMA',
'FROMSCHEMA',
'TOSCHEMA');
DBMS_DATAPUMP.start_job (h1);
job_state := 'UNDEFINED';
BEGIN
WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED')
LOOP
status :=
DBMS_DATAPUMP.get_status (
handle => h1,
mask => DBMS_DATAPUMP.ku$_status_job_error
+ DBMS_DATAPUMP.ku$_status_job_status
+ DBMS_DATAPUMP.ku$_status_wip,
timeout => -1);
job_state := status.job_status.state;
DBMS_LOCK.sleep (10);
END LOOP;
EXCEPTION
WHEN job_not_exist
THEN
DBMS_OUTPUT.put_line ('job finished');
END;
COMMIT;
END;
/