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

/

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