How to export data to an SQL file in PL/SQL?

1 year ago
Jackson Davis
2 minutes

In PL/SQL, you can use the DBMS_DATAPUMP package to export data to a SQL file. Below is an example code:

DECLARE
  l_dp_handle  NUMBER;
  l_status     VARCHAR2(20);
BEGIN
  -- 创建数据泵作业
  l_dp_handle := DBMS_DATAPUMP.OPEN(operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 'EXPORT_JOB');

  -- 添加导出任务
  DBMS_DATAPUMP.ADD_FILE(handle => l_dp_handle, filename => 'export.sql', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);

  -- 设置导出参数
  DBMS_DATAPUMP.SET_PARAMETER(handle => l_dp_handle, name => 'INCLUDE_METADATA', value => 1);
  DBMS_DATAPUMP.SET_PARAMETER(handle => l_dp_handle, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
  DBMS_DATAPUMP.SET_PARAMETER(handle => l_dp_handle, name => 'ESTIMATE', value => 'BLOCKS');
  DBMS_DATAPUMP.SET_PARAMETER(handle => l_dp_handle, name => 'TABLE_EXISTS_ACTION', value => 'APPEND');

  -- 开始导出
  DBMS_DATAPUMP.START_JOB(handle => l_dp_handle);

  -- 等待导出完成
  l_status := DBMS_DATAPUMP.JOB_STATUS(handle => l_dp_handle);

  WHILE l_status <> 'COMPLETED' AND l_status <> 'STOPPED' LOOP
    l_status := DBMS_DATAPUMP.JOB_STATUS(handle => l_dp_handle);
    DBMS_LOCK.SLEEP(5);
  END LOOP;

  -- 关闭数据泵作业
  DBMS_DATAPUMP.CLOSE(handle => l_dp_handle);
  
  DBMS_OUTPUT.PUT_LINE('Data export completed.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Data export failed: ' || SQLERRM);
END;

In the above code, firstly a Data Pump job is created, then the SQL file to be exported is added using DBMS_DATAPUMP.ADD_FILE. Next, export parameters are set using DBMS_DATAPUMP.SET_PARAMETER, such as including metadata, access methods, etc. The export is then started by calling DBMS_DATAPUMP.START_JOB and the export status is checked using DBMS_DATAPUMP.JOB_STATUS. Finally, the Data Pump job is closed and any potential exceptions are handled.

Please note that the exported SQL file will be saved in the specified directory. In the example code, the directory used is DATA_PUMP_DIR, make sure that this directory exists in the database and has the appropriate read and write permissions.

bannerAds