CODEWITHSUNDEEP
sql-loader
Doe
Doe

Reputation: 33

Generate different sequence number for each file

I am using SQL-loader to load .csv files into my oracle table. i have multiple files with same columns. Each file is having around 500 odd records. I have 1 extra column "COLUMN5" in table which is not in .csv file. I need to update COLUMN5 with 1 unique number for each .csv file.

Like 1st csv file is having 500 records, so for all 500 records it should be 1 Unique number. 2nd csv file having 300 records, so for all these 300 records, next unique number. Is there any way to achieve this. I have done below.

I am using Number.Nextval sequence but it is putting one up number for each record.

Below is my control file.

LOAD DATA

INFILE 'sample.csv'
INFILE 'sample2.csv'

APPEND INTO TABLE TABLE1 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5 "NUMBER.NEXTVAL" )

Upvotes: 0

Views: 1176

Answers (1)

Gary_W
Gary_W

Reputation: 10360

I had a similar requirement where I needed to add a load_date and sequence ID to each row for each file that was loaded to a table. That way it was easy to query by date, or if a certain file needed to be "backed out", we could delete where the load sequence id matched the file in question. First I created a sequence called X_LOAD_SEQ to hold the current value between sessions. Then I created a package with a load_date and load_seq_id variable, along with functions to return them. In the package body, code runs upon instantiation that sets the values. I added a LOAD_DATE and LOAD_SEQ_ID column to each table, created the package, and added these lines to the end of each control file (note the table cannot already contain columns with these names):

,LOAD_DATE    date "MM/DD/YYYY" "to_char(trunc(schema.load_seq.get_load_date), 'mm/dd/yyyy')"
,LOAD_SEQ_ID  decimal external  "schema.load_seq.get_load_seq_id"

The package:

CREATE OR REPLACE PACKAGE SCHEMA.LOAD_SEQ AS
    /******************************************************************************
       NAME:       LOAD_SEQ
       PURPOSE:    Sets unique load_date and Load_seq_id per session when
                   the package is instantiated.  Package functions are
                   intended to be called from control files so all rows in a
                   file load will have the same load_date and load_seq_id.

                   When the functions are called, the package is instantiated and
                   the code at the bottom is run once for the session, setting the
                   load_date and load_seq_id.  The functions simply return the values
                   which will remain the same for that session.

       EXAMPLE:    ,LOAD_SEQ_ID  DECIMAL EXTERNAL "load_seq.get_load_seq_id"
                   (each row then has the same load_seq_id).

       REVISIONS:
       Ver        Date        Author           Description
       ---------  ----------  ---------------  ------------------------------------
       1.0        2/20/2017   Gary_W           1. Created this package.
    ******************************************************************************/
    NEXT_LOAD_SEQ_ID   NUMBER;
    NEXT_LOAD_DATE     DATE;

    FUNCTION GET_LOAD_SEQ_ID RETURN NUMBER;
    FUNCTION GET_LOAD_DATE   RETURN DATE;

END LOAD_SEQ;
/

CREATE OR REPLACE PACKAGE BODY SCHEMA.LOAD_SEQ AS
    FUNCTION GET_LOAD_SEQ_ID RETURN NUMBER IS
    BEGIN
        RETURN LOAD_SEQ.NEXT_LOAD_SEQ_ID;
    END GET_LOAD_SEQ_ID;

    FUNCTION GET_LOAD_DATE RETURN DATE IS
    BEGIN
        RETURN LOAD_SEQ.NEXT_LOAD_DATE;
    END GET_LOAD_DATE;

BEGIN
    -- This code is run once, when the package is first called by the session.
    -- It sets the package variables which then do not change during the life of the session.
    SELECT SYSDATE, X_LOAD_SEQ.NEXTVAL
    INTO LOAD_SEQ.NEXT_LOAD_DATE, LOAD_SEQ.NEXT_LOAD_SEQ_ID
    FROM DUAL;
END LOAD_SEQ;
/

Upvotes: 0

Related Questions