Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORACLE ERROR ORA-009922: missing or invalid option

Posted on 2008-10-08
5
Medium Priority
?
1,490 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
ID: 22672366
In addition:  if i copy the same query and run it in SQL Developer it runs fine???
0
 

Author Comment

by:edwardui
ID: 22672511
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 2000 total points
ID: 22672659
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
ID: 22672791
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
ID: 22672928
>>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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

963 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