Hi Tom,

We have problem with SQL*LOADER.

Here is the problem.

We have two table, Which we have to populate using sqlloader.

The two INFILE we are getting every night has different name.File format is like
1)ISRO||'any number'.bat
2)ISBN||'any number'.bat

"any number" can be any number.

We used to have two control file before.But since INFILE
can have different name we can not use old control file.

We have to create control file dynamically because of INFILE.

Any idea how we can do this.

Thanks.

Hi Tom,

We can load multiple files in single table using only one control file. Here is the type of the control file that I use for daily upload and as Arun has said, I run it daily at night and get the data loaded during the off peak hours.

load data
infile 'data_daily1.csv'
infile 'data_daily2.csv'
infile 'data_daily3.csv'
append
into table daily_upload
fields terminated by ","
optionally enclosed by '"'
trailing nullcols
(
col1,
col2,
col3,
col4,
col5
)

I upload around 150000 approx. daily and have never faced any problems.

Tom,
If I have a table (say T1) with 4 columns (col1,col2,col3 and col4). We use SQLLOADER to load data into T1 from data.txt file. The data in data.txt is like this
1,5,test
5,8,condition
8,1,real
....

The data is being loaded into col1, col2 and col3 only and col4 is blank. Now we have another file (newfile.txt) having just 1 record - that is date. The data in the newfile is
09/05/2003.

What is the best way to load this data into col4 of T1.

Thanks

Tom,
If I have a table (say T1) with 4 columns (col1,col2,col3 and col4). We use SQLLOADER to load data into T1 from data.txt file. The data in data.txt is like this
1,5,test
5,8,condition
8,1,real
....

The data is being loaded into col1, col2 and col3 only and col4 is blank. Now we have another file (newfile.txt) having just 1 record - that is date. The data in the newfile is
09/05/2003.

What is the best way to load this data into col4 of T1.

Thanks

Sorry for the double post.I know Oracle has External table in 9i (and maybe other )but is there a special reason why Oracle is not enchancing SQL*loader. By enhancing I mean - inclusing IF/THEN/ELSE condition, loading files from two different files with different format into a single table etc. etc.
Thanks

I never stop learning !! I will need to read more about how to use
case statements
where clauses
etc in SQL*loader. Any pointers?
That was really fast response.

Hi Tom,
Thankx for the answer.It was of great help for me, since i am loading data to oracle from an punching card device.I was in search for such an answer.. because the file name i get from the punching machine will b different all the time..and i had asked the user to combine the data to an single file for uploading...I was wondering wht to do with tht..i tried really hard posting a ques but was not able to...anyway thankx once again
Now i have one more doubt...regarding this
u querry "for %f in ( *.dat ) do sqlldr tkyte/tkyte data.ctl"
I tried with *.txt format also..it worked but
i want to know whether its possible to load files with different extensions..like .dat,.txt etc
once again thank u very much..u have reduced me work a lot
regards

Hi Tom
I'm working on data uploading But on my pc it is giving me error as follows
Message 2100 not found; No message file for product=RDBMS, facility=ULMessage 21
00 not found; No message file for product=RDBMS, facility=UL
How can i resolve this problem

OS => Windows 2K & Oracle 8.1.6
I have installed Oracle Developer 6 and oracle dba on my pc

Hi Tom,
Thanx for your help
But now I have some different problem with salloader

I have a Excel file having data in cell.
But few of cells have some chr 13 /chr 10
If i convert this file into csv Column divide it contents into different line just because of chr 13 /chr 10
FILE data will be like this

ABC ,Test,"AAAAA
BBBBB
CCCCC"

3 Columns

How can i resolve this issue?

Hi Tom,

I have an application where I have to load multiple record sets (consisting of thousands of records) into the same table simultaneously using sql loader. However each record set must have a different job_id (generated from a sequence) assigned to it on the fly.

An example would be two record sets (could be hundreds) loaded simultaneously with sqlloader with just one column and the second column would be the job_id that would be assigned by a sequence on the fly.

record set #1
'ABC', 123
'BCD', 123
'CDE', 123

record set #2
'ABC', 456
'BCD', 456
'CDE', 456

Thanks,
Ivan

