RE: SQL Loader Question on 8.1.7

From: Paglia, Melissa <mpaglia_at_fastweb.com>
Date: Wed, 16 Jun 2004 18:02:33 -0400
Message-ID: <27B03C48A56CD044994059453A2FBBFB03FBFD92@naexcbe3.na.global.ad>

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. CREATE TABLE temp
  (id varchar2(1),
   Generic_num varchar2(20),
   Generic_Name varchar2(20));

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

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 cur_rec.id = '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 cur_rec.id='2' THEN
       /*employee record*/
      INSERT INTO emp ( deptno, empno, empname)
      VALUES (V_dept_no,  cur_rec.generic_num, cur_rec.generic_name);
     END IF; 

END LOOP;
COMMIT; 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.

HTH,
Melissa Paglia

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kevin Lange
Sent: Wednesday, June 16, 2004 4:25 PM
To: 'oracle-l_at_freelists.org'
Subject: SQL Loader Question on 8.1.7

Evening;
  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 tables:   

    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 appreciated.

Kevin

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


"Bringing people together to advance their lives." 


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jun 16 2004 - 17:00:40 CDT