Home » RDBMS Server » Server Utilities » SQL*Loader-485 (oracle11g solaris10)
SQL*Loader-485 [message #366275] Fri, 12 December 2008 10:20 Go to next message
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 #366295 is a reply to message #366275] Fri, 12 December 2008 12:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
I don't see any problems with what you posted, although the extensions unl and ctrl are a bit unusual, rather than the usual txt or dat and ctl. You might try changing those. 485 isn't even listed in the documentation. Can you post a create table statement for your table and a few lines of sample data from your text file?
Re: SQL*Loader-485 [message #375996 is a reply to message #366295] Mon, 15 December 2008 10:24 Go to previous messageGo to next message
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 #376001 is a reply to message #375996] Mon, 15 December 2008 11:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can you post a create table statement for your table

Quote:
Here is the table definition

This is not what Barbara asked.

Quote:
Thanks for your assistance.

If you want efficient assistance, post what is asked.
Also read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: SQL*Loader-485 [message #376012 is a reply to message #376001] Mon, 15 December 2008 11:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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 #376050 is a reply to message #376046] Mon, 15 December 2008 15:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
I am also using Oracle 11g, but on Windows Vista. I am completely baffled by such a simple test producing an undocumented error on your system. Have you ever gotten SQL*Loader to work at all on your system? It sounds like there may be something basically wrong with your setup and maybe time to contact support. I see that you are executing through SQL*Plus, which should work. Have you tried executing from the operating system, just to eliminate one level of potential problems?


Re: SQL*Loader-485 [message #376053 is a reply to message #376050] Mon, 15 December 2008 15:44 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Metalink Note 743937.1

Seems it is time to remember the golden rule in Oracle RDBMS.
Do not mess with dual table.

[Updated on: Mon, 15 December 2008 15:45]

Report message to a moderator

Re: SQL*Loader-485 [message #431419 is a reply to message #366275] Tue, 17 November 2009 05:08 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #431434 is a reply to message #366275] Tue, 17 November 2009 05:47 Go to previous messageGo to next message
rstones
Messages: 7
Registered: November 2009
Junior Member
Hi Thomas

How do I find Metalink note 743937.1?

Rgds

Bob
Re: SQL*Loader-485 [message #431442 is a reply to message #431434] Tue, 17 November 2009 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.google.com/search?hl=en&source=hp&q=My+Oracle+Support&aq=f&oq=&aqi=g10

Regards
Michel
Re: SQL*Loader-485 [message #431446 is a reply to message #366275] Tue, 17 November 2009 06:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 #431453 is a reply to message #431446] Tue, 17 November 2009 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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.

Welcome to Oracle Support World. ./fa/1686/0/

Regards
Michel
Re: SQL*Loader-485 [message #431456 is a reply to message #366275] Tue, 17 November 2009 06:53 Go to previous messageGo to next message
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 #431457 is a reply to message #431456] Tue, 17 November 2009 06:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>another in a user account
Just drop the one in user account.
Re: SQL*Loader-485 [message #431458 is a reply to message #366275] Tue, 17 November 2009 06:55 Go to previous messageGo to next message
rstones
Messages: 7
Registered: November 2009
Junior Member
Allow me to modify my last post,

Can I just drop the user account version?

Rgds

Bob
Re: SQL*Loader-485 [message #431459 is a reply to message #366275] Tue, 17 November 2009 06:55 Go to previous messageGo to next message
rstones
Messages: 7
Registered: November 2009
Junior Member
Ok, got it, thanks.
Re: SQL*Loader-485 [message #431463 is a reply to message #366275] Tue, 17 November 2009 07:07 Go to previous message
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
Previous Topic: SQL Loader loads all fields with double quotes into staging table
Next Topic: SQL*Loader and NULL value
Goto Forum:
  


Current Time: Sun Dec 04 12:38:41 CST 2016

Total time taken to generate the page: 0.07769 seconds