Since the release 10g, Oracle have redesigned the export / import method. Oracle still ships EXP and IMP but DATA PUMP is now the standard tool for fast data movement between databases. DATA PUMP syntax is very similar to EXP/IMP, although its technology is entirely different.

Data Pump runs independent database jobs (different from traditionally client EXP/IMP) in a distributed environment.  For instance, the DATA PUMP scheduler user can be different from the one that is monitoring the status of that export / import job.  The exported data is saved /loaded in / from a database DIRECTORY object and the user that exports must have write /read access to it.

What I’ll write today is not about DATA PUMP itself and all it features (google it it’s easy), but is to describe a simple way use the DBMS_DATAPUMP PL/SQL API to to export partial data from some tables on my database.  I guess the code explains itself…

First, create a database directory:

CREATE OR REPLACE DIRECTORY MY_DIR AS '/opt/my_oracle_dir';
GRANT READ, WRITE ON DIRECTORY MY_DIR TO joni;

Next, let’s create the procedure:

CREATE OR REPLACE PROCEDURE partial_export
AS
   l_dp_handle        NUMBER;
   l_last_job_state   VARCHAR2 (30)  := 'UNDEFINED';
   l_job_state        VARCHAR2 (30)  := 'UNDEFINED';
   l_sts              ku$_status;
   l_job_name         VARCHAR2 (100);
   l_dirname          VARCHAR2 (100);
   l_filename         VARCHAR2 (100);
BEGIN
   l_filename := 'myexpfile.dmp';
   -- sets the job name
   l_job_name := 'BZ' || SYSDATE;
   l_dp_handle :=
      DBMS_DATAPUMP.OPEN (operation        => 'EXPORT',
                          job_mode         => 'TABLE',
                          remote_link      => NULL,
                          job_name         => l_job_name,
                          VERSION          => 'LATEST'
                         );
   --specify the database directory  and the filename for the export file
   DBMS_DATAPUMP.add_file (handle         => l_dp_handle,
                           filename       => l_filename,
                           DIRECTORY      => 'MY_DYR'
                          );
   DBMS_DATAPUMP.add_file (handle         => l_dp_handle,
                           filename       => l_filename || '.LOG',
                           DIRECTORY      => 'MY_DYR',
                           filetype       => DBMS_DATAPUMP.ku$_file_type_log_file
                          );
   --specify the tables that I want to export. (CLIENTES,FORNECEDORES)
   DBMS_DATAPUMP.metadata_filter (handle      => l_dp_handle,
                                  NAME        => 'NAME_EXPR',
                                  VALUE       => 'IN (''CLIENTES'', ''FORNECEDORES'')'
                                 );
   -- set subset data export. exports only rows that id_empresa equals 2050
   DBMS_DATAPUMP.data_filter (handle          => l_dp_handle,
                              NAME            => 'SUBQUERY',
                              VALUE           => 'WHERE ID_EMPRESA=2050',
                              table_name      => 'CLIENTES'
                             );
   -- set subset data export. exports only rows that id_empresa equals 2050
   DBMS_DATAPUMP.data_filter (handle          => l_dp_handle,
                              NAME            => 'SUBQUERY',
                              VALUE           => 'WHERE ID_EMPRESA=2050',
                              table_name      => 'FORNECEDORES'
                             );
   DBMS_DATAPUMP.start_job (l_dp_handle);
   DBMS_DATAPUMP.detach (l_dp_handle);
END;

It’s ready to be invoked! Try it!!! Now you can export DATA directly from your application without any special coding. Just call this procedure! Next, you just have to code the method to get all that data out of the server directory