• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1504
  • Last Modified:

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 '#$^*%'
0
edwardui
Asked:
edwardui
  • 3
  • 2
1 Solution
 
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
 
gatorvipCommented:
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:
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now