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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 #443872 is a reply to message #443488] |
Wed, 17 February 2010 14:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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>
|
|
|
Goto Forum:
Current Time: Mon Sep 09 22:21:32 CDT 2024
|