Dynamic substitution of table and column names into SQL

[nagornyi]

I have a table SQL_INFO that keeps information on which columns should be selected from which tables in my SQL code:

ID     TABLE_NAME    COLUMN_NAME
==     ==========    ===========
125    ORDERS        CUSTOMER
131    PEOPLE        LAST_NAME
143    .......

I need a SQL that would take ID as parameter and adjust itself according to the SQL_INFO table.
I.e., if ID=125
I need the SQL to bring the results according to the query:
SELECT CUSTOMER FROM ORDERS, and fo on.

Thanks.

 

[LKBrwnDBA]

Look into "EXECUTE IMMEDIATE" statement.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Nagornyi,

The capability to create "on-the-fly", Dynamic SQL is a feature of PL/SQL, which is also the infrastructure for processing user-defined, stored procedures that would process your Dynamic SQL.

So that we can produce a working proof-of-concept example for you, could you please confirm a piece of sample/model code that would invoke (i.e., actually call and thus create) your dynamically generated statement, "SELECT CUSTOMER FROM ORDERS"?...Do you want the resulting CUSTOMER-column tuples to simply display on your client screen?...Do you want them written to a server-side flat file?...et cetera?

What is the application environment like in which all of this will function? (Your answer may dictate the implementation.)

So, how do you envision/want this all to take place?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[nagornyi]

EXECUTE IMMEDIATE works great for inserts and deletes, but how to use it for SELECT?
As for the environment...let's think of a report of parent-children type. I select parent level information, one piece of which is ID. Now I need to use that ID for the SQL_INFO table and bring a list of children, that can be either a list of customers from ORDERS table, or list of last names from the PEOPLE table, or ... So, I need it on the fly.
From the report prospective it looks like data model with two queries, the parent one is a plain SQL, the child query should be a ref cursor one. But what's inside the ref cursor procedure? Or without the ref cursor? That's where I am struggling..

 

[nagornyi]

For those interested, hese is how I solved the problem. I created procedure that runs before the report every time it is getting called. The procedure populates a temp taht I created, CHILD_DATA, by going in loop throug the SQL_INFO entries and constructing insert SQL for each line, and executing them.