How to export data from a database table using PL/SQL?

2 years ago
Noah Thompson
3 minutes

In PL/SQL, you have the option to export data from database tables using either the DBMS_DATAPUMP package or the EXPDP tool. Two methods are available for you to choose from.

Option 1: Utilize the DBMS_DATAPUMP package.

The DBMS_DATAPUMP is an Oracle built-in package for data pump operations. It allows you to export and import database objects and data using PL/SQL code. Below is an example code of using DBMS_DATAPUMP to export database table data.

DECLARE
  -- 定义变量
  handle NUMBER;
BEGIN
  -- 初始化导出任务
  handle := DBMS_DATAPUMP.OPEN(operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 
  'EXPORT_TABLE_DATA');
  -- 添加要导出的表
  DBMS_DATAPUMP.ADD_FILE(handle, file_name => 'EXPORT_TABLE_DATA.dmp', directory => 
  'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
  DBMS_DATAPUMP.METADATA_FILTER(handle, 'SCHEMA_EXPR', name => '<schema_name>', value => '');
  DBMS_DATAPUMP.METADATA_FILTER(handle, 'NAME_EXPR', name => '<table_name>', value => '');
  -- 开始导出
  DBMS_DATAPUMP.START_JOB(handle);
  -- 等待导出完成
  DBMS_DATAPUMP.WAIT_FOR_JOB(handle, TRUE);
  -- 关闭导出任务
  DBMS_DATAPUMP.CLOSE(handle);
END;
/

Please note that in the above code, and respectively represent the schema name and table name to be exported, you need to replace them with actual values.

Option 2: Utilize the EXPDP tool.

EXPDP is a command-line tool provided by Oracle for data pump operations. You can export database table data by running the following command in the operating system’s command line interface.

expdp <用户名>/<密码> DIRECTORY=<目录名称> DUMPFILE=<导出文件名>.dmp TABLES=<模式名称>.<表名称>

Please replace the following parts of the above command with the actual values.

<用户名>: 数据库用户的用户名
<密码>: 数据库用户的密码
<目录名称>: 导出文件的目录名称（需要在数据库中提前创建）
<导出文件名>: 导出文件的名称
<模式名称>: 要导出的模式名称
<表名称>: 要导出的表名称

After executing the above command, the exported data will be saved to the specified export file.

Here are two commonly used methods for exporting database table data. You can choose one based on your own needs.

bannerAds