Solved

ORACLE ERROR ORA-009922: missing or invalid option

Posted on 2008-10-08
5
1,391 Views
Last Modified: 2012-05-05
I am developing in Java and using an Oracle 10g database.  When i execute this query with a CallableStament I get an ORA-009922: missing or invalid option error.  

CallableStatement callst = conn.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

sql =  SET SCAN OFF;
SELECT TEST.TABLE1
FROM TESTTABLE
WHERE TABLE1.COL1 LIKE '#$^*%'
0
Comment
Question by:edwardui
  • 3
  • 2
5 Comments
 

Author Comment

by:edwardui
Comment Utility
In addition:  if i copy the same query and run it in SQL Developer it runs fine???
0
 

Author Comment

by:edwardui
Comment Utility
Correction on what sql =

sql =  SET SCAN OFF;
SELECT TEST.COL1
FROM TEST
WHERE TEST.COL1 LIKE '#$^*%'
0
 
LVL 20

Accepted Solution

by:
gatorvip earned 500 total points
Comment Utility
What is the actual java code you have?

Try to remove the "SET SCAN OFF;" and see if that fixes your problem
0
 

Author Comment

by:edwardui
Comment Utility
Thanks for your reply.
my java code is:
Connection conn = getConnection(db);
CallableStatement callst = conn.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,    
            ResultSet.CONCUR_READ_ONLY);
 callst.execute();

If I remove the SET SCAN OFF the query will run ok but in this particular case but in other cases if the sql WHERE looked something like this :
WHERE TEST.COL1 = '%'
I tried escaping the characters like this
sql = SELECT TEST.COL1
FROM TEST
WHERE TEST.COL1 LIKE '\%'
ESCAPE ='\';
 but i got an ORA-00933: SQL command not properly ended error

I have also tried SET DEFINE OFF
0
 
LVL 20

Expert Comment

by:gatorvip
Comment Utility
>>WHERE TEST.COL1 = '%'

what is this supposed to do?
If you want to search for the actual character % within a string, you can do something like

where instr(col1, chr(37))> 0


chr(37) is the ASCII code for %
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now