| How to use the column names generated from Dynamic SQL [message #576800] |
Fri, 08 February 2013 00:43  |
 |
kumar0828
Messages: 15
Registered: August 2012
Location: Mysore
|
Junior Member |
|
|
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.
|
|
|
|
| Re: How to use the column names generated from Dynamic SQL [message #576804 is a reply to message #576800] |
Fri, 08 February 2013 00:57  |
 |
Michel Cadot
Messages: 54225
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
This is very bad, search for SQL Injection on the web.
Have a loop at T.Kyte's print_table function.
From your previous topic:
Michel Cadot wrote on Mon, 24 September 2012 15:12Michel Cadot wrote on Mon, 24 September 2012 14:12...Use SQL*Plus and copy and paste your session, the WHOLE session.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.Also always post your Oracle version, with 4 decimals.
...
Regards
Michel
|
|
|
|
|
|
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
