OraFAQ Forum: Windows

Home » Infrastructure » Windows » SQLLDR

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

SQLLDR [message #560052]

Tue, 10 July 2012 04:33

bhibe_17
Messages: 24
Registered: July 2012

Junior Member

hi,
im getting an error message:
SQL*Loader-503: Error appending extension to file (%%g)
OSD-04503: Message 4503 not found; No message file for product=RDBMS, facility=SOSD

when i tried to create a bat file that should read and load all files in the folder specified.
here's my code:

@echo off
for /r c:\bret\infile %%g in (*) Do (
echo Load Data>loaderhdr.ctl
echo INFILE '%%g'>>loaderhdr.ctl
echo append into table INTR_INS_BRET_HDR WHEN (1:1) = "H" >>loaderhdr.ctl
echo (TRANS_ID "intr_ins_bret_hdr_seq.nextval",PROCESS_DATE "to_date(SYSDATE)", REC_TYPE position(1:1^), VERSION_NO position(2:4^), FILE_NAME position(5:44^), FILE_DATE position(45:52^), FILE_NO position(53:54^), PROCESS_TYPE position(55:55^), MERCHANT_NO position(56:70^), FILE_FORMAT_ID position(71:74^), REC_TYPE2 position(75:76^), BATCH_NO position(77:85^), DATE_SENT position(86:91^), TOT_NO_TRANS position(92:95^), SIGN_TOT_BILL_AMT position(105:105^), TOT_BILL_AMT position(96:104^) "decode(:sign_tot_bill_amt, '+', :TOT_BILL_AMT/100, :TOT_BILL_AMT/100*-1)", ESTABLISH_NO position(106:116^), INS_CODE position(117:121^), INS_TYPE position(122:122^)) >> loaderhdr.ctl
call loaddatahdr.bat
)

the program should create a CTL file based on the files inside c:\bret\infile, but its getting an error. appreciate your help. thanks!

Report message to a moderator

Re: SQLLDR [message #560061 is a reply to message #560052]

Tue, 10 July 2012 05:15

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Rewritten and reformatted (so that you'd actually see what you are doing): TEST.BAT:

for /f %%g in (infile.txt) Do (
  echo Load Data > loaderhdr.ctl
  echo INFILE '%%g' >> loaderhdr.ctl
  echo append into table INTR_INS_BRET_HDR WHEN (1:1^) = 'H' >> loaderhdr.ctl
  echo  ( TRANS_ID "intr_ins_bret_hdr_seq.nextval" >> loaderhdr.ctl
  echo  , PROCESS_DATE "to_date(SYSDATE)"          >> loaderhdr.ctl
  echo  , REC_TYPE position(1:1^)                  >> loaderhdr.ctl
  echo  , VERSION_NO position(2:4^)                >> loaderhdr.ctl
  echo  , FILE_NAME position(5:44^)                >> loaderhdr.ctl
  echo  , FILE_DATE position(45:52^)               >> loaderhdr.ctl
  echo  , FILE_NO position(53:54^)                 >> loaderhdr.ctl
  echo  , PROCESS_TYPE position(55:55^)            >> loaderhdr.ctl
  echo  , MERCHANT_NO position(56:70^)             >> loaderhdr.ctl
  echo  , FILE_FORMAT_ID position(71:74^)          >> loaderhdr.ctl
  echo  , REC_TYPE2 position(75:76^)               >> loaderhdr.ctl
  echo  , BATCH_NO position(77:85^)                >> loaderhdr.ctl
  echo  , DATE_SENT position(86:91^)               >> loaderhdr.ctl
  echo  , TOT_NO_TRANS position(92:95^)            >> loaderhdr.ctl
  echo  , SIGN_TOT_BILL_AMT position(105:105^)     >> loaderhdr.ctl
  echo  , TOT_BILL_AMT position(96:104^) "decode(:sign_tot_bill_amt, '+', :TOT_BILL_AMT/100, :TOT_BILL_AMT/100*-1)" >> loaderhdr.ctl
  echo  , ESTABLISH_NO position(106:116^)          >> loaderhdr.ctl
  echo  , INS_CODE position(117:121^)              >> loaderhdr.ctl
  echo  , INS_TYPE position(122:122^)              >> loaderhdr.ctl
  echo  ^)                                         >> loaderhdr.ctl
  call loaddatahdr.bat
 )

The above script seems to be *working* OK.

In my example: INFILE.TXT:

test1.csv
test2.csv

LOADDATAHDR.BAT:

echo Here I am!
pause

Execution:

M:\>test

M:\>for /F %g in (infile.txt) Do (
echo Load Data  1>loaderhdr.ctl
 echo INFILE '%g'  1>>loaderhdr.ctl
 echo append into table INTR_INS_BRET_HDR WHEN (1:1) = 'H'  1>>loaderhdr.ctl
 echo  ( TRANS_ID "intr_ins_bret_hdr_seq.nextval"  1>>loaderhdr.ctl
 echo  , PROCESS_DATE "to_date(SYSDATE)"           1>>loaderhdr.ctl
 echo  , REC_TYPE position(1:1)                   1>>loaderhdr.ctl
 echo  , VERSION_NO position(2:4)                 1>>loaderhdr.ctl
 echo  , FILE_NAME position(5:44)                 1>>loaderhdr.ctl
 echo  , FILE_DATE position(45:52)                1>>loaderhdr.ctl
 echo  , FILE_NO position(53:54)                  1>>loaderhdr.ctl
 echo  , PROCESS_TYPE position(55:55)             1>>loaderhdr.ctl
 echo  , MERCHANT_NO position(56:70)              1>>loaderhdr.ctl
 echo  , FILE_FORMAT_ID position(71:74)           1>>loaderhdr.ctl
 echo  , REC_TYPE2 position(75:76)                1>>loaderhdr.ctl
 echo  , BATCH_NO position(77:85)                 1>>loaderhdr.ctl
 echo  , DATE_SENT position(86:91)                1>>loaderhdr.ctl
 echo  , TOT_NO_TRANS position(92:95)             1>>loaderhdr.ctl
 echo  , SIGN_TOT_BILL_AMT position(105:105)      1>>loaderhdr.ctl
 echo  , TOT_BILL_AMT position(96:104) "decode(:sign_tot_bill_amt, '+', :TOT_BILL_AMT/100, :TOT_BILL_AMT/100*-1)"  1>>loaderhdr.ctl
 echo  , ESTABLISH_NO position(106:116)           1>>loaderhdr.ctl
 echo  , INS_CODE position(117:121)               1>>loaderhdr.ctl
 echo  , INS_TYPE position(122:122)               1>>loaderhdr.ctl
 echo  )                                          1>>loaderhdr.ctl
 call loaddatahdr.bat
)

M:\>(
echo Load Data  1>loaderhdr.ctl
 echo INFILE 'test1.csv'  1>>loaderhdr.ctl
 echo append into table INTR_INS_BRET_HDR WHEN (1:1) = 'H'  1>>loaderhdr.ctl
 echo  ( TRANS_ID "intr_ins_bret_hdr_seq.nextval"  1>>loaderhdr.ctl
 echo  , PROCESS_DATE "to_date(SYSDATE)"           1>>loaderhdr.ctl
 echo  , REC_TYPE position(1:1)                   1>>loaderhdr.ctl
 echo  , VERSION_NO position(2:4)                 1>>loaderhdr.ctl
 echo  , FILE_NAME position(5:44)                 1>>loaderhdr.ctl
 echo  , FILE_DATE position(45:52)                1>>loaderhdr.ctl
 echo  , FILE_NO position(53:54)                  1>>loaderhdr.ctl
 echo  , PROCESS_TYPE position(55:55)             1>>loaderhdr.ctl
 echo  , MERCHANT_NO position(56:70)              1>>loaderhdr.ctl
 echo  , FILE_FORMAT_ID position(71:74)           1>>loaderhdr.ctl
 echo  , REC_TYPE2 position(75:76)                1>>loaderhdr.ctl
 echo  , BATCH_NO position(77:85)                 1>>loaderhdr.ctl
 echo  , DATE_SENT position(86:91)                1>>loaderhdr.ctl
 echo  , TOT_NO_TRANS position(92:95)             1>>loaderhdr.ctl
 echo  , SIGN_TOT_BILL_AMT position(105:105)      1>>loaderhdr.ctl
 echo  , TOT_BILL_AMT position(96:104) "decode(:sign_tot_bill_amt, '+', :TOT_BILL_AMT/100, :TOT_BILL_AMT/100*-1)"  1>>loaderhdr.ctl
 echo  , ESTABLISH_NO position(106:116)           1>>loaderhdr.ctl
 echo  , INS_CODE position(117:121)               1>>loaderhdr.ctl
 echo  , INS_TYPE position(122:122)               1>>loaderhdr.ctl
 echo  )                                          1>>loaderhdr.ctl
 call loaddatahdr.bat
)

M:\>echo Here I am!
Here I am!

M:\>pause
Press any key to continue . . .

M:\>(
echo Load Data  1>loaderhdr.ctl
 echo INFILE 'test2.csv'  1>>loaderhdr.ctl
 echo append into table INTR_INS_BRET_HDR WHEN (1:1) = 'H'  1>>loaderhdr.ctl
 echo  ( TRANS_ID "intr_ins_bret_hdr_seq.nextval"  1>>loaderhdr.ctl
 echo  , PROCESS_DATE "to_date(SYSDATE)"           1>>loaderhdr.ctl
 echo  , REC_TYPE position(1:1)                   1>>loaderhdr.ctl
 echo  , VERSION_NO position(2:4)                 1>>loaderhdr.ctl
 echo  , FILE_NAME position(5:44)                 1>>loaderhdr.ctl
 echo  , FILE_DATE position(45:52)                1>>loaderhdr.ctl
 echo  , FILE_NO position(53:54)                  1>>loaderhdr.ctl
 echo  , PROCESS_TYPE position(55:55)             1>>loaderhdr.ctl
 echo  , MERCHANT_NO position(56:70)              1>>loaderhdr.ctl
 echo  , FILE_FORMAT_ID position(71:74)           1>>loaderhdr.ctl
 echo  , REC_TYPE2 position(75:76)                1>>loaderhdr.ctl
 echo  , BATCH_NO position(77:85)                 1>>loaderhdr.ctl
 echo  , DATE_SENT position(86:91)                1>>loaderhdr.ctl
 echo  , TOT_NO_TRANS position(92:95)             1>>loaderhdr.ctl
 echo  , SIGN_TOT_BILL_AMT position(105:105)      1>>loaderhdr.ctl
 echo  , TOT_BILL_AMT position(96:104) "decode(:sign_tot_bill_amt, '+', :TOT_BILL_AMT/100, :TOT_BILL_AMT/100*-1)"  1>>loaderhdr.ctl
 echo  , ESTABLISH_NO position(106:116)           1>>loaderhdr.ctl
 echo  , INS_CODE position(117:121)               1>>loaderhdr.ctl
 echo  , INS_TYPE position(122:122)               1>>loaderhdr.ctl
 echo  )                                          1>>loaderhdr.ctl
 call loaddatahdr.bat
)

M:\>echo Here I am!
Here I am!

M:\>pause
Press any key to continue . . .

M:\>

LOADERHDR.CTL:

Load Data 
INFILE 'test2.csv' 
append into table INTR_INS_BRET_HDR WHEN (1:1) = 'H' 
 ( TRANS_ID "intr_ins_bret_hdr_seq.nextval" 
 , PROCESS_DATE "to_date(SYSDATE)"          
 , REC_TYPE position(1:1)                  
 , VERSION_NO position(2:4)                
 , FILE_NAME position(5:44)                
 , FILE_DATE position(45:52)               
 , FILE_NO position(53:54)                 
 , PROCESS_TYPE position(55:55)            
 , MERCHANT_NO position(56:70)             
 , FILE_FORMAT_ID position(71:74)          
 , REC_TYPE2 position(75:76)               
 , BATCH_NO position(77:85)                
 , DATE_SENT position(86:91)               
 , TOT_NO_TRANS position(92:95)            
 , SIGN_TOT_BILL_AMT position(105:105)     
 , TOT_BILL_AMT position(96:104) "decode(:sign_tot_bill_amt, '+', :TOT_BILL_AMT/100, :TOT_BILL_AMT/100*-1)" 
 , ESTABLISH_NO position(106:116)          
 , INS_CODE position(117:121)              
 , INS_TYPE position(122:122)              
 )

Now, I'm not loading anything so there might be an error in the control file itself, but - as far as the batch scripting is concerned - that seems to be OK.

Before posting your next message, please, have a look here which will teach you how to properly post your code (and make it easier to read).

[Updated on: Tue, 10 July 2012 05:16]

Report message to a moderator

Re: SQLLDR [message #560145 is a reply to message #560061]

Tue, 10 July 2012 17:13

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Quote:

SQL*Loader-00503: Error appending extension to file (string)
Cause: SQL*Loader could not append the default extension to create the filename. The given name could be too long or contain illegal characters.
Action: Check the specified filename.

Oracle cannot handle some of the long names and spaces and such in Windows file names. If you use dir/x in Windows, you will get a list of substitute file names that can be used with Oracle. Typically, they consist of the first 6 acceptable characters followed by ~ followed by a number.

Report message to a moderator

Re: SQLLDR [message #560146 is a reply to message #560052]

Tue, 10 July 2012 21:47

bhibe_17
Messages: 24
Registered: July 2012

Junior Member

hi littlefoot,
thanks for your response. in the example that you gave, there's a textfile named infile.txt that contains all the control files to be loaded.
what i need to do is i have a folder named INFILE in C:\BRET, and inside the INFILE folder are the control files that will be used as INFILE.
ex:
location: C:\BRET\INFILE
inside C:\BRET\INFILE:
test1.csv
test2.csv
test3.csv
test4.csv
.
.
.
test10.csv

how can i go about this? Thanks!

Report message to a moderator

Re: SQLLDR [message #560152 is a reply to message #560146]

Wed, 11 July 2012 00:04

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I suppose that it is exactly what I did (only that my directory name is different).

By the way, did you see what Barbara said?

Report message to a moderator

Re: SQLLDR [message #560157 is a reply to message #560152]

Wed, 11 July 2012 00:48

bhibe_17
Messages: 24
Registered: July 2012

Junior Member

Hi littlefoot, Barbara,
i write this code and double-click the BAT File and it worked! Smile

Really appreciate your help guys! Big thanks to both of you! Smile

@echo off
for /r c:\bret\infile %%g in (*.BRET) Do (
echo Load Data> loaderhdr.ctl
echo INFILE '%%g'>> loaderhdr.ctl
echo append into table INTR_INS_BRET_HDR WHEN (1:1^) = "H" >> loaderhdr.ctl
echo (TRANS_ID "intr_ins_bret_hdr_seq.nextval" >> loaderhdr.ctl
echo , PROCESS_DATE "to_date(SYSDATE)" >> loaderhdr.ctl
echo , REC_TYPE position(1:1^) >> loaderhdr.ctl
echo , VERSION_NO position(2:4^) >> loaderhdr.ctl
echo , FILE_NAME position(5:44^) >> loaderhdr.ctl
echo , FILE_DATE position(45:52^) >> loaderhdr.ctl
echo , FILE_NO position(53:54^) >> loaderhdr.ctl
echo , PROCESS_TYPE position(55:55^) >> loaderhdr.ctl
echo , MERCHANT_NO position(56:70^) >> loaderhdr.ctl
echo , MERCHANT_NAME "func_merchant_name(:MERCHANT_NO)" >> loaderhdr.ctl
echo , FILE_FORMAT_ID position(71:74^) >> loaderhdr.ctl
echo , REC_TYPE2 position(75:76^) >> loaderhdr.ctl
echo , BATCH_NO position(77:85^) >> loaderhdr.ctl
echo , DATE_SENT position(86:91^) >> loaderhdr.ctl
echo , TOT_NO_TRANS position(92:95^) >> loaderhdr.ctl
echo , SIGN_TOT_BILL_AMT position(105:105^) >> loaderhdr.ctl
echo , TOT_BILL_AMT position(96:104^) "decode(:sign_tot_bill_amt, '+', :TOT_BILL_AMT/100, :TOT_BILL_AMT/100*-1)" >> loaderhdr.ctl
echo , ESTABLISH_NO position(106:116^) >> loaderhdr.ctl
echo , INS_CODE position(117:121^) >> loaderhdr.ctl
echo , INS_TYPE position(122:122^) >> loaderhdr.ctl
echo ^) >> loaderhdr.ctl
call loaddatahdr.bat
)

Report message to a moderator

Re: SQLLDR [message #560273 is a reply to message #560157]

Wed, 11 July 2012 11:48

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Alternatively, you can specify the file name in the SQL*Loader command line, instead of in the control file. So, all you would need is one control file without the infile, and one bat file with your SQL*Loader command including the data file, something like:

for /r c:\bret\infile %%g in (*.BRET) Do (
  sqlldr scott/tiger control=loaderhdr.ctl data='%%g' log=c:\my_oracle_files\test.log
 )

Report message to a moderator

Re: SQLLDR [message #561162 is a reply to message #560273]

Thu, 19 July 2012 04:16

bhibe_17
Messages: 24
Registered: July 2012

Junior Member

hi thanks for your suggestion, appreciate it! Razz

Report message to a moderator

Previous Topic:	How to pass null values in OracleCommand as a parameters
Next Topic:	Stuck at configuration process

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 11 15:05:42 CST 2024