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

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

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

You can emulate "dynamic record" using PLSQL table as follows -

type dynamic_field is record( fieldname varchar2(30), fieldtype
varchar2(30), numeric_value number, datetime_value date, varchar_value
varchar2(4000));
type dynamic_record is table of dynamic_field index by binary_integer;

You can populate "dynamic records" using dbms_sql.

Or, you can extract your records into xml and then parse it into dom.


What exactly is required anyway? You said that you need to dynamically
select all columns and put them into a record. What for?

I am not sure I understand your requirement in the part where you
say that you need to put it into a record. Suppose you got it there,
what are you going to do with it later?

If you want to handle dynamic amount of columns in arbitrary tables,
then I do not think you will be able to get it out of EXECUTE
IMMEDIATE statement. The statement that you put there will
simply not work because INTO clause is a PLSQL construct.
However, you can use plain old DBMS_SQL package and use
define()/bind()/column_value() functions to get the data out, but
you still will have a problem where to store it.

The anchoring of the type in the way you put it will also not work
because this is resolved at compile time.

Hth


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

rgaffuri@cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.0212241204.7c81a20b@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?

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@cox.net> wrote in message
news:1efdad5b.0212260345.3a372e8b@posting.google.c om...
> rgaffuri@cox.net (Ryan Gaffuri) wrote in message
news:<1efdad5b.0212241204.7c81a20b@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?

Why dont you try weakely typed ref cursors.

for eg

declare

c sys_refcursor;

begin

-- Get the table name
v_table_name := fn_get_the_table_name();

open c for ' select * from ' || v_table_name;

end;

-- rajXesh

--
Posted via http://dbforums.com

I think I can do this with DBMS_SQL, Id really rather avoid using C.
Id have to get that approved.

I thought I read on asktom, that pl/sql automatically takes pl/sql
variables and makes them bind variables so I really dont have to use
the 'using' command?

Sybrand Bakker <gooiditweg@sybrandb.demon.nl> wrote in message news:<v4sl0v05n74trk1g1urrtcfjlro3olne0s@4ax.com>. ..
> On 26 Dec 2002 03:45:07 -0800, rgaffuri@cox.net (Ryan Gaffuri) wrote:
>
> >I think I can do this with dbms_sql and dynamically create columns
> >with define_columns. Not sure how. Has anyone done this?
>
>
> The only thing that would probably help you is using pro*c, dynamic
> sql method 4 (from the top of my head), where you can call pro*c to
> describe a table and set up bind lists.
> Never done this myself though.
> Anyway the code snippet you provided should read execute immediate
> .... using, or your code will be completely unscalable.
>
> Hth
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

rajXesh <member@dbforums.com> wrote in message news:<2318730.1040915829@dbforums.com>...
> Why dont you try weakely typed ref cursors.
>
> for eg
>
> declare
>
> c sys_refcursor;
>
> begin
>
> -- Get the table name
> v_table_name := fn_get_the_table_name();
>
> open c for ' select * from ' || v_table_name;
>
> end;
>
> -- rajXesh

I cant anchor my record dynamically.

"Jim Kennedy" <kennedy-down_with_spammers@attbi.com> wrote in message news:<l5GO9.315712$pN3.23359@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@cox.net> wrote in message
> news:1efdad5b.0212260345.3a372e8b@posting.google.c om...
> > rgaffuri@cox.net (Ryan Gaffuri) wrote in message
> news:<1efdad5b.0212241204.7c81a20b@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.