variable column names in PL/SQL

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 Fri May 15 1998 - 00:00:00 CDT