HELLO ALL

I am trying to set up an SQL*Loader using fixed length files and I get following error:

SQL*Loader-350: Syntax error at line 16.
Expecting valid column specification, "," or ")", found "CDBCR".
CDBCR POSITION (42:43) CHAR, "DECODE(:CDBCR,'CR','D
^

syntax of the above column is as follows:

CDBCR POSITION (42:43) CHAR, "DECODE(:CDBCR,'CR','DR':CDBCR)"

and I tried that syntax too CDBCR CHAR terminated by ',', and I get following error

Expecting valid column specification, "," or ")", found "CDBCR".
CDBCR CHAR terminated by ',',

Could please someone advice of what should the syntax of that column be?

Regards

Dear user!

Please remove the , (coma) behind CHAR.

CDBCR POSITION (42:43) CHAR "DECODE(:CDBCR,'CR','DR':CDBCR)"

If this doesn't remove the error please post your complete SQL*Loader controlfile.

Yours sincerely

Florian W.

P.S. Please enclose scripts or code in {code} tags. One tag in front of your code and one tag behind.

Hi again!

It's like SeánMacGC told you. You forgott three , behind some lines. Please correct your code like that:

Load Data
Replace
 
INTO TABLE RRGSTD
(
IDCP1 POSITION (1:11) CHAR "DECODE(:IDCP1,'GR','ER')",
NCBNK POSITION (12:41) CHAR,
CDBCR POSITION (42:43) CHAR "DECODE(:CDBCR,'CR','DR':CDBCR)",
MTREG POSITION (44:58) DECIMAL EXTERNAL "REPLACE(:MTREG,',','.':MTREG)",
CODEV POSITION (59:61) CHAR,
DAREG POSITION (62:69) DATE 'YYYYMMDD',
DAVAL POSITION (70:77) DATE 'YYYYMMDD',
LIREG POSITION (78:95) CHAR
)

Best Regards

Florian W.

Edited by: Florian W. on 10.06.2009 12:01

Edited by: Florian W. on 10.06.2009 12:23

I think you're missing a ',' in

"DECODE(:CDBCR,'CR','DR':CDBCR)",

Try this:

"DECODE(:CDBCR,'CR','DR',:CDBCR)",

Thanx

I did passed the error and got following log :

SQL*Loader: Release 9.2.0.7.0 - Production on Wed Jun 10 13:12:00 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: C:\NBGLOADER\NBGPAYMENT.CTL
Data File: C:\NBGLOADER\MARG2.TXT
Bad File: C:\NBGLOADER\badData.BAD
Discard File: C:\NBGLOADER\discard.DIS
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 100000
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table RRGSTD, loaded from every logical record.
Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl Datatype

---

----------

---

---- ----

---

IDCP1 1:11 11 CHARACTER
SQL string for column : "DECODE(:IDCP1,'GR','ER')"
NCBNK 12:41 30 CHARACTER
CDBCR 42:43 2 CHARACTER
SQL string for column : "DECODE(:CDBCR,'CR','DR':CDBCR)"
MTREG 44:58 15 CHARACTER
"REPLACE(:MTREG,',','.':MTREG)" NEXT 1 CHARACTER
CODEV 59:61 3 CHARACTER
DAREG 62:69 8 DATE YYYYMMDD
DAVAL 70:77 8 DATE YYYYMMDD
LIREG 78:95 18 CHARACTER

SQL*Loader-466: Column "REPLACE(:MTREG,',','.':MTREG)" does not exist in table RRGSTD.

Still something wrong with REPLACE ( Actually for that column i have -12chars,2decimal chars e.g 000000000012,45 and i want to replace comma with dot

could you please advice on that too???

regards

Florian

sorry to bother u all time and thanx for your assistance but i get the following when running loader.

SQL*Loader: Release 9.2.0.7.0 - Production on Wed Jun 10 13:21:13 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: C:\NBGLOADER\NBGPAYMENT.CTL
Data File: C:\NBGLOADER\MARG2.TXT
Bad File: C:\NBGLOADER\badData.BAD
Discard File: C:\NBGLOADER\discard.DIS
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 100000
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table RRGSTD, loaded from every logical record.
Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl Datatype

---

----------

---

---- ----

---

IDCP1 1:11 11 CHARACTER
SQL string for column : "DECODE(:IDCP1,'GR','ER')"
NCBNK 12:41 30 CHARACTER
CDBCR 42:43 2 CHARACTER
SQL string for column : "DECODE(:CDBCR,'CR','DR':CDBCR)"
MTREG 44:58 15 CHARACTER
SQL string for column : "REPLACE(:MTREG,',','.':MTREG)"
CODEV 59:61 3 CHARACTER
DAREG 62:69 8 DATE YYYYMMDD
DAVAL 70:77 8 DATE YYYYMMDD
LIREG 78:95 18 CHARACTER

Record 1: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Record 2: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Record 3: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Record 4: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Record 5: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Record 6: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Record 7: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Record 8: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Record 9: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Record 185: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Record 186: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Record 187: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

up to

Record 759: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Record 760: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Record 761: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Record 762: Rejected - Error on table RRGSTD, column CDBCR.
ORA-00907: missing right parenthesis

Table RRGSTD:
0 Rows successfully loaded.
762 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 7296 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 762
Total logical records rejected: 762
Total logical records discarded: 0

Run began on Wed Jun 10 13:21:13 2009
Run ended on Wed Jun 10 13:21:19 2009

Elapsed time was: 00:00:05.44
CPU time was: 00:00:00.22

Please advice.

Regards

Hello again

Did replace 2 lines but get following error

Record 1: Rejected - Error on table RRGSTD, column MTREG.
ORA-00939: too many arguments for function

If I ommit commas THUS, MTREG POSITION (44:58) DECIMAL EXTERNAL "REPLACE(:MTREG,',','.':MTREG)", then i get

Record 1: Rejected - Error on table RRGSTD, column MTREG.
ORA-00907: missing right parenthesis

please advice

regards

Hope

We come to an end cause I did took a lot of your time.

I did the replace and got the following

Record 1: Rejected - Error on table RRGSTD, column MTREG.
ORA-01722: invalid number

Please advice

Regards