Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Record In PL/SQL (9.2 10g)
icon11.gif  Dynamic Record In PL/SQL [message #408024] Sat, 13 June 2009 00:38 Go to next message
lyzmoh
Messages: 1
Registered: June 2009
Location: Chennai
Junior Member
Hello Every One,

My Problem is

I am uploading excel file data to oracle.
I don't know no of column in excel.
So i have created class like below
final int colcount = w_cols;
class rec
{
String[] flds = new String[colcount];
}
rec[] recs = new rec[w_rows];

i am creating a temp table in oracle. all (varchar2 100).

And Storing All Values In The Temp Table.

Now In PL/SQL

I Don't Know Table Name in Advance So I am Using Ref Cursor.

But How To Create A Record Of UnKnown Columns(Both No of Columns and Names Of Columns) to Fetch These Records So I Can Process.

Please Tell Me.

With Regards,
G.Lyzander Mohan
Re: Dynamic Record In PL/SQL [message #408025 is a reply to message #408024] Sat, 13 June 2009 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But How To Create A Record Of UnKnown Columns(Both No of Columns and Names Of Columns) to Fetch These Records So I Can Process.

You can't.

Regards
Michel
Re: Dynamic Record In PL/SQL [message #408061 is a reply to message #408025] Sat, 13 June 2009 09:03 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
True, you cannot do exactly what was described.

However, you can dynamically construct code and execute it. Here are some ideas you should think about.

Alternative #1

1) how about creating a table with 1000 columns (max columns for 10gr2), all of type varchar2(4000) and naming them with generic names like C0001, C0002, C0003, ...

2) the you can insert null values for those columns you do not have in your excel sheet.

Alternative #2 if you have more info.

1) create a metadata table that defines the layout of the possible tables you might want to use.

2) as before, create a generic table

3) use the metadata to drive construction of dynamic sql. Not real efficient but it works. Alternatively, use a sql generator to preconstruct a plsql package that handles the inserting details. This is way more efficient that dynamic sql, but requires you to regenerate the code when every you change you metadata.

Advantage is that new layouts can be used just by changing data.
Disadvantage is that data integrity must be managed by the application and programmers won't do it (For good reason).

Alternative #3

1) since you have the metadata, preconstruct all possible necessary tables and then just switch betweet them as needed.

Advantage is that the database can do basic integrity management.
Disadvantage is that you loose all the "data driven design" flexibility which is the major focus of these kinds of solutions.

Naturally many people will balk at ideas like these. This is because you have basically thrown away any data modeling and embedded all knowledge of the data inside your application, or in your own metadata tables. For example, how would you enforce a primary key on a generic table. If you are keeping your own metadata, do you keep key definitions as well.

It is a bad idea but in fact I do support an application that does exactly #2 above. It is a programmer's solution to the problem, not a data management solution do the problem. Developers love the solution. DBAs hate it. Among other things, everything is fine till it breaks, then it is a real mess trying to figure out how to fix it because metadata must be added to any query against the generic table. Queries get ugly real fast.

Good luck, Kevin
Re: Dynamic Record In PL/SQL [message #408091 is a reply to message #408024] Sat, 13 June 2009 15:18 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>But How To Create A Record Of UnKnown Columns(Both No of Columns and Names Of Columns) to Fetch These Records So I Can Process.
This can be solved with the degenerate & nonscalable solution of a simple 2 column name/value pair table.
Previous Topic: sql script
Next Topic: Procedure to update the close_day
Goto Forum:
  


Current Time: Thu Dec 08 17:58:01 CST 2016

Total time taken to generate the page: 0.09399 seconds