Is it possible to write sql statement in the control file

[himridul]

Hi,

Is it possible to write sql statement in the control file of SQLLOADER?
I need to retrive a field from oracle table , in control file . I want to use query like:
select emp_no from emp;
in control file.
one more thing , can we use Oracle Sequence generator in the control file.

If anyone has any idea regarding this , plz help me.
Thanks in advance........

 

[aryeh1010]

If you only want to load one field, then you have to define other fields as FILLER. This way sql*loader knows that there is a field in the data file, but doesn't load it.

If this isn't what you meant, please clarify.

If you want to generate sequential numbers, use the RECNUM function. This is the sql*loader equivalent of ROWNUM in SQL.

Aryeh Keefe

 

[himridul]

Thanks Aryeh Keefe . But this is not what I meant .
Actually I want to know that ,
Can we use SELECT,DELETE,UPDATE statements in Control File .
any DML or any DDL statements can be used in SQL Loader ?
If possible then How ?

Thanks in advance..

 

[himridul]

One more thing .................
I've used RECNUM and got the sequence number . But the problem is that :

I've 2 input files and these two files will be loaded into a single table using 2 parallel sessions simultaneously. Now in this case , if each table has 100 records , the sequence of the table becomes 1 to 100 and then for second file again 1 to 100 . but it should be 1 to 200 . Can I set the RECNUM function to a specific value , so that it starts from a specific no , such as from 101.

eg,

sqlldr scott/tiger@xxx direct=true parallel=true ctl=first.ctl

sqlldr scott/tiger@xxx direct=true parallel=true ctl=second.ctl

--first.ctl
LOAD DATA
INFILE 'c:\mr.txt'
APPEND
INTO TABLE BDAY
fields terminated by "-"
(REC_NUM RECNUM,NAME ,EMPNO)

--second.ctl
LOAD DATA
INFILE 'c:\mr1.txt'
APPEND
INTO TABLE BDAY
fields terminated by "-"
(REC_NUM RECNUM,NAME ,EMPNO)

These commands are running parallely in different sessions.
I need rows in BDAY table with sequence no 1 to 200. Not 1 to 100 and 1 to 100 .

It's also possible that I don't know the no. of input records in the flat files.In that case what will be the solution to get proper sequnce no.?

 

[aryeh1010]

You can use one control file to load both files.

LOAD DATA
INFILE 'c:\mr.txt'
INFILE 'C:\mr1.txt'
APPEND
INTO TABLE BDAY
fields terminated by "-"
(REC_NUM RECNUM,NAME ,EMPNO)

I recently loaded 2 million records from 20 different files, which were all listed in one control file. In that case the recnum functin generated sequential numbers.

Regarding DML, DDL, SELECT, UPDATE, DELETE statements in the control file. You are limited to what you can do. I would also like to point out that sql*loader isn't the easiest utility to use.

My method is to load everything with direct load, into a staging table and then run sql scripts against that table to do any data modifications before I do one, clean insert into the destination table.

This makes things run much more quickly and clearly, as the sql*loader just loads and the sql scripts do any complicated work.

It makes my life much simpler.

Aryeh Keefe


Aryeh Keefe

 

[himridul]

Thanks Aryeh Keefe . But the thing is that I will have to run 4 sqlloader in parallel.Because the input flat file would have 200 millions of data and we've 4 processors. So to utilize 4 processors , I split the input file into 4 data files and running 4 sessions at a time using shell script.
For sequence no , instead of RECNUM , I'm using SEQUENCE(start no,increment by) .
Here the problem is ,I've to know the no. of records of each file , so that I can specify the "start no" of the sequence.

FOR eg,If I would have 2000 rows in the flat file then :

LOAD DATA
INFILE 'c:\mr.txt'
APPEND
INTO TABLE BDAY
fields terminated by "-"
(REC_NUM SEQUENCE(1,1),NAME ,EMPNO)

LOAD DATA
INFILE 'C:\mr1.txt'
APPEND
INTO TABLE BDAY
fields terminated by "-"
(REC_NUM SEQUENCE(500,1),NAME ,EMPNO)

LOAD DATA
INFILE 'C:\mr2.txt'
APPEND
INTO TABLE BDAY
fields terminated by "-"
(REC_NUM SEQUENCE(1000,1),NAME ,EMPNO)

LOAD DATA
INFILE 'C:\mr3.txt'
APPEND
INTO TABLE BDAY
fields terminated by "-"
(REC_NUM SEQUENCE(1500,1),NAME ,EMPNO)

 

[sem]

You do not need different sessions, use MULTITHREADING parameter and DIRECT path load.

Regards, Dima

http://seminihin.narod.ru

 

[himridul]

Yes I'm using direct path and parallel load .

sqlldr scott/tiger@xxx direct=true parallel=true ctl=mr.ctl
sqlldr scott/tiger@xxx direct=true parallel=true ctl=mr1.ctl
sqlldr scott/tiger@xxx direct=true parallel=true ctl=mr2.ctl
sqlldr scott/tiger@xxx direct=true parallel=true ctl=mr3.ctl

Why shouldn't I use 4 processors ? I don't know how to use MULTITHREADING concept.Will MULTITHREADING use 4 procesoors at a time?
Can u plz tell me , how to use MULTITHREADING?

Thanks .

 

[sem]

Read these:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch09.htm#1008034

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch09.htm#1007900

I'm not completely sure, but I think that you can not use multiple DIRECT loads simultaneously.

Regards, Dima

http://seminihin.narod.ru

 

[himridul]

SQL*Loader permits multiple, concurrent sessions to perform a direct path load into the same table, or into the same partition of a partitioned table. Multiple SQL*Loader sessions improve the performance of a direct path load given the available resources on your system.

This method of data loading is enabled by setting both the DIRECT and the PARALLEL parameters to true, and is often referred to as a parallel direct path load.

It is important to realize that parallelism is user managed. Setting the PARALLEL parameter to true only allows multiple concurrent direct path load sessions.

I've gone through MULTITHREADING concept , default is ON.
It's useful for single processor.

Thanks.