Всем привет. Вот такой вот код, в котором нужно выполнить третий пункт (он закомментирован). Что то с replace ни как. В программирование начинающий. Прошу помочь. Заранее всем благодарен.


create PROCEDURE exp_tables_w_qfilter_1
(
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
/*
Purpose: Export tables

MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/15/2012 Initial Build
*/
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(1000);
v_sysdate VARCHAR2(2000);
v_param1 VARCHAR2 (2000);

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

--------------------------------------------

-- Add a subquery
-- 1. объявить переменную и присвоить ей значение 3-го входного параметра p_table_filter
v_inpup_param3 := p_table_filter;
dbms_output.put_line(v_inpup_param3);



-- 2. объявить переменную v_sysdate и присвоить ей значение даты sysdate-365
-- insert into ... select ...
SELECT TO_CHAR(SYSDATE-365, 'DD.MM.YYYY HH24:MI:SS') INTO v_sysdate FROM DUAL;
dbms_output.put_line(v_sysdate);

-- 3. объявить переменную и присвоить ей значение переменной v_inpup_param3 с замененным словом 'time_templ' на значение переменной v_sysdate
-- replace

v_param1 := v_inpup_param3;

--dbms_output.put_line(v_param1);




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


--------------------------------------------
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_1; -- Procedure exp_tables_w_qfilter

/

Привет всем, помогите передать верно значение в эту строку DBMS_DATAPUMP.data_filter(handle => v_handle, name => 'SUBQUERY', VALUE => ---p_table_filter входящий параметр); У меня не срабатывает фильтр данных, выгружает всю таблицу, а нужно от текущей даты минус 1 год.

Запускаю процедуру execute exp_tables_w_qfilter ('LTP' схема, 'AUDITTABLE_TEST' таблица, 'where TIME=>что то там');
Заранее всем спасибо.

обновленный код:


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

--------------------------------------------
/*
-- Add a subquery
-- 1. объявить переменную и присвоить ей значение 3-го входного параметра p_table_filter
--v_inpup_param3
v_inpup_param3 := p_table_filter;
dbms_output.put_line(v_inpup_param3);

-- 2. объявить переменную v_sysdate и присвоить ей значение даты sysdate-365
-- insert into ... select ...
SELECT TO_CHAR(SYSDATE-365, 'DD.MM.YYYY HH24:MI:SS') INTO v_sysdate FROM DUAL;
dbms_output.put_line(v_sysdate);

-- 3. объявить переменную и присвоить ей значение переменной v_inpup_param3 с замененным словом 'time_templ' на значение переменной v_sysdate
-- replace

v_some_var := replace(v_inpup_param3, 'time_templ', TO_CHAR(v_sysdate, 'DD.MM.YYYY HH24:MI:SS'));

dbms_output.put_line(v_some_var);
*/
---------------------------------------------

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 => ---p_table_filter);


--------------------------------------------
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

/