Toad World® Forums

Trying to Insert data via Control file sql Loader


#1

Hi,

I am using SQLPlus to Insert data into temp table via sql Loader using Oracle 7i (old Server DB), it’s giving me an error saying:
ORA-00926: missing VALUES keyword

This is my control file, what I’m I missing?

LOAD DATA

INFILE *

replace

into table johntemp

fields terminated by ‘,’

OPTIONALLY ENCLOSED BY ‘"’

TRAILING NULLCOLS

(injured_party_id,

claim_sequence_nbr )

Thanks,

Hani


#2

Hi Hani,

Do you have the data you’re trying to insert listed in your control file as well? If you use “INFILE ***”,**SQL Loader will expect the data to exist within the control file itself, just after the “BEGINDATA” keyword. You’re using a pretty old version of Oracle, but I expect it would behave similar to more current versions of SQL Loader.

For more information on your version of SQL Loader, see the following:

http://docs.oracle.com/cd/A57673_01/DOC/dcommon/oin/index.htm

For similar documentation (which I think is a bit more straight-forward), see an updated version:

http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_control_file.htm#i1008015

-John

From: hani.chahelli [mailto:bounce-hanichahelli@toadworld.com]

Sent: Wednesday, September 24, 2014 1:07 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Trying to Insert data via Control file sql Loader

Trying to Insert data via Control file sql Loader

Thread created by hani.chahelli

Hi,

I am using SQLPlus to Insert data into temp table via sql Loader using Oracle 7i (old Server DB), it’s giving me an error saying:

ORA-00926: missing VALUES keyword

This is my control file, what I’m I missing?

LOAD DATA

INFILE *

replace

into table johntemp

fields terminated by ‘,’

OPTIONALLY ENCLOSED BY ‘"’

TRAILING NULLCOLS

(injured_party_id,

claim_sequence_nbr )

Thanks,

Hani

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


#3

Sorry… Here’s a better link for Oracle7’s SQL Loader Control File Reference:

http://docs.oracle.com/cd/A57673_01/DOC/server/doc/SUT73/ch5a.htm#toc089

From: John Bowman [mailto:bounce-jbowman@toadworld.com]

Sent: Wednesday, September 24, 2014 2:17 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Trying to Insert data via Control file sql Loader

RE: Trying to Insert data via Control file sql Loader

Reply by John Bowman

Hi Hani,

Do you have the data you’re trying to insert listed in your control file as well? If you use “INFILE ***”,**SQL Loader will expect the data to exist within the control file itself, just after the “BEGINDATA” keyword. You’re using a pretty old version of Oracle, but I expect it would behave similar to more current versions of SQL Loader.

For more information on your version of SQL Loader, see the following:

http://docs.oracle.com/cd/A57673_01/DOC/dcommon/oin/index.htm

For similar documentation (which I think is a bit more straight-forward), see an updated version:

http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_control_file.htm#i1008015

-John

From: hani.chahelli [mailto:bounce-hanichahelli@toadworld.com]

Sent: Wednesday, September 24, 2014 1:07 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Trying to Insert data via Control file sql Loader

Trying to Insert data via Control file sql Loader

Thread created by hani.chahelli

Hi,

I am using SQLPlus to Insert data into temp table via sql Loader using Oracle 7i (old Server DB), it’s giving me an error saying:

ORA-00926: missing VALUES keyword

This is my control file, what I’m I missing?

LOAD DATA

INFILE *

replace

into table johntemp

fields terminated by ‘,’

OPTIONALLY ENCLOSED BY ‘"’

TRAILING NULLCOLS

(injured_party_id,

claim_sequence_nbr )

Thanks,

Hani

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


#4

John,

thaniks, I have a seperate file, I removed the INFILE statement, was just testing my options. The error missing Values keyword is odd and not sure what is causing this.


#5

Hey Hani,

Well, that error usually appears when you use an INSERT statement without a VALUES keyword. I would first make sure you’re not trying to use an unfinished
INSERT statement anywhere else (just to be sure). If not, have you tried to load the control file directly using the sqlldr command line utility? Have you tried executing the control file within TOAD?

If the error is, in fact, being reported within SQLLoader for Oracle7, it might be an issue with how SQLLoader is interpreting quotes around specific values
– perhaps date-time values or some other data type? I’m assuming that the list of columns you gave in your original post may not be the entire list…

It’s a bit difficult to know what might be causing the problem without knowing the structure of the source data file and the structure of the destination table
into which you’re trying to import the data.

-John

From: hani.chahelli [mailto:bounce-hanichahelli@toadworld.com]

Sent: Wednesday, September 24, 2014 2:44 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Trying to Insert data via Control file sql Loader

RE: Trying to Insert data via Control file sql Loader

Reply by hani.chahelli

John,

thaniks, I have a seperate file, I removed the INFILE statement, was just testing my options. The error
missing Values keyword is odd and not sure what is causing this.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


#6

Hi John,

thanks for the reply, Unfortuantly Toad does not work with Oracle 7i or at least it is not installed on server so using SqlPlus. I am loading into temp table called johntemp as follows:

SQL> desc johntemp;

Name Null? Type


INJURED_PARTY_ID NUMBER(10)

this is how my .dat file look like:

9705080948

1999248443

control file:

LOAD DATA

replace

into table johntemp

fields terminated by ‘,’

OPTIONALLY ENCLOSED BY ‘"’

TRAILING NULLCOLS

(injured_party_id)

thanks,

Hani


#7

Toad will work with old versions of Oracle - BUT you MUST have old Oracle client versions. So for example an 8i client should work with both newer Toad and older Oracle. But you may need to use 32-bit Toad since older clients may only come in 32-bit version.


#8

Hi Team,

I have created sample example of sql loader which has .dat & .ctl file but i am not able import the data into respective table don’t know where is the issue can someone help me out…?

below is the error :
SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified


#9

Hi waiting for your response team


#10

Just google “Oracle ora-12154”, you’ll get a lot of hits. This can come from SQLPlus, SQLLoaader, anything that uses the OCI. It’s a generic problem.

Interestingly, and very off-topic, if you just google ora-12154, google will tell you where the 12154 zip code is.


#11

Some of these links may also provide some help to perhaps shed some light on what might be happening in your environment. I suspect there may be a configuration issue on your client somewhere:

https://community.oracle.com/thread/4010117?start=0&tstart=0

http://www.orafaq.com/forum/t/25048/

-John


#12

I tried so many things but didn’t work


#13

can you connect with sql*plus in the same oracle home and with the same connect string?


#14

I am actually having client HVD system, In that hvd i have install toad and using that i am pointing to the QA server,

Even i have followed below links :
https://community.oracle.com/thread/4010117?start=0&tstart=0

But no solution


#15

Toad may be using a different oracle client.
You don’t really have to supply a connect string to Toad, you just type in some boxes.

So either something is wrong with sql loader, or your connect string is wrong, or your oracle client is differnet than the one you are using in Toad, etc.

which is why I asked about sql*plus.


#16

Can i use the sql developer instead of sql plus…?
let me try this tomorrow because my day is off…

please ping me link of sql plus and i will get back to you again tomorrow


#17

SQL Developer doesn’t use the same connection method as SQLPlus and SQL Loader, so I wouldn’t.