OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » SQL*Loader-941

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL*Loader-941 [message #166621]

Fri, 07 April 2006 04:45

ptgeorge
Messages: 19
Registered: April 2005

Junior Member

Anyone seen this error ?

SQL*Loader-941: Error during describe of table TEMP_BILLED
ORA-03114: not connected to ORACLE

Report message to a moderator

Re: SQL*Loader-941 [message #166641 is a reply to message #166621]

Fri, 07 April 2006 06:23

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

ORA-03114: not connected to ORACLE
Your sqlldr session is not 'seeing' your database

start your database first
or
Fix your tnsentries (if your are using any)

Report message to a moderator

Re: SQL*Loader-941 [message #166650 is a reply to message #166641]

Fri, 07 April 2006 06:54

ptgeorge
Messages: 19
Registered: April 2005

Junior Member

Thanks, but I forgot to mention that I am able to log on through sqlplus. It's only sqlldr that has this problem. If it is a tns issue shouldn't sqlplus not work either ?

Report message to a moderator

Re: SQL*Loader-941 [message #166651 is a reply to message #166650]

Fri, 07 April 2006 06:58

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

are you using sqlldr and sqlplus from the same machine?
Please post your session.

Report message to a moderator

Re: SQL*Loader-941 [message #166661 is a reply to message #166651]

Fri, 07 April 2006 07:29

ptgeorge
Messages: 19
Registered: April 2005

Junior Member

Both sqlldr and sqlplus are run from the same script. The sqlplus works.

sqlplus $DB_USER/$DB_PASSWORD << eof
set serveroutput on ;
select 'test' from dual ;
eof

sqlldr  userid=$DB_USER/$DB_PASSWORD \
            control=$CTLFile \
            data=$InputDir/$File \
            log=$LogFile \
            bad=$BadFile \
            discard=$DiscardFile \
            ROWS=500 \
            ERRORS=999999

The output as follows

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> SQL>
'TEST'
--------------------------------
test

SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL*Loader: Release 9.2.0.6.0 - Production on    7 19:16:00 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL*Loader-941: Error during describe of table TEMP_UNBILLED
ORA-03114: not connected to ORACLE

The call to the sqlldr is followed by a PL/SQL script working on the same table which also executes properly. This is a problem happening during implementation. It has worked fine on other servers

This is an issue with all scripts using sqlldr not specific to this one alone

[Updated on: Fri, 07 April 2006 07:56]

Report message to a moderator

Re: SQL*Loader-941 [message #166666 is a reply to message #166661]

Fri, 07 April 2006 07:55

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You are not issuing any pl/sql here
And it works for me.
Something is wrong with your environment.
How do you pass the username/password?

oracle@mutation#cat somescript
export DB_USER=scott
export DB_PASSWORD=tiger
sqlplus -s $DB_USER/$DB_PASSWORD << eof
set serveroutput on ;
select 'test' from dual ;
eof
sqlldr  userid=$DB_USER/$DB_PASSWORD control=dept.ctl
oracle@mutation#somescript

'TES
----
test


SQL*Loader: Release 9.2.0.4.0 - Production on Fri Apr 7 08:54:45 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 6

Report message to a moderator

Re: SQL*Loader-941 [message #166670 is a reply to message #166666]

Fri, 07 April 2006 08:06

ptgeorge
Messages: 19
Registered: April 2005

Junior Member

Yes I haven't pasted the following pl/sql block, but it is executing it anyway. The username/password are set as environment variables. The same way you've specified it

export DB_USER=scott

Like I'd said this is happening only on one server, it is working fine everywhere else(HP-UX OS and a non-english DB incidentally)

So is it possible that it is a problem with sqlldr itself ?

Report message to a moderator

Re: SQL*Loader-941 [message #166673 is a reply to message #166670]

Fri, 07 April 2006 08:23

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

May be. I have no idea.
In a wild guess, did you check the existence of the table?
Is that a table or view?
If so try this.
create table sometable_from_view as select * from myview;
use sqlldr to load into some_table_from_view;
insert into myview (select * from some_table_from_view);

Report message to a moderator

Re: SQL*Loader-941 [message #166679 is a reply to message #166673]

Fri, 07 April 2006 08:46

ptgeorge
Messages: 19
Registered: April 2005

Junior Member

Yes, the table exists. Checked all that. Anyway if the table doesn't exist it usually gives an 'object doesnt exist' error. Also I have replaced the

select 'test' from dual
with
select * from temp_unbilled

The sqlplus bit works fine. Since sqlldr is connecting as the same user, it cant be anything to do with the table's visiblity, could it.

temp_unbilled is a table and the table is empty. So it shouldn't be an issue with the insert. Anyway the ctlfile is truncating into the table

LOAD DATA
TRUNCATE INTO TABLE TEMP_UNBILLED
FIELDS TERMINATED BY '|'
(
    id,
    anumber    "'+' || :anumber",
    amount
)

You think using an external table would help ? Im guessing its some environment problem like you'd said, since its working everywhere else, but I just can't seem to figure out what it is.

Thanks for your time anyway

[Updated on: Fri, 07 April 2006 09:30]

Report message to a moderator

Re: SQL*Loader-941 [message #166687 is a reply to message #166679]

Fri, 07 April 2006 09:52

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

What else is 'different' in that particular installation?
What is different in input datafile?
I cannot reproduce the case. So i am kind of 'left-in-dark' Smile

May be something is wrong with that particular sqlldr executable.
(did you try loading some other test load?)
And
In your script, why dont you strip all the parameters (rows/errors) and do a simple a load?

sqlldr userid=scott/tiger control=controlfile.ctl

Report message to a moderator

Re: SQL*Loader-941 [message #166858 is a reply to message #166687]

Mon, 10 April 2006 01:21

ptgeorge
Messages: 19
Registered: April 2005

Junior Member

Yeah, a direct load from cmd line worked. Its an env problem, figure a wrong oracle instance was being used. Thanks a lot

Report message to a moderator

Previous Topic:	Data Loading into a table with Identity type column(Sequence generator used) Direct path
Next Topic:	DATE CONVERSION - SQL LOADER

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 05 18:43:03 CDT 2024