Re: PL/SQL global variables

Dennis sent me the suggestion that I add "rows=1" to the sqlldr command line and that worked just fine. It forced the records to be processed one at a time, which fired the triggers correctly. I suspect it will degrade performance a bit, but perhaps not enough to matter.

Excerpts from netnews.comp.databases.oracle.tools: 10-Dec-98 Re: PL/SQL global variables by "P. Larsen"_at_ballston.usc
> Hi,
> Your misconception is that you think SQL*Loader process data in sequence. It
> doesn't. Oracle processes the datafile in a blockbased fashion - not
> record/line based.
> If you have a load to multible tables and seperate them with LOAD INTO ...
> WHEN clauses you can see examples of Oracle processing the second WHEN
> before the FIRST, even though the first record in the file you're loading
> comes first.
>
> Having had this problem myself, we had to leave the SQLLoader because we
> weren't allowed to use temporary tables (yeah, what do you give). A solution
> would be, create a de-normalized temporary table, load all records into this
> table, and use a stored procedure to split the data into master/detal
> records.
>
> Regards
> Peter Larsen
> Senior Oracle Consultant
>
> Joyce A Moore wrote in message ...
> >I'm new to PL/SQL and have inherited some code from a former colleague.
> >It wasn't finished, but we thought this part worked. Overall, we're
> >using sqlldr to load a file of records into one of two tables, one for
> >the header and one for the rest. A pre-insert trigger on the header
> >table stashes some data in PL/SQL global variables, one of them from a
> >sequence. A pre-insert trigger on the transaction table then grabs
> >another value from the same sequence and uses that, plus the two values
> >in the global variables to fill some columns.
> >
> >What I'm seeing is that the global values don't seem to be available
> >until after sqlldr has inserted the first 6 transactions. What's even
> >more interesting is that the sequence value of the header (1st record in
> >the file) is the one following that obtained by the 6th transaction
> >record. I'll try to make a picture of this.
> >
> >file contents:
> >
> >header FILE_ID (FILE_ID gets put into global variable and a seq # taken)
> >trans 1 (G_FILE_ID added and seq # taken)
> >trans 2 "
> >trans 3 "
> >trans 4 "
> >trans 5 "
> >trans 6 "
> >trans 7 "
> >trans 8 "
> >
> >header tuple:
> >
> >file_id header_sequence_val
> >FILE_ID 7
> >
> >transaction tuples:
> >
> >file_id header_sequence_val tr_sequence_val
> >null null 1
> >null null 2
> >null null 3
> >null null 4
> >null null 5
> >null null 6
> >FILE_ID 7 8
> >FILE_ID 7 9
> >
> >I have a work-around, but I'd like to understand what's going on here as
> >this is only the first Oracle project.
> >
> >I know there could be something else wierd going on here, but I'm too
> >much of a newbie to know why. If I swap the tables' "into when"
> >sections in the control file, it doesn't work. If the transaction table
> >section is first, all works fine. If I put the header table section
> >first, it doesn't.
> >
> >Joyce Moore
> >
Received on Fri Dec 11 1998 - 15:33:23 CET