I never thought of a logon trigger. Excellent Idea!!

Thanks,
Ivan

Hi tom,
i have table with 40 cols,
the datas from csv with differnt type,how to load it in to oracle?.in some places in csv file null values also accepted..
when i try to use various ctl file options i get this information..
Space allocated for bind array:
Space allocated for memory besides bind array:
i am using this ctl file format

OPTIONS (BINDSIZE=1000000, SILENT=(ERRORS, FEEDBACK) )
LOAD DATA

INFILE '/appl/noa/scripts/sample.csv'

INSERT INTO TABLE TBLSAP_HISTORY

FIELDS TERMINATED BY ','

TRAILING NULLCOLS

(

ID NUMBER EXTERNAL NULLIF (ID=BLANKS)

, SAP VARCHAR2(12) NULLIF (SAP=BLANKS)

, CSS_DATABASE VARCHAR2(2) NULLIF (CSS_DATABASE=BLANKS)

, COST_CENTRE VARCHAR2(255) NULLIF (COST_CENTRE=BLANKS)

, INSTALLATION_NAME VARCHAR2(70) NULLIF (INSTALLATION_NAME=BLANKS)

, VP_ACCOUNT VARCHAR2(12) NULLIF (VP_ACCOUNT=BLANKS)

, TELNO VARCHAR2(25) NULLIF (TELNO=BLANKS)

, BILLING_NAME VARCHAR2(70) NULLIF (BILLING_NAME=BLANKS)

, INSTALLATION_ADDRESS VARCHAR2(2000) NULLIF (INSTALLATION_ADDRESS=BLANKS)

, BILLING_ADDRESS VARCHAR2(2000) NULLIF (BILLING_ADDRESS=BLANKS)

,ORDER_ISSUER VARCHAR2(20) NULLIF (ORDER_ISSUER=BLANKS)

,CUSTOMER_TYPE VARCHAR2(20) NULLIF (CUSTOMER_TYPE=BLANKS)

, ORDER_NO VARCHAR2(20) NULLIF (ORDER_NO=BLANKS)

, ACTIVITY VARCHAR2(3) NULLIF (ACTIVITY=BLANKS)

, MAS VARCHAR2(100) NULLIF (MAS=BLANKS)

, MAI VARCHAR2(100) NULLIF (MAI=BLANKS)

, MFR VARCHAR2(100) NULLIF (MFR=BLANKS)

, TAIG VARCHAR2(100) NULLIF (TAIG=BLANKS)

, MDRA VARCHAR2(100) NULLIF (MDRA=BLANKS)

, MAI2 VARCHAR2(100) NULLIF (MAI2=BLANKS)

, FTP VARCHAR2(100) NULLIF (FTP=BLANKS)

, F_INDICATOR VARCHAR2(20) NULLIF (F_INDICATOR=BLANKS)

, ORDER_RECEIVED DATE "MM-DD-YYYY" NULLIF (ORDER_RECEIVED=BLANKS)

, CUSTID VARCHAR2(100) NULLIF (CUST_ID=BLANKS)

, REQUIRED_BY DATE "MM-DD-YYYY" NULLIF (REQUIRED_BY=BLANKS)

, SITEID VARCHAR2(20) NULLIF (SITEID=BLANKS)

, ORDER_TYPE VARCHAR2(20) NULLIF (ORDER_TYPE=BLANKS)

, BATCH_NO VARCHAR2(20) NULLIF (BATCH_NO=BLANKS)

, DUTY_REFERENCE VARCHAR2(20) NULLIF (DUTY_REFERENCE=BLANKS)

, BILL_GROUP VARCHAR2(20) NULLIF (BILL_GROUP=BLANKS)

, CUST_CLASSN CHAR(1) NULLIF (CUST_CLASSN=BLANKS)

, TRAWLED DATE "MM-DD-YYYY" NULLIF (TRAWLED=BLANKS)

, OLD_DATE VARCHAR2(10) NULLIF (OLD_DATE=BLANKS)

, OLD_MDRA VARCHAR2(12) NULLIF (OLD_MDRA=BLANKS)

, OLD_TAIG VARCHAR2(20) NULLIF (OLD_TAIG=BLANKS)

, MULTI_BATCH VARCHAR2(4) NULLIF (MULTI_BATCH=BLANKS)

, XSOURCE CHAR(1) NULLIF (XSOURCE=BLANKS)

, MULTI_COUNT NUMBER EXTERNAL NULLIF (MULTI_COUNT=BLANKS)

, ONEBILL CHAR(1) NULLIF (ONEBILL=BLANKS)

, EBPP VARCHAR2(100) NULLIF (COL5=BLANKS)

, TAIG_ERRDATE DATE "MM-DD-YYYY" NULLIF (COL6=BLANKS)

, ORD_ISSUER_OUC CHAR(10) NULLIF (ORD_ISSUER_OUC=BLANKS)

, VAT_STATUS CHAR(1) NULLIF (VAT_STATUS=BLANKS)

)

