ORACLE ERROR ORA-009922: missing or invalid option

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 '#$^*%'
edwarduiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
gatorvipConnect With a Mentor Commented:
What is the actual java code you have?

Try to remove the "SET SCAN OFF;" and see if that fixes your problem
0
 
edwarduiAuthor Commented:
In addition:  if i copy the same query and run it in SQL Developer it runs fine???
0
 
edwarduiAuthor Commented:
Correction on what sql =

sql =  SET SCAN OFF;
SELECT TEST.COL1
FROM TEST
WHERE TEST.COL1 LIKE '#$^*%'
0
 
edwarduiAuthor Commented:
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
 
gatorvipCommented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.