This discussion is archived
1 Reply Latest reply: Jan 18, 2013 6:24 AM by kgronau RSS

-1747,ORA-01747: invalid user.table.column, table.column, or column specifi

978183 Newbie
Currently Being Moderated
Hi
I am using execute immediate to dynamically insert into one table based on the configuration table
Here is the scenario

I have configuration table tab_config and table tab_lbl which is something like

tab_lbl is

col1 col2 col3


and tab_config

columns_of_lbl, mandatory_field ,variables_of_lbl
col1 'M' v_col1
col2 'null' v_col2
col3 'M' v_col3

and i want to dynamically insert the records in the tab_lbl table using execute immediate and the result should be

tab_lbl

col1 col2 col3
aa null cc

to achieve this i am writing below code


create or replace procedure ()
is

cursor data is select columns_of_lbl,variables_of_lbl
and mandatory_field = 'M';


v_column_list varchar2(200);
v_variable_list varchar2(200)
v_insert_list varchar2(200);
v_column_str varchar2(200);
v_variable_str varchar2(200);
v_col1 varchar2(3) := 'aa';
v_col3 varchar2(3) := 'cc';


begin

for i in data loop
v_column_str := i.columns_of_lbl;
v_column_list := v_column_list||v_column_str||',';

v_variable_str := i.variables_of_lbl;
v_variable_list := v_variable_list||v_variable_str||',';

end loop;

v_insert_list := 'insert into tab_lbl('||v_column_list||')
values('||v_variable_list||')';

dbms_output.put_line(v_insert_list);
execute immediate v_insert_list;


end;


and i am getting below error

below is the outputed string via dbms_output
insert into tab_lbl(col1,col3)
values(v_col1,v_col3)
and the exception is
-1747,ORA-01747: invalid user.table.column, table.column, or column specification

I am struggling to catch this error, is that something which i am doing wrong with the logic?

...appreciate your response

thanks,
chig

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points