Home » SQL & PL/SQL » SQL & PL/SQL » How to use the column names generated from Dynamic SQL (Oracle 10g)
How to use the column names generated from Dynamic SQL [message #576800] Fri, 08 February 2013 00:43 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58925
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:12
Michel 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
Re: How to use the column names generated from Dynamic SQL [message #576806 is a reply to message #576804] Fri, 08 February 2013 01:02 Go to previous messageGo to next message
kumar0828
Messages: 15
Registered: August 2012
Location: Mysore
Junior Member
Hi Michel,

To generate the print_table i need to have a static query.

But in the code which i have written the query generated dynamically.

So to get the print_table.
Re: How to use the column names generated from Dynamic SQL [message #576807 is a reply to message #576806] Fri, 08 February 2013 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58925
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
To generate the print_table i need to have a static query.


No.

Regards
Michel
Re: How to use the column names generated from Dynamic SQL [message #576819 is a reply to message #576807] Fri, 08 February 2013 03:04 Go to previous messageGo to next message
kumar0828
Messages: 15
Registered: August 2012
Location: Mysore
Junior Member
Hi Michel,

Can you please tell me how to generate Print table for Dynamic SQL.
Re: How to use the column names generated from Dynamic SQL [message #576831 is a reply to message #576819] Fri, 08 February 2013 05:59 Go to previous message
Michel Cadot
Messages: 58925
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Print table IS made for dynamic SQL.

Regards
Michel
Previous Topic: Rebuild the table Structure (merged)
Next Topic: SQL: How to cumulatively sum up values till a condition is satisfied?
Goto Forum:
  


Current Time: Thu Aug 28 06:16:30 CDT 2014

Total time taken to generate the page: 0.10355 seconds