Commit point reached
Commit point reached
Commit point reached
Commit point reached
Commit point reached

Kindly help!!!!

Error while loader data through ctl file using Sqlldr
Commit point reached - logical record count 8
Commit point reached - logical record count 9

Hi Tom,
I would like to know if it would be possible to control the commit point. I have got 100 records. I would like to make sure that the commit occurs only if all the records have been inserted in the table. In case there is a single bad record, I wish to do a rollback.

How do you achieve this in SQL Loader?

Cheers,
Ab

i'm in sql prompt what should i do to bring it to sqlloader or how do i create a control file,data file in sqlloader.

Hi Tom

I want to update a table using sqlplus and run sqlldr from one batch file.
I made a script but this runs only last file.

set ORACLE_SID=STAGING
set userid=stgkliuser
set passwd=stgkliuser123

for %f in ( *.ctl ) do set d1=%f ECHO EXECUTE STGPKG.LOG_START_TIME(%d1:~1,2%); | sqlplus %userid%/%passwd% sqlldr userid=%userid%/%passwd% control=%d1:~1,2%.ctl log=%d1:~1,2%.log bad=%d1:~1,2%.bad parallel=true ECHO EXECUTE STGPKG.LOG_END_TIME(%d1:~1,2%); | sqlplus %userid%/%passwd%

hi tom, i dont know how to ask question. using this preview forum. u have provided a command for multifile load for windows as follows

"C:\oracle\RDBMS\demo\test>for %f in ( *.dat ) do sqlldr tkyte/tkyte data.ctl
data=%f"


wat is the command to do the same in red had linux advance server 3 and 4.

thanks

Hello,

I would like to join AskTom community. How do I start?

Thank you!

This is first time iam writing a message to you..
please help me on how to upload one single file into two tables using sql loader. Input file is comma separated file.
And also if there is a way please tell on how to skip some column from input text file.

regards
vijay

Hi Tom,

I'm newbee in Oracle and going through this question. Here replying to pawan you said:

Followup:  
there isn't one. sqlldr doesn't update, it loads
you load that row into yet another table T2 and then
update t1 set col4 = (select * from t2); 
 
So to test this I did like below but unable to do this. Please suggest how to do this?

SQL> create table a(col1 varchar2(5), col2 varchar2(5), col3 varchar2(5),col4 number(4));
Table created.

SQL> create table b(col5 number (6));
Table created.

Here I inserted few data in both tables and kept data in cl4 as NULL.

SQL> select * from a;
COL1  COL2  COL3        COL4
----- ----- ----- ----------
aa    aa    aa
bb    bb    bb
cc    cc    cc

SQL> select * from b;
      COL5
----------
         2
         3
         4

SQL> update a set col4 = (select * from b);
update a set col4 = (select * from b)
                     *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row 

Tom,

While reading a file, UTL_FILE garantees row order.
Will SELECT * from external_table garantee the same?
Some of out inbound datafiles have multi-line
transactions, so order is the absolute must.

Oracle docs and several articles on "external tables"
say nothing about order garantee. Also,CONTINUEIF
and CONCATENATE (from sqlldr) are not implented here.

select from external tables is amasingly fast and
we duscussing replacing UTL_FILE.

Thanks,

Alex V.

Thanks, it is exactly what I was asking about.
My secondary consern is about performance of
explicit sort (order by r (=RECNUM)) for big files.
I guess it will be extremenly fast as records are
perfectly sorted anyway (in current implementation).
I will test it.

