This discussion is archived
8 Replies Latest reply: Feb 10, 2013 9:38 PM by rp0428 RSS

How to use the column names generated from Dynamic SQL

953658 Newbie
Currently Being Moderated
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

Legend

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