Re: Tricky Dynamic SQL question

see below

Tim X <timx_at_spamto.devnul.com> wrote in message news:<87znqpl1h4.fsf_at_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

I figured out how to do it. I build a comma delimited VARCHAR2 variables from user_tables

run a cursor on this and append each entry with a ',' Select distinct column_name
into varchar2_variable
from user_Tables
where table_name = v_table_name;

execute immediate 'SELECT ' || varchar2_variable rest of it.

There is something called Method 4 Dynamic SQL, which is mentioned in the documentation and more extensive in the Fuerstein Package Book. ITs a pain to write. Its used for ad-hoc querying. This is simpler.

Thanks guys. Received on Mon Dec 30 2002 - 07:28:25 CST