Home » RDBMS Server » Server Utilities » SQL Loader : How can we make use of RECNUM in SQL expression
SQL Loader : How can we make use of RECNUM in SQL expression [message #443488] Tue, 16 February 2010 01:04 Go to next message
ajay_net
Messages: 8
Registered: February 2010
Junior Member
Hi,

Can we make use of RECNUM in an SQL expression given below:


OPTIONS(SKIP = 1)
LOAD DATA

INFILE "D:\Test.txt"
APPEND
INTO TABLE TEST
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
FIRST_NAME,
LAST_NAME,
PHONE_NUM,
BATCH "FLOOR((<RECNUM> - 1) / 1000)" --Can we bring the value of RECNUM here?
)



I have tried out 'RECNUM' and ':RECNUM', however, it does not work.

Kindly let me know your suggestions.

Thanks
Re: SQL Loader : How can we make use of RECNUM in SQL expression [message #443491 is a reply to message #443488] Tue, 16 February 2010 01:08 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
According to the documentationQuote:
The SQL string cannot be used on RECNUM, SEQUENCE, CONSTANT, or SYSDATE fields.
Re: SQL Loader : How can we make use of RECNUM in SQL expression [message #443494 is a reply to message #443488] Tue, 16 February 2010 01:12 Go to previous messageGo to next message
vibhavari
Messages: 19
Registered: October 2009
Junior Member
you can use
loadseq SEQUENCE(MAX,1)
Regards, vibahavri
Re: SQL Loader : How can we make use of RECNUM in SQL expression [message #443538 is a reply to message #443488] Tue, 16 February 2010 03:10 Go to previous messageGo to next message
ajay_net
Messages: 8
Registered: February 2010
Junior Member
Hi,

I tried to make use of SEQUENCE, however, it's giving me an error :

SQL*Loader-466: Column LOADSEQ does not exist in table TEST


loadseq SEQUENCE(MAX,1),
BATCH "FLOOR((:loadseq - 1) / 1000)"


Thanks
Re: SQL Loader : How can we make use of RECNUM in SQL expression [message #443541 is a reply to message #443538] Tue, 16 February 2010 03:13 Go to previous messageGo to next message
vibhavari
Messages: 19
Registered: October 2009
Junior Member
loadseq SEQUENCE(MAX,1),
loadseq is a column in a table in which the autogenerated sequence will be inserted
u can try
BATCH SEQUENCE(MAX,1)
Re: SQL Loader : How can we make use of RECNUM in SQL expression [message #443766 is a reply to message #443488] Wed, 17 February 2010 04:47 Go to previous messageGo to next message
ajay_net
Messages: 8
Registered: February 2010
Junior Member
Hi,

I want to group the records in batches of 1000, that's why I have made use of FLOOR. So is there any way to pass the sequence number in it.

Thanks
Re: SQL Loader : How can we make use of RECNUM in SQL expression [message #443829 is a reply to message #443541] Wed, 17 February 2010 08:23 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Proper way to reference a sequence in a control file is SEQUENCE_NAME.nextval (just as you would use nextval in a SELECT).
Re: SQL Loader : How can we make use of RECNUM in SQL expression [message #443872 is a reply to message #443488] Wed, 17 February 2010 14:19 Go to previous message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
There are different kinds of sequences. There are RECNUM and SEQUENCE parameters just for SQL*Loader control files and there is a different kind of database SEQUENCE that can also be referenced in a SQL*Loader control file. The RECNUM and SEQUENCE parameters that are just for SQL*Loader control files have the limitations that you cannot apply a SQL expression, such as your computation using FLOOR to them, and they must apply to a column, not a variable, so you cannot get around the first limitation by assigning the value to a variable, then binding to it to apply the SQL expression. So, what you need is a database SEQUENCE, which must be created in the database before the load is run and referenced in the SQL*Loiader control file using your_sequence_name.NEXTVAL. Pleae see the example below that uses some of the data from the Oracle emp and dept demo tables and references a database SEQUENCE to increment the batch column every five rows, beginning with 0.

-- test.dat:

first, last, and phone
------------------------------------------------------------------
ADAMS,RESEARCH,20
ALLEN,SALES,30
BLAKE,SALES,30
CLARK,ACCOUNTING,10
FORD,RESEARCH,20
JAMES,SALES,30
JONES,RESEARCH,20
KING,ACCOUNTING,10
MARTIN,SALES,30
MILLER,ACCOUNTING,10
SCOTT,RESEARCH,20
SMITH,RESEARCH,20
TURNER,SALES,30
WARD,SALES,30


-- test.ctl:
OPTIONS (SKIP = 3)
LOAD DATA
INFILE "test.dat"
APPEND
INTO TABLE test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
first_name,
last_name,
phone_num,
batch "FLOOR ((test_seq.nextval - 1) / 5)"
)


-- create table and sequence, load data, and results:
SCOTT@orcl_11g> CREATE TABLE test
  2    (first_name VARCHAR2 (15),
  3  	last_name  VARCHAR2 (15),
  4  	phone_num  VARCHAR2 (15),
  5  	batch	   NUMBER)
  6  /

Table created.

SCOTT@orcl_11g> CREATE SEQUENCE test_seq
  2  /

Sequence created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11g> SELECT * FROM test
  2  /

FIRST_NAME      LAST_NAME       PHONE_NUM            BATCH
--------------- --------------- --------------- ----------
ADAMS           RESEARCH        20                       0
ALLEN           SALES           30                       0
BLAKE           SALES           30                       0
CLARK           ACCOUNTING      10                       0
FORD            RESEARCH        20                       0
JAMES           SALES           30                       1
JONES           RESEARCH        20                       1
KING            ACCOUNTING      10                       1
MARTIN          SALES           30                       1
MILLER          ACCOUNTING      10                       1
SCOTT           RESEARCH        20                       2
SMITH           RESEARCH        20                       2
TURNER          SALES           30                       2
WARD            SALES           30                       2

14 rows selected.

SCOTT@orcl_11g>



Previous Topic: sqlrd control file
Next Topic: Is it possible to change table name while import ?
Goto Forum:
  


Current Time: Tue Dec 06 14:37:15 CST 2016

Total time taken to generate the page: 0.16666 seconds