Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Vadim.

Asked: June 24, 2016 - 8:57 pm UTC

Answered by: Connor McDonald - Last updated: June 30, 2016 - 1:33 am UTC

Category: Database - Version: 11g

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Obfuscated SQL Contest: Stelios Vlasopoulos' Winning Entry

You Asked

Good afternoon, it is a beginning programmer. I had a problem with DBMS_DATAPUMP.data_filter. Data_filter not work.

create PROCEDURE exp_tables_w_qfilter
(
p_schema_name IN VARCHAR2, -- 'LTP'
p_table_name IN VARCHAR2, -- 'AUDITTABLE_TEST'
p_table_filter IN VARCHAR2 DEFAULT NULL -- 'WHERE TIME >= time_templ'
)
IS

v_sid VARCHAR2(200) := 'test1'; -- sid for this databaes
v_handle NUMBER; -- job handle
v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
v_start_time DATE; -- start time for log file
v_logfile_name VARCHAR2(200); -- logfile name
v_dumpfile_name VARCHAR2(200); -- logfile name
v_default_dir VARCHAR(30) := 'DPDIR'; -- directory
v_line_no INTEGER := 0; -- debug line no
v_sqlcode NUMBER; -- sqlcode
v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
vc_job_mode CONSTANT VARCHAR2(200) := 'TABLE'; -- Job mode
--v_inpup_param3 VARCHAR2(2000);
--v_sysdate VARCHAR2(2000);
--v_some_var VARCHAR2 (2000);
var1 varchar2(1000) := 'where time >= to_date(''repl_val'', ''DD-MM-YYYY'')';
var2 varchar2(1000) := 'repl_val';
var3 varchar2(1000);

BEGIN
v_line_no := 100; -- debug line no

-- Ceate the log and dumpfile names
IF v_compatible = 'COMPATIBLE'
THEN
v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.log';
v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.dmp';
ELSE
v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.log';
v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.dmp';
END IF;

v_line_no := 150; -- debug line no

-- Open the job
BEGIN
v_handle :=
DBMS_DATAPUMP.open(operation => 'EXPORT',
job_mode => vc_job_mode,
job_name => 'EXPORT_' || vc_job_mode || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI'),
version => v_compatible);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
RAISE;
END;

v_line_no := 200; -- debug line no

-- Add a logfile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => v_logfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
v_line_no := 400; -- debug line no
-- Add a datafile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => 'dp' || '_%U_' || v_dumpfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
v_line_no := 500; -- debug line no


-- Filter for the schemma
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
v_line_no := 550; -- debug line no

