Home » RDBMS Server » Server Utilities » call sequence in sql loader (10.2.0.5)
call sequence in sql loader [message #554882] Fri, 18 May 2012 05:30 Go to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Hi Guys,

I have to load data from sql loader in a table and the table has got a varchar2 column which accept data from sequence.

the expression would be.

TRANSACTION_ID "'AUTO-'||To_Char(prt_trs_id_seq.nextval)"

TRANSACTION_ID is the column name.

When i add this expression the loader doesn't work, when i remove it works and load null for this column and valid data with other column.

Thanks

Rajesh
Re: call sequence in sql loader [message #554885 is a reply to message #554882] Fri, 18 May 2012 06:01 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's an example: first, what do we have?
SQL> desc test;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 TRANSACTION_ID                                                 VARCHAR2(20)
 NAME                                                           VARCHAR2(20)

SQL> select * from test;

no rows selected

SQL> select seqa.nextval from dual;

   NEXTVAL
----------
        25

A control file:
LOAD DATA
INFILE *
INTO TABLE test
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(name char,
 transaction_id " 'auto-' || seqa.nextval"
)

begindata
intel
amd

Loading session and the result:
SQL> $sqlldr scott/tiger@ora10 control=test2.ctl log=test2.log

SQL*Loader: Release 11.2.0.2.0 - Production on Pet Svi 18 12:58:49 2012

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

Commit point reached - logical record count 1
Commit point reached - logical record count 2

SQL> select * from test;

TRANSACTION_ID       NAME
-------------------- --------------------
auto-26              intel
auto-27              amd

SQL>


So ... it kind of works OK. Perhaps you could post your own test case so that we could try it.
Re: call sequence in sql loader [message #554888 is a reply to message #554885] Fri, 18 May 2012 06:22 Go to previous message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Ok, It works for me as well when i remove the DIRECT = TRUE.

Thanks

Rajesh

Previous Topic: Skip column while loading data using SQL Loader
Next Topic: Error not a valid month in sql loader
Goto Forum:
  


Current Time: Sat Jan 25 13:51:52 CST 2020