I have a table:

create table D_DEFAULT
(
  table_name   VARCHAR2(200),
  column_name  VARCHAR2(200),
  column_value VARCHAR2(200)
);

In my project i have to take data from columns COLUMN_NAME and COLUMN_VALUE and insert it into another table.

Something like this:

select LISTAGG(COLUMN_NAME, ','), LISTAGG(COLUMN_VALUE,  ',')
  into v_columns, v_values
  from D_DEFAULT
  where TABLE_NAME = p_table || '_RTM';

v_sql := 'insert into table ' || p_table || ' values (' || v_values|| ') ';

CodePudding user response：

It is dynamic SQL you need (as you more or less figured out yourself).

Sample data and table:

SQL> select * From d_default;

TABLE_NAME      COLUMN_NAME     COLUMN_VALUE
--------------- --------------- ---------------
GCG_RTM         ID              -1
GCG_RTM         FILIAL_CODE     US

SQL> desc gcg_rtm
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 FILIAL_CODE                                        VARCHAR2(5)

Code:

SQL> declare
  2    l_tab  varchar2(30);
  3    l_cols varchar2(200);
  4    l_vals varchar2(200);
  5    l_str  varchar2(2000);
  6  begin
  7    select table_name,
  8           listagg(column_name, ',') within group (order by rowid),
  9           chr(39) || listagg(column_value, chr(39) ||','|| chr(39)) within group (order by rowid) ||chr(39)
 10    into l_tab, l_cols, l_vals
 11    from d_default
 12    where table_name = 'GCG_RTM'
 13    group by table_name;
 14
 15    l_str := 'insert into ' || l_tab || ' (' ||
 16              l_cols ||') values (' || l_vals || ')';
 17
 18    execute immediate l_str;
 19  end;
 20  /

PL/SQL procedure successfully completed.

Result:

SQL> select * from gcg_rtm;

        ID FILIA
---------- -----
        -1 US

SQL>

Problems you can expect: different datatypes. It is easy to insert strings or numbers (and rely on Oracle's implicit datatype conversion), but - what about e.g. dates? How to apply TO_DATE function? Which format model will you use? Maybe your D_DEFAULT table lacks in some more info - at least datatype and format_model.