DBD::Oracle, cursor_sharing, and ORA-01008

I ran into a problem with queries executed on an intranet site. The web
server was running with Apache on a Windows XP Pro machine using DBI 1.53
and DBD::Oracle 1.19. The database server was upgraded from Oracle 10.2.0.2
to 10.2.0.3.

As soon as the upgrade happened, all of my queries failed that were using
bound variables with the error: ORA-01008: not all variables bound

I found a message on forums.oracle.com (
http://forums.oracle.com/forums/thread.jspa?messageID=164777 9� )
that described the same issue as I was having. As you can see from that
thread, the resolution was to change the server setting of cursor_sharing to
EXACT.

Other Oracle clients such as SQL*Plus were able to query the server
successfully before the configuration change was made.

My questions are--why did I have to make the configuration change? Why can
SQL*Plus handle the queries but Oracle::DBD 1.19 could not?

Thanks.

Andrew McFarlane

____________________________________________________________ _____
Valentine’s Day -- Shop for gifts that spell L-O-V-E at MSN Shopping
http://shopping.msn.com/content/shp/?ctId=8323,ptnrid=37,ptn rdata=24095&tcode=wlmtagline

RE: :Oracle, cursor_sharing, and ORA-01008

Well, first of all, the poster on that forum did not state any reason
why he took that road (did Oracle support suggest this; was there a bug,
etc.), so I would take this with a large grain of salt until that route
is closed.

What version of the Oracle client are you running on your web server?
Is there some sort of incompatibility between the client and 10.2.0.3?
Can you connect via SQL*Plus from that web server?

Is there anything in the 10.2.0.3 release notes that suggest there is an
issue regarding cursor_sharing?

Have you logged an SR with Oracle support? If not, you probably should.

--
Ron Reidy,
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Andrew McFarlane [mailto:welkin_inc@hotmail.com]=20
Sent: Wednesday, January 31, 2007 3:31 PM
To: dbi-users@perl.org
Subject: DBD::Oracle, cursor_sharing, and ORA-01008

I ran into a problem with queries executed on an intranet site. The web

server was running with Apache on a Windows XP Pro machine using DBI
1.53=20
and DBD::Oracle 1.19. The database server was upgraded from Oracle
10.2.0.2=20
to 10.2.0.3.

As soon as the upgrade happened, all of my queries failed that were
using=20
bound variables with the error: ORA-01008: not all variables bound

I found a message on forums.oracle.com (=20
http://forums.oracle.com/forums/thread.jspa?messageID=3D1647 779� =
)

that described the same issue as I was having. As you can see from that

thread, the resolution was to change the server setting of
cursor_sharing to=20
EXACT.

Other Oracle clients such as SQL*Plus were able to query the server=20
successfully before the configuration change was made.

My questions are--why did I have to make the configuration change? Why
can=20
SQL*Plus handle the queries but Oracle::DBD 1.19 could not?

Thanks.

Andrew McFarlane

____________________________________________________________ _____
Valentine's Day -- Shop for gifts that spell L-O-V-E at MSN Shopping=20
http://shopping.msn.com/content/shp/?ctId=3D8323,ptnrid=3D37 ,ptnrdata=3D2=
4095&
tcode=3Dwlmtagline


This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

RE: :Oracle, cursor_sharing, and ORA-01008

Hi,

Could you be running into bug 5254759 slated to be fixed in 11i?


Regards,

Michael



> Date: Wed, 31 Jan 2007 15:45:43 -0700
> From: "Reidy, Ron"
> To: Andrew McFarlane , dbi-users@perl.org
> Subject: RE: :Oracle, cursor_sharing, and ORA-01008
>
> Well, first of all, the poster on that forum did not state any reason
> why he took that road (did Oracle support suggest this; was there a bug,
> etc.), so I would take this with a large grain of salt until that route
> is closed.
>
> What version of the Oracle client are you running on your web server?
> Is there some sort of incompatibility between the client and 10.2.0.3?
> Can you connect via SQL*Plus from that web server?
>
> Is there anything in the 10.2.0.3 release notes that suggest there is an
> issue regarding cursor_sharing?
>
> Have you logged an SR with Oracle support? If not, you probably should.
>
> --
> Ron Reidy,
> Lead DBA
> Array BioPharma, Inc.
>
> -----Original Message-----
> From: Andrew McFarlane [mailto:welkin_inc@hotmail.com]
> Sent: Wednesday, January 31, 2007 3:31 PM
> To: dbi-users@perl.org
> Subject: DBD::Oracle, cursor_sharing, and ORA-01008
>
> I ran into a problem with queries executed on an intranet site. The web
>
> server was running with Apache on a Windows XP Pro machine using DBI
> 1.53
> and DBD::Oracle 1.19. The database server was upgraded from Oracle
> 10.2.0.2
> to 10.2.0.3.
>
> As soon as the upgrade happened, all of my queries failed that were
> using
> bound variables with the error: ORA-01008: not all variables bound
>
> I found a message on forums.oracle.com (
> http://forums.oracle.com/forums/thread.jspa?messageID=164777 9� )
>
> that described the same issue as I was having. As you can see from that
>
> thread, the resolution was to change the server setting of
> cursor_sharing to
> EXACT.
>
> Other Oracle clients such as SQL*Plus were able to query the server
> successfully before the configuration change was made.
>
> My questions are--why did I have to make the configuration change? Why
> can
> SQL*Plus handle the queries but Oracle::DBD 1.19 could not?
>
> Thanks.
>
> Andrew McFarlane
>
> ____________________________________________________________ _____
> Valentine's Day -- Shop for gifts that spell L-O-V-E at MSN Shopping
> http://shopping.msn.com/content/shp/?ctId=8323,ptnrid=37,ptn rdata=24095&
> tcode=wlmtagline
>
>
> This electronic message transmission is a PRIVATE communication which contains
> information which may be confidential or privileged. The information is intended
> to be for the use of the individual or entity named above. If you are not the
> intended recipient, please be aware that any disclosure, copying, distribution
> or use of the contents of this information is prohibited. Please notify the
> sender of the delivery error by replying to this message, or notify us by
> telephone (877-633-2436, ext. 0), and then delete it from your system.
>
>

---//---
"He who learns must suffer. And even in our sleep,
pain that cannot forget, falls drop by drop upon the heart;
and, in our own despair, against our will,
comes wisdom to us by the awful grace of God."
--- Aeschylus