--Filter for the table
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_name || '''');

v_line_no := 570; -- debug line no

select sysdate-365 into var3 from dual;
dbms_output.put_line('First variable value is: ' || var1);
dbms_output.put_line('Second variable value is: ' || var2);
dbms_output.put_line('Third variable value is: ' || var3);
var1 := replace(var1, var2, var3);
dbms_output.put_line('First variable value after perlacement is: ' || var1);


DBMS_DATAPUMP.data_filter(handle => v_handle, name => 'SUBQUERY', VALUE => var1);


--------------------------------------------
v_line_no := 600; -- debug line no

-- Get the start time
v_start_time := SYSDATE;

-- Add a start time to the log file
DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);

v_line_no := 700; -- debug line no
-- Start the job
DBMS_DATAPUMP.start_job(handle => v_handle);
DBMS_DATAPUMP.detach(handle => v_handle);
v_line_no := 800; -- debug line no
EXCEPTION
WHEN OTHERS
THEN

BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
RAISE;
END exp_tables_w_qfilter; -- Procedure exp_tables_w_qfilter

/

and we said...

You're close - just a little date formatting changes needed. Here's a tidied up version of your code, line 47 the key formatting part.


SQL>
SQL> create or replace
  2  PROCEDURE exp_tables_w_qfilter
  3  (
  4  p_schema_name IN VARCHAR2 := 'SCOTT',
  5  p_table_name IN VARCHAR2 := 'EMP'
  6  )
  7  IS
  8
  9    v_sid VARCHAR2(200) := 'NP12'; -- sid for this databaes
 10    v_handle NUMBER; -- job handle
 11    v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
 12    v_start_time DATE; -- start time for log file
 13    v_logfile_name VARCHAR2(200); -- logfile name
 14    v_dumpfile_name VARCHAR2(200); -- logfile name
 15    v_default_dir VARCHAR(30) := 'TEMP'; -- directory
 16    v_line_no INTEGER := 0; -- debug line no
 17    v_sqlcode NUMBER; -- sqlcode
 18    v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
 19    vc_job_mode CONSTANT VARCHAR2(200) := 'TABLE'; -- Job mode
 20    var1 varchar2(1000) := 'where hiredate >= to_date(''repl_val'', ''DD-MM-YYYY'')';
 21    var2 varchar2(1000) := 'repl_val';
 22    var3 varchar2(1000);
 23
 24  BEGIN
 25
 26    v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.log';
 27    v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.dmp';
 28
 29    v_handle := DBMS_DATAPUMP.open(operation => 'EXPORT',
 30    job_mode => vc_job_mode,
 31    job_name => 'EXPORT_' || vc_job_mode || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI'),
 32    version => v_compatible);
 33
 34    DBMS_DATAPUMP.add_file(handle => v_handle,
 35    filename => v_logfile_name,
 36    directory => v_default_dir,
 37    filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
 38
 39    DBMS_DATAPUMP.add_file(handle => v_handle,
 40    filename => 'dp' || '_%U_' || v_dumpfile_name,
 41    directory => v_default_dir,
 42    filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
 43
 44    DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
 45    DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_name || '''');
 46
 47    select to_char(date '1981-07-01','DD-MM-YYYY') into var3 from dual;
 48
 49    dbms_output.put_line('First variable value is: ' || var1);
 50    dbms_output.put_line('Second variable value is: ' || var2);
 51    dbms_output.put_line('Third variable value is: ' || var3);
 52    var1 := replace(var1, var2, var3);
 53    dbms_output.put_line('First variable value after perlacement is: ' || var1);
 54
 55    DBMS_DATAPUMP.data_filter(handle => v_handle, name => 'SUBQUERY', VALUE => var1);
 56
 57    v_start_time := SYSDATE;
 58    DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);
 59
 60    DBMS_DATAPUMP.start_job(handle => v_handle);
 61    DBMS_DATAPUMP.detach(handle => v_handle);
 62  EXCEPTION
 63  WHEN OTHERS
 64  THEN
 65    BEGIN
 66    DBMS_DATAPUMP.detach(handle => v_handle);
 67    EXCEPTION
 68    WHEN OTHERS
 69    THEN
 70    NULL;
 71    END;
 72
 73  END;
 74  /

Procedure created.

SQL>
SQL>
SQL> set serverout on
SQL> exec exp_tables_w_qfilter
First variable value is: where hiredate >= to_date('repl_val', 'DD-MM-YYYY')
Second variable value is: repl_val
Third variable value is: 01-07-1981
First variable value after perlacement is: where hiredate >= to_date('01-07-1981', 'DD-MM-YYYY')

PL/SQL procedure successfully completed.


;;; Job Start at 27-Jun-16 09:09:12
FLASHBACK automatically enabled to preserve database integrity.
Starting "MCDONAC"."EXPORT_TABLE_2016_0627_0909":  
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "SCOTT"."EMP"                               8.531 KB       8 rows
Master table "MCDONAC"."EXPORT_TABLE_2016_0627_0909" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.EXPORT_TABLE_2016_0627_0909 is:
  C:\TEMP\DP_01_EXPDP_NP12_01_2016_0627_0909.DMP
Job "MCDONAC"."EXPORT_TABLE_2016_0627_0909" successfully completed at Mon Jun 27 09:09:14 2016 elapsed 0 00:00:02




and you rated our response

  (1 rating)

Is this answer out of date? If it is, please let us know via a Review

Reviews

datapump export using DBMS_DATAPUMP

June 29, 2016 - 10:03 am UTC

Reviewer: Vadim Stezhka from Ukraine

Thank you very much.
Connor McDonald

Followup  

June 30, 2016 - 1:33 am UTC

Glad we could help

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.