Re: Tricky Dynamic SQL question

You could handle the logic on the client end (and it would be more scalable) by selecting the sub records based upon the parent records. (use bind variables) Or you could use pl/sql and return the cursor based upon the parent.(if x then return this cursor elsif y then ...) I am assuming there are only so many child tables.

This dynamic stuff can really kill your performance and scalability.

Jim

"Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message news:1efdad5b.0212260345.3a372e8b_at_posting.google.com...
> 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 - 10:26:57 CST