Re: Tricky Dynamic SQL question

rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.0212241204.7c81a20b_at_posting.google.com>...
> 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);

Sorry its hard to explain. We have a table that stores a pointer to other suptype tables. So my supertype table is sort of a master table that tells me when I need to use certain other tables. Sort of a type hierarchy.

No my subtype tables are not identical. They have different column names and different numbers of columns.

So I select a row in the supertype table. It tells me to use table 'A' and another row tells me to use table 'B'. I need to make this dynamic because the subtype tables may change, plus we may add or subtract them.

I think I can do this with dbms_sql and dynamically create columns with define_columns. Not sure how. Has anyone done this? Received on Thu Dec 26 2002 - 05:45:07 CST