8 Replies Latest reply: Feb 10, 2013 11:38 PM by rp0428 RSS

    How to use the column names generated from Dynamic SQL

    kumar0828
      Hi,

      I have a problem with Dynamic SQL.

      I have written an SQL which will dynamically generate the Select statement with from and where clause in it.
      But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.

      For that i have used a ref cursor to open and insert the table.

      In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn

      Please find below the sample code:

      TYPE ref_csr IS REF CURSOR;

      insert_csr ref_csr;

      v_select VARCHAR2 (4000) := NULL;
      v_table VARCHAR2 (4000) := NULL;
      v_where VARCHAR2 (4000) := NULL;
      v_ins_tab VARCHAR2 (4000) := NULL;
      v_insert VARCHAR2 (4000) := NULL;
      v_ins_query VARCHAR2 (4000) := NULL;


      OPEN insert_csr FOR CASE
      WHEN v_where IS NOT NULL
      THEN 'SELECT '
      || v_select
      || ' FROM '
      || v_table
      || v_where
      || ';'
      ELSE 'SELECT ' || v_select || ' FROM ' || v_table || ';'
      END;

      LOOP
      v_ins_query :=
      'INSERT INTO '
      || v_ins_tab
      || '('
      || v_insert
      || ') VALUES ('
      || How to fetch the column names here
      || ');';

      EXECUTE IMMEDIATE v_ins_query;

      END LOOP;

      Please help me out with the above problem.

      Edited by: kumar0828 on Feb 7, 2013 10:40 PM

      Edited by: kumar0828 on Feb 7, 2013 10:42 PM