Home » RDBMS Server » Server Utilities » SQL LOADER (10g)
SQL LOADER [message #479309] Fri, 15 October 2010 17:46 Go to next message
aleek
Messages: 6
Registered: October 2010
Junior Member

Hello Everobody!
I have a problem with loading data using SQL loader.

I have such control file
LOAD DATA
INFILE 'D:\oracle\FIRMAS.txt'
INSERT INTO TABLE FIRMAS
FIELDS TERMINATED BY ","
(FIRMAS_REG_NR char,FIRMAS_NOS char,ID_PRODUCT char)

and data file "firmas.txt"
444557562, Avotoni SIA, 1332
445575627, Avotoni SIA, 1332
444557562, Avotoni SIA, 1332

When I try to execut this command
sqlldr userid=system/a1331 control=d:\oracle\firmas.ctrl an error occured (sql*loader-350) what should I do?
thank you
Re: SQL LOADER [message #479310 is a reply to message #479309] Fri, 15 October 2010 17:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

post DDL (CREATE TABLE FIRMAS) for table
Re: SQL LOADER [message #479311 is a reply to message #479310] Fri, 15 October 2010 18:03 Go to previous messageGo to next message
aleek
Messages: 6
Registered: October 2010
Junior Member

CREATE TABLE FIRMAS
(FIRMAS_REG_NR VARCHAR2(15) CONSTRAINT PK_FREG PRIMARY KEY,
FIRMAS_NOS VARCHAR2(25) CONSTRAINT NN_FNOS NOT NULL,
ID_PRODUCT VARCHAR(15),
CONSTRAINT FS_FK_PRID FOREIGN KEY(ID_PRODUCT) REFERENCES PRODUCTS (PRODUCT_ID)
ON DELETE CASCADE)
CLUSTER KLASTERIS1 (ID_PRODUCT)
Re: SQL LOADER [message #479312 is a reply to message #479311] Fri, 15 October 2010 18:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
CREATE TABLE firmas
  (
     firmas_reg_nr VARCHAR2(15) constraint pk_freg primary key,
     firmas_nos    VARCHAR2(25) constraint nn_fnos NOT NULL,
     id_product    VARCHAR(15),
     constraint fs_fk_prid foreign key(id_product) references products (
     product_id) ON DELETE CASCADE
  )
CLUSTER klasteris1 (id_product)  

In order to complete test case, need DDL for table PRODUCTS & DML (INSERT) for FK records
Re: SQL LOADER [message #479313 is a reply to message #479312] Fri, 15 October 2010 18:16 Go to previous messageGo to next message
aleek
Messages: 6
Registered: October 2010
Junior Member

CREATE TABLE PRODUCTS
(
PRODUCT_ID VARCHAR2(15) CONSTRAINT PK_PRID PRIMARY KEY,
PRODUCT_NOS VARCHAR2(30) CONSTRAINT NN_FPRO NOT NULL,
PRODUCT_MASA NUMBER DEFAULT 0,
PRODUCT_CENA NUMBER DEFAULT 0,
PIEGADES_ID_1 VARCHAR2(15) REFERENCES PIEGADE(PIEGADES_ID))
CLUSTER KLASTERIS2 (PIEGADES_ID_1)
and it is empty now
Re: SQL LOADER [message #479314 is a reply to message #479312] Fri, 15 October 2010 18:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Also, you should not be creating tables in the system schema. You should either use userid=user/password for whatever schema you are loading the table into or use into table user.firmas for the proper schema in the control file.

Also, there should be additional information beyond just sql*loader-350 as indicated by the documentation:

"SQL*Loader-00350: Syntax error at line number.
Cause: num identifies the line in the SQL*Loader control file at which the error occurred. This message is followed by another of the form -- Expecting str1, found str2 where str1 is a description of what SQL*Loader expected in the SQL*Loader control file, and str2 is what was found. Then SQL*Loader displays the offending line from the SQL*Loader control file, indicating the location of the error in the line by a carat (^) or an asterisk (*). An example follows: -- SQL*Loa er-350: Syntax error at line 28 -- Expecting column name, found keyword CHAR -- col3 ENCLOSED BY '"', CHAR ENCLOSED "'",
Action: Compare the DDL syntax against the syntax diagrams in Oracle7 Server Utilities and make any necessary corrections."

Also, is what you posted the actual code or a simplified version? Sometimes what is left out of a simplified post is what is causing the problem.
Re: SQL LOADER [message #479315 is a reply to message #479313] Fri, 15 October 2010 18:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
it works for me (with slight alterations)
SQL> @firmas
SQL> DROP TABLE PRODUCTS;
DROP TABLE PRODUCTS
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> CREATE TABLE PRODUCTS
  2  (
  3  PRODUCT_ID VARCHAR2(15) CONSTRAINT PK_PRID PRIMARY KEY,
  4  PRODUCT_NOS VARCHAR2(30) CONSTRAINT NN_FPRO NOT NULL,
  5  PRODUCT_MASA NUMBER DEFAULT 0,
  6  PRODUCT_CENA NUMBER DEFAULT 0)
  7  /

Table created.

SQL> drop table firmas;
drop table firmas
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> CREATE TABLE FIRMAS
  2  (FIRMAS_REG_NR VARCHAR2(15) CONSTRAINT PK_FREG PRIMARY KEY,
  3  FIRMAS_NOS VARCHAR2(25) CONSTRAINT NN_FNOS NOT NULL,
  4  ID_PRODUCT VARCHAR(15),
  5  CONSTRAINT FS_FK_PRID FOREIGN KEY(ID_PRODUCT) REFERENCES PRODUCTS (PRODUCT_ID)
  6  ON DELETE CASCADE)
  7  /

Table created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bcm@bcm-laptop:~$ sqlldr user1/user1 control=firmas.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Oct 15 16:21:04 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3
bcm@bcm-laptop:~$ cat firmas.ctl
LOAD DATA
INFILE 'firmas.txt'
INSERT INTO TABLE FIRMAS
FIELDS TERMINATED BY ","
(FIRMAS_REG_NR char,FIRMAS_NOS char,ID_PRODUCT char)


If you require additional assistance, post ALL steps as I have done above
Re: SQL LOADER [message #479316 is a reply to message #479315] Fri, 15 October 2010 18:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It reads 3 records, but none are inserted, because you cannot insert records into a child table with an empty parent table. You need to insert into the parent tables first or drop or disable or defer the constraints or some such thing. However, this would not be sufficient to cause the error. I have not tested with the clustering.


Re: SQL LOADER [message #479317 is a reply to message #479316] Fri, 15 October 2010 18:34 Go to previous messageGo to next message
aleek
Messages: 6
Registered: October 2010
Junior Member

in cmd i see only error number because i have oracle installed in russian, and only misunderstandable symbols appears
Re: SQL LOADER [message #479318 is a reply to message #479317] Fri, 15 October 2010 18:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please copy and paste and post the complete error message including whatever symbols appear.
Re: SQL LOADER [message #479319 is a reply to message #479318] Fri, 15 October 2010 18:52 Go to previous messageGo to next message
aleek
Messages: 6
Registered: October 2010
Junior Member

Here is

[Updated on: Fri, 15 October 2010 18:55]

Report message to a moderator

Re: SQL LOADER [message #479320 is a reply to message #479319] Fri, 15 October 2010 19:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
In your control file that you posted you have:

INFILE 'D:\oracle\FIRMAS.txt'

but your error message says that you have:

INFILE CD:\oracle\FIRMAS.txtT

That would be one error.
Re: SQL LOADER [message #479321 is a reply to message #479320] Fri, 15 October 2010 19:06 Go to previous messageGo to next message
aleek
Messages: 6
Registered: October 2010
Junior Member

maybe it's because of '' symbols?
Re: SQL LOADER [message #479322 is a reply to message #479321] Fri, 15 October 2010 19:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I am sorry to see that COPY & PASTE are broken for you
Re: SQL LOADER [message #479323 is a reply to message #479309] Fri, 15 October 2010 19:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It could be due to the symbols yes. Let's see if we can start with a simple test case and see if that works. From a SQL*Plus session connected as some user other than system, edit a .sql file, copy and paste the following into it, change scott/tiger to the user and password that you are connected as, save the file, and start it. It will drop any firmas table in that schema and recreate it, so run it in a separate schema from what you have your real stuff in. Then copy and paste and post the results of test.txt and test.log. Note that I also changed ctrl to ctl, which is standard.

store set saved_settings replace
set echo off feedback off heading off pagesize 0 verify off
spool firmas.ctl
prompt LOAD DATA
prompt INFILE *
prompt INSERT INTO TABLE firmas
prompt FIELDS TERMINATED BY ","
prompt trailing nullcols
prompt (FIRMAS_REG_NR char,FIRMAS_NOS char,ID_PRODUCT char)
prompt begindata:
prompt 444557562, Avotoni SIA, 1332
prompt 445575627, Avotoni SIA, 1332
spool off
start saved_settings
spool test.txt
drop table firmas
/
CREATE TABLE FIRMAS
(FIRMAS_REG_NR VARCHAR2(15) CONSTRAINT PK_FREG PRIMARY KEY,
FIRMAS_NOS VARCHAR2(25) CONSTRAINT NN_FNOS NOT NULL,
ID_PRODUCT VARCHAR(15)
)
/
host sqlldr userid=scott/tiger control=firmas.ctl log=test.log
select * from firmas
/
spool off
ed test.txt
ed test.log
Re: SQL LOADER [message #479424 is a reply to message #479309] Sat, 16 October 2010 17:08 Go to previous messageGo to next message
simplesql
Messages: 20
Registered: October 2010
Junior Member
Hi,
i could not help but notice that in your first post you are not giving the log file name or path. Could u repeat the same script declaring teh log file too and checking.....

but to get it clarified are you able to insert recs in the parent table without even declaring the log file??? pls let me know in that it wud help me too...Thnx.
Re: SQL LOADER [message #479425 is a reply to message #479424] Sat, 16 October 2010 17:25 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
A log file is a good practice and it helps with debugging, but it is not required. You can insert without a log file.

At this point, we have determined that what has been posted works for us on our systems. So, either the original poster is running something different or there is something different on his system. We are waiting for a copy and paste of all of the parts of an actual run or of the test script I offered. If we can start with something simple that works, then change or add one piece at a time, then we can determine where the problem is. Thus far we have identified one problem that the parent table is empty, but that would not cause the error. Other common problems are lack of permissions to files and directories and it can make a difference where the file is saved from. I provided the script and means of running it in order to start from a situation where the data is in the control file and that is created from a sql*plus session to the default directory, so there is no problem with privileges. If that runs without error, then I would try moving the data to a file created in the same manner. You could then move the file to its previous location and so on, changing one piece at a time, until you find where the error is.
Previous Topic: Ora 01455
Next Topic: CLOB inserted as varchar2
Goto Forum:
  


Current Time: Thu Mar 28 13:51:36 CDT 2024