Alex V.

Hi,
I have tried to get the data from the table nc_rec_params for a particular transaction id[it is a field in this table], but it returns this exception.
if i execute this query "select * from nc_rec_params" it returns the proper values.
we didn't use any index in our query,
we don't know where is the problem and how to rectify it

Please help us to rectify the problem.

I am looking for loading data from multiple files in Unix. But found it only for Windows. Since I dont know Unix shell script I have to fight for it. But still it gave me an idea from where to attack.
Thanks

i am a beginner and am using application express. i have the same question that ivan had in september 2004. you replied that he could use a logon trigger. your reply is below. where do i insert this text and how?

<code>create or replace trigger logon_trigger
after logon on schema
declare
    l_number number;
begin
    select s.nextval into l_number from dual;
end;

/


that'll "prime a sequence for any session created by that schema" -- letting you invoke s.currval
without having to call s.nextval. You can then load like this:

load data
into table t
append
fields terminated by ','
trailing nullcols
(
abc,
def,
job_id "s.currval"
)

job_id will have the sql function s.currval applied to it and assign a unique job id to each sqlldr
session.

Referring to Ivan from the USA's situation, I have the same situation. You suggested to create a logon trigger. I am currently using Apex and have no idea about how to even begin to create this logon trigger. Can you please walk me through where to type in the following code and how to relate it to mine (example where to fill in my column and table names). I have done HOURS of research and your suggestion is the closest I have seen to meet my needs. Thank you for your time. You are VERY appreciated!!!

create or replace trigger logon_trigger
after logon on schema
declare
    l_number number;
begin
    select s.nextval into l_number from dual;
end;
/

"that'll 'prime a sequence for any session created by that schema' -- letting you invoke s.currval
without having to call s.nextval. You can then load like this:"

load data
into table t
append
fields terminated by ','
trailing nullcols
(
abc,
def,
job_id "s.currval"
)

"job_id will have the sql function s.currval applied to it and assign a unique job id to each sqlldr
session."

Is there a way to use an external table in a way such that 2 sessions could use the same external table looking at 2 different files at the same time? Each session should only see the data from its own file.

I'm trying to use external tables to perform ETL, but I'd like to be able to process files as they arrive with one session per file, but I don't know how to change the source in one session without a affecting another session already using that external table.

I found an answer to my question: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:37593123416931#42075498219709

Any chance things have changed?

It'd be nice if you could somehow pass a file to an external table.....something like select * from ext_table('file1.txt'). Forcing the use of external tables to be serial makes their use nonscalable.

As is the case with the individual in the other thread, it's more of a timing thing. Files could come in at any time. If we have to do things serially, files would have to wait if the external table was currently being used. This is what I meant by not scalable.

A

Oh .. sorry ..
Serious, quick question:

SQL*Loader, feeding it 2 or more files. Same format, same - single table - target.
Simple enough, however, is there any way that SQL*loader has to help identify which file the records came from?

Reason is for some special logic, we want to set a column/flag to "A, B, C, etc" depending on which file it came from (that is later used inside Oracle/SQL for other more complex processing, ie RSP, vs RESP, vs TFSA, vs whatever ... ) I'm thinking it can't be done (easily?)
Currently we're loading with seperate ctl files to do this, and we were thinking it might be easier to merge them into 1 - if possible.

Thanks!

Chalk up another reason to upgrade!!

Sorry, forgot to mention which version ... 10g ..

(and I searched, but didn't see that thread ... probably since I searched for "sql*loader multi file", not "external table" .. ;) heh

Thanks!!

Hi Tom,
I am loading data through multiple csv files.
ex. OPTIONS (SKIP=1)
LOAD DATA
INFILE '100.csv'
INFILE '200.csv'
INFILE '300.csv'
TRUNCATE INTO TABLE XYZ_NEW
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
<column_names>

Here what required if i need to skip 1st header row of 2nd & 3rd csv files ?

Below error logged in logger :
Record 75680: Rejected - Error on table XYZ_NEW, column SOURCE.
ORA-01722: invalid number

Can u suggest a way to solve it ?