Home » RDBMS Server » Server Utilities » sqlldr (merged)
| sqlldr (merged) [message #274599] |
Tue, 16 October 2007 09:50  |
appsoracleuser
Messages: 9
Registered: October 2007
|
Junior Member |
|
|
wanted load data into test_data table using sqlldr
create table test_data(dollar number(6));
data to be loaded is $0.00
which is giving rise to the following error
ORA-00904: "DOLLAR": invalid identifier
SQL*Loader-941: Error during describe of table TEST_DATA
$ sqlldr apps/apps control=/data/a01/apps/atbappl/au/11.5.0/bin/rest.ctl
SQL*Loader: Release 8.0.6.3.0 - Production on Tue Oct 16 10:22:24 2007
(c) Copyright 1999 Oracle Corporation. All rights reserved.
ORA-00904: "DOLLAR": invalid identifier
SQL*Loader-941: Error during describe of table TEST_DATA
can any body help me
[Updated on: Tue, 16 October 2007 10:33] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: sqlldr [message #274618 is a reply to message #274614] |
Tue, 16 October 2007 10:51  |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You can't have the error you say you have with what you posted.
Post reality and not lies and stop wasting our time.
Regards
Michel
[Updated on: Tue, 16 October 2007 10:58] Report message to a moderator |
|
|
|
| Re: sqlldr [message #274655 is a reply to message #274599] |
Tue, 16 October 2007 13:12 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| appsoracleuser wrote on Tue, 16 October 2007 10:50 |
SQL*Loader: Release 8.0.6.3.0 - Production on Tue Oct 16 10:22:24 2007
(c) Copyright 1999 Oracle Corporation. All rights reserved.
|
Just a helpful piece of info that 8.0.6 was desupported September 30, 2001.
|
|
|
|
| sqlldr [message #274663 is a reply to message #274599] |
Tue, 16 October 2007 14:16 |
appsoracleuser
Messages: 9
Registered: October 2007
|
Junior Member |
|
|
this is ctl file
LOAD DATA
INFILE *
INTO TABLE test_data
FIELDS TERMINATED BY ','
(
Test_OC ,
APDat_C ,
TEST_M_Code ,
Test_MGL,
Test_Tcode,
Test_MGL1,
Credit_Acct,
dollar)
BEGINDATA
25206,D,236,101002,C134A,1012,131002,
25206,D,330,131002,NOBUD,1312,610001,
25206,D,081,610001,B302A,6101,219001,
25225,D,060,141099,HB206A,1417,211001,$0.00
25226,D,060,141099,HB206A,1417,211001,$0.00
25227,D,060,141099,HB206A,1417,211001,$0.00
25228,D,060,141099,HB206A,1417,211001,$0.00
25229,D,060,141099,HB206A,1417,211001,$0.00
25230,D,060,141099,HB206A,1417,211001,$0.00
25231,D,060,141099,HB206A,1417,211001,$0.00
25232,D,060,141099,HB206A,1417,211001,$0.00
25233,D,060,141099,HB206A,1417,211001,$0.00
25234,D,060,141099,HB206A,1417,211001,$0.00
25235,D,060,141099,HB206A,1417,211001,$0.00
25236,D,060,141099,HB206A,1417,211001,$0.00
25237,D,060,141099,HB206A,1417,211001,$0.00
and this my log file
Control File: /data_path/test.ctl
Data File: /data_path/test.ctl
Bad File: /data_path/test.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional
Table TEST_DATA, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TEST_OC FIRST * , CHARACTER
APDAT_C NEXT * , CHARACTER
TEST_M_CODE NEXT * , CHARACTER
TEST_MGL NEXT * , CHARACTER
TEST_TCODE NEXT * , CHARACTER
TEST_MGL1 NEXT * , CHARACTER
CREDIT_ACCT NEXT * , CHARACTER
DOLLAR NEXT * , CHARACTER
Record 1: Rejected - Error on table TEST_DATA, column DOLLAR.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table TEST_DATA, column DOLLAR.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table TEST_DATA, column DOLLAR.
Column not found before end of logical record (use TRAILING NULLCOLS ,
,
,
Record 50: Rejected - Error on table TEST_DATA, column DOLLAR.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 51: Rejected - Error on table TEST_DATA, column DOLLAR.
Column not found before end of logical record (use TRAILING NULLCOLS)
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table TEST_DATA:
0 Rows successfully loaded.
51 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: 63984 bytes(31 rows)
Space allocated for memory besides bind array: 0 bytes
Total logical records skipped: 0
Total logical records read: 51
Total logical records rejected: 51
Total logical records discarded: 0
Run began on Tue Oct 16 14:53:50 2007
Run ended on Tue Oct 16 14:53:50 2007
Elapsed time was: 00:00:00.04
CPU time was: 00:00:00.01
can any body help its very urgent.
|
|
|
|
|
|
| Re: sqlldr [message #274675 is a reply to message #274671] |
Tue, 16 October 2007 15:18 |
appsoracleuser
Messages: 9
Registered: October 2007
|
Junior Member |
|
|
Thanks Michel
use TRAILING NULLCOLS had rectified my problem, but the data for the dollar is not uploaded
this is the log file
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional
Table TEST_DATA, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TEST_OC FIRST * , CHARACTER
APDAT_C NEXT * , CHARACTER
TEST_M_CODE NEXT * , CHARACTER
TEST_MGL NEXT * , CHARACTER
TEST_TCODE NEXT * , CHARACTER
TEST_MGL1 NEXT * , CHARACTER
CREDIT_ACCT NEXT * , CHARACTER
DOLLAR NEXT * , CHARACTER
Record 325: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 326: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 327: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 328: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 333: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 343: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 612: Rejected - Error on table TEST_DATA, column DOLLAR.
ORA-01722: invalid number
Record 613: Rejected - Error on table TEST_DATA, column DOLLAR.
ORA-01722: invalid number
Record 614: Rejected - Error on table TEST_DATA, column DOLLAR.
ORA-01722: invalid number
Record 615: Rejected - Error on table TEST_DATA, column DOLLAR.
ORA-01722: invalid number
Record 616: Rejected - Error on table TEST_DATA, column DOLLAR.
ORA-01722: invalid number
Record 617: Rejected - Error on table TEST_DATA, column DOLLAR.
ORA-01722: invalid number
Record 618: Rejected - Error on table TEST_DATA, column DOLLAR.
ORA-01722: invalid number
Record 619: Rejected - Error on table TEST_DATA, column DOLLAR.
ORA-01722: invalid number
Record 631: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 645: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 646: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 650: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 651: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 8700: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 8701: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 8702: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 8703: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 8704: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 8705: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 8706: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 8707: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 8708: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 8969: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 8976: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 8977: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 8978: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 8982: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9042: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9043: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9044: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9045: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9046: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9047: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9048: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9049: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9050: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9051: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9052: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9053: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9054: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9055: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9056: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9057: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9058: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
Record 9059: Rejected - Error on table TEST_DATA, column TEST_M_CODE.
ORA-01722: invalid number
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table TEST_DATA:
9008 Rows successfully loaded.
51 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: 63984 bytes(31 rows)
Space allocated for memory besides bind array: 0 bytes
Total logical records skipped: 0
Total logical records read: 9089
Total logical records rejected: 51
Total logical records discarded: 0
Run began on Tue Oct 16 15:48:36 2007
Run ended on Tue Oct 16 15:48:36 2007
Elapsed time was: 00:00:00.26
CPU time was: 00:00:00.05
can you help me
|
|
|
|
|
|
| Re: sqlldr [message #274678 is a reply to message #274677] |
Tue, 16 October 2007 15:40 |
appsoracleuser
Messages: 9
Registered: October 2007
|
Junior Member |
|
|
my database table desc is here
SQL> desc test_data;
Name Null? Type
----------------------------------------- -------- -----------------
TEST_OC NUMBER(5)
APDAT_C VARCHAR2(1)
TEST_M_CODE NUMBER(10)
TEST_MGL VARCHAR2(6)
TEST_TCODE VARCHAR2(7)
TEST_MGL1 NUMBER(7)
CREDIT_ACCT NUMBER(7)
DOLLAR NUMBER(6)
can you help me now.I checked your answer but it is not entering the dollar column values into the database
[Updated on: Tue, 16 October 2007 15:44] Report message to a moderator |
|
|
|
| Re: sqlldr [message #274679 is a reply to message #274678] |
Tue, 16 October 2007 15:58 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
You didn't read nor view it carefully enough.
Table you've created says thatAt the same time, you are insertingwhich is a character, not a number.
One way to get rid of the dollar sign is to use such a line in the control file:dollar "replace(:dollar, '$', '')"
If you, however, insist on storing the dollar sign ($) into the column, you'll have to alter the table and set the dollar column to be of a VARCHAR2 datatype. It would be and extremely bad idea. Numbers should be stored as numbers, while formatting should be done for reporting purposes using required format masks.
|
|
|
|
|
|
| Re: sqlldr [message #274727 is a reply to message #274683] |
Wed, 17 October 2007 00:39 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Client's requirements are to be met, but not that way.
How do you, for example, plan to meet his requirements when he says "I'd like to know the average value of all 'dollar' values"?
Natural way would beSELECT AVG(dollar) FROM test_data;
Please, execute this statement on CREATE TABLE test_data
(dollar VARCHAR2(10));
INSERT INTO test_data (dollar) VALUES ('$2.00');
Once again: do NOT do such a foolish thing just to meet someone's silly requirements. On a report, he'll have his dollar sign, no problem - there's a format mask you can use to display this numeric value, such as select to_char(dollar, '$999,990.00') formatted_dollar from test_data; |
|
|
|
Goto Forum:
Current Time: Sun Jan 12 02:44:33 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
