Re: variable column names in PL/SQL

Yes you can. Simply repeat the key fields in the detail as well. Like,

INTO TABLE detail_table_1
WHEN (amt_1) != ' 0.00'
(

	key1 Position(1:10),
	key2 Position(11:15),
	amt_1 Position(21:30),
	code_1 Position(31:35),
	rate_1 Position(36:42)

raghuvir

brendan_o'brien_at_wrightexpress.com wrote in article <6jp9fg$u07$1_at_nnrp1.dejanews.com>...
> 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 - 10:45:06 CDT