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
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
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.
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
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
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.
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.
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
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.
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
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.
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
Hi waiting for your response team
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.
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
I tried so many things but didn’t work
can you connect with sql*plus in the same oracle home and with the same connect string?
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
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.
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
SQL Developer doesn’t use the same connection method as SQLPlus and SQL Loader, so I wouldn’t.