Home » RDBMS Server » Server Utilities » SQL*Loader-485 (oracle11g solaris10)
| SQL*Loader-485 [message #366275] |
Fri, 12 December 2008 10:20  |
Jack_zhai
Messages: 12
Registered: February 2006
|
Junior Member |
|
|
I tried to use sqlldr to load test data into oracle table.
Here is the content of the control file:
load data
infile 'tax_i00462.unl'
into table tax_invoice
fields terminated by ","
(
tinv_invoice_date,
tinv_invoice_id,
u_version,
tinv_type,
tinv_orig_inv_date,
tinv_orig_inv_id,
carr_abn,
tinv_total_amt,
tinv_total_gst,
cust_abn,
tinv_cust_name,
tinv_addr_1,
tinv_addr_2,
tinv_state,
tinv_post_code,
tinv_adjust_reason,
superseded_by_inv_date,
superseded_by_inv_id)
Here is the command I used:
sqlldr orion/orion control=tax_invoice.ctrl
Here is the message I got from the prompt:
SQL*Loader: Release 11.1.0.6.0 - Production on Fri Dec 12 07:32:35 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL*Loader-485: table TAX_INVOICE may not be loaded using a subtype
I checked the table and no record loaded in.
The data file tax_i00462.unl is text file.
Thanks,
Jack
|
|
|
|
|
|
| Re: SQL*Loader-485 [message #375996 is a reply to message #366295] |
Mon, 15 December 2008 10:24  |
Jack_zhai
Messages: 12
Registered: February 2006
|
Junior Member |
|
|
Hi Barbara,
Thanks for your assistance.
Here is the table definition:
SQL> desc tax_invoice
Name Null? Type
----------------------------------------- -------- ----------------------------
TINV_INVOICE_DATE NOT NULL DATE
TINV_INVOICE_ID NOT NULL NUMBER(10)
U_VERSION CHAR(1)
TINV_TYPE CHAR(1)
TINV_ORIG_INV_DATE DATE
TINV_ORIG_INV_ID NUMBER(10)
CARR_ABN CHAR(11)
TINV_TOTAL_AMT NUMBER(7,2)
TINV_TOTAL_GST NUMBER(6,2)
CUST_ABN CHAR(11)
TINV_CUST_NAME VARCHAR2(60)
TINV_ADDR_1 VARCHAR2(255)
TINV_ADDR_2 VARCHAR2(20)
TINV_STATE CHAR(3)
TINV_POST_CODE CHAR(4)
TINV_ADJUST_REASON CHAR(4)
SUPERSEDED_BY_INV_DATE DATE
SUPERSEDED_BY_INV_ID NUMBER(10)
Here are the four lines I tested and got the error message:
11/06/2007,3,',I,,,47564947264,334.0,33.4,,,,,,,TEXC,,,
12/04/2007,4,,I,,,47564947264,118.0,11.8,,,,,,,CINV,,,
12/06/2007,5,,I,,,47564947264,692.0,69.2,,,,,,,CINV,,,
12/06/2007,6,,I,,,47564947264,553.6,55.36,,,,,,,CINV,,,
Thanks,
Jack
|
|
|
|
|
|
| Re: SQL*Loader-485 [message #376012 is a reply to message #376001] |
Mon, 15 December 2008 11:59 |
Jack_zhai
Messages: 12
Registered: February 2006
|
Junior Member |
|
|
Here is the statement for creating the table:
create table tax_invoice
(
tinv_invoice_date date not null ,
tinv_invoice_id integer not null ,
u_version char(1),
tinv_type char(1),
tinv_orig_inv_date date,
tinv_orig_inv_id integer,
carr_abn char(11),
tinv_total_amt decimal(7,2),
tinv_total_gst decimal(6,2),
cust_abn char(11),
tinv_cust_name varchar(60),
tinv_addr_1 varchar(255),
tinv_addr_2 varchar(20),
tinv_state char(3),
tinv_post_code char(4),
tinv_adjust_reason char(4),
superseded_by_inv_date date,
superseded_by_inv_id integer
);
Hopefully this time I gave what you want!
Thanks,
Jack
|
|
|
|
| Re: SQL*Loader-485 [message #376014 is a reply to message #376012] |
Mon, 15 December 2008 12:17 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
C:\>sqlplus michel/michel
SQL*Plus: Release 10.2.0.4.0 - Production on Lun. DÚc. 15 19:14:01 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> create table tax_invoice
2 (
3 tinv_invoice_date date not null ,
4 tinv_invoice_id integer not null ,
5 u_version char(1),
6 tinv_type char(1),
7 tinv_orig_inv_date date,
8 tinv_orig_inv_id integer,
9 carr_abn char(11),
10 tinv_total_amt decimal(7,2),
11 tinv_total_gst decimal(6,2),
12 cust_abn char(11),
13 tinv_cust_name varchar(60),
14 tinv_addr_1 varchar(255),
15 tinv_addr_2 varchar(20),
16 tinv_state char(3),
17 tinv_post_code char(4),
18 tinv_adjust_reason char(4),
19 superseded_by_inv_date date,
20 superseded_by_inv_id integer
21 );
Table created.
SQL> host sqlldr michel/michel control=t.ctl data=t.dat
SQL*Loader: Release 10.2.0.4.0 - Production on Lun. DÚc. 15 19:13:50 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 4
SQL> select * from tax_invoice;
TINV_INVOICE_DATE TINV_INVOICE_ID U T TINV_ORIG_INV_DATE TINV_ORIG_INV_ID CARR_ABN TINV_TOTAL_AMT TINV_TOTAL_GST
------------------- --------------- - - ------------------- ---------------- ----------- -------------- --------------
CUST_ABN TINV_CUST_NAME
----------- ------------------------------------------------------------
TINV_ADDR_1
------------------------------------------------------------------------------------------------------------------------
TINV_ADDR_2 TIN TINV TINV SUPERSEDED_BY_INV_D SUPERSEDED_BY_INV_ID
-------------------- --- ---- ---- ------------------- --------------------
11/06/2007 00:00:00 3 ' I 47564947264 334 33.4
TEXC
12/04/2007 00:00:00 4 I 47564947264 118 11.8
CINV
12/06/2007 00:00:00 5 I 47564947264 692 69.2
CINV
12/06/2007 00:00:00 6 I 47564947264 553.6 55.36
CINV
4 rows selected.
Everything is fine (assuming you have a default date format compatible with the one in the data file).
Maybe you try to load in a schema that does not contain the table.
Regards
Michel
|
|
|
|
| Re: SQL*Loader-485 [message #376029 is a reply to message #376014] |
Mon, 15 December 2008 13:39 |
Jack_zhai
Messages: 12
Registered: February 2006
|
Junior Member |
|
|
Hi Michel,
Just now I tried to load another table which didn't have date as data type and got the same error message. Therefore I don't think it was date type compatibility issue.
By the way, what is the content of your control file for loading the data I supplied?
Thanks,
Jack
|
|
|
|
| Re: SQL*Loader-485 [message #376040 is a reply to message #376012] |
Mon, 15 December 2008 14:07 |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I also don't get any errors when running what you provided. In addition to the lack of date format, the first row of data with a single quote looks strange. You might try testing without that row to narrow the problem down. In trying to make sense of the error message, it seems like Oracle is either thinking that you are trying to use a subtype when you should not or not using a subtype when you should. The fact that you have the word type in some of your column names makes me suspicious. I am thinking that either there is an object type with a relationship to the table you are loading or some type with the same name as the table or one of the columns that is creating some sort of conflict. I would try putting just the empty table in a new schema and testing with that, making sure you specify the schema name, to see if you still get an error. I am assuming that you have provided full information and haven't, for example, left out any table columns that you are not trying to load into that are user-defined types or some such thing. Some times, in trying to simplify the problem and not provide irrelevant information, people inadvertently leave out the part that is causing the problem.
[Updated on: Mon, 15 December 2008 14:10] Report message to a moderator |
|
|
|
| Re: SQL*Loader-485 [message #376046 is a reply to message #376040] |
Mon, 15 December 2008 14:54 |
Jack_zhai
Messages: 12
Registered: February 2006
|
Junior Member |
|
|
Hi,
This time, I tried a very simple example.
SQL> create table zcw (name char(10));
Table created.
SQL> !vi zcw.dat
"zcw.dat" [New file]
ok
jj
jjj
llk
SQL> !vi zcw.ctl
"zcw.ctl" [New file]
load data
infile 'zcw.dat'
into table zcw
(name)
SQL> !sqlldr orion/orion control=zcw.ctl
SQL*Loader: Release 11.1.0.6.0 - Production on Mon Dec 15 12:49:43 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL*Loader-485: table ZCW may not be loaded using a subtype
Over here, I created a table zcw with one column, but still got the same error message.
I am not sure which version you guys tried. My version is 11g.
Thanks,
Jack
|
|
|
|
| Re: SQL*Loader-485 [message #376049 is a reply to message #376046] |
Mon, 15 December 2008 15:27 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Are you running RAC?
Maybe SQL*Loader is telling you it's Oracle error 485 vs. SQL*Loader error 485.
Oracle error 485 is
FOO SCOTT>!oerr ora 485
00485, 00000, "DIAG process terminated with error %s"
// *Cause: A global diagnostic process died
// *Action: Wait for process to restart
|
|
|
|
|
|
|
|
| Re: SQL*Loader-485 [message #431419 is a reply to message #366275] |
Tue, 17 November 2009 05:08 |
rstones
Messages: 7
Registered: November 2009
|
Junior Member |
|
|
Hi All
I'm getting the same error on an older 10g installation running on RHEL5. Did you get to the bottom of it Jack?
I'm curious as to the meaning of the post remarking on the dual table . . . What does this mean, I have accessed the dual table in a read only way in the past?
Rgds
Bob
|
|
|
|
| Re: SQL*Loader-485 [message #431426 is a reply to message #431419] |
Tue, 17 November 2009 05:19 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Have you read Metalink note 743937.1?
( Or should we start calling them "My Oracle Support" Notes? )
|
|
|
|
|
|
|
|
| Re: SQL*Loader-485 [message #431446 is a reply to message #366275] |
Tue, 17 November 2009 06:32 |
rstones
Messages: 7
Registered: November 2009
|
Junior Member |
|
|
Yes, tried that and got a server down/connection error.
I guess what you're telling me is that the Metalink service is actually part of Oracle's support infrastructure. I did try registering but only got so far before a whole series of errors were reported back, site down, connection not available.
At the risk of again being told to RTFM could you give me a summary of what I need to do to fix this as I can't get to the site you recommend.
Sorry if you feel you are being asked to re-write stuff plese be patient with a very new user
Thanks again
Rgds
Bob
|
|
|
|
| Re: SQL*Loader-485 [message #431451 is a reply to message #431446] |
Tue, 17 November 2009 06:38 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Oracle tends to break if you have a table somewhere named DUAL beside the system one in the sys schema.
If you run
SELECT * FROM all_objects WHERE object_name = 'DUAL'
You should have only two lines, the table with owner sys and a public synonym for it, nothing else.
|
|
|
|
| Re: SQL*Loader-485 [message #431452 is a reply to message #431446] |
Tue, 17 November 2009 06:39 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
A hint was given already.
>>Do not mess with dual table.
The "DUAL" is a very special object in Oracle owned by SYS.
First check if you have a local object named similarly.
Edit:
And ThomasG was faster.
[Updated on: Tue, 17 November 2009 06:39] Report message to a moderator |
|
|
|
|
|
| Re: SQL*Loader-485 [message #431456 is a reply to message #366275] |
Tue, 17 November 2009 06:53 |
rstones
Messages: 7
Registered: November 2009
|
Junior Member |
|
|
Hi All
I've got THREE!!, One in sys, one in public and another in a user account. can I just drop the user and public versions?
Rgds
Bob
|
|
|
|
|
|
|
|
|
|
| Re: SQL*Loader-485 [message #431463 is a reply to message #366275] |
Tue, 17 November 2009 07:07 |
rstones
Messages: 7
Registered: November 2009
|
Junior Member |
|
|
YES!! Many, many thanks. With just the sys and public versions of DUAL it all works fine. I even found where someone had created the offending DUAL table in the reset scripts for the system.
Rgds
Bob
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 13:07:06 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
