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.