When you build your query, instead of letting it return X_AMT_10 as a
column called X_AMT_10, give it a column alias of X_AMT. That way the
following PL/SQL code can always reference the same column name, but
actually be looking at different columns in the scratch table based on
how the query was written.
brendan_o'brien_at_wrightexpress.com wrote:
>
> 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 - 19:47:09 CDT
