Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic sql
Dynamic sql [message #387421] Thu, 19 February 2009 03:41 Go to next message
rajesh4851
Messages: 89
Registered: January 2007
Member
Hi,
im facing one problem.

I have one table called "temp". it has column "query". It contains sql statements.
I need to execute this sql statement and fetch the results into one table type variable.
Finally i have to export this data into single dimention array.
Note: i dont know how many columns in sql stament, but i know only one set of records it will retrieve.

Example:
I have one table variable like this:
TYPE varchar_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
tab_result varchar_table ;


i have query like 'select ename, job, manager from emp where emp_id = 1001;'
we got the result for this query as below:
ename job manager
----- ------- ---------
ADAMS CLERK DAVID

Now i have to add this data into tab_result variable which is of array type;
O/p should be like this:

tab_result
----------
ADAMS
CLERK
DAVID

Please help me to solve this problem.

Regards,
Rajesh
Re: Dynamic sql [message #387425 is a reply to message #387421] Thu, 19 February 2009 03:48 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Note: i dont know how many columns in sql stament, but i know only one set of records it will retrieve.

Then you have to use dbms_sql.
Search for print_table for an example.

Regards
Michel
Previous Topic: a question about explain plan
Next Topic: Sending email through pl/sql procedure
Goto Forum:
  


Current Time: Fri Dec 02 18:28:20 CST 2016

Total time taken to generate the page: 0.09071 seconds