Skip to Main Content
  • Questions
  • Sequence.nextval not working in sqlldr

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, gnanasekaran.

Asked: August 20, 2016 - 9:01 pm UTC

Last updated: August 22, 2016 - 10:29 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Viewed 10K+ times! This question is

You Asked

sequence.nextval not working when i am using in sqlldr , sqlldr loading null value in DB

Snapshot of my control file

load data
append
into table Selectiotab
WHEN (1:2) <> 'H|' and (1:2) <> 'T|'
fields terminated by "|" trailing nullcols
(
Txn_ID "SEQ_IFCE_ID.NEXTVAL",
Account Char)


and Connor said...

That is correct.

Options you have are:

1) Use the SEQ keyword to load records starting with integer 1.

http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_field_list.htm#SUTIL1233

2) Use a trigger on the table (I would not recommend this)

3) Don't use SQL Loader - its far more flexible to use an external table.

http://www.oracle.com/technetwork/issue-archive/2011/11-jul/o40asktom-402616.html


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

we could use sequence.nextval in a controlfile

Mikhail Velikikh, August 22, 2016 - 2:12 am UTC

Hello Connor,

I'm sorry, but I didn't get it.
Why did you say that was correct?
I could use SEQUENCE.NEXTVAL in a controlfile without any issues, i.e. I could load non-null values into a database.
Below is a test script to create a table and a sequence:
SQL> create table selectiotab(
  2    txn_id int,
  3    account varchar2(10));

Table created.

SQL> 
SQL> create sequence seq_ifce_id start with 10 increment by 5;

Sequence created.

Here is my control file:
load data
append
into table Selectiotab
WHEN (1:2) <> 'H|' and (1:2) <> 'T|'
fields terminated by "|" trailing nullcols
(
Txn_ID "SEQ_IFCE_ID.NEXTVAL",
Account Char)

It's my data file:
H|account1
A|account2
A|account3
T|account4

I've launched SQL*Loader as follows:
[velikikh@comp2364 20160822]$ sqlldr userid=velikikh@ora121 control=sqlldr_seq_nextval data=sqlldr_seq_nextval
Password:

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Aug 22 08:51:56 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

ORA-28002: the password will expire within 1 days
Path used:      Conventional
Commit point reached - logical record count 4

Table SELECTIOTAB:
  2 Rows successfully loaded.

Check the log file:
  sqlldr_seq_nextval.log
for more information about the load.

As a result, I've got 2 new rows in the table where their TXN_ID columns were populated with a SEQUENCE.NEXTVAL values from the database:
SQL> select * from selectiotab;

    TXN_ID ACCOUNT
---------- ------------------------------
        10 account2
        15 account3

The load was accomplished by the following SQL statement:
SQL> select a.sql_id,
  2         a.sql_text,
  3         a.executions,
  4         a.rows_processed
  5    from v$sqlarea a,
  6         v$sqlcommand c
  7   where a.parsing_schema_name = user
  8     and c.command_name = 'INSERT'
  9     and a.command_type = c.command_type
 10  /

SQL_ID        SQL_TEXT                                                                         EXECUTIONS ROWS_PROCESSED
------------- -------------------------------------------------------------------------------- ---------- --------------
cgjq45t9csu   INSERT INTO SELECTIOTAB   (TXN_ID,ACCOUNT) VALUES (SEQ_IFCE_ID.NEXTVAL,:ACCOUNT)          1              2


Connor McDonald
August 22, 2016 - 3:24 am UTC

Apologies..I should have elaborated more. If they original poster is getting nulls, then they must be using direct load.

C:\temp>sqlldr userid=mcdonac control=demo.ctl data=demo.dat 
Password:

SQL*Loader: Release 11.2.0.4.0 - Production on Mon Aug 22 11:21:49 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Load completed - logical record count 4.

SQL> select * from Selectiotab;

    TXN_ID ACCOUNT
---------- ----------
        10 account2
        15 account3

