Re: variable column names in PL/SQL

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
>
Received on Sat May 16 1998 - 10:53:43 CDT