DBI Error when attempting to pass Oracle's SYSDATE to a place hol der

ruby-talk
1

I’m using Ruby 1.6.7, Ruby/dbi .15 and I’m trying to call an Oracle stored
proc where the third parameter is of DATE type. I want to pass in the
current date & time by using SYSDATE. If I do the following everything
works as it should.
dbh.do( “BEGIN SOME_PROC( 1, 1, SYSDATE ); END” )

However, if I try to use place holders as below
sql = "BEGIN SOME_PROC( ?, ?, ? ); END"
dbh.do( sql, 1, 1, SYSDATE )

Oracle returns an error because of the SYSDATE value. Even putting quotes
around SYSDATE doesn’t do any good. Using similar code I get the same error
using perl.

Even doing the following works:
Dbh.do( sql, 1, 1, “#{Time.new.strftime( “%d-%b-%Y” ) }” ) Does work.

Has anyone else seen this error? Is this an error in DBI or in Oracle? Is
there a way to work around this problem in the current environment?

Bob Berge

2

I’m using Ruby 1.6.7, Ruby/dbi .15 and I’m trying to call an Oracle stored
proc where the third parameter is of DATE type. I want to pass in the
current date & time by using SYSDATE. If I do the following everything
works as it should.
dbh.do( “BEGIN SOME_PROC( 1, 1, SYSDATE ); END” )

However, if I try to use place holders as below
sql = “BEGIN SOME_PROC( ?, ?, ? ); END”
dbh.do( sql, 1, 1, SYSDATE )

Try the following:

sql = “BEGIN SOME_PROC( ?, ?, SYSDATE ); END”
dbh.do( sql, 1, 1)

There is no need to repalce all parmameters in a query if you use
perpared statements and parameter binding via “?”.

Oracle returns an error because of the SYSDATE value. Even putting quotes
around SYSDATE doesn’t do any good. Using similar code I get the same error
using perl.

SYSDATE is an ocracle function. With your code ruby trys to find the
constant “SYSDATE” which does not exist in ruby. Therefore your code cannot work.

Even doing the following works:
Dbh.do( sql, 1, 1, “#{Time.new.strftime( "%d-%b-%Y" ) }” ) Does work.

Here, ruby evaluates “Time.new…” which works of course.

Has anyone else seen this error? Is this an error in DBI or in Oracle? Is
there a way to work around this problem in the current environment?

See above.

My two cents,
-billy.

···

On Wed, Aug 14, 2002 at 07:54:07AM +0900, Berge, Robert wrote:

–
Meisterbohne Söflinger Straße 100 Tel: +49-731-399 499-0
eLösungen 89077 Ulm Fax: +49-731-399 499-9