PL/SQL global variables
Date: Wed, 9 Dec 1998 13:27:05 -0500
Message-ID: <AqPg3tG00iUx07V5A0_at_andrew.cmu.edu>
[Quoted] 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 Wed Dec 09 1998 - 19:27:05 CET