[Oracle]ストアドでエクスポート Data Pump 10g

Oracle10g ストアドプロシージャーでエクスポートを実行する

日付でファイル名やJOB名を変えていくことなどいろいろ組み込んでみました。参考にはしていただけるかと思います。

create or replace PROCEDURE fjexp_user2(ret OUT NUMBER) is

/* Oracle10g用エクスポート　*/

dmpfilename varchar2(40);
logfilename varchar2(40);
hand NUMBER;
ind NUMBER; -- loop index
pct_done NUMBER; -- percentage complete
job_state VARCHAR2(30); -- track job state
le ku$_LogEntry; -- WIP and error messages
js ku$_JobStatus; -- job status from get_status
jd ku$_JobDesc; -- job description from get_status
sts ku$_Status; -- status object returned by get_status

fstamp varchar2(20);
hBFile bfile;
nIgnore number;


BEGIN

--旧ファイルの削除(JAVA)
FJEXP_USERFILEDEL(ret);

IF ret <> 0 THEN
dbms_output.put_line('FJEXP_USERFILEDELが失敗しました');
ret :=1;
RETURN;
END IF;

--ファイル名の決定
fstamp := to_char(sysdate, 'yymmdd_hh24miss');
dmpfilename := 'HB01NICHIJIMAE' || fstamp || '.dmp';
logfilename := 'HB01NICHIJIMAE' || fstamp || '.log';

/*
--ファイル存在確認
hBFile := BFILENAME('ORADMP_USEREXP',dmpfilename);
nIgnore := DBMS_LOB.FILEEXISTS(hBFile);

IF nIgnore = 1 THEN
--あれば削除（削除しないとエクスポートできない)
UTL_FILE.FREMOVE('c:\oradmp\userexp',dmpfilename);
end if;
*/

hand := dbms_datapump.OPEN('EXPORT','SCHEMA', NULL,'EXP_NICHIJIMAE' || fstamp);

dbms_datapump.add_file(hand, dmpfilename ,'ORADMP_USEREXP',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
dbms_datapump.add_file(hand, logfilename,'ORADMP_USEREXP',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

dbms_datapump.metadata_filter ( hand, 'SCHEMA_EXPR', 'IN (HB01)');

dbms_datapump.start_job(hand);

-- monitor job
pct_done := 0;
job_state := 'UNDEFINED';
WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED') LOOP
dbms_datapump.get_status(hand, dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip, -1, job_state, sts);

js := sts.job_status;

-- If the percentage done changed, display the new value
IF js.percent_done != pct_done THEN
dbms_output.put_line('*** Job percent done = ' ||
to_char(js.percent_done));
pct_done := js.percent_done;
END IF;

-- If any work-in-progress (WIP) or error messages 
-- were received for the job, display them.
IF (BITAND(sts.mask,dbms_datapump.ku$_status_wip) != 0) THEN
le := sts.wip;
ELSE
IF (BITAND(sts.mask,dbms_datapump.ku$_status_job_error) != 0) THEN
le := sts.error;
ELSE
le := NULL;
END IF;
END IF;

IF le IS NOT NULL THEN
ind := le.FIRST;
WHILE ind IS NOT NULL LOOP
dbms_output.put_line(le(ind).LogText);
ind := le.NEXT(ind);
END LOOP;
END IF;
END LOOP;

-- Indicate that the job finished and detach from it.
dbms_output.put_line('Job has completed');
dbms_output.put_line('Final job state = ' || job_state);
dbms_datapump.detach(hand);
ret := 0;

EXCEPTION
WHEN OTHERS THEN
ret := 1;
dbms_datapump.stop_job(hand,1,0);


END fjexp_user2;