customSQL ORA-01006: bind variable does not exis

, ,
1

Hello All,
I am wondering what the proper syntax is for a wildcard in a customSQL adapter service.
Basically I have a sql statement that I’d like to implement as a customSQL adapter service, however I am getting a ORA-01006 error.

The statement in SQL is:

select var1,var2 from mytable where ID like ‘%0012%’

My corresponding sql statement in the adapter service is:

select var1,var2
from mytable
where ID like ‘%?%’

In my customSQL service, I define a input variable called "ID" which is of type varchar.  By using the 'like' clause and the '%' parameters, I want to be able to query the database for any matches that have the string "0012" or any string defined by the user in the ID.   I believe the jdbc adapter doesn't know what the single quotes or '%' is so is there a substitute for the wildcard?

Thanks, David

2

Instead of using the % in the custom SQL use it in the value of the input parameter instead… i.e. define your custom SQL as

SELECT var1, var2 FROM myTable WHERE ID LIKE ? (no quotes required)

Now, instead of sending 0012 in ID, send %0012%.

~Rohit

3

Thanks Rohit, your idea is a good workaround and it works great!

David