Dynamic SQL

[megatron33]

I'm actually trying to run a SQL script attached that will call the invalid date function and store any invalid dates in a variable called err_value. I can than insert err_value into an error table. When I try to do this using a dynamic SQL err_value contains the column name instead of the value.

source table trash table

date_table error_table
companyid_pk varchar2(10), col_pk varchar2(10),
empno_ky varchar2(10), col1_ky varchar2(10),
date_started varchar2(8) col2_ky varchar2(10),
col3_ky varchar2(10),
col4_ky varchar2(10),
col5_ky varchar2(10),
col_name varchar2(10),
err_value varchar2(10),
table_name varchar2(10)

Any help or advice would be greatly appreciated.


/************************************
* Declaration of cursors and variable
*************************************/
declare
source_table varchar2(30) := upper('&1') ;
trash_table varchar2(30) := upper('&2');
cursor c1(tabl varchar2) is select column_name from user_tab_columns
where table_name = tabl and substr(column_name,-3,3) in('_PK','_KY','KMC');

cursor c2(tabl varchar2) is select column_name from user_tab_columns
where table_name = tabl and substr(column_name,-3,3) in('_DC','DMC');
prim_key varchar2(30);
cid integer;
cid1 integer;
exec_str integer;
pri_str varchar2(100);
pri_str1 varchar2(100);
man_str varchar2(100);
err_value_str varchar2(400);
str varchar2(400);
trash_str varchar2(100);
cnt number(1) := 0;
err_value varchar2(20):= null;
begin

/*****************************************************
* To find the primary key,composite keys in the table
******************************************************/
for rec1 in c1(source_table)
loop
cnt := c1%rowcount ;
pri_str := pri_str||rec1.column_name||',';
end loop;

pri_str := substr(pri_str,1,length(pri_str)-1);

for i in 2..cnt
loop
trash_str :=trash_str||'col'||i||'_ky'||',';
end loop;
trash_str := 'col1_pk,'||trash_str||'col_name,err_value,table_name';
cnt := cnt + 1;

/***********************************************
* To find if a date has an invalid format
* and insert the bad record into a trash table.
***********************************************/

for rec2 in c2(source_table)
loop
man_str := ''''||rec2.column_name||'''';
cid := dbms_sql.open_cursor;
/*err_value_str := ' select '||pri_str||','||man_str||' from '||source_table||' where invalid_date('||man_str||') = 0';
dbms_sql.parse(cid,err_value_str,dbms_sql.v7);
dbms_sql.define_column(cid,cnt,err_value,20);
exec_str := dbms_sql.execute(cid);
loop
if dbms_sql.fetch_rows(cid) = 0 then
exit;
else
dbms_sql.column_value(cid,cnt,err_value);
end if;
end loop;*/
cid := dbms_sql.open_cursor;
str := 'insert into '||trash_table||' ('||trash_str||')'||
' select ' ||pri_str||','||man_str||','||''''||err_value||''''||','||''''||'&1'||''''||' from '||source_table||' where invalid_date('||man_str||') = 0';
dbms_sql.parse(cid,str,dbms_sql.v7);
exec_str := dbms_sql.execute(cid);
end loop;
dbms_sql.close_cursor(cid);
end;
/