Re: Tricky Dynamic SQL question

I am not sure I understand your requirement in the part where you say that you need to put it into a record. Suppose you got it there, what are you going to do with it later?

If you want to handle dynamic amount of columns in arbitrary tables, then I do not think you will be able to get it out of EXECUTE IMMEDIATE statement. The statement that you put there will simply not work because INTO clause is a PLSQL construct. However, you can use plain old DBMS_SQL package and use define()/bind()/column_value() functions to get the data out, but you still will have a problem where to store it.

The anchoring of the type in the way you put it will also not work because this is resolved at compile time.

Hth

Ryan Gaffuri wrote:

> Im new to this project and its in rapid development so there is little
> or no documentation so please bare with me.
>
> We have supertype tables that keep logs of a variety of different
> tables. I have a function. So a record in a specific supertype table
> will tell you when a specific subtype table is being used.
>
> I have a function(that I didnt write) that I call earlier in the
> procedure, that tells me which table I need to use.
>
> I have a requirement where I need to dynamically select all columns in
> this table and put it into a record.
>
> Problem is how to define my record? I cant anchor it to the variable
> that contains tablename? My record will need to be dynamic because the
> columns in
> one table are different than in others.
>
> Also, I thought of using USER_TAB_COLUMNS and passing them into
> variables, but there are not always the same number of columns in each
> table.
>
> rec_tableName v_TableName%ROWTYPE;
> The above anchoring does not work.
>
> Any ideas how to do this? Im stumped.
>
> EXECUTE IMMEDIATE
> ' SELECT * ' ||
> ' INTO ' || v_Table ' ||
> ' FROM ' || v_tableName ||
> ' WHERE PRIM_ID = ' || rec_record.prim_id ||
> ' AND PRIM_SEQ = ' || rec_record.prim_seq);
Received on Wed Dec 25 2002 - 01:57:55 CST