Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Tricky Dynamic SQL question

Re: Tricky Dynamic SQL question

From: Tim X <timx_at_spamto.devnul.com>
Date: 29 Dec 2002 18:37:43 +1100
Message-ID: <87znqpl1h4.fsf@tiger.rapttech.com.au>


rgaffuri_at_cox.net (Ryan Gaffuri) writes:

> 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?

I'm stumped as to how you will know what data your retrieving if your sub-tables have different numbers of columns and different column names - there is no easy way (or even doable way I can think of) where you can have some sort of plsql record here because you have to define your record at compile time and at that time, you have no idea of how many columns or possibly even the type of the columns.

If your data was all of the same type, you could possibly just use a simple collection type e.g. table of varchar2 or table of number etc. You could even convert all the data to the same type (e.g. varchar2) and define the table as a new type which is returned by your function - but I still see problems with actually getting the data into the table if you don't know how many columns are being returned. You would have to have something somewhere which indicates the number of columns which will be returned (maybe in the master table?).

It all sounds very messy and poorly conceived to me - but I guess thats RAD for you.

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!
Received on Sun Dec 29 2002 - 01:37:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US