Home » Open Source » Programming Interfaces » Problems with Bind Variables and Prepared Statements (Oracle 10Gr2, Linux)
Problems with Bind Variables and Prepared Statements [message #447486] Mon, 15 March 2010 07:42 Go to next message
Rudolphk
Messages: 7
Registered: March 2009
Location: Washington D.C.
Junior Member
I think the following issue occurs in all languages which support the prepared statement (I am currently using Ruby with the OCI8 libraries)

The issue I am having is with creating prepared statements to query the surrogate key of a table based on an alternate-key definition.

example:

select id from mytable where mycol1 = :1 and mycol2 = :2

The problem is that mycol2 in this example may be NULL. When mycol2 is null, Oracle does not return the proper result set.

select id from mytable where mycol = 'some value' and mycol2 is null (works)

select id from mytable where mycol = 'some value' and mycol2 = NULL
(does not work)

Is there an elegant coding solution to this problem? I have a workaround, but its not great. I am trying to construct generic code which can look at a table's definition and dynamically create code to perform table lookups.
Re: Problems with Bind Variables and Prepared Statements [message #447490 is a reply to message #447486] Mon, 15 March 2010 07:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select id from mytable where mycol1 = :1
and ( (:2 is not null and mycol2 = :2) or (:2 is null and mycol2 is null) )

Oracle optimizer recognizes this exclusion between the 2 parts and well handles it.

Regards
Michel

Re: Problems with Bind Variables and Prepared Statements [message #447498 is a reply to message #447486] Mon, 15 March 2010 08:11 Go to previous message
Rudolphk
Messages: 7
Registered: March 2009
Location: Washington D.C.
Junior Member
Thanks . . . I'll try it out
Previous Topic: help me overcome this error in jdbcodbc
Next Topic: show incorrect result
Goto Forum:
  


Current Time: Thu Mar 28 18:05:05 CDT 2024