Forums | Admin

Discussion Forums: open-discussion

Start New Thread Start New Thread

 

By: Daniel Berger
RE: Bind parameter issues using Oracle 10 [ reply ]  
2005-09-30 16:49
Upon further review, my issue seems to be on the database side.

Disregard. Sorry for the noise.

By: Daniel Berger
RE: Bind parameter issues using Oracle 10 [ reply ]  
2005-09-30 15:35
Hm....sqlplus seems to work fine. It comes back instantly. However, both Ruby and Perl hang during the fetch if I use a script.

My client is 9.2.0.1.0. Below is the actual query.

djberge@~/local/src/misc-670>sqlplus acs_support@qcrpts />
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Sep 30 09:36:22 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining Scoring Engine options

SQL> var sop varchar2(20)
SQL> var mytime varchar2(40)
SQL> exec :sop := 'C30035463'

PL/SQL procedure successfully completed.

SQL> exec :mytime := '01/18/2005 18:10:02';

PL/SQL procedure successfully completed.

SQL> select min(sle.service_log_date_time) service_log_date_time
2 from acs.service_log_entry sle, acs.service_order so
3 where so.service_order_id = :sop
4 and sle.service_ai = so.service_ai
5 and sle.user_login = 'smlcm'
6 and sle.service_log_type_code = 'TRANSLATE'
7 and sle.service_log_text like '%successful%'
8 and sle.service_log_date_time >= to_date(:mytime, 'MM/DD/YYYY HH24:MI:SS');

SERVICE_L
---------
01-JUL-05

By: Kubo Takehiro
RE: Bind parameter issues using Oracle 10 [ reply ]  
2005-09-29 23:54
How about SQL*Plus?

SQL> var bar varchar2(20)
SQL> var baz number
SQL> exec :bar := 'hello';
SQL> exec :baz := 5;
SQL> select * from foo where bar = :bar and baz = :baz;

What client version do you use?

By: Daniel Berger
RE: Bind parameter issues using Oracle 10 [ reply ]  
2005-09-29 19:54
I should mention that I see the same behavior with Perl's DBI and DBD::Oracle.

By: Daniel Berger
Bind parameter issues using Oracle 10 [ reply ]  
2005-09-29 16:11
Hi,

It looks like bind parameters aren't working properly when used against an Oracle 10 database. For example, if I do this:

select *
from foo
where bar = 'hello'
and baz = 5

Everthing works fine

But if I do this:

select *
from foo
where bar = :1
and baz = :2

And later I do sth.execute('hello', 5), it just hangs.

This doesn't seem to happen against Oracle 9i databases and earlier.

Any ideas?

Thanks.

Dan