Re: Tricky Dynamic SQL question

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);

My first thought is that your project needs an architect more interested in producing something scalable and stable than using the latest bells-and-whistles. Were it on my plate I'd be seriously examining basic design assumptions.

Daniel Morgan Received on Tue Dec 24 2002 - 19:12:05 CST