Home » RDBMS Server » Server Utilities » sqlldr (merged)
sqlldr (merged) [message #274599] Tue, 16 October 2007 09:50 Go to next message
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 #274600 is a reply to message #274599] Tue, 16 October 2007 09:58 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Why do you have "dollars" in the CREATE TABLE; but error reports "DOLLAR" (no trailing letter "s")?
Re: sqlldr [message #274601 is a reply to message #274600] Tue, 16 October 2007 10:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
With three "L"s, also. Wink
Re: sqlldr [message #274602 is a reply to message #274601] Tue, 16 October 2007 10:03 Go to previous messageGo to next message
appsoracleuser
Messages: 9
Registered: October 2007
Junior Member
I didn't get what your saying
Re: sqlldr [message #274603 is a reply to message #274600] Tue, 16 October 2007 10:07 Go to previous messageGo to next message
appsoracleuser
Messages: 9
Registered: October 2007
Junior Member
Its dollar not dollars
can you help me now
Re: sqlldr [message #274604 is a reply to message #274599] Tue, 16 October 2007 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>can you help me now
I'll try, but since I can't see what you've done; I'll wait until after you post some meaningful information.
Re: sqlldr [message #274612 is a reply to message #274599] Tue, 16 October 2007 10:41 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Post the contents of /data/a01/apps/atbappl/au/11.5.0/bin/test.ctl

Re: sqlldr [message #274614 is a reply to message #274599] Tue, 16 October 2007 10:46 Go to previous messageGo to next message
appsoracleuser
Messages: 9
Registered: October 2007
Junior Member
LOAD DATA
INFILE *
INTO TABLE test_data
(dollar)
BEGINDATA
$0.00
$0.00
$4.00
.
.
.
.
$9.00
$8.00



Re: sqlldr [message #274618 is a reply to message #274614] Tue, 16 October 2007 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
joy_division
Messages: 4644
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 Go to previous messageGo to next message
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 #274671 is a reply to message #274663] Tue, 16 October 2007 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

use TRAILING NULLCOLS

It is above all urgent you read Utilities manual, Part II SQL*Loader.

Regards
Michel

[Updated on: Tue, 16 October 2007 14:58]

Report message to a moderator

Re: sqlldr [message #274675 is a reply to message #274671] Tue, 16 October 2007 15:18 Go to previous messageGo to next message
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 #274677 is a reply to message #274675] Tue, 16 October 2007 15:31 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oracle

ORA-01722 invalid number


Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.


We saw your sample data and control file. But, how does the TABLE look like?
Re: sqlldr [message #274678 is a reply to message #274677] Tue, 16 October 2007 15:40 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You didn't read nor view it carefully enough.

Table you've created says that
DOLLAR NUMBER(6)
At the same time, you are inserting
$0.00
which 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 #274683 is a reply to message #274679] Tue, 16 October 2007 16:23 Go to previous messageGo to next message
appsoracleuser
Messages: 9
Registered: October 2007
Junior Member
Thanks Littlefoot

It worked.what u said is correct but i have to meet client req's
that why i have to use varchar2
Re: sqlldr [message #274727 is a reply to message #274683] Wed, 17 October 2007 00:39 Go to previous message
Littlefoot
Messages: 20901
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 be
SELECT 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');
You'll get the INVALID NUMBER error.

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;
Previous Topic: Encoding Issue on Oracle9i
Next Topic: import using data pump
Goto Forum:
  


Current Time: Sat Dec 10 18:56:11 CST 2016

Total time taken to generate the page: 0.10309 seconds