C:\temp>sqlldr userid=mcdonac control=demo.ctl data=demo.dat DIRECT=TRUE
Password:

SQL*Loader: Release 11.2.0.4.0 - Production on Mon Aug 22 11:21:49 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Load completed - logical record count 4.

SQL>  select * from Selectiotab;

    TXN_ID ACCOUNT
---------- ----------
           account2
           account3



external_table=execute

Mikhail Velikikh, August 22, 2016 - 4:52 am UTC

Thank you for your clarification. I understood it now.
Just to add to your answer, I think we could use SQL*Loader with external_table=execute option to get "the best of both worlds" (external tables and SQL*Loader).
For instance, I've launched SQL*Loader as follows (the important part here is external_table=execute command line parameter):

[velikikh@comp2364 20160822]$ sqlldr userid=velikikh control=demo data=demo external_table=execute
Password:

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Aug 22 11:44:42 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

ORA-28002: the password will expire within 1 days
Path used:      External Table

Table SELECTIOTAB:
  2 Rows successfully loaded.

Check the log files:
  demo.log
  demo.log_xt
for more information about the load.



Under the hood, the load created an intermediate directory object and an external table, which may have seen in the log file:
created temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for path /pub/home/velikikh/oracle/tmp

creating external table "SYS_SQLLDR_X_EXT_SELECTIOTAB"

CREATE TABLE "SYS_SQLLDR_X_EXT_SELECTIOTAB" 
(
  "TXN_ID" VARCHAR2(255),
  "ACCOUNT" VARCHAR2(10)
)
ORGANIZATION external 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    LOAD WHEN (((1: 2) != "H|") AND ((1: 2) != "T|"))
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo.bad'
    LOGFILE 'demo.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "|" LDRTRIM 
    MISSING FIELD VALUES ARE NULL 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      "TXN_ID" CHAR(255)
        TERMINATED BY "|",
      "ACCOUNT" CHAR(255)
        TERMINATED BY "|"
    )
  )
  location 
  (
    'demo.dat'
  )
)REJECT LIMIT UNLIMITED


And then the following SQL statement was generated (the important part here is the APPEND hint - a sign of a possible direct load operation unless we haven't violated any direct-load restrictions):
executing INSERT statement to load database table SELECTIOTAB

INSERT /*+ append */ INTO SELECTIOTAB 
(
  TXN_ID,
  ACCOUNT
)
SELECT 
  SEQ_IFCE_ID.NEXTVAL,
  "ACCOUNT"
FROM "SYS_SQLLDR_X_EXT_SELECTIOTAB"

With the following execution plan (a true direct load):
SQL_ID  fcnvxmdwn2vsq, child number 0
-------------------------------------
INSERT /*+ append */ INTO SELECTIOTAB  (   TXN_ID,   ACCOUNT ) SELECT
 SEQ_IFCE_ID.NEXTVAL,   "ACCOUNT" FROM "SYS_SQLLDR_X_EXT_SELECTIOTAB"

Plan hash value: 3995769479

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |                              |       |       |   342 (100)|          |
|   1 |  LOAD AS SELECT                  |                              |       |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |                              |   102K|   697K|   342   (1)| 00:00:01 |
|   3 |    SEQUENCE                      | SEQ_IFCE_ID                  |       |       |            |          |
|   4 |     EXTERNAL TABLE ACCESS FULL   | SYS_SQLLDR_X_EXT_SELECTIOTAB |   102K|   697K|   342   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

I've got 2 rows in the table with the TXN_ID column populated by SEQUENCE.NEXTVAL:
SQL> select * from selectiotab;

    TXN_ID ACCOUNT
---------- ------------------------------
        10 account2
        15 account3


Connor McDonald
August 22, 2016 - 10:29 am UTC

Agreed.

One of the very cool things with sqlldr is the "generate_only" option so you can get the same DDL commands made available to you.

Because one thing I *never* get right first time is the external table ddl syntax - so I let sqlldr do the hard work for me.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library