1 Reply Latest reply: Jan 18, 2013 8:24 AM by Kgronau-Oracle RSS

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

    978183
      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
        • 1. Re: -1747,ORA-01747: invalid user.table.column, table.column, or column specifi
          Kgronau-Oracle
          Is the table located in the Oracle database or are you going to insert into a remote foreign database using a gateway?

          If you're using the execute immediate statement to insert into an Oracle database table, please close this thread and post it again in the OL/SQL and SQL forum: PL/SQL

          If you're using a gateway, what's the gateway you're using and which foreign database are you connecting to. Also please provide the table description as it is defined in the foreign database.

          - Klaus