Oracle query to gets all COLUMN_NAMES during run-time and then fetch value from each ROW w.r.t. each column

Question

I want an Oracle sql query, which on runtime gets all COLUMN_NAMES from a particular TABLE and then use those COLUMN_NAMES to fetch value from each ROW,considering that I don't know the number and name of COLUMNS within the TABLE.

TABLE1:

COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4

value11 value12 value13 value14

value21 value22 value23 value24
...

As a result of a sql query I am expecting an Insert statement corresponding to each row as follows:

INSERT INTO TABLE1 VALUES (value11,value12,value13,value14);

INSERT INTO TABLE1 VALUES (value21,value22,value23,value24);

...

My approach so far is as follows:

QUERY 1

SELECT 'INSERT INTO TABLE1 VALUES (' ||COLUMN_1 ||',' ||COLUMN_2 ||',' ||COLUMN_3 ||',' ||COLUMN_4 ||');' AS INSERTSCRIPT FROM TABLE1 ORDER BY COLUMN_1;

Using this I get the desired insert statments, but the problem is "I want to avoid specifying the column names manually for each TABLE (as there are more than hundreds of such TABLES)".

So I have been trying next with:

QUERY 2)

SELECT LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID) FROM USER_TAB_COLS WHERE TABLE_NAME = 'TABLE1';

which lists the columns as COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4 but the problem is that I am not able to use this result in the QUERY_1

Thanks in Advance!

Answer 1

I guess you need DML generator. Something like that:

declare
  v_table_name varchar2(30) := 'YOUR_TABLE';

  v_date_mask varchar2(64) := 'dd.mm.yyyy hh24:mi:ss';
  v_sql varchar2(4000); 

  cur int;
  n   int;
  v_cols dbms_sql.desc_tab;

  v_varchar2 varchar2(10);
  v_number number;
  v_date date;

  v_column_list varchar2(4000);
  v_values_list varchar2(4000);
  v_single_value varchar2(4000);
begin
  v_sql := 'select * from ' || v_table_name;

  cur := dbms_sql.open_cursor;
  dbms_sql.parse(cur, v_sql, dbms_sql.native);

  dbms_sql.describe_columns(c => cur, col_cnt => n, desc_t => v_cols);

  for i in v_cols.first..v_cols.last
    loop
      case
        when v_cols(i).col_type = dbms_sql.Varchar2_Type
        then
          dbms_sql.define_column_char(c => cur, position => i, column => v_varchar2 , column_size => v_cols(i).col_max_len);
        when v_cols(i).col_type = dbms_sql.Number_Type
        then
          dbms_sql.define_column(c => cur, position => i, column => v_number);
        when v_cols(i).col_type = dbms_sql.Date_Type
        then
          dbms_sql.define_column(c => cur, position => i, column => v_date);
      end case;

      v_column_list := v_column_list || ',' || v_cols(i).col_name;   
    end loop;
  v_column_list := ltrim(v_column_list,',');    

  n := dbms_sql.execute(cur);

  n := dbms_sql.fetch_rows(cur);

  while (n > 0)
    loop
      for i in v_cols.first..v_cols.last
        loop
          case
            when v_cols(i).col_type = dbms_sql.Varchar2_Type
            then
              dbms_sql.column_value_char(c => cur, position => i, value => v_varchar2);
              v_single_value := case
                                 when v_varchar2 is not null
                                 then '''' || trim(v_varchar2) || ''''
                                 else 'NULL'
                                end;
            when v_cols(i).col_type = dbms_sql.Number_Type
            then
              dbms_sql.column_value(c => cur, position => i, value => v_number);
              v_single_value := nvl(to_char(v_number), 'NULL');
            when v_cols(i).col_type = dbms_sql.Date_Type
            then
              dbms_sql.column_value(c => cur, position => i, value => v_date);
              v_single_value := case
                                 when v_date is not null
                                 then 'to_date(''' || to_char(v_date, v_date_mask) || ''',''' || v_date_mask || ''')'
                                 else 'NULL'
                                end;
          end case;
          v_values_list := v_values_list || ',' || v_single_value;    
        end loop;
      v_values_list := ltrim(v_values_list,',');

      dbms_output.put_line ('INSERT INTO ' || v_table_name || '(' || v_column_list || ')');
      dbms_output.put_line ('  VALUES (' || v_values_list || ');' || chr(10));

      v_values_list := null;

      n := dbms_sql.fetch_rows(cur);
    end loop;

  dbms_sql.close_cursor(cur);
  exception
    when others then
      dbms_sql.close_cursor(cur);
      raise;
end;

Answer 2

  select 'select ' || LISTAGG(column_name , ',') within group (order by column_id) || ' from my_table' 
  from user_tab_columns 
  where table_name = 'MY_TABLE';

Such query will produce for you query to select all columns from MY_TABLE.

In PL/SQL you can do:

declare
querysql varchar2;
  select 'insert into my_table select ' || LISTAGG(column_name , ',') within group (order by column_id) || ' from my_table' into querysql 
    from user_tab_columns 
    where table_name = 'REFERENCE1';
  execute immediate querysql;
end;