RE: SQL Loader Question on 8.1.7

  • From: Kevin Lange <klange@xxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 16 Jun 2004 17:16:47 -0500

You are right.  The dependent data (in this case Employee Data) always
follows the Parent Data (in this case the Department Data).  Thats why I was
wondering if a variable could be set inside of SQLLDR to hold the data for
the "Department Number" from the Parent Data so it could be used in the
Dependent Data.
Of couse a procedure could be written to do this just fine.  But,  all of
the facilities and dependent code already exists to make these loads using
SQLLDR.  We wanted to do the least work possible i.e. only change a SQLLDR
control file intead of changing all the scripts that run this.

-----Original Message-----
From: Paglia, Melissa [mailto:mpaglia@xxxxxxxxxxx]
Sent: Wednesday, June 16, 2004 5:03 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: SQL Loader Question on 8.1.7

Hi Kevin- 

From the sample data you provided, it looks like the only way to know which
department the employee is from is that the department precedes the employee
record.  Not an easy relation.  

If that is the case, I would import the data to a temp table. 
  (id varchar2(1), 
   Generic_num  varchar2(20), 
   Generic_Name  varchar2(20)); 

  Then write a stored procedure to do the insert statements to your real

Declare a cursor of 
  Cursor cur is 
    SELECT * from temp; 
And some variables to store your data: 

V_dept_no  varchar2(50); 

Then, in your procedure body: 

FOR cur_rec IN cur LOOP 
    IF = '1' THEN 
       /*new department*/ 
      V_dept_no:= cur_rec.generic_num; 
      INSERT INTO dept (deptno, deptname) 
      VALUES (cur_rec.generic_num, cur_rec.generic_name); 

    ELSIF'2' THEN 
       /*employee record*/ 
      INSERT INTO emp ( deptno, empno, empname) 
      VALUES (V_dept_no,  cur_rec.generic_num, cur_rec.generic_name); 
     END IF; 

Please note: 
This assumes that your first record is a department record and that the way
the data retrieved from the cursor is in the same order that it was in the
datafile used for the import.

Melissa Paglia 

-----Original Message----- 
From: oracle-l-bounce@xxxxxxxxxxxxx [ mailto:oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx> ] On Behalf Of Kevin Lange 
Sent: Wednesday, June 16, 2004 4:25 PM 
To: 'oracle-l@xxxxxxxxxxxxx' 
Subject: SQL Loader Question on 8.1.7 

  I figured if anyone would know ... you all would.   Here is a simple 
example given in the Oracle SQLLDR manual: 

  You have conditional data in a singl file to be loaded into different 
    1  50  Manufacturing 
    2  1100  Smith  
    2  1200 Snyder  
    1  60  Shipping 
    2  1121  Stevens  

  Based on the Record ID, you want either the Department or the Employee 
table to be loaded: 

    Into Table dept 
      When recid = 1 
      (recid Position(1:1) Integer External, 
       deptno Position(3:4) Integer External, 
       depname Position(8:21) Char) 
    Into Table emp 
      When recid = 2 
      (recid Position(1:1) Integer External, 
       empno Position(3:6) Integer External, 
       empname Position(8:17) Char 
       commission Position(19:25)) 

That is fairly simple. 

It gets more difficult when you ask the question  ....  Can you carry the 
data from one of the When sections over into another section ??  i.e. Is 
there a way to make a presistant variable that is set in one section and 
then used in another section ?? 
Using the sample above, we need to have the "Department Number" (depno) 
inserted into the EMP table without having to add it to each individual 
record where recid = 2 since it is already on the record where recid = 1. 

Any help or direction to go for samples if possible would be greatly 

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx 
put 'unsubscribe' in the subject line. 
Archives are at
FAQ is at

"Bringing people together to advance their lives." 

Other related posts: