OraFAQ Forum: Server Utilities » SQL Loader : How can we make use of RECNUM in SQL expression

Home » RDBMS Server » Server Utilities » SQL Loader : How can we make use of RECNUM in SQL expression

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Loader : How can we make use of RECNUM in SQL expression [message #443488]

Tue, 16 February 2010 01:04

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

Report message to a moderator

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

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

vibhavari
Messages: 20
Registered: October 2009

Junior Member

you can use
loadseq SEQUENCE(MAX,1)
Regards, vibahavri

Report message to a moderator

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

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

Report message to a moderator

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

vibhavari
Messages: 20
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)

Report message to a moderator

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

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

Report message to a moderator

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

joy_division
Messages: 4963
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).

Report message to a moderator

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

Barbara Boehmer
Messages: 9102
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>

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 14 07:29:34 CST 2025