Re: PL/SQL global variables

Hi,
[Quoted] 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 Thu Dec 10 1998 - 15:17:21 CET