Re: Tricky Dynamic SQL question

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:<l5GO9.315712$pN3.23359_at_sccrnsc03>...
> 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?

Ok let me explain it a little clearer. I have the Fuerstein Built-In packages book in front of me and I think method 4 dynamic sql is how to go.

OK here is the model. Sorry about my explanation before, supertype, subtype are more object oriented concepts.

I have a table. The table has a column called TABLE_NAME. This tells me which table to use. So I search on the primary key and then I know look at the TABLE_NAME column.

I then go and do my inserts, updates, and deletes in that specified table. Now, we have 7 of these tables that contain information about the 'sub' tables. Essentially we use them as pointers to these tables. Sort of an index for us similiar to how Oracle uses the Data Dictionary to do its processing.

Now our 'index' tables(those with the TABLE_NAME column) may point to any of 20-30 subtype tables. Each of these tables have different columns. Plus new tables may be added or deleted.

It really is necessary given our requirements. I think I can do this using DBMS_SQL.DEFINE_COLUMN. Havent been able to work on it until now. I was checking to see if anyone has done it.

Sorry about the confusion. Its rather difficult to explain. Received on Fri Dec 27 2002 - 06:29:57 CST