Re: variable column names in PL/SQL

Thanks for your suggestion. Forgive my ignorance (and laziness for not looking this up in the Utilitie manual before responding to you), but since my detail records need to contain a lot of the same data that's in my master_table, in your scenario can a 're-write' 'key_1', 'key_2', etc. into the detail records as well?

-Brendan

In article <01bd80e3$03deb360$450937a6_at_raghus-computer>,   "rok" <rok_at_MCI2000.com> wrote:
>
> Can you not use SQL*Loader itself to do the splitting? Still you have to
> code for each table separately.
>
> Ctl file will look like this:
>
> LOAD DATA
> REPLACE(or APPEND)
> INTO TABLE master_table
> ( key1 Position(1:10),
> key2 Position(11:15),
> data1 Position(16:20)
> )
> INTO TABLE detail_table_1
> WHEN (amt_1) != ' 0.00'
> (
> amt_1 Position(21:30),
> code_1 Position(31:35),
> rate_1 Position(36:42)
> )
> INTO TABLE detail_table_2
> WHEN (amt_2) != ' 0.00'
> (
> amt_2 Position(43:52),
> code_2 Position(53:57),
> rate_2 Position(58:64)
> )
>
> ...
>
> and so on. Once you have created the ctl file adding a new table should not
> be difficult. Simple copy and paste and make the minor changes. You get
> lots of flexibility using a ctl file. Hope it is helpful.
>
> raghuvir
>
> brendan_o'brien_at_wrightexpress.com wrote in article
> <6ji7tm$38j$1_at_nnrp1.dejanews.com>...
> > Hi all.
> >
> > I've loaded a temporary table from a flat file extracted from a
> mainframe.
> > I now have to write a procedure for creating a record in a master table
> > and 0-15 records in an FK'd detail table for each master record.
> >
> > There are 100 cols in the scratch table. The first ten are 'master'
> related
> > and get dumped straight into the 'master' table. The remaining 90 cols
> all
> > relate to 15 possible detail records with 6 cols per record. The naming
> > scheme of the cols are:
> >
> > amt_X
> > code_X
> > rate_X
> > etc...
> >
> > ...where 'X' is a number from 1 to 15. The variables I'm reading the
> > values into are all called 'x_amt_X', 'x_code_X', etc. With me so far?
> >
> > Good. Now I have a single cursor that grabs every col then scrubs the
> master
> > data and inserts the master record. I want to have a 'for i in 1..15'
> loop,
> > inside of which I build an insert for each of the 15 detail records. I
> know
> > that I can build an INSERT statement as a string that appends 'i' to the
> > variable names where appropriate, then pass that string to DBMS_SQL.PARSE
> as
> > its statement parameter. That's not the problem...
> >
> > The problem is that I'd like to *evaluate* the 'x_amt_X' column and
> > conditionally perform the dynamic SQL insert ONLY if the value > 0.
> >
> > While DBMS_SQL.PARSE will nicely accept the string I built using
> 'x_amt_'||i,
> > an IF statement obviously will not, i.e. the statement
> >
> > IF x_amt_||i > 0 then
> > INSERT ...
> > END IF;
> >
> > raises a compilation error.
> >
> > Obviously, I could avoid this whole thing by having 15 separate sets of
> > conditional inserts. Not only is that grotesque, but the number of
> 'detail'
> > records here could increase in the future. I'd LOOOOVE to code this into
> a
> > 'for' loop.
> >
> > This isn't the first time I've run into the problem of performing
> operations
> > on variable variable/column names (other than DML/DDL using
> DBMS_SQL.PARSE)
> > and have never found a good solution.
> >
> > Eternal indebtedness to whomever might lend a hand here (are you
> listening Tom
> > Kyte?). Email replies would be especially welcome.
> >
> > Thanks, and have a nice weekend,
> > -Brendan O'Brien
> > brendan_o'brien_at_wrightexpress.com
> >
> > -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> > http://www.dejanews.com/ Now offering spam-free web-based newsreading
> >
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Mon May 18 1998 - 07:27:28 CDT