Solved

ORACLE ERROR ORA-009922: missing or invalid option

Posted on 2008-10-08
5
1,401 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Insert with SET how to handle join 6 56
scheduler for Procedure in DB with 3 arguments in 10g 7 28
SQL join help to a thrid table 51 75
grouping on time windows 6 41
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

932 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