Re: Design Pattern to keep a connection opened?

[John R Pierce]

On 11/05/11 8:46 PM, Bruce Johnson wrote:

Why not just open a database connection, run the procedure and close it again? :

Loop
open db
run proc
close db
End loop

?
If the connection is staying open long enough to get stale and be disconnected, 
then you shouldn't be running into timing issues.




or do exactly that, only with a connection pooler like pgbouncer...



--
john r pierceN 37, W 122
santa cruz ca mid-left coast

Re: Design Pattern to keep a connection opened?

[John R Pierce]

On 11/05/11 3:57 PM, Brandon Phelps wrote:
The main question is, how can I efficiently determine if a db object 
is usable? 


put the actual execute SQL command calls in a procedure which checks for 
an error code, and if the error is of the connection variety, reopens 
the connection and retries the query a reasonable number of times.




--
john r pierceN 37, W 122
santa cruz ca mid-left coast

RE: DBI-Users> RE: DBD-Oracle - obtaining OCI handles from $dbh

[John Scoles]

Glad we can help.
 
Keep us informed on how it works out
 
cheers
John
 

> Date: Thu, 27 Oct 2011 14:39:15 -0400
> From: bro...@deseret.com
> To: byter...@hotmail.com
> CC: martin.ev...@easysoft.com; dbi-users@perl.org
> Subject: Re: DBI-Users> RE: DBD-Oracle - obtaining OCI handles from $dbh
> 
> Ok, so with the following addition to Oracle.pm
> 
> DBD::Oracle::db->install_method("ora_oci_handles");
> 
> my little test program "worked". By "worked", I mean I did
> a connect to the database, then did
> 
> my @h = $dbh->ora_oci_handles();
> 
> and it returned 4 integers (ie. the value of the pointers), which
> is what I expected/wanted.
> 
> I haven't yet tested that I can now pass these pointer values to
> the C++ libraries and have them digest it properly...but that would
> be next.
> 
> As for how much anyone else might find use for thisprobably not
> a wide audience. But it is a nice hack!
> 
> Thanks for the pointers.
> 
> Quoting John Scoles (byter...@hotmail.com):
> > 
> > 
> > > Date: Thu, 27 Oct 2011 14:14:03 -0400
> > > From: bro...@deseret.com
> > > To: martin.ev...@easysoft.com
> > > CC: dbi-users@perl.org
> > > Subject: Re: DBD-Oracle - obtaining OCI handles from $dbh
> > > 
> > > 
> > > Thanks for those pointers.
> > > 
> > > I do agree with what Martin points out. My Perl script using DBI
> > > and some XS bindings to the legacy C++ libraries would share the
> > > same address space (not using threads in my application). This
> > > is why I thought I could return the handles/pointers as scalars.
> > > 
> > > Taking John's suggestions, here is a quick code hack that I made
> > > to Oracle.xs (I haven't tested this ...other than it compiles).
> > > "Looks like it should work." ;-)
> > > 
> > > void
> > > ora_oci_handles(dbh)
> > > SV *dbh
> > > PREINIT:
> > > D_imp_dbh(dbh);
> > > PPCODE:
> > > 
> > > /* Verify what is passed in is a $dbh object */
> > > if ( ! sv_derived_from(ST(0), "DBI::db")) {
> > > Perl_croak(aTHX_ "dbh is not of type DBI::db");
> > > }
> > > 
> > > mXPUSHi( (IV) imp_dbh->envhp ); /* Environment handle */
> > > mXPUSHi( (IV) imp_dbh->svchp ); /* Service Context handle */
> > > mXPUSHi( (IV) imp_dbh->srvhp ); /* Server handle */
> > > mXPUSHi( (IV) imp_dbh->authp ); /* Session handle */
> > > 
> > > XSRETURN(4);
> > > 
> > > 
> > > Then my idea is to use this in Perl space...
> > > 
> > > my($envhp, $svchp, $srvhp, $authp) = $dbh->ora_oci_handles();
> > > 
> > > 
> > > # Now share the OCI handles from DBI with the custom
> > > # C++ libraries.
> > > 
> > > my $cpp_dbh = MyCppOracleClass->new(); # creates custom C++ object
> > > 
> > > $cpp_dbh->envhp($envhp);
> > > $cpp_dbh->svchp($svchp);
> > > $cpp_dbh->srvhp($srvhp);
> > > $cpp_dbh->authp($authp);
> > > 
> > > # Do something interesting with the C++ object
> > > 
> > > $cpp_dbh->make_legacy_call_to_db();
> > > 
> > > 
> > > 
> > 
> > Yup that should work I didn't put two and two together and figure you 
> > already had XS for the C++. 
> > 
> > It could be something we could add to DBD::Oracle but I would return all of 
> > the handles not just the few you need.
> > 
> > I wonder how much it would be used though??
> > 
> > 
> > Cheers 
> > 
> > > 
> > > 
> > > Quoting Martin J. Evans (martin.ev...@easysoft.com):
> > > > On 27/10/2011 17:43, John Scoles wrote:
> > > > >Hmm!!
> > > > >
> > > > >Well yes could be done but not as part of any release of DBD::Oracle 
> > > > >it 
> > > > >would have to be you own hacked version
> > > > >
> > > > Why is that John? What is the problem with returning a C pointer via a 
> > > > DBD::Oracle attribute? It is just a pointer to some memory and loads of 
> > > > XS modules do this. There is an Oracle OCI module I played with for a 
> > > > short time but it is problematic to build. I looked at it as I could 
> > > > implement OCI calls separately from DBD::Oracle. I don't seem the harm 
> > > > in exposing OCI handles via DBD::Oracle - it would be useful for peo

RE: DBD-Oracle - obtaining OCI handles from $dbh

[John Scoles]

> Date: Thu, 27 Oct 2011 14:14:03 -0400
> From: bro...@deseret.com
> To: martin.ev...@easysoft.com
> CC: dbi-users@perl.org
> Subject: Re: DBD-Oracle - obtaining OCI handles from $dbh
> 
> 
> Thanks for those pointers.
> 
> I do agree with what Martin points out. My Perl script using DBI
> and some XS bindings to the legacy C++ libraries would share the
> same address space (not using threads in my application). This
> is why I thought I could return the handles/pointers as scalars.
> 
> Taking John's suggestions, here is a quick code hack that I made
> to Oracle.xs (I haven't tested this ...other than it compiles).
> "Looks like it should work." ;-)
> 
> void
> ora_oci_handles(dbh)
> SV *dbh
> PREINIT:
> D_imp_dbh(dbh);
> PPCODE:
> 
> /* Verify what is passed in is a $dbh object */
> if ( ! sv_derived_from(ST(0), "DBI::db")) {
> Perl_croak(aTHX_ "dbh is not of type DBI::db");
> }
> 
> mXPUSHi( (IV) imp_dbh->envhp ); /* Environment handle */
> mXPUSHi( (IV) imp_dbh->svchp ); /* Service Context handle */
> mXPUSHi( (IV) imp_dbh->srvhp ); /* Server handle */
> mXPUSHi( (IV) imp_dbh->authp ); /* Session handle */
> 
> XSRETURN(4);
> 
> 
> Then my idea is to use this in Perl space...
> 
> my($envhp, $svchp, $srvhp, $authp) = $dbh->ora_oci_handles();
> 
> 
> # Now share the OCI handles from DBI with the custom
> # C++ libraries.
> 
> my $cpp_dbh = MyCppOracleClass->new(); # creates custom C++ object
> 
> $cpp_dbh->envhp($envhp);
> $cpp_dbh->svchp($svchp);
> $cpp_dbh->srvhp($srvhp);
> $cpp_dbh->authp($authp);
> 
> # Do something interesting with the C++ object
> 
> $cpp_dbh->make_legacy_call_to_db();
> 
> 
> 
 
Yup that should work I didn't put two and two together and figure you already 
had XS for the C++. 
 
 It could be something we could add to DBD::Oracle but I would return all of 
the handles not just the few you need.
 
I wonder how much it would be used though??
 
 
Cheers 
 
> 
> 
> Quoting Martin J. Evans (martin.ev...@easysoft.com):
> > On 27/10/2011 17:43, John Scoles wrote:
> > >Hmm!!
> > >
> > >Well yes could be done but not as part of any release of DBD::Oracle it 
> > >would have to be you own hacked version
> > >
> > Why is that John? What is the problem with returning a C pointer via a 
> > DBD::Oracle attribute? It is just a pointer to some memory and loads of 
> > XS modules do this. There is an Oracle OCI module I played with for a 
> > short time but it is problematic to build. I looked at it as I could 
> > implement OCI calls separately from DBD::Oracle. I don't seem the harm 
> > in exposing OCI handles via DBD::Oracle - it would be useful for people 
> > like the OP.
> > 
> > >A few pointers to start.
> > >
> > >You will not be able to 'get' a handle and retrun it as a Scalar it will 
> > >only ever be a pointer so you will just get some sort of number,
> > >
> > >You would simly edit the Oracle.xs file
> > >
> > >add in the includes to your C++ .h files
> > >
> > >then add a few extra
> > >
> > >'ora_'
> > >
> > >functions to take care of you C++ calls;
> > >
> > >A quick example
> > >
> > >void
> > >ora_some_c_call(dbh)
> > > SV *dbh
> > > PREINIT:
> > > D_imp_dbh(dbh); //this gets all the OCI handles for you (see dbdimp.h 
> > > for the sturct imp_dbh_st)
> > > CODE:
> > > MYSomeC_Plus_Plus_method(dbh->envhp,dbh->svchp,dbh->seshp,dbh->srvhp);
> > >
> > >
> > He does not need to do this surely. So long as the C++ code and 
> > DBD::Oracle XS is running in the same process the pointers obtained from 
> > DBD::Oracle are just as valid in the C++ code as XS. However, if the 
> > code is multi-threaded there could be issues of multiple threads 
> > accessing the OCI handles at the same time.
> > 
> > >
> > >Myself I would write a small 'c' wrapper that would call you c++ and just 
> > >a single .XS function that calls that small 'c' wrapper to fire your 
> > >function.
> > >
> > My impression was that this was already done.
> > I quote:
> > 
> > "I have created some Perl bindings for some existing custom C++ libraries."
> > 
> > Martin
> > -- 
> > 
> > Martin J. Evans
> > Easysoft Limited
> > http://www.easysoft.com
> > 
> > 
> > >Hope this helps
> 

RE: DBI-Users> RE: DBD-Oracle - obtaining OCI handles from $dbh

[John Scoles]

 

> Date: Thu, 27 Oct 2011 18:42:23 +0100
> From: martin.ev...@easysoft.com
> To: dbi-users@perl.org
> Subject: Re: DBI-Users> RE: DBD-Oracle - obtaining OCI handles from $dbh
> 
> On 27/10/2011 17:43, John Scoles wrote:
> > Hmm!!
> >
> > Well yes could be done but not as part of any release of DBD::Oracle it 
> > would have to be you own hacked version
> >
> Why is that John? What is the problem with returning a C pointer via a 
> DBD::Oracle attribute? It is just a pointer to some memory and loads of 
> XS modules do this. 
 
I have no real problem with this either i guess here I am talking about calling 
his functions from XS as I denonstrated below.
 
>There is an Oracle OCI module I played with for a 
> short time but it is problematic to build. I looked at it as I could 
> implement OCI calls separately from DBD::Oracle. I don't seem the harm 
> in exposing OCI handles via DBD::Oracle - it would be useful for people 
> like the OP.
>
Oh yeah the old code of Tim B.  Never could get it to compile for me. I think 
the make file is only a few lines long and is only for 9i
 
> > A few pointers to start.
> >
> > You will not be able to 'get' a handle and retrun it as a Scalar it will 
> > only ever be a pointer so you will just get some sort of number,
> >
> > You would simly edit the Oracle.xs file
> >
> > add in the includes to your C++ .h files
> >
> > then add a few extra
> >
> > 'ora_'
> >
> > functions to take care of you C++ calls;
> >
> > A quick example
> >
> > void
> > ora_some_c_call(dbh)
> > SV *dbh
> > PREINIT:
> > D_imp_dbh(dbh); //this gets all the OCI handles for you (see dbdimp.h for 
> > the sturct imp_dbh_st)
> > CODE:
> > MYSomeC_Plus_Plus_method(dbh->envhp,dbh->svchp,dbh->seshp,dbh->srvhp);
> >
> >
> He does not need to do this surely. So long as the C++ code and 
> DBD::Oracle XS is running in the same process the pointers obtained from 
> DBD::Oracle are just as valid in the C++ code as XS. However, if the 
> code is multi-threaded there could be issues of multiple threads 
> accessing the OCI handles at the same time.
> 
> >
> > Myself I would write a small 'c' wrapper that would call you c++ and just a 
> > single .XS function that calls that small 'c' wrapper to fire your function.
> >
> My impression was that this was already done.
> I quote:

> 
> "I have created some Perl bindings for some existing custom C++ libraries."
> 
 
I guess I am just reading it differntly than you.  
 
Anway best would be to see some prototye code so we can get an better Idea of 
what he wants done.
 
If you just need the pointers to the OCI handles I think there is a way to have 
a look at them in Perl.  Seems I remember doing that or seeing it done one day 
but that would be a real hack.
 
I will have to look at some code at home. It may involve getting a little to 
deep into the exe tree.
 
cheers
John
> Martin
> -- 
> 
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
> 
> 
> > Hope this helps
> >
> > Cheers
> > John
> >
> >
> >> Date: Thu, 27 Oct 2011 09:48:54 -0400
> >> From: bro...@deseret.com
> >> To: byter...@hotmail.com
> >> CC: dbi-users@perl.org
> >> Subject: Re: DBI-Users> RE: DBD-Oracle - obtaining OCI handles from $dbh
> >>
> >> Yes, I assumed I would need to extend DBD::Oracle is some manner
> >> to allow those handles to be extracted from a $dbh object.
> >>
> >> The specific OCI handles that the C++ libraries use are
> >>
> >> - Environment handle
> >> - Service Context handle
> >> - Session handle
> >> - Server handle
> >>
> >> My initial thought process on how it might work is this
> >>
> >> Create a method in the DBD::Oracle XS code to retrieve those
> >> handles and return them back to Perl space as a scalar.
> >>
> >> Then with the Perl scalars that hold the OCI handles obtained from
> >> $dbh, pass those values to my Perl bindings to the custom C++ libraries.
> >> (I would need to extend the C++ libraries to allow the set methods
> >> to the objects that hold those OCI handles).
> >>
> >> ...then the C++ libraries should work the same.
> >>
> >> The motivation for this approach is that I have a large code base
> >> of these C++ libraries that have been tested, so it would cost
> >> prohibitive to simply replace them with a pure Perl implementation.
> >>
&g

RE: DBI-Users> RE: DBD-Oracle - obtaining OCI handles from $dbh

[John Scoles]

Hmm!!
 
Well yes could be done but not as part of any release of DBD::Oracle it would 
have to be you own hacked version
 
A few pointers to start.
 
You will not be able to 'get' a handle and retrun it as a Scalar it will only 
ever be a pointer so you will just get some sort of number,
 
You would simly edit the Oracle.xs file
 
add in the   includes to your C++ .h files
 
then add a few extra 
 
'ora_'
 
functions to take care of you C++ calls;
 
A quick example
 
void
ora_some_c_call(dbh)
   SV *dbh
   PREINIT:
   D_imp_dbh(dbh);  //this gets all the OCI handles for you (see dbdimp.h for 
the sturct imp_dbh_st)
   CODE:
   MYSomeC_Plus_Plus_method(dbh->envhp,dbh->svchp,dbh->seshp,dbh->srvhp);
 
 
 
Myself I would write a small 'c' wrapper that would call you c++ and just a 
single .XS function that calls that small 'c' wrapper to fire your function.
 
Hope this helps
 
Cheers
John
 

> Date: Thu, 27 Oct 2011 09:48:54 -0400
> From: bro...@deseret.com
> To: byter...@hotmail.com
> CC: dbi-users@perl.org
> Subject: Re: DBI-Users> RE: DBD-Oracle - obtaining OCI handles from $dbh
> 
> Yes, I assumed I would need to extend DBD::Oracle is some manner
> to allow those handles to be extracted from a $dbh object.
> 
> The specific OCI handles that the C++ libraries use are
> 
> - Environment handle
> - Service Context handle
> - Session handle
> - Server handle 
> 
> My initial thought process on how it might work is this
> 
> Create a method in the DBD::Oracle XS code to retrieve those
> handles and return them back to Perl space as a scalar.
> 
> Then with the Perl scalars that hold the OCI handles obtained from
> $dbh, pass those values to my Perl bindings to the custom C++ libraries.
> (I would need to extend the C++ libraries to allow the set methods
> to the objects that hold those OCI handles).
> 
> ...then the C++ libraries should work the same.
> 
> The motivation for this approach is that I have a large code base
> of these C++ libraries that have been tested, so it would cost
> prohibitive to simply replace them with a pure Perl implementation.
> 
> However, it is so much easier to work with Perl and DBI, it would
> be useful to have DBI connect to the database and do some table
> lookups. The C++ code could then be integrated seemlessly with my
> Perl code. As time allows, I would gradually peel away functionality
> from the legacy C++ libraries and implement it in Perl. But to 
> ease the migration path, this approach seemed to have some merits.
> 
> 
> Quoting John Scoles (byter...@hotmail.com):
> > 
> > 
> > 
> > 
> > > Date: Wed, 26 Oct 2011 21:46:30 -0400
> > > From: bro...@deseret.com
> > > To: dbi-users@perl.org
> > > Subject: DBD-Oracle - obtaining OCI handles from $dbh
> > > 
> > > I have created some Perl bindings for some existing custom C++ 
> > > libraries.
> > > 
> > > One of these C++ libraries implements a class that uses Oracle
> > > OCI calls.
> > > 
> > > I would like to create a connection to the Oracle database 
> > > using Perl's DBI (DBD::Oracle) module, and then ideally share
> > > that connection with the C++ libraries.
> > > 
> > > This would require me to extract the Oracle OCI handles from
> > > the $dbh object...and then pass them to the C++ libraries.
> > > 
> > > What would be the best way to get access to the underlying
> > > Oracle OCI handles from a $dbh object?
> > 
> > Hmm! Interesting concept. Which OCI handles are we talking about??
> > Like Matin said you would have to do that with .XS and you would pass I 
> > guess a pointer to the 
> > 'C' struct that holds the pointers to DBD::Oraclles pointers? 
> > 
> > You would have to cast that struct into you C++ somehow??
> > 
> > There would be a good number of handles to pass over. You might be able to 
> > find them by looking at the Perl process tree
> > and finding the SV that holds the struct and then sening that over?
> > 
> > All just guesses on my part.
> > 
> > Interesting concept non the less.
> > 
> > Cheers
> > John
> > 
  

RE: DBD-Oracle - obtaining OCI handles from $dbh

[John Scoles]

 

> Date: Wed, 26 Oct 2011 21:46:30 -0400
> From: bro...@deseret.com
> To: dbi-users@perl.org
> Subject: DBD-Oracle - obtaining OCI handles from $dbh
> 
> I have created some Perl bindings for some existing custom C++ 
> libraries.
> 
> One of these C++ libraries implements a class that uses Oracle
> OCI calls.
> 
> I would like to create a connection to the Oracle database 
> using Perl's DBI (DBD::Oracle) module, and then ideally share
> that connection with the C++ libraries.
> 
> This would require me to extract the Oracle OCI handles from
> the $dbh object...and then pass them to the C++ libraries.
> 
> What would be the best way to get access to the underlying
> Oracle OCI handles from a $dbh object?

Hmm! Interesting concept.  Which OCI handles are we talking about??
Like Matin said you would have to do that with .XS and you would pass I guess a 
pointer to the 
'C' struct that holds the pointers to DBD::Oraclles pointers? 
 
You would have to cast that struct into you C++ somehow??
 
There would be a good number of handles to pass over.  You might be able to 
find them by looking at the Perl process tree
and finding the SV that holds the struct and then sening that over?
 
All just guesses on my part.
 
Interesting concept non the less.
 
Cheers
John
  

RE: DBD::Oracle 1.25 and DRCP

[John Scoles]

No Problem  Glad to help out.  
 
And even more glad to see someone using it.
 
I tried when I wrote the DRCP code to get it to take as many conenction styles 
I could think of.  But in the very large world there are as many way to connect 
to Oracle as there are Oracle devleopers so there is bound to be a few that 
slip though the cracks. 
 
Cheers
John Scoles
 

> Date: Sat, 15 Oct 2011 15:24:59 -0400
> Subject: Re: DBD::Oracle 1.25 and DRCP
> From: frie...@gmail.com
> To: byter...@hotmail.com
> CC: dbi-users@perl.org
> 
> Thanks John!
> 
> that seems to have fixed it:
> 
> charset id=1, name=US7ASCII, ncharset id=1, name=US7ASCII
> (csid: utf8=871 al32utf8=873)
> Useing DRCP Connection
> dbd_st_prepare'd sql ALTER (pl1, auto_lob1, check_sql1)
> dbd_describe skipped for ALTER
> dbd_st_execute ALTER (out0, lob0)...
> Statement Execute Mode is 0 (DEFAULT)
> dbd_st_execute ALTER returned (SUCCESS, rpc0, fn52, out0)
> 
> 
> and I am seeing my Connection Class with Num_hits incrementing:
> 
> CCLASS_NAME
> 
> NUM_REQUESTS NUM_HITS NUM_MISSES NUM_WAITS WAIT_TIME CLIENT_REQ_TIMEOUTS
>  -- -- -- -- ---
> NUM_AUTHENTICATIONS
> ---
> $user.feedAutomation
> 3 2 1 0 0 0
> 2
> 
> I had thought defining POOLED in the tnsnames.ora was enough to pick it up.
> 
> 
> On Fri, Oct 14, 2011 at 7:12 PM, John Scoles  wrote:
> > Your connection style is not setting up DRCP.
> >
> > Consult the DBD::Oracle docs to try another way to connect using DRCP
> >
> > I would use something like this
> >
> > $dbh = DBI->connect('dbi:Oracle:DB','username','password',{ora_drcp=>1})
> >
> >
> > set the verbose to 3 and it should (if it can) show you some place in the
> > trace
> >
> > Useing DRCP Connection
> >
> > just after it reports on your NLS env
> >
> > Just need to connect no need for any qurreries etc
> >
> > cheers
> > John Scoles
> >
> >> Date: Fri, 14 Oct 2011 18:53:09 -0400
> >> Subject: Re: DBD::Oracle 1.25 and DRCP
> >> From: frie...@gmail.com
> >> To: byter...@hotmail.com
> >> CC: dbi-users@perl.org
> >>
> >> Hey John,
> >>
> >> I had to trim out some data ( mostly just Environment Variables ). let
> >> me know if there are any specific you would like to know the value of
> >> if I did not provide below. here is the rest of the output:
> >>
> >> ORACLE_HOME=/app/oracle/product/current
> >> /app/oracle/product/current -> 11.2.0.2
> >>
> >> OCINlsEnvironmentVariableGet(1,0,93,0,2)=SUCCESS
> >> OCINlsEnvironmentVariableGet(1,0,94,0,2)=SUCCESS
> >> OCIEnvNlsEnvCreate(99359fc,THREADED | OBJECT,3,0,0,0,0,0,0,1,1)=SUCCESS
> >> OCIHandleAlloc(99a1a88,9935a00,OCI_HTYPE_ERROR,0,0)=SUCCESS
> >> OCIAttrGet(99a1a88,OCI_HTYPE_ENV,7c,0,31,99d5c94)=SUCCESS
> >> OCIAttrGet(99a1a88,OCI_HTYPE_ENV,7e,0,262,99d5c94)=SUCCESS
> >> charset id=1, name=US7ASCII, ncharset id=1, name=US7ASCII
> >> (csid: utf8=871 al32utf8=873)
> >> OCIHandleAlloc(99a1a88,9935a04,OCI_HTYPE_SERVER,0,0)=SUCCESS
> >> OCIHandleAlloc(99a1a88,9935a08,OCI_HTYPE_SVCCTX,0,0)=SUCCESS
> >> OCIServerAttach(99d6438, 99d5c94, "uiis01", 6, mode=DEFAULT,0)=SUCCESS
> >> OCIAttrSet(99d5c18,OCI_HTYPE_SVCCTX,
> >> 99d6438,0,Attr=OCI_ATTR_SERVER,99d5c94)=SUCCESS
> >> OCIHandleAlloc(99a1a88,9935a0c,OCI_HTYPE_SESSION,0,0)=SUCCESS
> >> OCIAttrSet(9a017b0,OCI_HTYPE_SESSION,
> >> 98505d0,8,Attr=OCI_ATTR_USERNAME,99d5c94)=SUCCESS
> >> OCIAttrSet(9a017b0,OCI_HTYPE_SESSION,
> >> 99357a0,7,Attr=OCI_ATTR_PASSWORD,99d5c94)=SUCCESS
> >> OCISessionBegin(99d5c18,99d5c94,9a017b0,1,mode=DEFAULT 0)=SUCCESS
> >> OCIAttrSet(99d5c18,OCI_HTYPE_SVCCTX,
> >> 9a017b0,0,Attr=OCI_ATTR_SESSION,99d5c94)=SUCCESS
> >> OCIHandleAlloc(99a1a88,9a13b38,OCI_HTYPE_STMT,0,0)=SUCCESS
> >> OCIStmtPrepare(99e8914,99d5c94,'alter session set
> >> NLS_DATE_FORMAT = 'DD-MON- HH24:MI:SS'',60,1,0)=SUCCESS
> >>
> >> OCIAttrGet(99e8914,OCI_HTYPE_STMT,9a13b44,0,OCI_ATTR_STMT_TYPE,99d5c94)=SUCCESS
> >> dbd_st_prepare'd sql ALTER (pl1, auto_lob1, check_sql1)
> >> dbd_describe skipped for ALTER
> >> dbd_st_execute ALTER (out0, lob0)...
> >> Statement Execute Mode is 0 (DEFAULT)
> >> OCIStmtExecute(99d5c18,99e8914,99d5c94,1,0,0,0,mode=DEFAULT,0)=SUCCESS
>

RE: DBD::Oracle 1.25 and DRCP

[John Scoles]

Your connection style is not setting up DRCP.
 
Consult the DBD::Oracle docs to try another way to connect using DRCP
 
I would use something like this
 
$dbh = DBI->connect('dbi:Oracle:DB','username','password',{ora_drcp=>1})

 
set the verbose to 3 and it should (if it can) show you some place in the trace 
 
Useing DRCP Connection
 
just after it reports on your NLS env
 
Just need to connect no need for any qurreries etc
 
cheers
John Scoles
 

> Date: Fri, 14 Oct 2011 18:53:09 -0400
> Subject: Re: DBD::Oracle 1.25 and DRCP
> From: frie...@gmail.com
> To: byter...@hotmail.com
> CC: dbi-users@perl.org
> 
> Hey John,
> 
> I had to trim out some data ( mostly just Environment Variables ). let
> me know if there are any specific you would like to know the value of
> if I did not provide below. here is the rest of the output:
> 
> ORACLE_HOME=/app/oracle/product/current
> /app/oracle/product/current -> 11.2.0.2
> 
> OCINlsEnvironmentVariableGet(1,0,93,0,2)=SUCCESS
> OCINlsEnvironmentVariableGet(1,0,94,0,2)=SUCCESS
> OCIEnvNlsEnvCreate(99359fc,THREADED | OBJECT,3,0,0,0,0,0,0,1,1)=SUCCESS
> OCIHandleAlloc(99a1a88,9935a00,OCI_HTYPE_ERROR,0,0)=SUCCESS
> OCIAttrGet(99a1a88,OCI_HTYPE_ENV,7c,0,31,99d5c94)=SUCCESS
> OCIAttrGet(99a1a88,OCI_HTYPE_ENV,7e,0,262,99d5c94)=SUCCESS
> charset id=1, name=US7ASCII, ncharset id=1, name=US7ASCII
> (csid: utf8=871 al32utf8=873)
> OCIHandleAlloc(99a1a88,9935a04,OCI_HTYPE_SERVER,0,0)=SUCCESS
> OCIHandleAlloc(99a1a88,9935a08,OCI_HTYPE_SVCCTX,0,0)=SUCCESS
> OCIServerAttach(99d6438, 99d5c94, "uiis01", 6, mode=DEFAULT,0)=SUCCESS
> OCIAttrSet(99d5c18,OCI_HTYPE_SVCCTX,
> 99d6438,0,Attr=OCI_ATTR_SERVER,99d5c94)=SUCCESS
> OCIHandleAlloc(99a1a88,9935a0c,OCI_HTYPE_SESSION,0,0)=SUCCESS
> OCIAttrSet(9a017b0,OCI_HTYPE_SESSION,
> 98505d0,8,Attr=OCI_ATTR_USERNAME,99d5c94)=SUCCESS
> OCIAttrSet(9a017b0,OCI_HTYPE_SESSION,
> 99357a0,7,Attr=OCI_ATTR_PASSWORD,99d5c94)=SUCCESS
> OCISessionBegin(99d5c18,99d5c94,9a017b0,1,mode=DEFAULT 0)=SUCCESS
> OCIAttrSet(99d5c18,OCI_HTYPE_SVCCTX,
> 9a017b0,0,Attr=OCI_ATTR_SESSION,99d5c94)=SUCCESS
> OCIHandleAlloc(99a1a88,9a13b38,OCI_HTYPE_STMT,0,0)=SUCCESS
> OCIStmtPrepare(99e8914,99d5c94,'alter session set
> NLS_DATE_FORMAT = 'DD-MON- HH24:MI:SS'',60,1,0)=SUCCESS
> OCIAttrGet(99e8914,OCI_HTYPE_STMT,9a13b44,0,OCI_ATTR_STMT_TYPE,99d5c94)=SUCCESS
> dbd_st_prepare'd sql ALTER (pl1, auto_lob1, check_sql1)
> dbd_describe skipped for ALTER
> dbd_st_execute ALTER (out0, lob0)...
> Statement Execute Mode is 0 (DEFAULT)
> OCIStmtExecute(99d5c18,99e8914,99d5c94,1,0,0,0,mode=DEFAULT,0)=SUCCESS
> OCIAttrGet(99e8914,OCI_HTYPE_STMT,bfd81d48,0,OCI_ATTR_ROW_COUNT,99d5c94)=SUCCESS
> OCIAttrGet(99e8914,OCI_HTYPE_STMT,bfd81d4e,0,OCI_ATTR_SQLFNCODE,99d5c94)=SUCCESS
> dbd_st_execute ALTER returned (SUCCESS, rpc0, fn52, out0)
> OCIAttrGet(99e8914,OCI_HTYPE_STMT,bfd81d58,0,OCI_ATTR_ROW_COUNT,99d5c94)=SUCCESS
> dbd_st_destroy
> OCIHandleFree(99e8914,OCI_HTYPE_STMT)=SUCCESS
> OCITransRollback(99d5c18,99d5c94,mode=DEFAULT 0)=SUCCESS
> OCISessionEnd(99d5c18,99d5c94,9a017b0,mode=DEFAULT 0)=SUCCESS
> OCIServerDetach(99d6438,99d5c94,mode=DEFAULT,0)=SUCCESS
> OCIHandleFree(9a017b0,OCI_HTYPE_SESSION)=SUCCESS
> OCIHandleFree(99d5c18,OCI_HTYPE_SVCCTX)=SUCCESS
> OCIHandleFree(99d6438,OCI_HTYPE_SERVER)=SUCCESS
> OCIHandleFree(99d5c94,OCI_HTYPE_ERROR)=SUCCESS
> 
> 
> Also, passing the Sid this way works perfectly fine for connecting to
> the DB. it's just not sending through the Connection Class and I saw
> no way of just sending PURITY = SELF which is another way to do this
> per the OCI:
> 
> http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28395/oci09adv.htm#autoId34
> 
> thanks!
> -Rob
> 
> On Fri, Oct 14, 2011 at 5:52 PM, John Scoles  wrote:
> > Hard to say.
> >
> > Not sureif the way you are pasing in the sid will work.
> >
> > add dbd_verbose=>15
> >
> > to the conection options hash
> > Run the perl and post the output on this thread
> > so I can have a look at what is going on.
> >
> > Just do a simple connect no need for any other DBI stuff.
> >
> > I would also give the other flavours of connecting a try as well
> >
> > Cheers
> > John
> >
> >> Date: Fri, 14 Oct 2011 15:06:48 -0400
> >> Subject: Re: DBD::Oracle 1.25 and DRCP
> >> From: frie...@gmail.com
> >> To: dbi-users@perl.org
> >>
> >> Hey Cliff and John,
> >>
> >> on Sept 22, 2010 you posted about issues you were having with getting
> >> Oracle DRCP working using the ora_drcp_class. I am running into
> >> similar issues and was wond

RE: DBD::Oracle 1.25 and DRCP

[John Scoles]

Hard to say. 
 
Not sureif the way you are pasing in the sid will work.
 
add dbd_verbose=>15
 
to the conection options hash 
Run the perl and post the output on this thread
so I can have a look at what is going on.
 
Just do a simple connect no need for any other DBI stuff.
 
I would also give the other flavours of connecting a try as well
 
Cheers
John
 

> Date: Fri, 14 Oct 2011 15:06:48 -0400
> Subject: Re: DBD::Oracle 1.25 and DRCP
> From: frie...@gmail.com
> To: dbi-users@perl.org
> 
> Hey Cliff and John,
> 
> on Sept 22, 2010 you posted about issues you were having with getting
> Oracle DRCP working using the ora_drcp_class. I am running into
> similar issues and was wondering if you got DRCP to work and have it
> share the connection. We are using oracle 11.2.0.2 and I have perl
> v5.8.8, DBI 1.602 and DBD::Oracle 1.28.
> 
> here is my Tnsnames and basic connect string(DRCP is setup at the Listener):
> 
> $sid = (DESCRIPTION = (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = $host)(PORT = 1521)))
> (CONNECT_DATA = (SERVICE_NAME = $service)(SERVER=POOLED)))
> 
> 
> $self->{dbh} = DBI->connect("dbi:Oracle:$config->{sid}",
> $config->{user}, $config->{pass},
> { AutoCommit => 0, ora_drcp_class =>
> "feedAutomation" } );
> 
> yet I am not seeing the $user.feedAutomation CClass and I see no
> increase in Num_hits for any class that is there:
> 
> 1* select * from v$cpool_cc_stats
> 
> CCLASS_NAME
> 
> NUM_REQUESTS NUM_HITS NUM_MISSES NUM_WAITS WAIT_TIME CLIENT_REQ_TIMEOUTS
>  -- -- -- -- ---
> NUM_AUTHENTICATIONS
> ---
> 
> $user.SHARED
> 1346245 0 1346245 61582 0 0
> 1346245
> 
> Everything runs fine, it just doesn't share the connections and
> increase Num_hits.
> 
> any thoughts?
> 
> thanks!
> -Rob
  

RE: DBD::ODBC fails, SQL*Plus works

[John Scoles]

 Why even use DBD::ODBC?
 
Why not use DBD::Oracle?
 
Cheers
John
 

> Subject: Re: DBD::ODBC fails, SQL*Plus works
> From: john...@pharmacy.arizona.edu
> Date: Wed, 5 Oct 2011 10:53:48 -0700
> CC: dbi-users@perl.org
> 
> 
> On Oct 5, 2011, at 9:09 AM, Scott Stansbury wrote:
> 
> > 
> > It returns (after a few seconds) with an ORA-12154 error: TNS:could not 
> > resolve the connect identifier specified ( SQL-08004).
> 
> 
> Basic questions: the script is running in an environment where the env 
> variables $ORACLE_HOME and $TNS_ADMIN are available? Your tnsnames.ora file 
> is present and correct?
> 
> (if not, the those vars and put this:
> 
> PROD77 = 
> (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.15.200)(PORT=1535)))(CONNECT_DATA=(SID=PROD77)))
> 
> in a text file, save it as tnsnames.ora in your $ORACLE_HOME directory and 
> see if it works now)
> 
> -- 
> Bruce Johnson
> University of Arizona
> College of Pharmacy
> Information Technology Group
> 
> Institutions do not have opinions, merely customs
> 
> 
  

RE: DBI Proxy + transactions

[John Scoles]

I would hope it would depend on which type of DB you are trying to proxy to. 
 
If the DB at the other end of the proxy does not support 'begin_work' and 
'commit' or 'transactions'  then DBI Proxy will not magically enable it.
 
What flavour of DB is behind the proxy??
 
I have never used it for this just for cache of selects but the above would be 
one place to start.
 
Cheers
John 

 

> Subject: DBI Proxy + transactions
> From: bori...@fides.com
> To: dbi-users@perl.org
> Date: Tue, 4 Oct 2011 19:58:59 +0400
> 
> Hi!
> 
> Tell me, please, how can I use db transactions ( $dbh->begin_work() ...
> $dbh->commit() ) with DBI Proxy ?
> 
> --
> With regards,
> Borissov Pavel
> 
> 
  

RE: Connecting to Oracle 11g with perl 5.6.1

[John Scoles]

 Well you DBI is about 55+ updates behind and your DBD::Oracle is about 13 
behind.
 
I would say update both.
 
The error below is telling my you are still using a very old way to connect to 
Oracle which I think has been dropped in 11+ and dropped from DBD::Oracle in I 
think 1.11
 
hope this helps
 
Cheers
John Scoles
 

> Subject: Connecting to Oracle 11g with perl 5.6.1
> Date: Tue, 4 Oct 2011 21:04:16 +0530
> From: s...@cisco.com
> To: dbi-users@perl.org
> 
> Hi,
> 
> 
> 
> I have a perl program that is using Perl 5.6.1 and Oracle 10g. We have
> migrated the database from 10g to 11g. I modified ORACLE_HOME to use 11g
> database. However, now the program fails with,
> 
> 
> 
> ORA-06401: NETCMN: invalid driver designator (DBD ERROR:
> OCIServerAttach)
> 
> 
> 
> Anything else that I need to take care of when moving from 10g to 11g?
> Should I updated DBI or DBD::Oracle modules? These are the current
> versions that am using,
> 
> 
> 
> DBI: 1.18
> 
> DBD::Oracle - 1.07
> 
> 
> 
> Thanks,
> 
> Shobha Deepthi V
> 
> 
> 
  

RE: Tail Module + DBI Module, can\'t keep up!

[John Scoles]

 

> Date: Thu, 15 Sep 2011 09:26:41 -0700
> From: tigerpeng2...@yahoo.com
> Subject: Re: Tail Module + DBI Module, can\'t keep up!
> To: bphe...@gls.com
> CC: dbi-users@perl.org
> 
> Separate the process into two steps and figure out which step should be fixed 
> first.
> 
> 1. Parse the log with Tail and dump the data to plain file.
> 2. Load the plain file with DBI (are you using batch insert?)

 
This would be my stratigy as well.  you could also give the insert_array at try 
but I do not think that will give you much as you are useing MySQL but at lease 
you could use one script that
 
1) Bufferes  up X-n records to insert in an array/arrays
2) at buffer X do an insert with  execute_array
3) carry on burrering up the array.
 
one other thing to look out for is to make sure you are not 'prepareing' the 
same SQL over and over agian.  If you have only 1 query just prepare it once 
and reuse tha handle.  Also make sure you are not trying to reconnect each time 
as both of these actions are rather heavy on resources.
 
Cheers
John  

RE: Need help with DBI connect

[John Scoles]

You will most likely have to reinstall DBD::Oracle and the Oracle client one 
your new target linux box
 
try
 
Perl -MDBD::Oracle -e 'print DBD::Oracle::VERSION'
 
To see if it is installed on your new box.
 
If not you will have to get it from CPAN as well as an Oracle Client 
 
I would use the instant client which you can get here
 
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
 CheersJohn

> Subject: Need help with DBI connect
> Date: Mon, 12 Sep 2011 10:18:05 -0500
> From: khushboo.chokh...@bestbuy.com
> To: dbi-users@perl.org
> 
> Hi,
> 
> 
> 
> I have been stuck with this error since long now, and have tried almost
> everything available on web but no luckL
> 
> Any help would be greatly appreciated.
> 
> 
> 
> When I try to execute a PERL scripts I am getting an error:
> 
> 
> 
> ORACLE_HOME=/u01/app/oracle/product/10.2.0
> 
> TNS_ADMIN=/usr/local/tns
> 
> Can't locate object method "connect" via package "DBI" at
> /opt/mabl/agg/dgt/batch-proc/contentdownload/PROD-BBY/bin/DeltaFlag.pm
> line 67.
> 
> 
> 
> Before getting this error, I was getting an error where the script was
> not able to locate the DBI.pm module, hence I pointed my script to use
> the below path to locate DBI.pm:
> 
> 
> 
> /u01/app/oracle/product/10.2.0/perl/lib/site_perl/5.8.3/Apache
> 
> 
> 
> We are migrating from HPUX system to Linux, hence the scripts which we
> have been using since long are not working as is and they require some
> changes.
> 
> 
> 
> Pls help!
> 
> 
> 
> Thanks!
> 
> 
> 
> Khushboo Chokhani
> 
> Wipro Technologies
> Mobile : +1 (612)-354-1243
> 
> 
> 
> 
> 
  

Re: Apache:DBI DBD::Informix and dbping

[John R Pierce]

On 09/01/11 2:10 AM, Clive Eisen wrote:


What 'simple' piece of sql do the team suggest?

select 1 from systables where tabid =1


why not just SELECT TRUE;   ?



--
john r pierceN 37, W 122
santa cruz ca mid-left coast

Re: Extracting values from one table and query the values as a query for second table

[John R Pierce]

On 08/28/11 3:13 AM, pe...@vanroose.be wrote:

First remark: that's an unusual table design: I would have expected the
pivoted form of Table2. Why? Well, every time a row is added to Table1 (DML),
a*column*  has to be added to Table2 (DDL).


indeed, that was my initial reaction, it looked like a schema designed 
by a spreadsheet user.   It violates the tenets of relational data design.


If the table was properly structured, the required data could be queried 
with a single JOIN, albeit not in the exact output form shown.


--
john r pierceN 37, W 122
santa cruz ca mid-left coast

RE: Turn off "Issuing rollback..." warning for AutoCommit.

[John Scoles]

I think you can turn that one off by using WARN
 
http://search.cpan.org/~timb/DBI-1.616/DBI.pm#Warn
 
if memory serves me correctly it is a true 'warn()'  and not a PrintWarn.
 
It is very deep in the code.  I think in Driver.xst.
 
It is at the DBI side of things and has nothing to to with DBD::Oracle
 
Not sure if this will work in  DBI 1.30 though as I do not have that code base 
arount to look at.
 
Hope this helps
 
cheers
John
 

> Subject: Re: Turn off "Issuing rollback..." warning for AutoCommit.
> From: da...@kineticode.com
> Date: Wed, 24 Aug 2011 09:52:22 -0700
> CC: dbi-users@perl.org
> To: tigerpeng2...@yahoo.com
> 
> On Aug 24, 2011, at 7:51 AM, tiger peng wrote:
> 
> > I am subclassing DBI for authenticating purpose by overwriting connection.
> > 
> > I try to turn off warning message "Issuing rollback() for database handle 
> > being DESTROY'd without explicit disconnect()" by explicitly issuing 
> > rollback, disconnect and set AutoCommit to 0 without success. Could you 
> > please provide any solution and/or suggestion? My environment is quite old 
> > (Perl/5.80; DBI/1.30; DBD::Oracle/1.12).
> 
> Well first of all, upgrade Perl and those modules, if at all possible. That 
> DBI is NINE YEARS OLD!
> 
> As for the warning, what I generally do is disconnect in an END block right 
> after I connect, like so:
> 
> my $dbh = DBI->connect('dbi:Oracle:...');
> 
> END { $dbh->disconnect if $dbh }
> 
> I also recommend against having AutoCommit set to 0, as that means you have a 
> transaction open all the time. You should just use transactions when you have 
> reason to use them, that is for database writes.
> 
> Best,
> 
> David
> 
  

RE: Perl to connect Netezza

[John Scoles]

Doesn't seem to be a DBD for Netezza  but you can connect though 'ODBC'  so you 
will be able to use DBI.  
 
Perhaps in the long run you might want to write a DBD yourself as long as there 
is some sort of interface from IBM.
 
Doesn't seem to be much of anything on Netezza  on CPAN yet.
 
Cheers
 

 

> Date: Mon, 8 Aug 2011 14:58:58 +0530
> Subject: Perl to connect Netezza
> From: chote2cha...@gmail.com
> To: dbi-users@perl.org
> 
> Hi,
> 
> I'm working in an MNC. I have a new requirement where I have to connect to
> Netezza using perl. I would like to know whether I can use DBI module to
> connect to Netezza using perl. I would also like to know whether any special
> modules need to be installed for this purpose or is there any other better
> procedure?
> 
> Please provide your valuable inputs.
> 
> Thanks & Regards,
> Gauz.
  

RE: Reading hebrew from oracle - get ??????

[John Scoles]

Check the NLS_LANG and other local ENV setting such as Country etc. setting on 
the box where your client resides. 
 
 You might have to change it to one that can display.
 
Make sure the data is going in correctly first and that your display can 
display it.
 
Hope this helps.
 
Cheers
John
 

> From: shlomit.af...@weizmann.ac.il
> To: dbi-users@perl.org
> CC: shlomit.af...@weizmann.ac.il
> Subject: Reading hebrew from oracle - get ??
> Date: Mon, 18 Jul 2011 09:38:20 +
> 
> 
> 
> Hi,
> 
> I'm trying to connect to oracle server by oracle client with DBD.
> When I read Hebrew information, I get it as a lot of questions mark. 
> ().
> The information in the oracle is UTF8 and I build the xml file that hold of 
> the information I read with UTF8.
> 
> Is someone know why? What I'm doing wrong?
> 
> Shlomit.
  

RE: is it possible to use two sth from same dbh at the same time ?

[Carlson, John W.]

What happens with Apache::DBI?

-Original Message-
From: David Nicol [mailto:davidni...@gmail.com] 
Sent: Tuesday, July 12, 2011 12:00 PM
To: ZhangJun
Cc: dbi-users@perl.org
Subject: Re: is it possible to use two sth from same dbh at the same time ?

when it isn't possible, you can create two database handles, and they can
have different attributes.

my $dbhA = DBI->connect();
my $dbhB = DBI->connect();
my $sth1 = $dbhA->prepare( $sql_a );
my $sth2 = $dbhB->prepare( $sql_b );

$sth1->execute;
$dbhB->begin_work;  END { $dbhB->commit}


while ( my ($id) = $sth1->fetchrow_array ) {
 state $counter = 1;
 $sth2->execute( $id );
$counter++ % 2000 or $dbhB->commit;
};

Re: is it possible to use two sth from same dbh at the same time ?

[John R Pierce]

On 07/12/11 1:57 AM, Martin J. Evans wrote:

On 12/07/11 08:32, ZhangJun wrote:


my $dbh = DBI->connect();
my $sth1 = $dbh->prepare( $sql_a );
my $sth2 = $dbh->prepare( $sql_b );

$sth1->execute;
$sth2->execute;

while ( my ($id) = $sth1->fetchrow_array ) {
   $sth2->execute( $id )
}


# is it possible ?


Sometimes. Depends on which DBD you are using and then sometimes it 
depends on which driver you are using under that DBD.


You'll need to tell us which DBD (and possibly driver) you are using. 


I might also comment that you can generally achieve that sort of 
operation via a single statement using a SQL JOIN, and do so far more 
efficiently than what you show above, as it will reduce round trips to 
the database.


--
john r pierceN 37, W 122
santa cruz ca mid-left coast

RE: Segmentation fault

[John Scoles]

We will need a little more to go on than that.  At a min we need
 
1) DBI version
2) DBD driver name and version
3) Database system name and version
4) OS
 
What would be perfect is a perl script that recreates the error.
 
 
Cheers
DBI users
 
> Subject: Segmentation fault
> Date: Mon, 6 Jun 2011 20:01:32 +0530
> From: venkateswarara...@cognizant.com
> To: dbi-users@perl.org
> 
> Hi,
> 
> 
> 
> I am getting error like the follows.
> 
> 
> 
> server bbmkscretl01 gave "1709 Segmentation Fault(core dump)" error when
> tried to execute a script which calls truncate query inside the 
> 
> script.
> 
> 
> 
> Can you please help me out.
> 
> 
> 
> 
> 
> 
> 
> 
> This e-mail and any files transmitted with it are for the sole use of the 
> intended recipient(s) and may contain confidential and privileged information.
> If you are not the intended recipient, please contact the sender by reply 
> e-mail and destroy all copies of the original message.
> Any unauthorized review, use, disclosure, dissemination, forwarding, printing 
> or copying of this email or any action taken in reliance on this e-mail is 
> strictly prohibited and may be unlawful.
  

RE: perl DBI Oracle NCLOB fetching

[John Scoles]

Well if you can't change you DBD there may not be any other 'Majick'  that you 
will be able to do to fix this.
 
You might was to install one of the 'instantclients' and see what you get the 
same error.
 
the dbd_verbose was for 22 or later for dbi so that would be normal.
 
Try a trace=>10 but you will get a whold lot more tracing
 
 
 
Cheers
John 
> Date: Mon, 6 Jun 2011 10:57:24 +0200
> From: y...@mailueberfall.de
> Subject: Re: RE: perl DBI Oracle NCLOB fetching
> To: byter...@hotmail.com; dbi-users@perl.org
> 
> Hello,
> 
> setting dbd_verbose leads to no additional output. It's only:
> 
> DBD::Oracle::st fetchrow_arrayref failed: ORA-24806: LOB form mismatch (DBD 
> ERROR: OCILobRead) [for Statement "select * from ttt where rownum <2"] at 
> s.pl line 15.
> record couldn't be fetched!! at s.pl line 15.
> 
> It seems that I can't install a newer DBI or DBD version. The Perlscipt has 
> to run on this (our productive) environment.
> 
> Here my script:
> 
> #!/usr/local/bin/perl
> 
> use DBI;
> use strict;
> 
> my $dbh = DBI->connect('DBI:Oracle:db','u','p',{dbd_verbose => 9}) or die "No 
> database connection!";
> $dbh->{LongReadLen} = 16384; # set buffer length for blobs
> # session settings
> my $sth = $dbh->prepare("alter session set 
> NLS_DATE_FORMAT='-MM-DD_hh24:mi:ss'") or die "No prepare: ".DBI->errstr;
> $sth->execute or die "No exec".$sth->errstr;
> $sth->finish();
> 
> my $sthRow = $dbh->prepare("select * from ttt where rownum <2") or die 
> "Prepare of Database query failed!";
> $sthRow->execute() or die "Execution of database query failed!";
> $sthRow->fetchrow_arrayref or die "record couldn't be fetched!!";
> ---
> 
> 
> DBD Version:
> $Revision: 11.21 $
> 
> SQL> show parameter nls
> 
> NAME TYPE VALUE
>  --- 
> --
> nls_calendar string
> nls_comp string
> nls_currency string
> nls_date_format string
> nls_date_language string
> nls_dual_currency string
> nls_iso_currency string
> nls_language string AMERICAN
> nls_length_semantics string CHAR
> nls_nchar_conv_excp string FALSE
> nls_numeric_characters string
> 
> NAME TYPE VALUE
>  --- 
> --
> nls_sort string
> nls_territory string AMERICA
> nls_time_format string
> nls_time_tz_format string
> nls_timestamp_format string
> nls_timestamp_tz_format string
> SQL>
> 
> yoyo
> 
> 
>  Original-Nachricht 
> > Datum: Fri, 3 Jun 2011 13:27:54 -0400
> > Von: John Scoles 
> > An: y...@mailueberfall.de, dbi-users@perl.org
> > Betreff: RE: perl DBI Oracle NCLOB fetching
> 
> > 
> > That only ocures when the nclob going in is not compatiable with the nclob
> > field you are trying to stuff it into.
> > 
> > one thing that wil give us a little more info is to connect with
> > dbd_verbose=9 on the attributes and that will tell us you NSL setting.
> > 
> > also get your DBA to check 
> > 
> > NLS_CHARACTERSET
> > NLS_NCHAR_CHARACTERSET
> > NLS_LANGUAGE
> > NLS_TERRITORY
> > 
> > 
> > These should all match up.
> > 
> > Your DBI is rather old what is you version of DBD::Oracle
> > 
> > you might want to upgrade both
> > 
> > Cheers
> > John
> > 
> > 
> > > Date: Fri, 3 Jun 2011 19:03:46 +0200
> > > From: y...@mailueberfall.de
> > > To: dbi-users@perl.org
> > > Subject: perl DBI Oracle NCLOB fetching
> > > 
> > > Hello,
> > > 
> > > my Perl script uses uses the DBI interface to connect to Oracle and runs
> > > a select statement. But if one column is of type NCLOB it fails with:
> > > 
> > > DBD::Oracle::st fetchrow_hashref failed: ORA-24806: LOB form mismatch
> > > (DBD ERROR: OCILobRead) [for Statement "SELECT * FROM ttt WHERE
> > ROWNUM=1"]
> > > 
> > > I googled a lot and didn't found any solution for it. According to the
> > > used versions of Perl, DBI and Oracle and the NLS settings it should
> > work.
> > > 
> > > Any idea how to get it working?
> > > 
> > > Here are my used versions:
> > > 
> > > DBI::VERSION = "1.41"
> > > perl, v5.8.0
> > > 
> > > SQL> desc ttt
> > > Name Null? Type
> > > 
> > > T1 NCLOB
> > > UMSISDN VARCHAR2(200 CHAR)
> > > SPARTID NUMBER(2)
> > > 
> > > bash-3.00$ env | grep -i nls
> > > NLS_LANG=AMERICAN_AMERICA.UTF8
> > >
> > NLSPATH=:/export/home/omni/locale/%L/%N.cat:/export/home/omni/locale/%L/%N
> > > 
> > > Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
> > Production
> > > 
> > > much thanks,
> > > yoyo
> > > 
> > 
> 
> -- 
> NEU: FreePhone - kostenlos mobil telefonieren! 
> Jetzt informieren: http://www.gmx.net/de/go/freephone
  

RE: perl DBI Oracle NCLOB fetching

[John Scoles]

That only ocures when the nclob going in is not compatiable with the nclob 
field you are trying to stuff it into.
 
one thing that wil give us a little more info is to connect with dbd_verbose=9 
on the attributes and that will tell us you NSL setting.
 
also get your DBA to check 
 
NLS_CHARACTERSET
NLS_NCHAR_CHARACTERSET
NLS_LANGUAGE
NLS_TERRITORY
 
 
These should all match up.
 
Your DBI is rather old what is you version of DBD::Oracle
 
you might want to upgrade both
 
Cheers
John

 
> Date: Fri, 3 Jun 2011 19:03:46 +0200
> From: y...@mailueberfall.de
> To: dbi-users@perl.org
> Subject: perl DBI Oracle NCLOB fetching
> 
> Hello,
> 
> my Perl script uses uses the DBI interface to connect to Oracle and runs
> a select statement. But if one column is of type NCLOB it fails with:
> 
> DBD::Oracle::st fetchrow_hashref failed: ORA-24806: LOB form mismatch
> (DBD ERROR: OCILobRead) [for Statement "SELECT * FROM ttt WHERE ROWNUM=1"]
> 
> I googled a lot and didn't found any solution for it. According to the
> used versions of Perl, DBI and Oracle and the NLS settings it should work.
> 
> Any idea how to get it working?
> 
> Here are my used versions:
> 
> DBI::VERSION = "1.41"
> perl, v5.8.0
> 
> SQL> desc ttt
> Name Null? Type
> 
> T1 NCLOB
> UMSISDN VARCHAR2(200 CHAR)
> SPARTID NUMBER(2)
> 
> bash-3.00$ env | grep -i nls
> NLS_LANG=AMERICAN_AMERICA.UTF8
> NLSPATH=:/export/home/omni/locale/%L/%N.cat:/export/home/omni/locale/%L/%N
> 
> Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
> 
> much thanks,
> yoyo
> 
  

RE: DBD::Sybase and DBI->get_info()

[John Scoles]

Well looking at the code there does not seem to be any get_info in DBD::Sybase  
so I think you are out of luck
 
Cheers
John
 
> From: eric.b...@barclayscapital.com
> To: dbi-users@perl.org
> Date: Fri, 3 Jun 2011 11:38:59 -0400
> Subject: DBD::Sybase and DBI->get_info()
> 
> As we migrate our codebase from Sybase (DBD::Sybase) to MS SQL (DBD::ODBC), 
> it would be helpful to have low-level info about the drivers available, and 
> it seems that the DBI->get_info() method is the place to go for that. It is 
> supported adequately in DBD::ODBC, but it appears that DBD::Sybase doesn't 
> support get_info().
> 
> Am I missing something about using get_info(), or does DBD::Sybase provide 
> low-level driver info another way?
> 
> Any idea how to get info out of DBD::Sybase?
> 
> Thanks.
> 
> Eric
> 
> ___
> 
> This e-mail may contain information that is confidential, privileged or 
> otherwise protected from disclosure. If you are not an intended recipient of 
> this e-mail, do not duplicate or redistribute it by any means. Please delete 
> it and any attachments and notify the sender that you have received it in 
> error. Unless specifically indicated, this e-mail is not an offer to buy or 
> sell or a solicitation to buy or sell any securities, investment products or 
> other financial product or service, an official confirmation of any 
> transaction, or an official statement of Barclays. Any views or opinions 
> presented are solely those of the author and do not necessarily represent 
> those of Barclays. This e-mail is subject to terms available at the following 
> link: www.barcap.com/emaildisclaimer. By messaging with Barclays you consent 
> to the foregoing. Barclays Capital is the investment banking division of 
> Barclays Bank PLC, a company registered in England (number 1026167) with its 
> registered office at 1 Churchill Place, London, E14 5HP. This email may 
> relate to or be sent from other members of the Barclays Group.
> ___
  

RE: Need Help with DBI for oracle

[John Scoles]

Simple connection problem.
 
Try to connect SQLPlus if you can do that then you can use DBD::Oracel
 
Try the same connection string in DBD:Oracle
 
Hope this helps
 
cheers
John 
> CC: dbi-users@perl.org
> From: jona...@gmail.com
> Subject: Re: Need Help with DBI for oracle
> Date: Fri, 3 Jun 2011 16:05:23 +0200
> To: ganesh.b.ben...@gmail.com
> 
> As you can read from the error message is not interpolated, exchange the 
> single quotes for double quotes. Sorry for the abrupt response, but I am in 
> transit
> 
> jonasbn
> 
> Sent from my iPhone
> 
> On 02/06/2011, at 15.47, Ganesh Bendre  wrote:
> 
> > Hi,
> > 
> > I am using the following code for fetching some data.
> > but not able to connect
> > 
> > *CODE:*
> > #!C:/Perl/bin/perl.exe
> > 
> > use DBI;
> > use DBD::Oracle;
> > 
> > print "Content-type: text/html \n\n";
> > 
> > $platform = "ORACLE";
> > $database = "I8Q.GRP.COM";
> > $tablename = "COUNTY_ADD";
> > $user = "dba";
> > $pw = "dbapass";
> > 
> > $dsn = 'dbi:Oracle:$database:in-gh01:1521';
> > 
> > $connect = DBI->connect($dsn, $user, $pw);
> > 
> > $query = "select ctycode,ctydesc from county_add";
> > $query_handle = $connect->prepare($query);
> > 
> > $query_handle->execute();
> > 
> > $query_handle->bind_columns(undef, \$ctycode, \$ctydesc);
> > 
> > while($query_handle->fetch()) {
> > print "$ctycode, $ctydesc ";
> > }
> > *CODE END*
> > **
> > **
> > I am getting the following error when i try to run the above code
> > **
> > *Error:*
> > DBI connect('$database:in-gh01:1521','dba',...) failed: ORA-06401: NETCMN:
> > id driver designator (DBD ERROR: OCIServerAttach) at exequery2.pl line 21
> > Can't call method "prepare" on an undefined value at exequery2.pl line 25.
> > *Error END*
> > 
> > *I have installed *
> > *DBD::Oracle*
> > *DBI*
> > **
> > **
> > *i am stuck with this for almost one week
> > Please help me out*
> > **
> > *Ganesh.*
> > **
  

RE: AIX 5.3 DBD::Oracle issues

[John Scoles]

Well in that case bounce back to 1.25 which does not use OCIPing and you should 
be ok.
 
Also check which oracle client versions are on both boxes Credits to Navy Beans 
that they are different.
 
Cheers
John
> From: stephen.willi...@twcable.com
> To: dbi-users@perl.org
> Date: Wed, 25 May 2011 17:05:19 -0400
> Subject: RE: AIX 5.3 DBD::Oracle issues
> 
> Sorry I did forget to include that I am working with 1.27. I checked the 
> oci.def and OCIPing is in it. I am very perplexed as this same install worked 
> fine on another node.
> 
> From: John Scoles [mailto:byter...@hotmail.com]
> Sent: Wednesday, May 25, 2011 4:57 PM
> To: Williams, Stephen; dbi-usin...@perl.org
> Subject: RE: AIX 5.3 DBD::Oracle issues
> 
> Like the error message says "OCIPing" is not present in you Oracle.so file.
> 
> Can be cured a number of ways.
> 
> 1) check and see if OCIPing is in the OCI.def file of the version of 
> DBD::Oracel you have installed.
> 2) If it is not there add it and reinstall
> 
> If the above does not work try a version of DBD::Oracle lower than 1.25 or a 
> newer version like 1.27+
> 
> You did not say which version of DBD::oracle you were using.
> 
> cheers
> John
> 
> > From: stephen.willi...@twcable.com
> > To: dbi-usin...@perl.org<mailto:dbi-usin...@perl.org>
> > Date: Wed, 25 May 2011 16:04:37 -0400
> > Subject: AIX 5.3 DBD::Oracle issues
> >
> > Afternoon all! I have installed a standalone install of Perl and 
> > DBI/DBD::Oracle on my AIX 5.3 TL9 SP7 I have this working fine on two boxes 
> > but on one I keep getting the below error when I try a simple test 
> > connection. Has anyone seen this or know what may be wrong? I have been 
> > searching for an answer but have yet to turn anything up.
> >
> > ./foo
> > install_driver(Oracle) failed: Can't load 
> > '/product/cvc/perl5.12.3_64bit/lib/site_perl/5.12.3/aix-thread-multi-64all/auto/DBD/Oracle/Oracle.so'
> >  for module DBD::Oracle: rtld: 0712-001 Symbol OCIPing was referenced
> > from module 
> > /product/cvc/perl5.12.3_64bit/lib/site_perl/5.12.3/aix-thread-multi-64all/auto/DBD/Oracle/Oracle.so(),
> >  but a runtime definition
> > of the symbol was not found.
> > rtld: 0712-002 fatal error: exiting. at 
> > /product/cvc/perl5.12.3_64bit/lib/5.12.3/aix-thread-multi-64all/DynaLoader.pm
> >  line 200.
> > at (eval 3) line 3
> > Compilation failed in require at (eval 3) line 3.
> > Perhaps a required shared library or dll isn't installed where expected
> > at ./foo line 10
> >
> > Perl configuration
> >
> > /product/cvc/perl/bin/perl -V
> > Summary of my perl5 (revision 5 version 12 subversion 3) configuration:
> >
> > Platform:
> > osname=aix, osvers=5.3.0.0, archname=aix-thread-multi-64all
> > uname='aix cvcpstg01 3 5 00c510fc4c00 '
> > config_args='-d -Dcc=gcc -Duseshrplib -Dusethreads -Duse64bitall 
> > -Dprefix=/product/cvc/perl5.12.3_64bit -s'
> > hint=recommended, useposix=true, d_sigaction=define
> > useithreads=define, usemultiplicity=define
> > useperlio=define, d_sfio=undef, uselargefiles=define, usesocks=undef
> > use64bitint=define, use64bitall=define, uselongdouble=undef
> > usemymalloc=n, bincompat5005=undef
> > Compiler:
> > cc='gcc -maix64', ccflags ='-D_THREAD_SAFE -D_ALL_SOURCE -D_ANSI_C_SOURCE 
> > -D_POSIX_SOURCE -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT 
> > -fno-strict-aliasing -pipe -maix64 -DUSE_64_BIT_ALL',
> > optimize='-O',
> > cppflags='-D_THREAD_SAFE -D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE 
> > -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -fno-strict-aliasing -pipe'
> > ccversion='', gccversion='4.2.0', gccosandvers=''
> > intsize=4, longsize=8, ptrsize=8, doublesize=8, byteorder=87654321
> > d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=8
> > ivtype='long long', ivsize=8, nvtype='double', nvsize=8, Off_t='off_t', 
> > lseeksize=8
> > alignbytes=8, prototype=define
> > Linker and Libraries:
> > ld='gcc -maix64', ldflags =' -Wl,-brtl -Wl,-bdynamic -L/usr/local/lib 
> > -Wl,-b64'
> > libpth=/usr/local/lib /lib /usr/lib /usr/ccs/lib
> > libs=-lbind -lnsl -ldbm -ldl -lld -lm -lcrypt -lpthreads -lc
> > perllibs=-lbind -lnsl -ldl -lld -lm -lcrypt -lpthreads -lc
> > libc=/lib/libc.a, so=a, useshrplib=true, libperl=libperl.a
> > gnulibc_version=''
> > Dynamic Linking:
> > dlsrc=dl_aix.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Xlinker 
> &g

RE: (Fwd) Question about DBD::Oracle

[John Scoles]

Hmm talk about ancient history.
 
Not a but in either me thinks
 
I believe this is the default behaviour of early  OCI clients like OCI 7 to 
trim all varchars.  This changed for some reason by Oracle in 
OCI 8 and then changed again by Oracle in 9 and later clients.  There are 
different ways the various clients handle null terminated strings as well which 
is another kettle of fish.
 
The short of it is since 9 there has been no changes but eailer version there 
where a number of different ways of handling trailing spaces.
 
Correct me if I am wrong Tim but I do not think it is a bug in DBD::Oracle just 
different implementations on OCI clients.
 
Hope this helps
 
cheers
John
 
> Date: Tue, 24 May 2011 14:40:54 +0100
> From: tim.bu...@pobox.com
> To: dbi-users@perl.org
> CC: sumiy...@tradewintech.co.jp
> Subject: (Fwd) Question about DBD::Oracle
> 
> - Forwarded message from Sumiya  -
> 
> Date: Wed, 16 Mar 2011 09:19:15 +0900
> From: Sumiya 
> To: tim.bu...@pobox.com
> Subject: Question about DBD::Oracle
> 
> Dear Mr. Tim Bunce,
> 
> I would like to ask you about DBD::Oracle.
> Would you tell me what makes difference behaviourbetween
> OCI 8 and OCI 9.2 on ORA_VARCHAR2?
> Is is a bug of DBD or OCI?
> Please explain more detail.
> 
> 
> http://search.cpan.org/~pythian/DBD-Oracle-1.27/Oracle.pm#Connect_Attributes
> 
> ORA_VARCHAR2
> Oracle clients using OCI 8 will strip trailing spaces and allow embedded
> \0 bytes. Oracle clients using OCI 9.2 do not strip trailing spaces and
> allow embedded \0 bytes. This is the normal default placeholder type.
> 
> 
> Yours faithfully,
> 
> Kiyoshi Sumiya,
> 
> 
> 
> 
> 
> 
> 
> - End forwarded message -
  

RE: (Fwd) Re: DBD::Oracle Slow cursors

[Carlson, John W.]

It looks like you're doing a full table scan to pull back all rows of 
tbl_qm_dashboard currently--how many rows does that have?

-Original Message-
From: Carlson, John W. [mailto:carlso...@llnl.gov] 
Sent: Wednesday, May 18, 2011 10:05 AM
To: P S Jameel Ahamed; 'John Scoles'; 'Martin Evans'; dbi-users@perl.org; 'Tim 
Bunce'
Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors

When I say simple, I mean on a table with just a few rows.  You may want to 
look at how many rows are pulled back from the database at a time on your large 
table (batching rows).

John

-Original Message-
From: Carlson, John W. [mailto:carlso...@llnl.gov] 
Sent: Wednesday, May 18, 2011 9:46 AM
To: P S Jameel Ahamed; 'John Scoles'; 'Martin Evans'; dbi-users@perl.org; 'Tim 
Bunce'
Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors

Try doing a  very simple refcursor and see if you still have the same 
performance issues.

John

From: P S Jameel Ahamed [mailto:jaha...@idexcel.com]
Sent: Wednesday, May 18, 2011 5:04 AM
To: 'John Scoles'; 'Martin Evans'; dbi-users@perl.org; Carlson, John W.; 'Tim 
Bunce'
Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors

John,

Irrespective of I calling in Java or executing it on the SQL developer or in 
TOAD, I am getting the same response time when compared to executing the SQL's 
separately

Regards
P S Jameel Ahamed


From: John Scoles [mailto:byter...@hotmail.com]
Sent: 18 May 2011 12:21
To: jaha...@idexcel.com; Martin Evans; dbi-users@perl.org; carlso...@llnl.gov; 
Tim Bunce
Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors

Does this have anything to do at all with DBD::Oracle???
You mentioned you are calling this with JAVA??

Where is the Perl code??

> From: jaha...@idexcel.com
> To: martin.ev...@easysoft.com; dbi-users@perl.org; byter...@hotmail.com; 
> carlso...@llnl.gov; tim.bu...@pobox.com
> Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors
> Date: Wed, 18 May 2011 11:07:56 +0100
>
> Hi All,
>
> First of all I thank you all in replying to my email. Below are details of
> one of the SP which we are having issues with.
>
> In the below SP we are passing in a Username and then for that user are
> fetching his counts and other information. The output parameter is a
> sys_refcursur.
>
> The problem is that when I fire each and every insert and select statement,
> Its firing in 0.2 seconds. But when retrieving these details in the java
> code when it come to the line to loop the cursor then it gets struck for
> about 2 to 3 mins and the response is coming back.
>
> We have seen this behavior in most of the places where we used this
> sys_refcursur.
>
> The table circuit is a huge table of million records and it has all the
> indexces in place and other tables as well.
>
> I have been googleing for a long time for a solution for this sys-refcursor
> issue and I am not sure why its going back to the DB after executing the SP
> while looping through the cursor.
>
> We have Oracle 10G 10.1.0.4.
>
> I have also tried to combain all the select statements into one using the
> UNION command and insert into the tbl_qm_dashboard. The insert fires in 1.12
> seconds, but again the cursuor looping is the issue.
>
> Note : tbl_qm_dashboard is a temp table.
>
> Hope I will get an break through with all your expertise.
>
> PROCEDURE sp_select_dashboard(strUser IN VARCHAR2,
> rcSelectDashboard OUT SYS_REFCURSOR) IS
>
> BEGIN
>
> EXECUTE IMMEDIATE 'ALTER SESSION SET
> NLS_COMP=LINGUISTIC';
> EXECUTE IMMEDIATE 'ALTER SESSION SET
> NLS_SORT=BINARY_CI';
>
>
> INSERT INTO tbl_qm_dashboard
> SELECT
> dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt))
> ,0)avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old
> _5days/DECODE(closed_old_5days,0,1,closed_old_5days)),0)
> avg_response_old5days
> FROM
> (
> SELECT 'AccessCircuit' dashboard_type,
> SUM(case when (A.ot_queue_status_id IN(1,2) AND
> ((C.status_id <> 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3)))
> then 1 else 0 END ) active_cnt,
> SUM(case when (A.ot_queue_status_id IN(1,2) AND
> ((C.status_id <> 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3)))
> then (SYSDATE - c.created_on) else 0 END ) age,
> SUM(case when (c.created_on <= (sysdate - 5) AND
> A.ot_queue_status_id IN(1,2) AND ((C.status_id <> 3) OR (C.status_id = 3
> AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days,
> SUM(case when (A.LAST_UPDATED_ON >= (sysdate - 5) AND
> A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days,
> SUM(case when (A.LAST_UPDATED_ON >= (sysdate - 5) AND
> A.ot_qu

RE: (Fwd) Re: DBD::Oracle Slow cursors

[Carlson, John W.]

When I say simple, I mean on a table with just a few rows.  You may want to 
look at how many rows are pulled back from the database at a time on your large 
table (batching rows).

John

-Original Message-
From: Carlson, John W. [mailto:carlso...@llnl.gov] 
Sent: Wednesday, May 18, 2011 9:46 AM
To: P S Jameel Ahamed; 'John Scoles'; 'Martin Evans'; dbi-users@perl.org; 'Tim 
Bunce'
Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors

Try doing a  very simple refcursor and see if you still have the same 
performance issues.

John

From: P S Jameel Ahamed [mailto:jaha...@idexcel.com]
Sent: Wednesday, May 18, 2011 5:04 AM
To: 'John Scoles'; 'Martin Evans'; dbi-users@perl.org; Carlson, John W.; 'Tim 
Bunce'
Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors

John,

Irrespective of I calling in Java or executing it on the SQL developer or in 
TOAD, I am getting the same response time when compared to executing the SQL's 
separately

Regards
P S Jameel Ahamed


From: John Scoles [mailto:byter...@hotmail.com]
Sent: 18 May 2011 12:21
To: jaha...@idexcel.com; Martin Evans; dbi-users@perl.org; carlso...@llnl.gov; 
Tim Bunce
Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors

Does this have anything to do at all with DBD::Oracle???
You mentioned you are calling this with JAVA??

Where is the Perl code??

> From: jaha...@idexcel.com
> To: martin.ev...@easysoft.com; dbi-users@perl.org; byter...@hotmail.com; 
> carlso...@llnl.gov; tim.bu...@pobox.com
> Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors
> Date: Wed, 18 May 2011 11:07:56 +0100
>
> Hi All,
>
> First of all I thank you all in replying to my email. Below are details of
> one of the SP which we are having issues with.
>
> In the below SP we are passing in a Username and then for that user are
> fetching his counts and other information. The output parameter is a
> sys_refcursur.
>
> The problem is that when I fire each and every insert and select statement,
> Its firing in 0.2 seconds. But when retrieving these details in the java
> code when it come to the line to loop the cursor then it gets struck for
> about 2 to 3 mins and the response is coming back.
>
> We have seen this behavior in most of the places where we used this
> sys_refcursur.
>
> The table circuit is a huge table of million records and it has all the
> indexces in place and other tables as well.
>
> I have been googleing for a long time for a solution for this sys-refcursor
> issue and I am not sure why its going back to the DB after executing the SP
> while looping through the cursor.
>
> We have Oracle 10G 10.1.0.4.
>
> I have also tried to combain all the select statements into one using the
> UNION command and insert into the tbl_qm_dashboard. The insert fires in 1.12
> seconds, but again the cursuor looping is the issue.
>
> Note : tbl_qm_dashboard is a temp table.
>
> Hope I will get an break through with all your expertise.
>
> PROCEDURE sp_select_dashboard(strUser IN VARCHAR2,
> rcSelectDashboard OUT SYS_REFCURSOR) IS
>
> BEGIN
>
> EXECUTE IMMEDIATE 'ALTER SESSION SET
> NLS_COMP=LINGUISTIC';
> EXECUTE IMMEDIATE 'ALTER SESSION SET
> NLS_SORT=BINARY_CI';
>
>
> INSERT INTO tbl_qm_dashboard
> SELECT
> dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt))
> ,0)avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old
> _5days/DECODE(closed_old_5days,0,1,closed_old_5days)),0)
> avg_response_old5days
> FROM
> (
> SELECT 'AccessCircuit' dashboard_type,
> SUM(case when (A.ot_queue_status_id IN(1,2) AND
> ((C.status_id <> 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3)))
> then 1 else 0 END ) active_cnt,
> SUM(case when (A.ot_queue_status_id IN(1,2) AND
> ((C.status_id <> 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3)))
> then (SYSDATE - c.created_on) else 0 END ) age,
> SUM(case when (c.created_on <= (sysdate - 5) AND
> A.ot_queue_status_id IN(1,2) AND ((C.status_id <> 3) OR (C.status_id = 3
> AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days,
> SUM(case when (A.LAST_UPDATED_ON >= (sysdate - 5) AND
> A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days,
> SUM(case when (A.LAST_UPDATED_ON >= (sysdate - 5) AND
> A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON - c.created_on) else 0
> END ) response_old_5days
> FROM tbl_ot_queue A, tbl_circuit C, tbl_opportunity D
> WHERE A.assigned_to = struser
> AND C.opportunity_id = D.opportunity_id
> -- AND (D.key_bid = 'N' OR (D.key_bid = 'Y' AND
> C.release_to_region_on IS NOT NULL))
> AND A.circuit_id = C.circuit_id
> AND A.circuit_current_stage = C.Circ

RE: (Fwd) Re: DBD::Oracle Slow cursors

[Carlson, John W.]

Try doing a  very simple refcursor and see if you still have the same 
performance issues.

John

From: P S Jameel Ahamed [mailto:jaha...@idexcel.com]
Sent: Wednesday, May 18, 2011 5:04 AM
To: 'John Scoles'; 'Martin Evans'; dbi-users@perl.org; Carlson, John W.; 'Tim 
Bunce'
Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors

John,

Irrespective of I calling in Java or executing it on the SQL developer or in 
TOAD, I am getting the same response time when compared to executing the SQL's 
separately

Regards
P S Jameel Ahamed


From: John Scoles [mailto:byter...@hotmail.com]
Sent: 18 May 2011 12:21
To: jaha...@idexcel.com; Martin Evans; dbi-users@perl.org; carlso...@llnl.gov; 
Tim Bunce
Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors

Does this have anything to do at all with DBD::Oracle???
You mentioned you are calling this with JAVA??

Where is the Perl code??

> From: jaha...@idexcel.com
> To: martin.ev...@easysoft.com; dbi-users@perl.org; byter...@hotmail.com; 
> carlso...@llnl.gov; tim.bu...@pobox.com
> Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors
> Date: Wed, 18 May 2011 11:07:56 +0100
>
> Hi All,
>
> First of all I thank you all in replying to my email. Below are details of
> one of the SP which we are having issues with.
>
> In the below SP we are passing in a Username and then for that user are
> fetching his counts and other information. The output parameter is a
> sys_refcursur.
>
> The problem is that when I fire each and every insert and select statement,
> Its firing in 0.2 seconds. But when retrieving these details in the java
> code when it come to the line to loop the cursor then it gets struck for
> about 2 to 3 mins and the response is coming back.
>
> We have seen this behavior in most of the places where we used this
> sys_refcursur.
>
> The table circuit is a huge table of million records and it has all the
> indexces in place and other tables as well.
>
> I have been googleing for a long time for a solution for this sys-refcursor
> issue and I am not sure why its going back to the DB after executing the SP
> while looping through the cursor.
>
> We have Oracle 10G 10.1.0.4.
>
> I have also tried to combain all the select statements into one using the
> UNION command and insert into the tbl_qm_dashboard. The insert fires in 1.12
> seconds, but again the cursuor looping is the issue.
>
> Note : tbl_qm_dashboard is a temp table.
>
> Hope I will get an break through with all your expertise.
>
> PROCEDURE sp_select_dashboard(strUser IN VARCHAR2,
> rcSelectDashboard OUT SYS_REFCURSOR) IS
>
> BEGIN
>
> EXECUTE IMMEDIATE 'ALTER SESSION SET
> NLS_COMP=LINGUISTIC';
> EXECUTE IMMEDIATE 'ALTER SESSION SET
> NLS_SORT=BINARY_CI';
>
>
> INSERT INTO tbl_qm_dashboard
> SELECT
> dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt))
> ,0)avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old
> _5days/DECODE(closed_old_5days,0,1,closed_old_5days)),0)
> avg_response_old5days
> FROM
> (
> SELECT 'AccessCircuit' dashboard_type,
> SUM(case when (A.ot_queue_status_id IN(1,2) AND
> ((C.status_id <> 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3)))
> then 1 else 0 END ) active_cnt,
> SUM(case when (A.ot_queue_status_id IN(1,2) AND
> ((C.status_id <> 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3)))
> then (SYSDATE - c.created_on) else 0 END ) age,
> SUM(case when (c.created_on <= (sysdate - 5) AND
> A.ot_queue_status_id IN(1,2) AND ((C.status_id <> 3) OR (C.status_id = 3
> AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days,
> SUM(case when (A.LAST_UPDATED_ON >= (sysdate - 5) AND
> A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days,
> SUM(case when (A.LAST_UPDATED_ON >= (sysdate - 5) AND
> A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON - c.created_on) else 0
> END ) response_old_5days
> FROM tbl_ot_queue A, tbl_circuit C, tbl_opportunity D
> WHERE A.assigned_to = struser
> AND C.opportunity_id = D.opportunity_id
> -- AND (D.key_bid = 'N' OR (D.key_bid = 'Y' AND
> C.release_to_region_on IS NOT NULL))
> AND A.circuit_id = C.circuit_id
> AND A.circuit_current_stage = C.Circuit_Current_Stage
> AND EXISTS (SELECT M.country_code FROM tbl_user_region_country
> M, tbl_user N
> WHERE N.login_id = struser
> AND M.user_id = N.user_id
> AND M.active = 'Y'
> AND M.country_code = C.country_code)
> );
>
> INSERT INTO tbl_qm_dashboard
> SELECT
> dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt))
> ,0)
> avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((respon

RE: (Fwd) Re: DBD::Oracle Slow cursors

[John Scoles]

Why are you asking for help on a Perl Mailing list then???
 
There is nothing we can do for you here it is PLSQL problem.
 
I have some expreriance in PLSQL and all I can know is that sys_refcursors are 
allways going to be slower than direct calls with SQLPlus.
 
Not sure how we can help you here.
 

 Perhaps you should rethink the refcursor idea or try spliting it up into two 
or more calls
 
Cheers


From: jaha...@idexcel.com
To: byter...@hotmail.com; martin.ev...@easysoft.com; dbi-users@perl.org; 
carlso...@llnl.gov; tim.bu...@pobox.com
Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors
Date: Wed, 18 May 2011 13:03:34 +0100










John,
 
Irrespective of I calling in Java or executing it on the SQL developer or in 
TOAD, I am getting the same response time when compared to executing the SQL’s 
separately
 
Regards
P S Jameel Ahamed 
 




From: John Scoles [mailto:byter...@hotmail.com] 
Sent: 18 May 2011 12:21
To: jaha...@idexcel.com; Martin Evans; dbi-users@perl.org; carlso...@llnl.gov; 
Tim Bunce
Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors
 
Does this have anything to do at all with DBD::Oracle???
You mentioned you are calling this with JAVA??
 
Where is the Perl code??
 
> From: jaha...@idexcel.com
> To: martin.ev...@easysoft.com; dbi-users@perl.org; byter...@hotmail.com; 
> carlso...@llnl.gov; tim.bu...@pobox.com
> Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors
> Date: Wed, 18 May 2011 11:07:56 +0100
> 
> Hi All,
> 
> First of all I thank you all in replying to my email. Below are details of
> one of the SP which we are having issues with.
> 
> In the below SP we are passing in a Username and then for that user are
> fetching his counts and other information. The output parameter is a
> sys_refcursur.
> 
> The problem is that when I fire each and every insert and select statement,
> Its firing in 0.2 seconds. But when retrieving these details in the java
> code when it come to the line to loop the cursor then it gets struck for
> about 2 to 3 mins and the response is coming back.
> 
> We have seen this behavior in most of the places where we used this
> sys_refcursur. 
> 
> The table circuit is a huge table of million records and it has all the
> indexces in place and other tables as well.
> 
> I have been googleing for a long time for a solution for this sys-refcursor
> issue and I am not sure why its going back to the DB after executing the SP
> while looping through the cursor.
> 
> We have Oracle 10G 10.1.0.4.
> 
> I have also tried to combain all the select statements into one using the
> UNION command and insert into the tbl_qm_dashboard. The insert fires in 1.12
> seconds, but again the cursuor looping is the issue.
> 
> Note : tbl_qm_dashboard is a temp table.
> 
> Hope I will get an break through with all your expertise.
> 
> PROCEDURE sp_select_dashboard(strUser IN VARCHAR2,
> rcSelectDashboard OUT SYS_REFCURSOR) IS
> 
> BEGIN
> 
> EXECUTE IMMEDIATE 'ALTER SESSION SET
> NLS_COMP=LINGUISTIC';
> EXECUTE IMMEDIATE 'ALTER SESSION SET
> NLS_SORT=BINARY_CI';
> 
> 
> INSERT INTO tbl_qm_dashboard
> SELECT
> dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt))
> ,0)avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old
> _5days/DECODE(closed_old_5days,0,1,closed_old_5days)),0)
> avg_response_old5days
> FROM
> (
> SELECT 'AccessCircuit' dashboard_type,
> SUM(case when (A.ot_queue_status_id IN(1,2) AND
> ((C.status_id <> 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3)))
> then 1 else 0 END ) active_cnt,
> SUM(case when (A.ot_queue_status_id IN(1,2) AND
> ((C.status_id <> 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3)))
> then (SYSDATE - c.created_on) else 0 END ) age,
> SUM(case when (c.created_on <= (sysdate - 5) AND
> A.ot_queue_status_id IN(1,2) AND ((C.status_id <> 3) OR (C.status_id = 3
> AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days,
> SUM(case when (A.LAST_UPDATED_ON >= (sysdate - 5) AND
> A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days,
> SUM(case when (A.LAST_UPDATED_ON >= (sysdate - 5) AND
> A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON - c.created_on) else 0
> END ) response_old_5days
> FROM tbl_ot_queue A, tbl_circuit C, tbl_opportunity D
> WHERE A.assigned_to = struser
> AND C.opportunity_id = D.opportunity_id
> -- AND (D.key_bid = 'N' OR (D.key_bid = 'Y' AND
> C.release_to_region_on IS NOT NULL))
> AND A.circuit_id = C.circuit_id
> AND A.circuit_current_stage = C.Circuit_Current_Stage
> AND EXISTS (SELECT M.country_code FROM tbl_user_region_country
> M, tbl_user N
> WHERE N.login_id = struser
> AND M.user_id = N.user_id
> AND M.active = 

RE: (Fwd) Re: DBD::Oracle Slow cursors

[John Scoles]

Does this have anything to do at all with DBD::Oracle???
You mentioned you are calling this with JAVA??
 
Where is the Perl code??
 
> From: jaha...@idexcel.com
> To: martin.ev...@easysoft.com; dbi-users@perl.org; byter...@hotmail.com; 
> carlso...@llnl.gov; tim.bu...@pobox.com
> Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors
> Date: Wed, 18 May 2011 11:07:56 +0100
> 
> Hi All,
> 
> First of all I thank you all in replying to my email. Below are details of
> one of the SP which we are having issues with.
> 
> In the below SP we are passing in a Username and then for that user are
> fetching his counts and other information. The output parameter is a
> sys_refcursur.
> 
> The problem is that when I fire each and every insert and select statement,
> Its firing in 0.2 seconds. But when retrieving these details in the java
> code when it come to the line to loop the cursor then it gets struck for
> about 2 to 3 mins and the response is coming back.
> 
> We have seen this behavior in most of the places where we used this
> sys_refcursur. 
> 
> The table circuit is a huge table of million records and it has all the
> indexces in place and other tables as well.
> 
> I have been googleing for a long time for a solution for this sys-refcursor
> issue and I am not sure why its going back to the DB after executing the SP
> while looping through the cursor.
> 
> We have Oracle 10G 10.1.0.4.
> 
> I have also tried to combain all the select statements into one using the
> UNION command and insert into the tbl_qm_dashboard. The insert fires in 1.12
> seconds, but again the cursuor looping is the issue.
> 
> Note : tbl_qm_dashboard is a temp table.
> 
> Hope I will get an break through with all your expertise.
> 
> PROCEDURE sp_select_dashboard(strUser IN VARCHAR2,
> rcSelectDashboard OUT SYS_REFCURSOR) IS
> 
> BEGIN
> 
> EXECUTE IMMEDIATE 'ALTER SESSION SET
> NLS_COMP=LINGUISTIC';
> EXECUTE IMMEDIATE 'ALTER SESSION SET
> NLS_SORT=BINARY_CI';
> 
> 
> INSERT INTO tbl_qm_dashboard
> SELECT
> dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt))
> ,0)avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old
> _5days/DECODE(closed_old_5days,0,1,closed_old_5days)),0)
> avg_response_old5days
> FROM
> (
> SELECT 'AccessCircuit' dashboard_type,
> SUM(case when (A.ot_queue_status_id IN(1,2) AND
> ((C.status_id <> 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3)))
> then 1 else 0 END ) active_cnt,
> SUM(case when (A.ot_queue_status_id IN(1,2) AND
> ((C.status_id <> 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3)))
> then (SYSDATE - c.created_on) else 0 END ) age,
> SUM(case when (c.created_on <= (sysdate - 5) AND
> A.ot_queue_status_id IN(1,2) AND ((C.status_id <> 3) OR (C.status_id = 3
> AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days,
> SUM(case when (A.LAST_UPDATED_ON >= (sysdate - 5) AND
> A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days,
> SUM(case when (A.LAST_UPDATED_ON >= (sysdate - 5) AND
> A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON - c.created_on) else 0
> END ) response_old_5days
> FROM tbl_ot_queue A, tbl_circuit C, tbl_opportunity D
> WHERE A.assigned_to = struser
> AND C.opportunity_id = D.opportunity_id
> -- AND (D.key_bid = 'N' OR (D.key_bid = 'Y' AND
> C.release_to_region_on IS NOT NULL))
> AND A.circuit_id = C.circuit_id
> AND A.circuit_current_stage = C.Circuit_Current_Stage
> AND EXISTS (SELECT M.country_code FROM tbl_user_region_country
> M, tbl_user N
> WHERE N.login_id = struser
> AND M.user_id = N.user_id
> AND M.active = 'Y'
> AND M.country_code = C.country_code)
> );
> 
> INSERT INTO tbl_qm_dashboard
> SELECT
> dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt))
> ,0)
> avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old_5d
> ays/DECODE(closed_old_5days,0,1,closed_old_5days)),0) avg_response_old5days
> FROM
> (
> SELECT 'AccessRFQSentSupplier' dashboard_type,
> SUM(case when (A.ot_queue_status_id IN(1,2) AND
> ((C.status_id <> 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3)))
> then 1 else 0 END ) active_cnt,
> SUM(case when (A.ot_queue_status_id IN(1,2) AND
> ((C.status_id <> 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3)))
> then (SYSDATE - c.created_on) else 0 END ) age,
> SUM(case when (c.created_on <= (sysdate - 5) AND
> A.ot_queue_status_id IN(1,2) AND ((C.status_id <> 3) OR (C.status_id = 3 AND
> C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days,
> SUM(case when (A.LAST_UPDATED_ON >= (sysdate - 5) AND
> A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days,
> SUM(case when (A.LAST_UPDATED_ON >= (sysdate - 5) AND
> A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON - c.created_on) else 0
> END ) response_old_5days
> FROM tbl_ot_queue A, tbl_circuit C, tbl_opportunity E
> WHERE A.assigned_to = struser
> AND A.circuit_id = C.circuit_id
> AND A.circuit_current_stage = C.Circuit_Current_Stage
> AND C.opportunity_id =

RE: (Fwd) Re: DBD::Oracle Slow cursors

[John Scoles]

What is the issue exatly?
 
It is just slow??
 
Can you give us some examples code to play with.
 
Slowness can be caused by anything from low-ban width, poor SQL, a badly 
partiioned DB or just too much data??
 

We need to know the version of DBD::Oracle you are using as well
 
Cheers
John
 
 
> Date: Tue, 17 May 2011 22:18:22 +0100
> From: tim.bu...@pobox.com
> To: dbi-users@perl.org
> CC: jaha...@idexcel.com
> Subject: (Fwd) Re: DBD::Oracle Slow cursors
> 
> - Forwarded message from P S Jameel Ahamed  -
> 
> Date: Tue, 17 May 2011 16:11:04 +0100
> From: P S Jameel Ahamed 
> To: tim.bu...@pobox.com
> Subject: Re: DBD::Oracle Slow cursors
> X-Mailer: Microsoft Office Outlook 11
> 
> HI Tim,
> 
> 
> 
> We are facing huge issues with the SYS_refcurors of oracle 10G when returning 
> from Stored procedure. Is
> there any solution you found for the issue?
> 
> 
> 
> Many Thanks in Advance
> 
> 
> 
> Regards
> 
> P S Jameel Ahamed
> 
> Confidentiality Notice: This e-mail message, including any attachments, is 
> for the sole use of the
> intended recipient(s) and may contain confidential and privileged 
> information. Any unauthorized review,
> use, disclosure or distribution is prohibited. If you are not the intended 
> recipient, please contact the
> sender by reply e-mail and destroy all copies of the original message
> 
> - End forwarded message -
  

RE: (Fwd) Re: DBD::Oracle Slow cursors

[Carlson, John W.]

Don't use dynamic queries?  You can use PL/SQL variables directly in the SQL 
statement w/o specifying them as bind variables.  That is, pass them as 
parameters to your stored procedure, then just put them directly in the SQL 
without decoration.  Return XML CLOB instead of cursor?

Just some ideas.  Would need to see code to know what to suggest next.  I am 
not proposing that my solution is any faster, just different.

John

-Original Message-
From: Tim Bunce [mailto:tim.bu...@pobox.com] 
Sent: Tuesday, May 17, 2011 2:18 PM
To: dbi-users@perl.org
Cc: jaha...@idexcel.com
Subject: (Fwd) Re: DBD::Oracle Slow cursors

- Forwarded message from P S Jameel Ahamed  -

Date: Tue, 17 May 2011 16:11:04 +0100
From: P S Jameel Ahamed 
To: tim.bu...@pobox.com
Subject: Re: DBD::Oracle Slow cursors
X-Mailer: Microsoft Office Outlook 11

   HI Tim,



   We are facing huge issues with the SYS_refcurors of oracle 10G when 
returning from Stored procedure. Is
   there any solution you found for the issue?



   Many Thanks in Advance



   Regards

   P S Jameel Ahamed

   Confidentiality Notice: This e-mail message, including any attachments, is 
for the sole use of the
   intended recipient(s) and may contain confidential and privileged 
information. Any unauthorized review,
   use, disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the
   sender by reply e-mail and destroy all copies of the original message

- End forwarded message -

RE: What's the relationship between LongReadLen and TEXTSIZE?

[John Scoles]

Martin Evans would be the expert on that.  But it does sound funny that 
TEXTSIZE is working and LongReadLen is not??  LongReadLen should work no matter 
the driver.
Perhaps you should try DBD::ADO


 
> From: eric.b...@barclayscapital.com
> To: dbi-users@perl.org
> Date: Tue, 17 May 2011 12:11:55 -0400
> Subject: What's the relationship between LongReadLen and TEXTSIZE?
> 
> I'm setting up to use DBD::ODBC with the FreeTDS driver to go against a MS 
> SQL server and am hoping to nail down the specifics of the relationship 
> between setting $dbh->{LongReadLen} and TEXTSIZE.
> 
> So far, LongReadLen has had exactly no impact on retrieval of long data from 
> my MS sql server. Regardless of to what LongReadLen is set, the data that I 
> retrieve is complete beyond the LongReadLeng setting as long as TEXTSIZE is 
> adequately large.
> 
> Setting TEXTSIZE to a value that is less than the length of the data, 
> however, does result in truncated data returned from the dataserver.
> 
> Additionally, truncated values end with an extended ascii char...probably a 
> null...I have to admit I haven't looked yet.
> 
> So, can we just ignore LongReadLen? We're migrating from Sybase, and have 
> been using LongReadLen as a connection parameter in Sybase for years to set 
> the size of long data returned by the dataserver.
> 
> Any light you can shed on this is appreciated.
> 
> Eric
> 
> ___
> 
> This e-mail may contain information that is confidential, privileged or 
> otherwise protected from disclosure. If you are not an intended recipient of 
> this e-mail, do not duplicate or redistribute it by any means. Please delete 
> it and any attachments and notify the sender that you have received it in 
> error. Unless specifically indicated, this e-mail is not an offer to buy or 
> sell or a solicitation to buy or sell any securities, investment products or 
> other financial product or service, an official confirmation of any 
> transaction, or an official statement of Barclays. Any views or opinions 
> presented are solely those of the author and do not necessarily represent 
> those of Barclays. This e-mail is subject to terms available at the following 
> link: www.barcap.com/emaildisclaimer. By messaging with Barclays you consent 
> to the foregoing. Barclays Capital is the investment banking division of 
> Barclays Bank PLC, a company registered in England (number 1026167) with its 
> registered office at 1 Churchill Place, London, E14 5HP. This email may 
> relate to or be sent from other members of the Barclays Group.
> ___
  

RE: Help Needed for DBI

[John Scoles]

Are you uisng activestate Perl?
 
You will also have to insall DBD::Oracle and an instantclient as well.
 
If not you will have to compile DBD::Oracle yourself (takes a little while but 
it can be done) see the windows readmes to find instrunctions
 
Cheers
John Scoles
 

 
> Date: Fri, 29 Apr 2011 20:38:12 +1000
> Subject: Re: Help Needed for DBI
> From: rc...@pcug.org.au
> To: vikrant@rbs.co.uk
> CC: dbi-users@perl.org
> 
> > Hello,
> > I am really struggling to connect to database, I am not sure how to
> > ensure that I HAVE DBI sintalled and if not then how to make it
> > activate
> > it on my machine, I have perl installed but when I give the following
> > command on my command prompt then
> >
> > perl -e 'use DBI; print $DBI::VERSION,"\n";' it gives following
> > errorcd c\
> > Can't find string terminator "'" anywhere before EOF at -e line 1.
> 
> It works for me!
> owen@owen-desktop:~$ perl -e 'use DBI; print $DBI::VERSION,"\n";'
> 1.607
> owen@owen-desktop:~$
> 
> But you are on Windows, and there is always some problem with single
> and double quotes.
> 
> I think windows does not allow single quotes, so try that with double
> quotes, eg # perl -e "use DBI; print $DBI::VERSION,"\n";"
> 
> 
> 
> 
> 
> 
> 
> > Also when I am exccuting a database program it gives the following
> > highlighted error, any help on this will be higly appreciated...
> > #!/usr/bin/perl -w
> > use strict;
> > use dbi();
> > use lib;
> >
> >
> > my $dbh = dbi->connect('dbi:Oracle:',
> > '',
> > '',
> > {
> > RaiseError => 1,
> > AutoCommit => 0
> > }
> > )|| die "Database connection not made:
> > $dbi::errstr";
> > my $sql = qq{ SELECT * FROM tab where rownum<3 };
> > my $sth = $dbh->prepare( $sql );
> > $sth->execute();
> > $dbh->disconnect();
> > Name "dbi::errstr" used only once: possible typo at C:\seven.pl line
> > 14.
> > Can't locate object method "connect" via package "dbi" at C:\seven.pl
> > line 7.
> 
> 
> Again, my lack of windows knowledge fails me, and maybe capitalization
> doesn't matter, but the package is DBI, so I would have written,
> 
> use DBI;
> ...
> my $dbh = DBI->connect('dbi:Oracle:', ...
> 
> Again, perhaps the single quotes may cause a problem, try double
> quotes if need be
> 
> 
> 
> Owen
> 
> 
> 
> 
  

Re: General DBI spec Question?

[John Scoles]

 On 18/04/2011 3:29 PM, Tim Bunce wrote:

[redirected to dbi-users list]

On Fri, Apr 15, 2011 at 03:05:59PM -0400, John Scoles wrote:

I was given a patch for DBD::Oracle today the just of it was to open
up the OCI commands for starting up and shuting down the DB.

So the question is would this be out of the DBI spec to allow direct
control of the DB like this or does this fall under the custom
function  category.

The general approach is for drivers to add such things as private
methods. If several drivers have implemented similar functionality
then, and only then, would it be safe to consider extending the DBI to
cover that kind of functionality.


That's what I figured.

Tim.

p.s. In this case I'd like to see the Oracle::OCI module revived.
It could handle this requirement and much else besides.

No movement on that mod in what 10 years

Cheers
John

Re: Need Perl DBI and DBD modules for Perl 5.8.8

[John Scoles]

 On 13/04/2011 5:19 PM, mmilli...@fruit.com wrote:
Well nothing special

The latest DBI and DBD::Oracle and I would go with the latest version of 
the Oracle instant client 11g.


Compiling for AIX is always a bit problematic depending on how the 
system is set up, how the Perl was compiled and which C compiler you have.


DBI should install no problem you will most likely hit a snag when it 
comes to DBD::Oracle.


Once you give that a try post the results of the Perl Makefile.PL and 
the nmake and we will see what we can come up with.


Cheers
John



Hello Experts,

Need to recompile the DBI and DBD modules for perl 5.8.8 and the server
uses Oracle 11g on AIX 6.1.5 If anyone has done this or know which module
we need to download your help is reall appreciated, we need to response as
quick as possible. Thanks


Have a great Day!
Senior Unix Technical Support
Michael Milliner
Ph. 270.781.6400 Ext: 2108
**
This communication contains information which is confidential and
may also be privileged. It is for the exclusive use of the intended
recipient(s). If you are not the intended recipient(s), please note
that any distribution, copying or use of this communication or the
information in it is strictly prohibited. If you have received this
communication in error, please notify the sender immediately and
then destroy any copies of it.
**

Re: DBI Installation

[John R Pierce]

On 04/07/11 3:10 AM, Ganesh KS wrote:

Hi,
when i run the command,
perl  D:\Perl\DBI-1.616\DBI-1.616\Makefile.pl
I am getting the attached error. Please can you tell a possible solution.



read the error message, follow the instructions

RE: DBD::Oracle 1.27 and bind_col()

[John Scoles]

I will have to look into this one in detail later next week as I am off at a 
conferace this week. Seems like something got in there.
 
can you send me run with DBD_Verbose=7
 
Cheers
John
 
> Date: Thu, 24 Mar 2011 16:26:00 +0100
> From: alexan...@foken.de
> To: dbi-users@perl.org
> Subject: DBD::Oracle 1.27 and bind_col()
> 
> Hello List,
> 
> could someone with a working DBD::Oracle 1.28 please verify that the 
> tests 2 and 4 in the attached test script fail?
> 
> All test pass with DBD::Oracle 1.23, tests 2 and 4 fail with DBD::Oracle 
> 1.27.
> 
> Background information: I use Perl to provide access to an ancient 
> legacy system, that system sends strings to a Perl server, the server 
> invokes DBI methods and passes those strings as parameters. The call to 
> $sth->bind_col() worked fine with DBD::Oracle 1.23, it crashes with 
> "Invalid column number" on DBD::Oracle 1.27. I think DBD::Oracle broke 
> somewhere between 1.23 and 1.27.
> 
> In dbdim.c of DBD::Oracle 1.27, dbd_st_bind_col() tests SvIOK(col), but 
> makes no attempts to convert col to a number first. Older versions seem 
> to use a default implementation of bind_col provided by DBI in DBI.xs. 
> In that file, dbih_sth_bind_col() fetches the column number by calling 
> SvIV(col).
> 
> Thanks,
> Alexander
  

RE: Error 'making DBD:Oracle 1.28 on Cygwin W

[John Scoles]

Ok I think I know what it is looks like I forgot to put
 
OCIServerRelease
 
in the 
 
oci.def
 
Add the word in there and see what happens
 
Silly mistake really but hard to catch  unless you have the right test system 
handy
 
We would of caught this if we had a broader test page for the Release 
Cadidates, hint hint nudge nudge ;) ;)
 
Cheers
John Scoles
 
> Date: Thu, 24 Mar 2011 13:27:20 +0100
> From: alexan...@foken.de
> To: dbi-users@perl.org
> CC: jason.thurs...@gmail.com
> Subject: Re: Fwd: Error 'making DBD:Oracle 1.28 on Cygwin W
> 
> DBD::Oracle 1.27 compiled, tested and installed without any problems. 
> The changes in 
> http://search.cpan.org/~pythian/DBD-Oracle-1.28/Changes#Changes_in_DBD-Oracle_1.28_%28svn_rev_14765%29>
>  
> don't look relevant to my environment. I can live with that.
> 
> Now it is clear that some change from 1.27 to 1.28 broke compatibilty 
> with Oracle 10.2g.
> 
> Alexander
> 
> On 03/24/2011 12:18 PM, Alexander Foken wrote:
> > Hmmm, on my other system, DBD::Oracle 1.23 and Oracle 10.2g work 
> > perfectly. So, what's the difference?
> >
> > XP vs. 2000 -- unlikely
> > Much older DBD::Oracle
> >
> > Looking at the DBD::Oracle diffs available at search.cpan.org, I see 
> > that OCIServerRelease exists in the diffs from 1.27 to 1.28 (in 
> > Oracle.xs, condition added to avoid calling OCIServerVersion on 
> > servers < 10.2), and in the diffs from 1.24b to 1.25 (don't quite 
> > understand what happens there). But I see no change containing 
> > OCIServerVersion in the diffs from 1.24b to 1.25.
> >
> > I will try compiling 1.27, then 1.23 after lunch ...
> >
> > Alexander
> >
> > On 03/24/2011 11:59 AM, Alexander Foken wrote:
> >> Hello,
> >>
> >> sorry, just a "me too" posting. I was just about to ask for nearly(?) 
> >> the same problem.
> >>
> >> I'm trying with Strawberry Perl 5.12.2.0 and 5.10.0.4, Oracle 10g, 
> >> DBD::Oracle 1.28, Windows 2000 Professional.
> >>
> >> Final problem after tons of warnings: oci8.o: oci8.c: undefined 
> >> reference to 'OCIServerRelease'
> >>
> >> See attached logfile of running perl Makefile.PL and dmake (with Perl 
> >> 5.12, same messages with 5.10).
> >>
> >> I would guess that there is a problem with DBD::Oracle, because we 
> >> get the same missing symbol for different OS versions, different 
> >> Perl ports, different Perl versions, different DBI versions, 
> >> different Oracle versions.
> >>
> >> Alexander
> >>
> >> On 03/24/2011 12:15 AM, Jason Thurston wrote:
> >>> Hello,
> >>>
> >>> Does anyone have any ideas where to go from here?
> >>>
> >>> I think "perl Makefile.PL" is successful but I get an error when
> >>> trying the next step "make".
> >>> The Error I get when trying to "make" is
> >>> "/home/foo/DBD-Oracle-1.28/oci8.c:4619: undefined reference to
> >>> `_OCIServerRelease'". See Below for details.
> >>>
> >>> I originally tried with the cygwin cpan tool but then downloaded the
> >>> packaged and tried manually but got the exact same error.
> >>>
> >>>
> >>> DETAILS:
> >>>
> >>> OS:
> >>> Windows 2003 Enterprise X64 Edition
> >>>
> >>> $ uname -a
> >>> CYGWIN_NT-5.2-WOW64 mck-rnmwv4-ip42 1.7.8(0.236/5/3) 2011-03-01 09:36
> >>> i686 Cygwin
> >>>
> >>> $ env|grep -i ora|two
> >>> ORACLE_USER=foo1/foo1@foo
> >>> ORACLE_SID=foo
> >>> ORACLE_USERID=foo1/foo1@foo
> >>> TWO_TASK=foo
> >>> TNS_ADMIN=E:\oracle\instantclient_11_2
> >>> PATH=/usr/local/bin:/usr/bin:/cygdrive/e/oracle/instantclient_11_2:/cygdrive/c/WINDOWS/system32:/cygdrive/c/WINDOWS:/cygdrive/c/WINDOWS/System32/Wbem:/cygdrive/c/WINDOWS/system32/WindowsPowerShell/v1.0:/cygdrive/e/PROGRA~1/BMCSOF~1/Patrol3/bin:/cygdrive/e/PROGRA~1/BMCSOF~1/MASTER~1/server/bin:/cygdrive/c/WINDOWS/system32/WindowsPowerShell/v1.0:/opt/java/bin
> >>>  
> >>>
> >>> CLASSPATH=.;E:\oracle\instantclient_11_2/ojdbc6.jar
> >>> ORACLE_HOME=E:\oracle\instantclient_11_2
> >>>
> >>> $ pwd
> >>> /home/foo/DBD-Oracle-1.28
> >>>
> >>>
> >>> $ perl -v
> >>>
> >>> This is perl, v5.10.1 (*) built for i686-cygwin-thread-multi-64int
> >&

Re: (Fwd) Re: DBD::Oracle: table_info, PUBLIC schema

[John Scoles]

 On 21/03/2011 2:49 PM, Tim Bunce wrote:

- Forwarded message from Satish Patil  -

Date: Mon, 21 Mar 2011 07:37:45 -0700 (PDT)
From: Satish Patil
To: tim.bu...@pobox.com, s.goeld...@eurodata.de
Subject: Re: DBD::Oracle: table_info, PUBLIC schema
X-Mailer: YahooMailClassic/11.4.20 YahooMailWebService/0.8.109.295617

Hi Tim and Steffen,

How can I get the sample schema for DBD or scripts for DBD. Any URL.

Not sure what you mean by schema?

Do you want a schema to play with or to find out info on a schema or a 
tutorial for DBI/DBD


Either way use this
http://search.cpan.org/~pythian/DBD-Oracle-1.28/Oracle.pm#Metadata
to look up the info on about a schema

and this one for a tutorial

http://www.pythian.com/news/wp-content/uploads/introduction-dbd-oracle.html

cheers
John


Please send it to me asap.

Thanks,

Satish Patil

- End forwarded message -

Re: Understanding trace output

[John Scoles]

Well you are getting a ORA-24812 error so that tells me it is some
sort of Pass through error.

Where this error is taking place is anyone's guess.  It could be bad
data in the table or a misconfiguration in the TNS entry or your local
ENV.

You might want to set the ora_charset, or  ora_ncharset on your
connection string with DBIx

cheers
John


On Thu, Mar 10, 2011 at 4:01 PM, Ivan Wills  wrote:
> On 10 March 2011 10:09, John Scoles  wrote:
>
>>  CSID in this case is the the national character set in the first
>> environemtn it is 873 in the second it is 1 so it cannot traslate betwwen
>> one and another.
>>
>> As well it is the csform =0 that worries me more as that should come out as
>> the  as one of the allowable something like SQLCS_NCHAR or SQLCS_EXPLICIT
>>
>> Can we see more of the trace??
>>
>> cheers
>> John
>>
>> > Date: Thu, 10 Mar 2011 09:19:11 +1100
>> > Subject: Understanding trace output
>> > From: ivan.wi...@gmail.com
>> > To: dbi-users@perl.org
>>
>> >
>> > Hi,
>> >
>> > I'm trying to debug a problem with some code that is erroring when trying
>> > read a CLOB field from an oracle 10g database through DBIx::Class and
>> direct
>> > DBI calls in Catalyst. I can get the code to work when I am not trying to
>> do
>> > through Catalyst.
>> >
>> > I have noticed the following trace line from the Catalyst call
>> > dbd_rebind_ph(): bind :p1 <== '152756' (in, not-utf8, csid 873->0->873,
>> > ftype 1 (VARCHAR), csform 0->0, maxlen 8, maxdata_size 0)
>> > when I run the code the out side catalyst the line changes to
>> > dbd_rebind_ph(): bind :p1 <== '152756' (in, not-utf8, csid 1->0->1, ftype
>> > 1 (VARCHAR), csform 0->0, maxlen 8, maxdata_size 0)
>> >
>> > It looks like something is changing/setting some value to cause the
>> change
>> > but I can't work out what would be doing it or how to reset the value.
>> >
>> > Any suggestions on where to look or how to set the csid value?
>> >
>> > Thanks,
>> > Ivan
>> >
>> > --
>> > email/jabber: ivan.wi...@gmail.com
>> > /
>> > / _ _
>> > / \ / | | | |
>> > / \/ \_| | |
>>
>
> Hi John,
> Here is the rest of the trace:
>
>    DBI 1.609-ithread default trace level set to 0x0/4 (pid 4122) at
> Inbox.pm line 386 via Inbox.pm line 346
>    -> prepare_cached for DBD::Oracle::db (DBI::db=HASH(0x???)~0x???
> 'SELECT me.id, me.part_ref, me.image_id, me.text_id, me.binary_id, me.name,
> me.bin_mimetype, md_mms_repos_text.text, md_mms_repos_text.text_mime FROM
> md_mms_repos_part me LEFT JOIN md_mms_repos_text md_mms_repos_text ON
> md_mms_repos_text.text_id = me.text_>
> 1   -> prepare for DBD::Oracle::db (DBI::db=HASH(0x???)~INNER 'SELECT
> me.id, me.part_ref, me.image_id, me.text_id, me.binary_id, me.name,
> me.bin_mimetype, md_mms_repos_text.text, md_mms_repos_text.text_mime FROM
> md_mms_repos_part me LEFT JOIN md_mms_repos_text md_mms_repos_text ON
> md_mms_repos_text.text_id = me.text_id WHERE ( >
>    dbd_preparse scanned 1 distinct placeholders
>    dbd_st_prepare'd sql SELECT (pl1, auto_lob1, check_sql1)
>    dbd_describe SELECT (EXPLICIT, lb 200)...
> Describe col #1 type=2(NVARCHAR2)
> Described col  1: dbtype 2(NVARCHAR2), scale -127, prec 0, nullok 0, name ID
>          : dbsize 22, char_used 0, char_size 0, csid 0, csform 0, disize
> 171
>    fbh 1: 'ID'»NO null , otype   2->  5, dbsize 22/172, p0.s-127
> Describe col #2 type=2(NVARCHAR2)
> Described col  2: dbtype 2(NVARCHAR2), scale -127, prec 0, nullok 0, name
> PART_REF
>          : dbsize 22, char_used 0, char_size 0, csid 0, csform 0, disize
> 171
>    fbh 2: 'PART_REF'»··NO null , otype   2->  5, dbsize 22/172, p0.s-127
> Describe col #3 type=2(NVARCHAR2)
> Described col  3: dbtype 2(NVARCHAR2), scale -127, prec 0, nullok 1, name
> IMAGE_ID
>          : dbsize 22, char_used 0, char_size 0, csid 0, csform 0, disize
> 171
>    fbh 3: 'IMAGE_ID'»··NULLable, otype   2->  5, dbsize 22/172, p0.s-127
> Describe col #4 type=2(NVARCHAR2)
> Described col  4: dbtype 2(NVARCHAR2), scale -127, prec 0, nullok 1, name
> TEXT_ID
>          : dbsize 22, char_used 0, char_size 0, csid 0, csform 0, disize
> 171
>    fbh 4: 'TEXT_ID'»···NULLable, otype   2->  5, dbsize 22/172, p0.s-127
> Describe col #5 type=2(NVARCHAR2)
> Described col  5: dbtype 2(NVARCHAR2), scale -127, prec 0, nullok 1, name
> BINARY_ID
>          :

RE: Understanding trace output

[John Scoles]

CSID in this case is the the national character set in the first environemtn it 
is 873 in the second it is 1 so it cannot traslate betwwen one and another.
 
As well it is the csform =0 that worries me more as that should come out as the 
 as one of the allowable something like SQLCS_NCHAR or SQLCS_EXPLICIT
 
Can we see more of the trace??
 
cheers
John
 
> Date: Thu, 10 Mar 2011 09:19:11 +1100
> Subject: Understanding trace output
> From: ivan.wi...@gmail.com
> To: dbi-users@perl.org
> 
> Hi,
> 
> I'm trying to debug a problem with some code that is erroring when trying
> read a CLOB field from an oracle 10g database through DBIx::Class and direct
> DBI calls in Catalyst. I can get the code to work when I am not trying to do
> through Catalyst.
> 
> I have noticed the following trace line from the Catalyst call
> dbd_rebind_ph(): bind :p1 <== '152756' (in, not-utf8, csid 873->0->873,
> ftype 1 (VARCHAR), csform 0->0, maxlen 8, maxdata_size 0)
> when I run the code the out side catalyst the line changes to
> dbd_rebind_ph(): bind :p1 <== '152756' (in, not-utf8, csid 1->0->1, ftype
> 1 (VARCHAR), csform 0->0, maxlen 8, maxdata_size 0)
> 
> It looks like something is changing/setting some value to cause the change
> but I can't work out what would be doing it or how to reset the value.
> 
> Any suggestions on where to look or how to set the csid value?
> 
> Thanks,
> Ivan
> 
> -- 
> email/jabber: ivan.wi...@gmail.com
> /
> / _ _
> / \ / | | | |
> / \/ \_| | |
  

RE: Numeric and string columns.

[John Scoles]

Common to all DBD me thinks.  Me thinks you only get in or out scalars (ie 
strings) unless you tell your DBD differently
 
I think you have to use  $sth->bind_co
 
 
to get your int back as an int try something like this
 $sth->bind_col(1, undef, { TYPE => SQL_INTEGER });
hope this helps
 
> From: b...@wards.net
> Date: Fri, 25 Feb 2011 17:56:59 -0800
> Subject: Re: Numeric and string columns.
> To: mose...@hank.org
> CC: dbi-users@perl.org
> 
> Sounds like DBD::Pg should know better than to do that!
> But then, DBD::mysql has the same problem. So maybe it is at the DBI level?
> 
> On Fri, Feb 25, 2011 at 5:50 PM, Bill Moseley  wrote:
> 
> > Columns coming from DBD::Pg seems to all be Perl strings and then when I
> > encode to json then end up quoted:
> >
> > music=# \d cd;
> > Table "public.cd"
> > Column | Type | Modifiers
> > -+-+-
> > id | integer | not null default nextval('cd_id_seq'::regclass)
> > year | integer |
> > name | text |
> > artist | integer | not null
> > deleted | boolean | not null default false
> > Indexes:
> > "cd_pkey" PRIMARY KEY, btree (id)
> >
> >
> > my $x = $dbh->selectrow_hashref( "select * from cd where id = 1" );
> > $x->{foo} = 0;
> >
> > print Dumper $x;
> > print encode_json( $x );
> >
> > Results in:
> >
> > $VAR1 = {
> > 'artist' => '1',
> > 'name' => 'One first CD',
> > 'foo' => 0,
> > 'deleted' => '0',
> > 'id' => '1',
> > 'year' => '2010'
> > };
> > {"artist":"1","name":"One first
> > CD","foo":0,"deleted":"0","id":"1","year":"2010"}
> >
> > Notice how the deleted boolean and integer columns are quoted, but the
> > "foo"
> > I injected is not quoted?
> >
> > In a javascript library we are using it's seeing the deleted value as true.
> > So, I'm curious if I can make DBI (or DBD::Pg) return the non-text columns
> > looking like numbers to Perl.
> >
> > I suppose I could do something silly like:
> >
> > /^\d+$/ && ($_ += 0) for values %{$x};
> >
> >
> > which then returns:
> >
> > {"artist":1,"name":"One first CD","foo":0,"deleted":0,"id":1,"year":2010}
> >
> > --
> > Bill Moseley
> > mose...@hank.org
> >
> 
> 
> 
> -- 
> Check out my LEGO blog at http://www.brickpile.com/
> View my photos at http://flickr.com/photos/billward/
> Follow me at http://twitter.com/williamward
  

Re: Problem with UTF8 and array binding....

[John Scoles]

 On 17/02/2011 4:38 PM, Bobak, Mark wrote:
Sorry Mark I saw that

PI_CIBD_CODE_TAB was a clob so that is where I though you where having your 
problem. There is such a thing
as a nvarchar never used it myself perhaps that will help.





Sorry, but I'm not sure I understand the suggestion.  NCLOB is (as far as I can 
see), not in the picture...

Datatype of table is VARCHAR2(255 CHAR)

The PL/SQL table type is table of table_name.column_name%type indexed by 
binary_integer

IN TYPE is named type defined as the table type mentioned above...

I don't see how/where NCLOB fits into the picture?

-Mark

-Original Message-----
From: John Scoles [mailto:sco...@pythian.com]
Sent: Thursday, February 17, 2011 4:10 PM
To: dbi-users@perl.org
Subject: Re: Problem with UTF8 and array binding

   On 17/02/2011 3:05 PM, Bobak, Mark wrote:

Perhaps you have to declare you in type as a NCLOB??

Cheers
John

Hi all,

I'm running into a 'PLS-00418: array bind type must match PL/SQL table row 
type' error, but only when passing UTF8 data.

The details are as follows.  I have a PL/SQL packaged function that looks like 
this:
FUNCTION MSTInsUpdCIT(
PI_CGP_ID   IN  VARCHAR2
,PI_CBL_ID   IN  VARCHAR2
,PI_VCL_ID   IN  VARCHAR2
,PI_CIL_LOCATOR  IN  VARCHAR2
,PI_VCL_ID_TAB   IN  vcl_id_tab_type
,PI_CIL_LOCATOR_TAB  IN  cil_locator_tab_type
,PI_CIT_HITLISTLINE  IN  VARCHAR2
,PI_CIT_ALPHA_DATE   IN  VARCHAR2
,PI_CIT_START_DATE   IN  VARCHAR2
,PI_CIT_END_DATE IN  VARCHAR2
,PI_CIT_SORT_DATEIN  VARCHAR2
,PI_CIT_NUMERIC_DATE IN  VARCHAR2
,PI_VIBC_ID_TAB  IN  VARCHAR2
,PI_CIBD_CODE_TABIN  CLOB
,PI_CIT_ABS_WORD_CNT IN  NUMBER
,PI_CIT_TXT_WORD_CNT IN  NUMBER
,PI_CIT_TYPE IN  VARCHAR2
,PI_CIT_UNDATED_FLAG IN  VARCHAR2
,PI_CIT_DELETE_FLAG  IN  VARCHAR2
,PI_ROUTER_PRIORITY  IN  NUMBER
,PI_CIT_COVERIN  VARCHAR2
,PO_VCS_IDOUT NUMBER
,PO_DELETE_FLAG  OUT  varchar2
,PO_HOLD_STATUS  OUT  varchar2
,PO_CIT_EXISTS   OUT NUMBER
,po_cit_id   OUT VARCHAR2
,PO_MESSAGE_GROUP_ID OUT VARCHAR2) RETURN NUMBER;

The 'cil_locator_tab_type' is defined as:
TYPE cil_locator_TAB_TYPE is table of cnt_item_locators.cil_locator%type index 
by binary_integer;

And cnt_item_locators.cil_locator is VARCHAR2(255 CHAR).

Database characterset is AL32UTF8.

In Perl, I'm defining an array reference as:
my $cil_locator_tab = ['2004322656 Vädskulturmuseet i Göorg',
 '9789187484124 (Vädskulturmuseet i Göorg)',
 '2004322656 Vädskulturmuseet i Göorg'];

which has UTF8 characters in it.

Next, I'm setting the multibyte flag via:
Encode::_utf8_on(@{$cil_locator_tab}[0]);
Encode::_utf8_on(@{$cil_locator_tab}[1]);
Encode::_utf8_on(@{$cil_locator_tab}[2]);

Finally, I define a param_hash as:
my $paramHash = {cbl_id   =>   '23481',
   vcl_id   =>   '15',
   cil_locator  =>   '2004322656  Vädskulturmuseet i Göorg',
   vcl_id_tab   =>   $vcl_id_tab,
   cil_locator_tab  =>   $cil_locator_tab,
   hitlistline  =>   'Viñ cielo, luna y arena. El espacio 
Calchaquín el folclore argentino moderno~~~2003~Book Chapter',
   alphadate=>   '2003',
   startdate=>   '20030101',
   sortdate =>   '20030101',
   numdate  =>   '20030101',
   vibc_id_tab  =>   '1',
   cibd_code_tab=>   'MLA',
   cit_abs_word_cnt =>   '0',
   cit_txt_word_cnt =>   '0',
   cit_undated_flag =>   'N',
   cit_delete_flag  =>   'N',
   router_priority  =>   '5',
   cover_flag   =>   'N',
  };
And the binding of the datatype is here:
  $sth->bind_param(':PI_CIL_LOCATOR_TAB', $paramHash->{cil_locator_tab},
  {ora_type =>   ORA_VARCHAR2_TABLE, ora_maxarray_numentries =>   
$maxrows});

Now, I call my PL/SQL, passing the paramHash, and I'm getting the PLS-00418 
error described above.

So, I guess I need to understand how to map my bind variable to match my PL/SQL 
table type.

This seems to work for non-UTF8 data, so, I'm not sure if this is a coding 
error in my code, or some DBD/DBI bug?

Can anyone offer any insights?

Thanks,

-Mark

Re: Problem with UTF8 and array binding....

[John Scoles]

 On 17/02/2011 3:05 PM, Bobak, Mark wrote:

Perhaps you have to declare you in type as a NCLOB??

Cheers
John

Hi all,

I'm running into a 'PLS-00418: array bind type must match PL/SQL table row 
type' error, but only when passing UTF8 data.

The details are as follows.  I have a PL/SQL packaged function that looks like 
this:
FUNCTION MSTInsUpdCIT(
   PI_CGP_ID   IN  VARCHAR2
,PI_CBL_ID   IN  VARCHAR2
,PI_VCL_ID   IN  VARCHAR2
,PI_CIL_LOCATOR  IN  VARCHAR2
,PI_VCL_ID_TAB   IN  vcl_id_tab_type
,PI_CIL_LOCATOR_TAB  IN  cil_locator_tab_type
,PI_CIT_HITLISTLINE  IN  VARCHAR2
,PI_CIT_ALPHA_DATE   IN  VARCHAR2
,PI_CIT_START_DATE   IN  VARCHAR2
,PI_CIT_END_DATE IN  VARCHAR2
,PI_CIT_SORT_DATEIN  VARCHAR2
   ,PI_CIT_NUMERIC_DATE IN  VARCHAR2
,PI_VIBC_ID_TAB  IN  VARCHAR2
,PI_CIBD_CODE_TABIN  CLOB
,PI_CIT_ABS_WORD_CNT IN  NUMBER
,PI_CIT_TXT_WORD_CNT IN  NUMBER
,PI_CIT_TYPE IN  VARCHAR2
,PI_CIT_UNDATED_FLAG IN  VARCHAR2
,PI_CIT_DELETE_FLAG  IN  VARCHAR2
,PI_ROUTER_PRIORITY  IN  NUMBER
,PI_CIT_COVERIN  VARCHAR2
,PO_VCS_IDOUT NUMBER
,PO_DELETE_FLAG  OUT  varchar2
,PO_HOLD_STATUS  OUT  varchar2
,PO_CIT_EXISTS   OUT NUMBER
,po_cit_id   OUT VARCHAR2
,PO_MESSAGE_GROUP_ID OUT VARCHAR2) RETURN NUMBER;

The 'cil_locator_tab_type' is defined as:
TYPE cil_locator_TAB_TYPE is table of cnt_item_locators.cil_locator%type index 
by binary_integer;

And cnt_item_locators.cil_locator is VARCHAR2(255 CHAR).

Database characterset is AL32UTF8.

In Perl, I'm defining an array reference as:
my $cil_locator_tab = ['2004322656 Vädskulturmuseet i Göorg',
'9789187484124 (Vädskulturmuseet i Göorg)',
'2004322656 Vädskulturmuseet i Göorg'];

which has UTF8 characters in it.

Next, I'm setting the multibyte flag via:
Encode::_utf8_on(@{$cil_locator_tab}[0]);
Encode::_utf8_on(@{$cil_locator_tab}[1]);
Encode::_utf8_on(@{$cil_locator_tab}[2]);

Finally, I define a param_hash as:
my $paramHash = {cbl_id   =>  '23481',
  vcl_id   =>  '15',
  cil_locator  =>  '2004322656  Vädskulturmuseet i Göorg',
  vcl_id_tab   =>  $vcl_id_tab,
  cil_locator_tab  =>  $cil_locator_tab,
  hitlistline  =>  'Viñ cielo, luna y arena. El espacio 
Calchaquín el folclore argentino moderno~~~2003~Book Chapter',
  alphadate=>  '2003',
  startdate=>  '20030101',
  sortdate =>  '20030101',
  numdate  =>  '20030101',
  vibc_id_tab  =>  '1',
  cibd_code_tab=>  'MLA',
  cit_abs_word_cnt =>  '0',
  cit_txt_word_cnt =>  '0',
  cit_undated_flag =>  'N',
  cit_delete_flag  =>  'N',
  router_priority  =>  '5',
  cover_flag   =>  'N',
 };
And the binding of the datatype is here:
 $sth->bind_param(':PI_CIL_LOCATOR_TAB', $paramHash->{cil_locator_tab},
 {ora_type =>  ORA_VARCHAR2_TABLE, ora_maxarray_numentries =>  
$maxrows});

Now, I call my PL/SQL, passing the paramHash, and I'm getting the PLS-00418 
error described above.

So, I guess I need to understand how to map my bind variable to match my PL/SQL 
table type.

This seems to work for non-UTF8 data, so, I'm not sure if this is a coding 
error in my code, or some DBD/DBI bug?

Can anyone offer any insights?

Thanks,

-Mark

Re: BCP for passing $password to a command-line Perl script?

[John Scoles]

 On 16/02/2011 2:45 AM, Ivan Shmakov wrote:
Well if you are using DBD::Oracle I would just use an Oracle Wallet to 
do that for you.


I am sure there are other solutions as well.

Cheers

BTW, what is the best current practice to pass ->connect ()
$password to a command-line Perl script?

Both specifying it via a command-line argument or via an
environment variable (DBI_PASS) is insecure, as on some systems
this information could easily be seen by the other users on the
same host.

Perhaps, some variation on Net::Netrc should be used instead?

My primary interests currently are PostgreSQL, which can use
Kerberos, and SQLite, which uses filesystem access rights
instead, but just for the case…

DBD-Oracle-1.28 Release Candidate 1

[John Scoles]

Here is the latest and greatest DBD::Oracle for your programming pleasure.

You can find the Zip file here

href="http://www.pythian.com/news/wp-content/uploads/DBD-Oracle-1.28_RC_1.zip";>DBD-Oracle-1.28-RC1.zip


This is a long overdue maintenance release  that fixes a large number of 
bug and issues which are detailed below in the Changes.


Don't worry there are some new goodies in this release namely I have 
added in 4 new

server side tracing/debugging attributes

ora_driver_name
For 11g and later you can now set the name of the driver layer using OCI.
PERL, PERL5, ApachePerl so on. Names starting with "ORA" are reserved. You
can enter up to 8 characters.  If none is enter then this will default to
DBDO where  is the current version number. This value can be
retrieved on the server side using V$SESSION_CONNECT_INFO or
GV$SESSION_CONNECT_INFO

ora_client_info
When passed in on the connection attributes it can specify any info you want
onto the session up t0 64 bytes. This value can be
retrieved on the server side using V$SESSION view.

ora_client_identifier
When passed in on the connection attributes it specifies the user 
identifier

in the session handle. Most useful for web app as it can pass in the session
user name which might be different than the connection user name. Can be up
to 64 bytes long do not to include the password for security reasons and the
first character of the identifier should not be ':'. This value can be
retrieved on the server side using V$SESSION view.

ora_action
You can set this value to anything you want up to 32byes.This value can be
retrieved on the server side using V$SESSION view.

We have also added in the connection attribute

ora_connect_with_default_signals

Whit this attribute you can localize the $SIG{} so this should solve the
problems with  $SIG{} events that sometimes occur when using DBD::Oracle


Finally I would like to thank Martin Evans for volunteering to be 
another co-maintainer of DBD::Oracle




Cheers
John Scoles


Changes
  Added connection attribute 'ora_connect_with_default_signals' that 
will localize Perl's $SIG{INT} handler from Brian Phillips and T. Bunce
  Fix in execute_array to stop possible endless loop when using a fetch 
sub by Martin J. Evans
  Adapted Martin J. Evans' ODBC 70execute_array.t into t/26exe_array.t 
by John Scoles
  Fix for execute_array to bring it up to spec. by Martin J. Evans and 
John Scoles
  Marked ProC, Oraperl.pm, ora_explain.pl, ora_context, 
ora_use_proc_connection and ora_parse_lang as deprecated  to be removed 
in 1.29
  Added in 4 new server side debug/trace attributes, ora_driver_name, 
ora_client_info, ora_session_user and ora_action on the connection 
handle from John Scoles

  Cleaned up the pod a little by John Scoles
  Fix for function name length, Some function names are over 31char 
long which may cause problems for some OS/Compilers (VMS IA64 box.) from 
Jakob Snoer
  Fix for OCIPing in case where a 10 client tries to ping a <10 DB 
from Tim Oertel
  Fix for DBD-Oracle stored proc with array bug where second call array 
size is unchanged from Tim Oertel

  Fix for rt.cpan.org Ticket #=63332: Spelling error in POD from jonasbn
  Fix for rt.cpan.org Ticket #=62152: t/28array_bind.t and t/31lob.t 
may call plan() twice and others do not fail on not connect from John Scoles
  Fix for rt.cpan.org Ticket #=61511 ORA-00942 when inserting into a 
table with a LOB column over a synonym on HP-UX from Kris Lemaire
  Fix for rt.cpan.org Ticket #=42842 Test 31lob fails with 64-bit 
Instant Client by John Scoles
  Fix for support for objects on big endian platforms from Charles 
Jardine, John R Pierce
  Fix for rt.cpan.org Ticket #=61225 Windows install (Stawberry Perl) 
fails on long path names from David Tulloh
  Fix for rt.cpan.org Ticket #=rt64524 Memory Leak when Oracle 
connection fails by Martin J. Evans
  Added all the missing ora_drcp values to dbh private_attribute_info 
by Martin J. Evans
  Removed a load of attributes from sth private_attribute_info which 
are not handle attributes but attributes to bind_param/prepare by Martin 
J. Evans
  Fix for rt.cpan.org Ticket #=64244 - don't bail out, skip tests 
when we cannot connect by Martin J. Evans and John Scoles

  Added DBI to PREREQ_PM in Makefile.PL by Martin J. Evans
  Added build_requires in Makefile.PL by Martin J. Evans
  Added workaround for ExtUtils::MakeMaker problems by Martin J. Evans
  Added LICENSE to Makefile.PL by Martin J. Evans

Cheers John Scoles

Re: Errors with installing DBD-Oracle-1.27

[John Scoles]

 On 11/02/2011 1:16 AM, Jayadevan M wrote:

Hmm looking at the env and the MakeFile.pl output perhaps your 
ORACLE_HOME is not correct


I would give /u01/app/oracle/product/10.2.0/ a try rather than

/u01/app/oracle/product/10.2.0/db_1

just a suggestion

Cheers
John

Hi,
I am tying to install DBD-Oracle-1.27 on CentOS (32 bit). Details of 
path, version etc are all available in the log files attached as per 
instructions in README.


1) perl Makefile.PL - Output is in perlMake.log

2) make - Output  is in make.log

3) make test - Output is in maketest.log

4)Environment variables are there in env.log

The issue seems to be that it is trying to get files from lib32 when 
my libraries are there in lib. For example, perl Makefile.PL says
cat: /u01/app/oracle/product/10.2.0/db_1/lib32/sysliblist: No such 
file or directory


I have

-bash-3.2# ls -al /u01/app/oracle/product/10.2.0/db_1/*lib32*/sysliblist
ls: /u01/app/oracle/product/10.2.0/db_1/lib32/sysliblist: No such file 
or directory


-bash-3.2# ls -al /u01/app/oracle/product/10.2.0/db_1/lib/sysliblist
-rw-rw-rw- 1 oracle oinstall 31 Jun 22  2005 
/u01/app/oracle/product/10.2.0/db_1/*lib*/sysliblist



How can I fix this?

Thanks and Regards,
Jayadevan M







DISCLAIMER:

"The information in this e-mail and any attachment is intended only 
for the person to whom it is addressed and may contain confidential 
and/or privileged material. If you have received this e-mail in error, 
kindly contact the sender and destroy all copies of the original 
communication. IBS makes no warranty, express or implied, nor 
guarantees the accuracy, adequacy or completeness of the information 
contained in this email or any attachment and is not liable for any 
errors, defects, omissions, viruses or for resultant loss or damage, 
if any, direct or indirect."

Re: AIX DBD::Oracle perl Makefile.PL core dumps

[John Scoles]

On Fri, Jan 28, 2011 at 4:01 PM, Williams, Stephen <
stephen.willi...@twcable.com> wrote:

> root@nimdinf01:/var/tmp/DBD-Oracle-1.27 ] perl Makefile.PL
> Illegal instruction (core dumped)
>
> Here is a truss
>
> statx("/usr/opt/perl5/lib/5.8.8/aix-thread-multi/auto/DBI", 0x20008CB8,
> 128, 010) Err#2  ENOENT
> statx("/usr/opt/perl5/lib/5.8.8/auto/DBI", 0x20008CB8, 128, 010) Err#2
>  ENOENT
> statx("/usr/opt/perl5/lib/site_perl/5.8.8/aix-thread-multi/auto/DBI",
> 0x20008CB8, 128, 010) = 0
> statx("/usr/opt/perl5/lib/site_perl/5.8.8/aix-thread-multi/auto/DBI/DBI.so",
> 0x20008CB8, 128, 010) = 0
> statx("/usr/opt/perl5/lib/site_perl/5.8.8/aix-thread-multi/auto/DBI/DBI.bs",
> 0x20008CB8, 128, 010) = 0
> __loadx(0x014D0080, 0x2FF205C0, 0x0960, 0x2043E708, 0x) =
> 0xF21CDE80
> loadquery(2, 0x20146158, 0x1000)= 0
> __loadx(0x02000200, 0xF2136400, 0x3E80, 0xF21CDE80, 0x204451C0) =
> 0x
> __loadx(0x0700, 0x2043D5C8, 0x0017, 0xF21CDE80, 0x0197) =
> 0xF21D2588
>Received signal #4, SIGILL [default]
> *** process killed ***
>
> Also on a side note I am usingo compile since it was used by IBM to compile
> Perl.
>
>
> I see from the perl -v

Compiler:
   cc='cc_r', ccflags ='-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -


the compiler was cc_r

is that the same as xlC?

and even if it is it has to be the exact same version of xlC

Hope this helps/

If all else fails rebuild your perl from scratch with xlC t if you have a
few hours to wast;)

Cheers
John

>
> -Original Message-
> From: John Scoles [mailto:sco...@pythian.com]
> Sent: Friday, January 28, 2011 3:59 PM
> To: Williams, Stephen
> Cc: dbi-users@perl.org
> Subject: Re: AIX DBD::Oracle perl Makefile.PL core dumps
>
>  Installing dbd oracle on any version AIX has always been problematic.
> Usually you have to rebuild your perl with the exact same version of
> compiler that compiled the perl.
>
> If you are using the Perl that comes with AIX that may be hard to do.
>
> As well there are always 64-32 bit issues.
>
> Can you post what happens on the perl Makefile.PL
>
> Cheers
> John
>
> On Fri, Jan 28, 2011 at 3:22 PM, Williams, Stephen <
> stephen.willi...@twcable.com> wrote:
>
> > Afternoon all,
> >
> > I am having trouble with trying to compile the DBD drive for Oracle on my
> > AIX 5.3 TL11 host. I have DBI-1.616 installed on the node fine, but when
> I
> > go to run the perl Makefile.PL I keep getting a core dump. The perl
> > installation is the OS supplied perl (information below).
> >
> > root@testnode:/var/tmp/AIX/DBD-Oracle-1.27<mailto:root@testnode
> :/var/tmp/AIX/DBD-Oracle-1.27>
> > ] perl Makefile.PL
> > Illegal instruction (core dumped)
> >
> > root@testnode:/var/tmp/AIX/DBD-Oracle-1.27<mailto:root@testnode
> :/var/tmp/AIX/DBD-Oracle-1.27>
> > ] perl -V
> > Summary of my perl5 (revision 5 version 8 subversion 8) configuration:
> >  Platform:
> >osname=aix, osvers=5.3.0.0, archname=aix-thread-multi
> >uname='aix akash79 3 5 00011a85d600 '
> >config_args='-desr -Dinstallprefix=/usr/opt/perl5
> > -Dprefix=/usr/opt/perl5 -Dcc=xlc_r -Duseshrplib -Dusethreads'
> >hint=recommended, useposix=true, d_sigaction=define
> >usethreads=define use5005threads=undef useithreads=define
> > usemultiplicity=define
> >useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
> >use64bitint=undef use64bitall=undef uselongdouble=undef
> >usemymalloc=n, bincompat5005=undef
> >  Compiler:
> >cc='cc_r', ccflags ='-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE
> > -qmaxmem=-1 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32
> > -D_LARGE_FILES -qlonglong',
> >optimize='-O',
> >cppflags='-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=-1
> > -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT'
> >ccversion='9.0.0.2', gccversion='', gccosandvers=''
> >intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=4321
> >d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=8
> >ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t',
> > lseeksize=8
> >alignbytes=8, prototype=define
> >  Linker and Libraries:
> >ld='ld', ldflags =' -brtl -bdynamic -b32'
> >libpth=/lib /usr/lib /usr/ccs/lib
> >libs=-lbind -lnsl -lgdbm -ldbm -ldb -ldl -lld -lm -lc

Re: AIX DBD::Oracle perl Makefile.PL core dumps

[John Scoles]

 Installing dbd oracle on any version AIX has always been problematic.
Usually you have to rebuild your perl with the exact same version of
compiler that compiled the perl.

If you are using the Perl that comes with AIX that may be hard to do.

As well there are always 64-32 bit issues.

Can you post what happens on the perl Makefile.PL

Cheers
John

On Fri, Jan 28, 2011 at 3:22 PM, Williams, Stephen <
stephen.willi...@twcable.com> wrote:

> Afternoon all,
>
> I am having trouble with trying to compile the DBD drive for Oracle on my
> AIX 5.3 TL11 host. I have DBI-1.616 installed on the node fine, but when I
> go to run the perl Makefile.PL I keep getting a core dump. The perl
> installation is the OS supplied perl (information below).
>
> root@testnode:/var/tmp/AIX/DBD-Oracle-1.27<mailto:root@testnode:/var/tmp/AIX/DBD-Oracle-1.27>
> ] perl Makefile.PL
> Illegal instruction (core dumped)
>
> root@testnode:/var/tmp/AIX/DBD-Oracle-1.27<mailto:root@testnode:/var/tmp/AIX/DBD-Oracle-1.27>
> ] perl -V
> Summary of my perl5 (revision 5 version 8 subversion 8) configuration:
>  Platform:
>osname=aix, osvers=5.3.0.0, archname=aix-thread-multi
>uname='aix akash79 3 5 00011a85d600 '
>config_args='-desr -Dinstallprefix=/usr/opt/perl5
> -Dprefix=/usr/opt/perl5 -Dcc=xlc_r -Duseshrplib -Dusethreads'
>hint=recommended, useposix=true, d_sigaction=define
>usethreads=define use5005threads=undef useithreads=define
> usemultiplicity=define
>useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
>use64bitint=undef use64bitall=undef uselongdouble=undef
>usemymalloc=n, bincompat5005=undef
>  Compiler:
>cc='cc_r', ccflags ='-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE
> -qmaxmem=-1 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32
> -D_LARGE_FILES -qlonglong',
>optimize='-O',
>cppflags='-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=-1
> -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT'
>ccversion='9.0.0.2', gccversion='', gccosandvers=''
>intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=4321
>d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=8
>ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t',
> lseeksize=8
>alignbytes=8, prototype=define
>  Linker and Libraries:
>ld='ld', ldflags =' -brtl -bdynamic -b32'
>libpth=/lib /usr/lib /usr/ccs/lib
>libs=-lbind -lnsl -lgdbm -ldbm -ldb -ldl -lld -lm -lcrypt -lpthreads -lc
> -lbsd
>perllibs=-lbind -lnsl -ldl -lld -lm -lcrypt -lpthreads -lc -lbsd
>libc=, so=a, useshrplib=true, libperl=libperl.a
>gnulibc_version=''
>  Dynamic Linking:
>dlsrc=dl_aix.xs, dlext=so, d_dlsymun=undef, ccdlflags='
>  -bE:/usr/opt/perl5/lib/5.8.8/aix-thread-multi/CORE/perl.exp'
>cccdlflags=' ', lddlflags='-bhalt:4 -bexpall -G -bnoentry -lpthreads
> -lc'
>
>
> Characteristics of this binary (from libperl):
>  Compile-time options: MULTIPLICITY PERL_IMPLICIT_CONTEXT
>PERL_MALLOC_WRAP USE_ITHREADS USE_LARGE_FILES
>USE_PERLIO USE_REENTRANT_API
>  Built under aix
>  Compiled at Jun  2 2009 16:11:40
>  @INC:
>/usr/opt/perl5/lib/5.8.8/aix-thread-multi
>/usr/opt/perl5/lib/5.8.8
>/usr/opt/perl5/lib/site_perl/5.8.8/aix-thread-multi
>/usr/opt/perl5/lib/site_perl/5.8.8
>/usr/opt/perl5/lib/site_perl
>.
>
> Thanks,
> -Stephen
>
>
>
> 
> This E-mail and any of its attachments may contain Time Warner Cable
> proprietary information, which is privileged, confidential, or subject to
> copyright belonging to Time Warner Cable. This E-mail is intended solely for
> the use of the individual or entity to which it is addressed. If you are not
> the intended recipient of this E-mail, you are hereby notified that any
> dissemination, distribution, copying, or action taken in relation to the
> contents of and attachments to this E-mail is strictly prohibited and may be
> unlawful. If you have received this E-mail in error, please notify the
> sender immediately and permanently delete the original and any copy of this
> E-mail and any printout.
>

--
The best compliment you could give Pythian for our service is a referral.

Re: DBD::Oracle: Object support on sparc platforms

[John Scoles]

Yeah that has been around for a long time

I will put that into trunk so it will get into 1.28 which should be out next
week sometime.

Thanks allot Charles

Cheers
John

On Fri, Jan 28, 2011 at 10:25 AM, Charles Jardine  wrote:

> Attached is a patch against DBD::Oracle version 1.27 which corrects
> some type mismatches which prevent the support for objects working
> on big-endian platforms such as Sun-sparc.
>
> There was a thread on this topic in this mail list last April.
> See http://www.mail-archive.com/dbi-users@perl.org/msg32902.html
>
> The original poster reported that tests in t/58object.t in
> version 1.24a did not work on his sparc platform. I posted a patch.
> the OP tested it, and found that my patch allowed him to get further,
> but later tests still failed. I posted a second patch and asked the OP
> to test it. I never got a reply.
>
> The first patch got into DBD::Oracle-1.27, but the second patch,
> being untested, did not.
>
> I have now managed to borrow some sparc hardware. I have re-worked
> the second patch for 1.27, and tested it. With the patch applied,
> DBD::Oracle passes all its tests on a 64-bit sparc running
> Solaris 10. I can now run some of my production scripts, which
> use objects, on the sparc.
>
> I recommend that this patch be incorporated into version 1.28.
>
> --
> Charles Jardine - Computing Service, University of Cambridge
> c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
>

--
The best compliment you could give Pythian for our service is a referral.

Re: Downside to ora_envhp=>0 to fix OCIHandleAlloc(OCI_HTYPE_ERROR) problem

[John Scoles]

I would say simply convert over to Apache::DBI you would not have to change
any of your code except

use DBI;  to

use Apache::DBI


Will most likely solve any problems you are having
cheers


On Thu, Jan 27, 2011 at 4:26 PM, Bill Ward  wrote:

>
>
> On Thu, Jan 27, 2011 at 4:04 AM, John Scoles  wrote:
>
>>  On 26/01/2011 3:35 PM, Bill Ward wrote:
>>
>>> On Wed, Jan 26, 2011 at 12:27 PM, John Scoles
>>>  wrote:
>>>
>>>> Or, is there some way to verify the OCI environment and reset it when
>>>>> it is found to be unusable - in other words, trap the
>>>>> "OCIHandleAlloc(OCI_HTYPE_ERROR)" error and reconnect?
>>>>>
>>>>>  Well normally an environment will not die unless you loose
>>>> connectivity.  If
>>>> you have lost connectivity then there would not be much point in trying
>>>> to
>>>> reconnect automatically your network or DB is Down??
>>>>
>>> Databases go down sometimes.  There could be a power failure or
>>> hardware crash, or maybe the DB is just being bounced by the DBAs
>>> after making a configuration change or upgrade.  The problem is even
>>> if the database comes back up, it still keeps generating the same
>>> error.  Why should I also have to manually restart the Apache server
>>> just because the database has come back online?
>>>
>> That's odd, Are you using Apache::DBI ??? Which version of DBD::Oracle and
>> OCI are you using?
>>
>>
> Nope.  These apps were written long before Apache::DBI was a twinkle in
> Edmund's eye.
>
> We're using DBI 1.607, DBD::Oracle 1.22, and Apache 2.2.9 with mod_perl
> 2.04
>
> I don't know about OCI in particular but we're using Oracle database
> version 10.2.0.4.0.
>
> <#12dc95fa6bae3667_>
> <#12dc95fa6bae3667_> <#12dc95fa6bae3667_>   
> <#12dc95fa6bae3667_>
>

--
The best compliment you could give Pythian for our service is a referral.

Re: Downside to ora_envhp=>0 to fix OCIHandleAlloc(OCI_HTYPE_ERROR) problem

[John Scoles]

 On 26/01/2011 3:35 PM, Bill Ward wrote:

On Wed, Jan 26, 2011 at 12:27 PM, John Scoles  wrote:

Or, is there some way to verify the OCI environment and reset it when
it is found to be unusable - in other words, trap the
"OCIHandleAlloc(OCI_HTYPE_ERROR)" error and reconnect?


Well normally an environment will not die unless you loose connectivity.  If
you have lost connectivity then there would not be much point in trying to
reconnect automatically your network or DB is Down??

Databases go down sometimes.  There could be a power failure or
hardware crash, or maybe the DB is just being bounced by the DBAs
after making a configuration change or upgrade.  The problem is even
if the database comes back up, it still keeps generating the same
error.  Why should I also have to manually restart the Apache server
just because the database has come back online?
That's odd, Are you using Apache::DBI ??? Which version of DBD::Oracle 
and OCI are you using?


cheers
John


  dbi->connect() should
just start working once the DB is back online.  Once that error occurs
once, how about deleting the OCI environment so that the next
->connect after that will succeed?

Re: Downside to ora_envhp=>0 to fix OCIHandleAlloc(OCI_HTYPE_ERROR) problem

[John Scoles]

On 25/01/2011 8:21 PM, William Ward gmail wrote:

In regards to this thread:
http://www.mail-archive.com/dbi-users@perl.org/msg32115.html
(found via google)

I'm having the same problem at $JOB and before we throw ora_envhp=>0
into our connect attribute, we were wondering if this will cause
performance issues?  Is there a downside to ora_envhp that isn't
described in the thread?
Yes that will case some performance slow downs as it will have to 
connect and recreate the environment(connection) for each dbi->connect().
Normally it will reuse the environment(connection) and just create a new 
session for each dbi->connect().


Or, is there some way to verify the OCI environment and reset it when
it is found to be unusable - in other words, trap the
"OCIHandleAlloc(OCI_HTYPE_ERROR)" error and reconnect?



Well normally an environment will not die unless you loose connectivity. 
 If you have lost connectivity then there would not be much point in 
trying to reconnect automatically your network or DB is Down??


Who knows how long it will take to come back up?

I have seen people use Ping to keep the session alive which does test 
the connection each time (does only a single a round trip, in later 
DBD::ORacle versions) if this fails then their programs tries to create 
a new environment(connection) x number of times and eventually fails.



I think there way too many variables here for DBD::Oracle to take care 
of me thinks, there are so many ways to disconnect/loose a session or 
loose connectivity to get them all covered.


Just off the top of my head there are 3 ways to loose a session and at 
least that many for a connection that can be started on the server end 
of things. At this point in time there is no way in DBD::Oracle to tell 
how you lost connectivity, connection or session



What I am working on now for 1.29 (March) is User callback and TAF 
(http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/ocitaf.htm) which 
should address some of these issues


Though TAF is rather obsolete now so I do not see many people using it.

Once that is in there you will at least be able to know how you lost 
connectivity and then program for that.


cheers John

Re: When might DBI v2 be released

[John Scoles]

 On 25/01/2011 10:40 AM, Jonathan Leffler wrote:


On Tue, Jan 25, 2011 at 07:28, John Scoles <mailto:sco...@pythian.com>> wrote:


 On 25/01/2011 10:25 AM, Jonathan Leffler wrote:

Please change the subject line to match the content of the
question!
(Old subject: "DBI 1.611 + DBD::mysql 4.016 = segfaults on
Ubuntu 10.10
amd64 ?"

On Tue, Jan 25, 2011 at 06:54, Tony
Espositomailto:tony1234567...@yahoo.co.uk>>wrote:

Hello Tim,
  Just curious, Tim, when is DBI 2.0 to be released?
 Thanks for all your
hard
work, by the way.  DBI->great_stuff()!

IIRC, the plan (if the term is justified for it) is for DBI v2
to be
released some time (shortly) after Perl 6 is released.


Ah sometime before Easter then;)


Is Perl 6 at that stage, or was there no year specified with that 
Easter (like there hasn't been a year specified for Perl 6 for a 
while).  I gave up tracking Perl 6 a few years ago.  I know that 
Parrot and Rakudo are around and related, but ...


Sorry to get your hopes up Jonathan it is sort of an 'in' joke from one 
of the YAPCes so no year is specified.


I just replaces the 'Perl 6 will be out by Christmas' line.

I guess the idea is now that there is 'a working beta' the first 
production release would be earlier in the year than 'Christmas'.


Cheers
John
(http://dev.perl.org/perl6/faq.html doesn't give any indication of 
which Easter - and previous announcements from 2009 about 2010 show up 
with a Google search for 'perl6 release date').


There was (is?) a DBI v2 mailing list - I don't think I've
seen any messages
on it for about 5 years, and there never were more than a very
few.


--
Jonathan Leffler <mailto:jonathan.leff...@gmail.com>>  #include 

Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease 
to be amused."

Re: When might DBI v2 be released

[John Scoles]

 On 25/01/2011 10:25 AM, Jonathan Leffler wrote:

Please change the subject line to match the content of the question!
(Old subject: "DBI 1.611 + DBD::mysql 4.016 = segfaults on Ubuntu 10.10
amd64 ?"

On Tue, Jan 25, 2011 at 06:54, Tony Espositowrote:


Hello Tim,
   Just curious, Tim, when is DBI 2.0 to be released?  Thanks for all your
hard
work, by the way.  DBI->great_stuff()!


IIRC, the plan (if the term is justified for it) is for DBI v2 to be
released some time (shortly) after Perl 6 is released.


Ah sometime before Easter then;)

There was (is?) a DBI v2 mailing list - I don't think I've seen any messages
on it for about 5 years, and there never were more than a very few.

Re: make test failed with DBD::Oracle 1.27

[John Scoles]

 On 05/01/2011 4:31 AM, ZHANG Jiaqiang A wrote:

Yep those are two known bugs with 64bit system.

Still working on getting rid of them.  It is hard to reproduce  but one 
of the ones we will be fixing soon


The good news is unless you are using embedded objects like VARRY, 
OBJECT or TABLE you can ignore the 58object tests


On 31lob front the test that fails is a rater obscure OCI check dealing 
with the length of a returned lob.  I doubt it will give you a problem


cheers
John

Hello All,

I need your help, now I am trying to use DBD::Oracle on Oracle 11g on Solaris 
10.

I am blocked with two test files: t/31lob.t and t/58object.t:

1)There are some garbled characters when I executed the 31lob.t alone.
2)When I executed the 58object.t alone, it quits without any error msg.


~/DBD-Oracle-1.27$make test
PERL_DL_NONLAZY=1 /home/tester/perl/bin/perl "-MExtUtils::Command::MM" "-e" 
"test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/01base.t .. # Test loading DBI, DBD::Oracle and version
t/01base.t .. ok
t/10general.t ... ok
t/12impdata.t ... ok
t/14threads.t ... ok
t/15nls.t ... ok
t/20select.t  ok
t/21nchar.t . ok
t/22nchar_al32utf8.t  ok
t/22nchar_utf8.t  ok
t/23wide_db.t ... ok
t/23wide_db_8bit.t .. ok
t/23wide_db_al32utf8.t .. ok
t/24implicit_utf8.t . ok
t/25plsql.t . ok
t/26exe_array.t . ok
t/28array_bind.t  ok
t/30long.t .. ok
t/31lob.t ... Dubious, test returned 1 (wstat 256, 0x100)
Failed 4/12 subtests
t/31lob_extended.t .. ok
t/32xmltype.t ... ok
t/34pres_lobs.t . ok
t/40ph_type.t ... 1/19  Placeholder behaviour for ora_type=1 
VARCHAR2 (the default) varies with Oracle version.
 Oracle 7 didn't strip trailing spaces, Oracle 8 did, until 9.2.x
 Your system doesn't. If that seems odd, let us know.
t/40ph_type.t ... ok
t/50cursor.t  ok
t/51scroll.t  ok
t/55nested.t  ok
t/56embbeded.t .. ok
t/58object.t  Dubious, test returned 1 (wstat 256, 0x100)
Failed 28/51 subtests
t/60reauth.t  skipped: ORACLE_USERID_2 not defined.
t/70meta.t .. ok
t/80ora_charset.t ... ok


Test Summary Report
---
t/31lob.t (Wstat: 256 Tests: 8 Failed: 0)
  Non-zero exit status: 1
  Parse errors: Bad plan.  You planned 12 tests but ran 8.
t/58object.t  (Wstat: 256 Tests: 23 Failed: 0)
  Non-zero exit status: 1
  Parse errors: Bad plan.  You planned 51 tests but ran 23.
Files=30, Tests=2254, 85 wallclock secs ( 2.34 usr  0.72 sys + 44.29 
cusr  4.22 csys = 51.57 CPU)
Result: FAIL
Failed 2/30 test programs. 0/2254 subtests failed.
*** Error code 255
make: Fatal error: Command failed for target `test_dynamic'


~/DBD-Oracle-1.27$perl -Mlib=./lib,./blib/lib,./blib/arch t/31lob.t
1..12
ok 1 - returned valid locator
ok 2 - inserted into BLOB successfully
ok 3 - got back what we put in
ok 4 - returned valid locator
ok 5 - returned valid locator
ok 6 - returned initialized locator
ok 7 - returned length
ok 8 - returned written value
Errors in file :
OCI-21500: internal error code, arguments: [kpullas-1], [], [], [], [], 
[], [], []

}ãØ¿yÍßsl`}ãØ¿yÍßsl`}ãØ¿yÍßsl`}ãØ¿yÍßsl`}ãØ¿yÍßsl`}ãØ¿yÍßsl`Errors
 in file :
OCI-21500: internal error code, arguments: [kpullas-1], [], [], [], [], 
[], [], []

}ãØ¿yÍßsl`}ãØ¿yÍßsl`}ãØ¿yÍßsl`}ãØ¿yÍßsl`}ãØ¿yÍßsl`}ãØ¿yÍßsl`~/DBD-Oracle-1.27$

~/DBD-Oracle-1.27$perl -Mlib=./lib,./blib/lib,./blib/arch t/58object.t
1..51
ok 1 - use DBI;
ok 2 - ora_objects flag is set to 1
ok 3 - ora_objects flag is set to 0
ok 4 - The object isa DBI::db
ok 5 - Fetch current schema name
ok 6 - old: Prepare select
ok 7 - old: Execute select
ok 8 - old: Fetch first row
ok 9 - old: Row 1 column 2 is an ARRAY
ok 10 - old: Row 1 column 2 is has 2 elements
ok 11 - old: Fetch second row
ok 12 - old: Row 2 column 2 is an ARRAY
ok 13 - old: Row 2 column 2 is has 2 elements
ok 14 - old: Fetch third row
ok 15 - 

Re: DBD::Oracle, 10g Lob Refetch problem

[John Scoles]

 On 21/12/2010 10:48 AM, Furst, Carl wrote:

Thanks to all of you for taking the time to answer my questions.
Unfortunately I don't have the output from the compile. However consider
that we are able to connect, select, insert, and update regular fields, it
would seem that for the most part things were successful, unless I'm missing
something.

As to the make test. Pretty much all tests passed, however there was one
test that failed and that was, you guessed it, the 31lob.t test. The
31lob_extended.t passed.

So 31lob.t produced :
t/31lob.t ... 2/12 DBD::Oracle::db do failed: ORA-00942: table
or view does not exist (DBD ERROR: error possibly near<*>  indicator at char
14 in 'select * from<*>v$session where 0=1') [for Statement "select * from
v$session where 0=1"] at t/31lob.t line 79.


If the above is the case then I think I know what your problems int

In later release of 10 and 11 they have removes some permissions by 
default.  One of them is the create session


Check with your DBA that the use that is connection has the create 
session privilege.  Usually this get turned off with an upgrade to 10 or 11.


The root cause is DBD::Oracle will want to create a new session for your 
ORA_CLOB to use to insert all of your clob no matter how big.


I works without the ORA_CLOB because it is just a straight insert up to 
a set value ,the value of 'LongReadLen' me thinks?


Hope this helps.

Cheers
John Scoles


the user doesn't have access to v$session and we can't really change that.

Anyway, as I have updated before it would seem that the use of the ora_types
constants is what's affecting this. When I bind parameters using an %attr
hash with bind params, it triggers the lob refetch issue. Without it,
however things seems fine.

my bind params call was something like

$sth->bind_param($binder_symbol, $value, {ora_types=>ORA_CLOB,
ora_field=>$fieldname});

So without the anonymous hash ref, things seem hunky dory, however the docs
say I need those params for handling lobs, so it has me worried.

I'll keep y'all posted.

Thanks,

Carl Furst
CMS Developer
MLB Advanced Media
-Original Message-
From: John Scoles [mailto:sco...@pythian.com]
Sent: Tuesday, December 21, 2010 8:51 AM
To: dbi-users@perl.org
Subject: Re: DBD::Oracle, 10g Lob Refetch problem

   On 20/12/2010 3:17 PM, Furst, Carl wrote:

Hello,

I just built DBD::Oracle 1.26 on Solaris SPARC 2.10 using perl 5.8.5 32

bit

against client 10.0.2.4

We've been having trouble since day one. The biggest problem is that we

are

having a problem writing LOB fields. We get the following error:

DBD::Oracle::st execute failed: ORA-00903: invalid table name (DBD
ERROR: OCIStmtExecute/LOB refetch)


To start we will need to see the output from the perl Makefile.PL and
the make, and make test to see if there is something wrong with the way
it is being built


We think it's the LOB refetch that's causing the issue.

the encoding of the database and the NLS_LANG parameter are both UTF8
nls_lang specifically is AMERICAN_AMERICA.UTF8

If anyone has any advice about this, it would be a big help.


Next we will need to see an example of your code as there are at least 4
different ways to write lob fields with DBD::Oracle


My questions are the following:
1) do we need an actual Oracle server to build the DBD - if so what libs
would we need to link against?

No you do not need a server you only need the client.  Instant client
should work fine

2) Has anyone else experienced this; building again lib32 client libs.

Solaris SPARC tends to have more problems that other OS when attempting to
compile DBD::Oracle in 32bit but since you
have it working you should be past this point



3) What role does oraperl have in all this? If oraperl fails to compile,

is

that a blocker for DBD?


No it is a separate and deprecated hunk of code for perl 4.  It may be a
symptom of a deeper problem so we need to see the Makefile.pl and make
output to know for sure.

Cheers
John Scoles

Thanks in advance,

Carl Furst
CMS Developer
MLB Advanced Media







**

MLB.com: Where Baseball is Always On








**

MLB.com: Where Baseball is Always On

Re: DBD::Oracle, 10g Lob Refetch problem

[John Scoles]

 On 20/12/2010 3:17 PM, Furst, Carl wrote:

Hello,

I just built DBD::Oracle 1.26 on Solaris SPARC 2.10 using perl 5.8.5 32 bit
against client 10.0.2.4

We've been having trouble since day one. The biggest problem is that we are
having a problem writing LOB fields. We get the following error:

DBD::Oracle::st execute failed: ORA-00903: invalid table name (DBD
ERROR: OCIStmtExecute/LOB refetch)



To start we will need to see the output from the perl Makefile.PL and 
the make, and make test to see if there is something wrong with the way 
it is being built



We think it's the LOB refetch that's causing the issue.

the encoding of the database and the NLS_LANG parameter are both UTF8
nls_lang specifically is AMERICAN_AMERICA.UTF8

If anyone has any advice about this, it would be a big help.

Next we will need to see an example of your code as there are at least 4 
different ways to write lob fields with DBD::Oracle



My questions are the following:
1) do we need an actual Oracle server to build the DBD - if so what libs
would we need to link against?
No you do not need a server you only need the client.  Instant client 
should work fine

2) Has anyone else experienced this; building again lib32 client libs.


Solaris SPARC tends to have more problems that other OS when attempting to 
compile DBD::Oracle in 32bit but since you
have it working you should be past this point



3) What role does oraperl have in all this? If oraperl fails to compile, is
that a blocker for DBD?

No it is a separate and deprecated hunk of code for perl 4.  It may be a 
symptom of a deeper problem so we need to see the Makefile.pl and make 
output to know for sure.


Cheers
John Scoles

Thanks in advance,

Carl Furst
CMS Developer
MLB Advanced Media







**

MLB.com: Where Baseball is Always On

Re: DBD-Oracle Compilation error on OpenVMS

[John Scoles]

Thanks for that

However I an not sure what you did

 dbd_phs_ora_varchar2_table_
>
> fixup_after_execute
>
> with
>
>dbd_phs_ora_varchar2_table_fea
> in
>

does not make any sense to me.  I thinks your email was cut off somehow

can you attach a copy of your DBDIMP.C file so I can have a look at it.

Cheers
John


On Thu, Dec 16, 2010 at 12:50 PM, Jakob noer  wrote:

> I have installed DBD-Oracle on an OpenVMS IA64 box.
>
> Versions:
>
> oracle 10.2.0.4 (Client - Db is 11.1.0.7 on solaris)
> DBI 1.615
> DBD 1.26
> Perl 5.8.6
>
>
> It is as such completed, but I got this error on the way:
>
>
> CC/DECC
> /Include=[]/Standard=Relaxed_ANSI/Prefix=All/Obj=.obj/List/Machine/Show=Expan
> /NOANSI_ALIAS/float=ieee/ieee=denorm_results/D
>
> efine=(UTF8_SUPPORT,"ORA_OCI_VERSION=""10.2.0.4""",ORA_OCI_102,"VERSION=""1.26""","XS_VERSION=""1.26""")/Include=(perl_root:[lib.VMS
>
> _IA64.5_8_6.CORE],GLO120:[ORACLE.ORACLE102.oracli.rdbms],GLO120:[ORACLE.ORACLE102.oracli.rdbms.public],GLO120:[ORACLE.ORACLE102.orac
>
> li.rdbms.demo],GLO120:[ORACLE.ORACLE102.oracli.rdbms.demo.oci_demo],GLO120:[ORACLE.ORACLE102.oracli.netconfig.demo],perl_root:[lib.s
> ite_perl.VMS_IA64.auto.DBI])/List/Machine/Show=Expan  DBDIMP.c
>
>if(exe_count <= 0)
> ...^
> %CC-I-QUESTCOMPARE, In this statement, the unsigned expression "exe_count"
> is being compared with a relational operator to a constant whose value is
> not greater than zero.  This might not be what you intended.
> at line number 3564 in file USR:[PERLMOD.DBD-ORACLE-1_26]DBDIMP.C;1
>
> int dbd_phs_ora_varchar2_table_fixup_after_execute(phs_t *phs){
> ^
> %CC-W-LONGEXTERN, The external identifier name exceeds 31 characters;
> truncated to "DBD_PHS_ORA_VARCHAR2_TABLE_FIXU".
> at line number 1897 in file USR:[PERLMOD.DBD-ORACLE-1_26]DBDIMP.C;1
>
> int dbd_phs_ora_number_table_fixup_after_execute(phs_t *phs){
> ^
> %CC-W-LONGEXTERN, The external identifier name exceeds 31 characters;
> truncated to "DBD_PHS_ORA_NUMBER_TABLE_FIXUP_".
> at line number 2263 in file USR:[PERLMOD.DBD-ORACLE-1_26]DBDIMP.C;1
> %MMK-F-ERRUPD, error status %X10B91260 occurred when updating target
> DBDIMP.OBJ
>
> This may well be an OpenVMS problem, that an external identifier name must
> not exceed 31 charachters ?
>
> What I did was replace all occurrencies of
>
>dbd_phs_ora_varchar2_table_fixup_after_execute
>
> with
>
>dbd_phs_ora_varchar2_table_fea
> in
>
>DBDIMP.C
>
> which helped a lot.
>
> Is this a known problem? or a serious one ?
> Venlig hilsen/Regards
>
> Jakob Snoer
>

--
>From the Pythian family to yours, Happy Holidays and all the best in 2011!

Re: DBD-Oracle on AIX

[John Scoles]

I went through this  and checked the patch and it was not fully applied

I have patched trunk and your can find it here

http://svn.perl.org/modules/dbd-oracle/trunk

Please note It will be release in 1.28 not 1.27

Cheers
John Scoles

On Tue, Sep 14, 2010 at 6:48 PM, John R Pierce  wrote:

>  On 09/14/10 6:58 AM, John Scoles wrote:
>
>>  On 9/14/2010 9:17 AM, John R Pierce wrote:
>>
>>> Sorry,  I meant, the make test problems with t/58object on big endian
>>> machines like Sparc, IBM Power
>>>
>>
>> Ok that clears it up a little.
>>
>> I am not sure if it was  fixed yet.
>>
>> Give the latest trunk version a try and see what happens.  Just a side
>> note if you are not using VARRY, TABLE or OBJECT in your tables you can
>> ignore this failure as you will never hit it.
>>
>
> yup, aware thats the case, and yes, we've been ignoring the failure as the
> perl programs in question are truly ancient and wouldn't know what these
> things are if they bit them :)
>
> I'll try and find the time to get around to building this again from trunk
> and seeing whether or not it recurs.
>
>
>

--
>From the Pythian family to yours, Happy Holidays and all the best in 2011!

Re: oracle multiple cursor sub query in different DBI versions

[John Scoles]

A little slow on this one and I apologize for that.

Just closing things out for 1.28 version of DBD::Oracle and noticed this one
hanging about.

So I though I would give it a few tests

I checked 1.24 and it fails but it seems to be fixed in 1.62 and in trunk

So I will check this one off my list for 1.28

Cheers
John Scoles



On Wed, Aug 4, 2010 at 2:27 PM, LK  wrote:

> In the process of moving from centos 4 to a centos 5 machine one
> script stopped working. I distilled it down to this problem :
>
>
> #! /usr/bin/perl -w
> use strict;
> use DBI;
>
> my $dbh = DBI->connect("dbi:Oracle:host=;sid=", '',
> '',
>   {RaiseError => 1,
>AutoCommit => 0,
>LongReadLen => 5000
>}
>   ) || die "Database connection not made:
> $DBI::errstr";
>
> my $q = qq/
>  SELECT
>(2+3),
>cursor(select 2+3 from dual),
>cursor(select 3+3 from dual)
>FROM table where rownum < 10
> /;
>
> my $sth = $dbh->prepare($q);
> $sth->execute();
> while ( my @d = $sth->fetchrow_array ) {
>  print join(" " , @d) . "\t";
>  while ( my @g = $d[1]->fetchrow_array ) {
>  print join(" " ,@g) . "\t";
>  }
>   while ( my @g = $d[2]->fetchrow_array ) {
>  print join(" " ,@g ). "\t";
>  }
>  print "\n";
> }
>
> no problem in sqlplus for this query in either machine.
>
> On the server with DBI 1.58 , DBD::Oracle 1.19 and oracle instant
> client 10.1 it works fine.
>
> On the server with DBI 1.611 , DBD::Oracle 1.24b and oracle instant
> client 11.1 it returns nothing, with no errors.
>
> Use  of DBI_TRACE returns nothing I can work with, it just stops on
> the newer server:
>
>-> prepare for DBD::Oracle::db
> (DBI::db=HASH(0x1be48c80)~0x1be48c40 '
>  SELECT
>(2+3),
>cursor(select 2+3 from dual),
>cursor(select 3+3 from dual)
>FROM table where rownum < 10
> ') thr#1bb1a010
>dbd_st_prepare'd sql SELECT (pl1, auto_lob1, check_sql1)
>dbd_describe SELECT (EXPLICIT, lb 5000)...
> Described col  1: dbtype 2(NVARCHAR2), scale 0, prec 0, nullok 1, name
> (2+3)
>  : dbsize 2, char_used 0, char_size 0, csid 0, csform
> 0(0), disize 171
>fbh 1: '(2+3)'  NULLable, otype   2->  5, dbsize 2/172, p0.s0
> Described col  2: dbtype 116(SQLT_RSET  OCI 8 cursor variable), scale
> 0, prec 0, nullok 1, name CURSOR(SELECT2+3FROMDUAL)
>  : dbsize 8, char_used 0, char_size 0, csid 0, csform
> 0(0), disize 8
>fbh 2: 'CURSOR(SELECT2+3FROMDUAL)'  NULLable, otype 116-
> >116, dbsize 8/8, p0.s0
> Described col  3: dbtype 116(SQLT_RSET  OCI 8 cursor variable), scale
> 0, prec 0, nullok 1, name CURSOR(SELECT3+3FROMDUAL)
>  : dbsize 8, char_used 0, char_size 0, csid 0, csform
> 0(0), disize 8
>fbh 3: 'CURSOR(SELECT3+3FROMDUAL)'  NULLable, otype 116-
> >116, dbsize 8/8, p0.s0
>cache settings DB Handle RowCacheSize=0,Statement Handle
> RowCacheSize=0, OCI_ATTR_PREFETCH_ROWS=339,
> OCI_ATTR_PREFETCH_MEMORY=0, Rows per Fetch=339, Multiple Row Fetch=On
>dbd_describe'd 3 columns (row bytes: 18 max, 20 est avg,
> cache: 0)
><- prepare= DBI::st=HASH(0x1be48ee0) at trek_cursor_query.pl line
> 33
>-> execute for DBD::Oracle::st
> (DBI::st=HASH(0x1be48ee0)~0x1be48e20) thr#1bb1a010
>   dbd_st_execute SELECT (out0, lob0)...
> Statement Execute Mode is 0 (DEFAULT)
>rs_array_init:imp_sth->rs_array_size=2, rs_array_idx=0,
> prefetch_rows=0, rs_array_status=SUCCESS
>dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
><- execute= '0E0' at trek_cursor_query.pl line 34
>-> fetchrow_array for DBD::Oracle::st
> (DBI::st=HASH(0x1be48ee0)~0x1be48e20) thr#1bb1a010
>dbd_st_fetch 3 fields...
>
>
>
> Removing one of the cursor statements makes it work on both machines!
> I am out of ideas.
>
> Any ideas are most appreciated!
>
> Thanks,
> LK
>
>
>
>
>
>
>
>

--
>From the Pythian family to yours, Happy Holidays and all the best in 2011!

Re: DBD::Oracle dbd_st_execute slow speed

[John Scoles]

 On 16/12/2010 10:08 AM, ericbamba...@discover.com wrote:

something for 1.29 I guess I am fully booked for 1.28:(

BTW Peat are you able to give the release 1.27 candidate a quick spin??


http://www.pythian.com/news/wp-content/uploads/DBD-Oracle-1.27-RC1.zip


cheers
John



I won't be getting a 10046 trace as it took me several days just to
truncate the table. Yes, in dev. Yay for the paperwork of large
corporations. However, if you're curious, you might be able to recreate
this by having 2 tables related by a FK, insert a few million junk rows
into one then use DELETE FROM $TABLE. Then try to insert in the other.

Multiple versions of perl using multiple versions of DBI and DBD::Oracle
exhibited the problem while sqlplus and sql developer did not so it might
be easy to hit this issue.




"Peter J. Holzer"
12/16/2010 07:29 AM

To

cc

Subject
Re: DBD::Oracle dbd_st_execute slow speed






On 2010-12-16 07:15:02 -0500, John Scoles wrote:

  On 16/12/2010 7:06 AM, Ludwig, Michael wrote:

-Original Message-
From: John Scoles
More likely SQLplus is spawning a thread while DBD::Oracle does not.

You mean performing the actual work in the background while making
the prompt available for the user to enter the next command?

yep It might I could ask an oracle buddy of mine who works on it if
you want?

Its been a while since the last time I tried to get OCI treads to
work but in the case of an update statement it would make perfect
sense to use them for that as there is no 'return' from the DB like
'select' statement.

Sqlplus does display the result of the insert (either "1 row created."
or a suitable error message (like "ORA-1: unique constraint
(FIWPROD.SYS_C0028271) violated") before the next prompt, so I doubt
very much that it does anything in the background.


SQLplus might also be using the array interface under the hood for
all inserts which could be faster.

It might, but for a single row that shouldn't make much difference.

The 10046 trace will be interesting ...

My guess is that oracle uses an index when the query comes from sqlplus,
but doesn't when the query comes from perl. It is sometimes hard to
determine why Oracle chooses a specific plan.

Oh, and I think it hasn't been mentioned that you can display plans for
queries which have already been executed.

First find the query:

sys...@dbi:Oracle:fiw>  select sql_id, child_number from v$sql
where sql_text= 'select * from setcoords sc where sc.base_set=:p1';
+-++
|SQL_ID   |CHILD_NUMBER|
+-++
|9bvzsg998zgy5|0   |
|9bvzsg998zgy5|1   |
|9bvzsg998zgy5|2   |
+-++
[3 rows of 2 fields returned]


then get the plan for the query:

sys...@dbi:Oracle:fiw>  SELECT * FROM
table(DBMS_XPLAN.DISPLAY_CURSOR('9bvzsg998zgy5', 2));
+---+
|PLAN_TABLE_OUTPUT  |
+---+
|SQL_ID  9bvzsg998zgy5, child number 2  |
|-  |
|select * from setcoords sc where sc.base_set=:p1  |
|  |
|Plan hash value: 1863347061  |
|  |
|---|
|| Id  | Operation | Name  | Rows  | Bytes | Cost (%CPU)| Time
 ||
|---|
||   0 | SELECT STATEMENT  |   |   |   |  7529 (100)|  ||
||*  1 |  TABLE ACCESS FULL| SETCOORDS | 87312 |  1961K|  7529   (2)|
00:01:31 ||
|---|
|  |
|Predicate Information (identified by operation id):  |
|---  |
|  |
|   1 - filter("SC"."BASE_SET"=TO_NUMBER(:P1))  |
|  |
+---+
[18 rows of 1 fields returned]

You need special privileges for that, though. I don't think a normal
user can do it even for their own queries.

 hp

Re: DBD::Oracle dbd_st_execute slow speed

[John Scoles]

 On 16/12/2010 7:06 AM, Ludwig, Michael wrote:

-Original Message-
From: John Scoles
More likely SQLplus is spawning a thread while DBD::Oracle does not.

You mean performing the actual work in the background while making
the prompt available for the user to enter the next command?
yep It might I could ask an oracle buddy of mine who works on it if you 
want?


Its been a while since the last time I tried to get OCI treads to work 
but in the case of an update statement it would make perfect sense to 
use them for that as there is no 'return' from the DB like 'select' 
statement.


SQLplus might also be using the array interface under the hood for all 
inserts which could be faster.


You can give that a try by using the execute_array function with the 
same insert.


Cheers
John Scoles

Oracle's sqlplus doesn't appear to have a -verbose switch, but maybe
there is an option you can set on the connection to find out what's
going on inside.




  This still doesn't explain the difference between

DBD::Oracle and

sqlplus. They must have had different execution plans

internally somehow.

Michael

Re: DBD::Oracle dbd_st_execute slow speed

[John Scoles]

 On 15/12/2010 4:19 PM, ericbamba...@discover.com wrote:
More likely SQLplus is spawning a thread while DBD::Oracle does not.

cheers
John

List,

 Truncating the table solved the problem. Since I inserted 2
million rows into a parent table then deleted all of them the Oracle high
watermark was very high.

 This still doesn't explain the difference between DBD::Oracle and
sqlplus. They must have had different execution plans internally somehow.

 Thank you everyone for your help and ideas.





Mike Towery
12/13/2010 01:35 PM

To

cc

Subject
Re: DBD::Oracle dbd_st_execute slow speed






I would suggest you do a 10046 trace on the perl job and looked at what
is causing the waits.  Also with 9i and above there is wait option is
included in Tkprof.  This will provide some insight on what the database
is waiting on.

You can google 10046 oracle trace if you need information on how to do it.

Regards,
Mike Towery





Please consider the environment before printing this email.

Re: DBD::Oracle dbd_st_execute slow speed

[John Scoles]

 On 09/12/2010 12:24 PM, Martin J. Evans wrote:

On 09/12/10 16:52, ericbamba...@discover.com wrote:

Martin,

 do() takes 4 seconds as well. Sorry about the SQL_INTEGER junk. I
was testing things. It takes the same amount of time if I leave off the
types. I remember seeing the trace show it bind as VARCHAR or something
when I leave off the type and it was still slow.

 Here is the code snippet I used with do(). The script printed:

Do TOOK 4 seconds

 my $do_q = "INSERT INTO FL_SYS_STAT_HIST
(ABI_FMT_ID,DATA_COLL_ID,WRK_SPCE_FREE_KB_CNT,WRK_SPCE_USE_KB_CNT)
  VALUES
(".$_->get_id().",".$s->{id}.",".$_->get_free_space().",".$_->get_used_space().")";

 $start = time();
 $s->{_dbh}->do($do_q) or
   die("Do failed: $DBI::errstr\n");
 $end = time();
 print STDERR "Do TOOK ".($end-$start)." seconds\n";
 die("Dying for do()\n");



There are still things you've not told us:

Oracle database version
which Oracle client libs are you using and what version
DBI version
DBD::Oracle version
Perl version

However, the only time I've seen something like this it was index related and 
yet you maintain sqlplus does not suffer from the same issue. One time it was 
missing indexes and the other it was Oracle deciding not to use the index 
because of data type mismatches.

Is there perhaps a difference in how you are connecting between DBD::Oracle and 
sqlplus? Was sqlplus command and Perl script run from same machine?


One other thing to remember is that sqlplus is threaded for each command 
while DBD::Oracle is not.


So in sqlplus the commit may be on a different thread so it be quick to 
return even if the commit hits a 2meg index regenerate.


Do you know if the execute or the commit is taking a long time in 
DBD::Oracle??


One other thing to think of check with your DBA that your table is not 
an 'Index Organized Table' that will really bugger you up if you have a 
big index

and few rows



Martin

Re: DBD::Oracle dbd_st_execute slow speed

[John Scoles]

 On 09/12/2010 11:59 AM, ericbamba...@discover.com wrote:

List,

 I'm not sure if this is relevant but I recently loaded 2 million
rows into a parent table NDE_DATA_HIST then issued a DELETE FROM to delete
all of the rows. I dont have access to TRUNCATE(). The table now has only
about 200 rows. However, the 2 other tools are still fast, its just DBI
that is slow so I dont think its related.




Ha! I very much think so. ;)

Most likely the index of the 200+ is still being updated

Rebuild the indexes and then get back to me

cheers
John


"Martin J. Evans"
12/09/2010 10:33 AM

To

cc

Subject
Re: DBD::Oracle dbd_st_execute slow speed






On 09/12/10 15:37, ericbamba...@discover.com wrote:

DBI Users,

 I'm having trouble with DBD::Oracle and very simple insert
statements into tables with less than 200 records only 2 FKs and no
indexes taking over 4 seconds. Inserts to other tables seem unaffected.

I

can run the exact same statement from SQLPlus or SQL Developer with no
speed issues. It is lightning quick unless I use my perl code.  The

exact

same issue presents itself whether I use $dbh->do() or a traditional
prepare/execute.

 Does anyone know why only DBD::Oracle would have trouble? It

looks

like it hangs in the driver on OCIStmtExecute_log_stat but I cant be

100%.

Something wacky is happening in the interaction between the driver and
server.

 The issue is in $sth->execute(). My timing from just that part
shows about 4-5 seconds consistently. It happens each and every query.

Almost my exact issue seems to have been covered before on a slightly
different DB version but no answer was posted:

http://www.nntp.perl.org/group/perl.dbi.users/2006/11/msg30473.html

 Please help me troubleshoot this issue and let me know if I can
provide any more information to the group.

Here is the perl code I'm using.

   my $fs_store_q = "INSERT INTO FL_SYS_STAT_HIST
  (ABI_FMT_ID,DATA_COLL_ID,WRK_SPCE_FREE_KB_CNT,WRK_SPCE_USE_KB_CNT)
 VALUES ( ?,?,?,?)";
   my $fs_sth;
   $s->{_dbh}->{ora_verbose}   = 6;
   $s->{_dbh}->trace(6);
   unless($fs_sth = $s->{_dbh}->prepare($fs_store_q,)){
 carp("Can't prepare stmt: $DBI::errstr\n");
 return undef;
   };
foreach(@{$s->{workspaces}}){
  $fs_sth->bind_param(1,$_->get_id(),SQL_INTEGER);
  $fs_sth->bind_param(2,$s->{id},SQL_INTEGER);
  $fs_sth->bind_param(3,$_->get_free_space(),SQL_INTEGER);
  $fs_sth->bind_param(4,$_->get_used_space(),SQL_INTEGER);

Not that I believe this is the source of your problem but I don't think
DBD::Oracle knows what an SQL_INTEGER is:

dbd_bind_ph(): bind :p1<== '6' (type 4 ((UNKNOWN SQL TYPECODE 4)))

and might be better written as ora_type =>  SQLT_INT or leave the type off
the bind.

DBD::Oracle by defaults binds strings to parameters and I have seen Oracle
get upset when it receives strings for another type where it basically
ignores the index.

When you are using sqlplus or sql developer or some_other_tool are they
binding the parameter as DBD::Oracle does or are they just passing in the
entire SQL e.g., there is a world of difference between:

insert into mytable values(1,2,3)

and

prepare
   insert into mytable values(?,?,?)
bind params 1-3 as strings
execute

How long does it take if it is straight forward do method call with just
SQL and no parameters?


 $start = time();
 unless($fs_sth->execute()){
 carp("Can't execute stmt: $DBI::errstr\n");
 return undef;
 };
 $end = time();
 $s->{_dbh}->{ora_verbose}   = 0;
 print STDERR "STORE TOOK ".($end-$start)." seconds\n";
  }

$ sqlplus mjmc_u...@db30

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 9 10:20:13 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

SQL>  select * from v$version where banner like 'Oracle%';

BANNER




Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production

The table and insert statement are dead simple. Here they are. Sorry

about

the lengthy DDL its autogenerated.

INSERT INTO FL_SYS_STAT_HIST
(ABI_FMT_ID,DATA_COLL_ID,WRK_SPCE_FREE_KB_CNT,WRK_SPCE_USE_KB_CNT)

VALUES

(1,28990,0,0);

CREATE TABLE MJMC.FL_SYS_STAT_HIST
(
 ABI_FMT_ID   NUMBER(5)  NOT NULL,
 DATA_COLL_ID NUMBER(10) NOT NULL,
 WRK_SPCE_FREE_KB_CNT NUMBER(15) NULL,
 WRK_SPCE_USE_KB_CNT  NUMBER(15) NULL
)
TABLESPACE MJMC_D_01
LOGGING
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE

ALTER TABLE MJMC.FL_SYS_STAT_HIST
 ADD CONSTRAINT FL_SYS_

Re: DBD::Oracle dbd_st_execute slow speed

[John Scoles]

 On 09/12/2010 10:37 AM, ericbamba...@discover.com wrote:

Sounds odd really

We will have to know the version of DBD::Oracle and DBI for starters

Send me the output from the dbd_verbose=>15  so I can check a few things

cheers
John

DBI Users,

 I'm having trouble with DBD::Oracle and very simple insert
statements into tables with less than 200 records only 2 FKs and no
indexes taking over 4 seconds. Inserts to other tables seem unaffected. I
can run the exact same statement from SQLPlus or SQL Developer with no
speed issues. It is lightning quick unless I use my perl code.  The exact
same issue presents itself whether I use $dbh->do() or a traditional
prepare/execute.

 Does anyone know why only DBD::Oracle would have trouble? It looks
like it hangs in the driver on OCIStmtExecute_log_stat but I cant be 100%.
Something wacky is happening in the interaction between the driver and
server.

 The issue is in $sth->execute(). My timing from just that part
shows about 4-5 seconds consistently. It happens each and every query.

Almost my exact issue seems to have been covered before on a slightly
different DB version but no answer was posted:

http://www.nntp.perl.org/group/perl.dbi.users/2006/11/msg30473.html

 Please help me troubleshoot this issue and let me know if I can
provide any more information to the group.

Here is the perl code I'm using.

   my $fs_store_q = "INSERT INTO FL_SYS_STAT_HIST
  (ABI_FMT_ID,DATA_COLL_ID,WRK_SPCE_FREE_KB_CNT,WRK_SPCE_USE_KB_CNT)
 VALUES ( ?,?,?,?)";
   my $fs_sth;
   $s->{_dbh}->{ora_verbose}   = 6;
   $s->{_dbh}->trace(6);
   unless($fs_sth = $s->{_dbh}->prepare($fs_store_q,)){
 carp("Can't prepare stmt: $DBI::errstr\n");
 return undef;
   };
foreach(@{$s->{workspaces}}){
  $fs_sth->bind_param(1,$_->get_id(),SQL_INTEGER);
  $fs_sth->bind_param(2,$s->{id},SQL_INTEGER);
  $fs_sth->bind_param(3,$_->get_free_space(),SQL_INTEGER);
  $fs_sth->bind_param(4,$_->get_used_space(),SQL_INTEGER);

 $start = time();
 unless($fs_sth->execute()){
 carp("Can't execute stmt: $DBI::errstr\n");
 return undef;
 };
 $end = time();
 $s->{_dbh}->{ora_verbose}   = 0;
 print STDERR "STORE TOOK ".($end-$start)." seconds\n";
  }

$ sqlplus mjmc_u...@db30

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 9 10:20:13 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

SQL>  select * from v$version where banner like 'Oracle%';

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production

The table and insert statement are dead simple. Here they are. Sorry about
the lengthy DDL its autogenerated.

INSERT INTO FL_SYS_STAT_HIST
(ABI_FMT_ID,DATA_COLL_ID,WRK_SPCE_FREE_KB_CNT,WRK_SPCE_USE_KB_CNT) VALUES
(1,28990,0,0);

CREATE TABLE MJMC.FL_SYS_STAT_HIST
(
 ABI_FMT_ID   NUMBER(5)  NOT NULL,
 DATA_COLL_ID NUMBER(10) NOT NULL,
 WRK_SPCE_FREE_KB_CNT NUMBER(15) NULL,
 WRK_SPCE_USE_KB_CNT  NUMBER(15) NULL
)
TABLESPACE MJMC_D_01
LOGGING
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE

ALTER TABLE MJMC.FL_SYS_STAT_HIST
 ADD CONSTRAINT FL_SYS_STAT_HIST_PK
 PRIMARY KEY (ABI_FMT_ID,DATA_COLL_ID)
 USING INDEX TABLESPACE MJMC_X_01
 STORAGE(BUFFER_POOL DEFAULT)
 ENABLE
 VALIDATE

CREATE INDEX MJMC.FL_SYS_STAT_HIST_FK1_X
 ON MJMC.FL_SYS_STAT_HIST(ABI_FMT_ID)
TABLESPACE MJMC_X_01
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCOMPRESS
/
CREATE INDEX MJMC.FL_SYS_STAT_HIST_FK2_X
 ON MJMC.FL_SYS_STAT_HIST(DATA_COLL_ID)
TABLESPACE MJMC_X_01
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCOMPRESS

ALTER TABLE MJMC.FL_SYS_STAT_HIST
 ADD CONSTRAINT FL_SYS_STAT_HIST_FK1
FOREIGN KEY (ABI_FMT_ID)
REFERENCES MJMC.ABI_FL_SYS_FMT (ABI_FMT_ID)
ENABLE
/
ALTER TABLE MJMC.FL_SYS_STAT_HIST
 ADD CONSTRAINT FL_SYS_STAT_HIST_FK2
FOREIGN KEY (DATA_COLL_ID)
REFERENCES MJMC.NDE_DATA_HIST (DATA_COLL_ID)
ENABLE


The attached trace should show 2 inserts and then I called die() otherwise
the program is designed to run forever in a loop.


Please consider the environment before printing this email.

Re: libocci - when is it linked into Oracle.so?

[John Scoles]

 On 22/11/2010 2:58 PM, E R wrote:

Hi,

I have two DBD/Oracle/Oracle.so shared libraries. In one case ldd
reports that libocci is linked in, and in another case libocci doesn't
show up.

I don't know how the first version was built, but the second one was
build using:

export ORACLE_HOME=... # path the instantclient 10.2.0.4
export LB_LIBRARY_PATH="$ORACLE_HOME:"

perl Makefile.PL
make
make install

(I.e., a pretty generic build)

Does anyone know how libocci gets picked up when building Oracle.so? I
am using instantclient version 10.2.0.4

Thanks,
ER

I know in the instant client the  so files are all squished together into a

libclntsh.so.xx.x

file where xx.x is the version number of the client.  ie 
libclntsh.so.10.1 for ic 10.1


What I have to do to get DBD::Oracle to compile is create a symbolic 
link to it like this


ln -s libclntsh.so.10.1  libclntsh.so

if you happen to already have a link like that in your path someplace it 
could be a problem.


I have noticed on some other OS system that all the .so files are place 
in a common folder one I cannot find or do not have access to in the 
Makeifle.PL


you might want to look into that.

After the compile it no longer needs the link.

Hope this helps

Cheers
John Scoles

Re: libocci - when is it linked into Oracle.so?

[John R Pierce]

On 11/22/10 11:58 AM, E R wrote:

Hi,

I have two DBD/Oracle/Oracle.so shared libraries. In one case ldd
reports that libocci is linked in, and in another case libocci doesn't
show up.


wild guess says one was linked statically (eg, libocci.a rather than 
libocci.so).   static linking embeds a copy of the lib in the module so 
its not externally referenced.   good in that you don't have to worry 
about the lib path, bad in that if the lib is updated your module won't 
see the new one, good in that if those updates break something, it won't 
effect you.

Re: Memory corruption with Perl (DBD::Oracle)

[John Scoles]

 On 22/11/2010 9:07 AM, Thierry Terrier wrote:
Thanks for your feedback, we're going to install the preconised 
patchsets on our 11.1.0.6 client.


What about the warning of compilation BDB-Oracle?
WARNING: Unable to interpret Oracle build commands from 
/app/oracle/product/11.1.0/client_1/rdbms/demo/demo_rdbms32.mk.

(Will continue by using fallback approach.)


Not sure about that one.

I know that in 11g  they changed the .mk files up a little.  If it 
compiles with the fallback is should still work ok.


You are using a 32 bit client I hope??

Cheers
John Scoles

Regards.


Seems this may be an 11g issue and not a perl/DBI/DBD issuse

Have you applied all the patches for 11to the 11g DB??

Cheers
John Scoles


Date: Fri, 19 Nov 2010 18:06:21 +0100
From: li...@alixen.fr
To: dbi-users@perl.org
Subject: Memory corruption with Perl (DBD::Oracle)

Hi,

Our (standalone) Perl scripts often encounter memory corruptions 
after a migration on RHEL5.5 (Perl 5.8.8) with Oracle

client 11.1.0 and DBD-Oracle-1.26.
Some Oracle core dumps are present in the Oracle client directory 
../incident/incdir_... with the generic error code

"oci-24550".

We had done before a compilation directly on Oracle libraries with 
perl Makefile.PL -l.
After an installation of Oracle Database 11g Examples and a standard 
compilation the problems remain the same.
The service task stops once or twice a day with an error message 
like 'Attempt to free unreferenced scalar'.


Once, a debug session session showed corruption on an undef array :
main::(script.pl:73): undef @tache;
DB<3>  x @tache
0 '($@, $!, $^E, $,, $/, $\\, $^W) = @saved;package main; $^D = $^D 
| $DB::db_stop;

@tache;

;'
DB<4>  n
Attempt to free unreferenced scalar: SV 0x8b34580, Perl interpreter: 
0x83b8008 at script.pl line 73.


The problem doesn't seem to appear with Apache mod_perl.

Did anyone encounter similar issues or has an idea what is going 
wrong here?

Tell me what complementary information I can provide.

RE: Memory corruption with Perl (DBD::Oracle)

[John Scoles]

Seems this may be an 11g issue and not a perl/DBI/DBD issuse
 
Have you applied all the patches for 11to the 11g DB??
 
Cheers
John Scoles
 
> Date: Fri, 19 Nov 2010 18:06:21 +0100
> From: li...@alixen.fr
> To: dbi-users@perl.org
> Subject: Memory corruption with Perl (DBD::Oracle)
> 
> Hi,
> 
> Our (standalone) Perl scripts often encounter memory corruptions after a 
> migration on RHEL5.5 (Perl 5.8.8) with Oracle 
> client 11.1.0 and DBD-Oracle-1.26.
> Some Oracle core dumps are present in the Oracle client directory 
> ../incident/incdir_... with the generic error code 
> "oci-24550".
> 
> We had done before a compilation directly on Oracle libraries with perl 
> Makefile.PL -l.
> After an installation of Oracle Database 11g Examples and a standard 
> compilation the problems remain the same.
> The service task stops once or twice a day with an error message like 
> 'Attempt to free unreferenced scalar'.
> 
> Once, a debug session session showed corruption on an undef array :
> main::(script.pl:73): undef @tache;
> DB<3> x @tache
> 0 '($@, $!, $^E, $,, $/, $\\, $^W) = @saved;package main; $^D = $^D | 
> $DB::db_stop;
> @tache;
> 
> ;'
> DB<4> n
> Attempt to free unreferenced scalar: SV 0x8b34580, Perl interpreter: 
> 0x83b8008 at script.pl line 73.
> 
> The problem doesn't seem to appear with Apache mod_perl.
> 
> Did anyone encounter similar issues or has an idea what is going wrong here?
> Tell me what complementary information I can provide.
  

Re: Potential problem with DBD::Oracle

[John Scoles]

 On 18/11/2010 3:15 PM, Steve Baldwin wrote:
Ok one more to look at for DBD::Oracle 1.28

Hi,

I ran across an issue recently that appears to have existed for quite some
time.

Consider the following script ...

#!/usr/bin/perl -w
use strict;
use warnings;
use DBI;

sub main {
 my $dbh = DBI->connect(
 'dbi:Oracle:',
 'usr/p...@conn',
 '',
 { PrintError =>  0, AutoCommit =>  0, RaiseError =>  1 },
 );
 my $sql =<<'END_SQL';
SELECT  1 srt, 'AA' txt
FROMdual
UNION
SELECT  2, 'BBB'
FROMdual
UNION
SELECT  3, ''
FROMdual
ORDER
BY  1
END_SQL
 my $sth = $dbh->prepare($sql);
 $sth->execute;
 my ($srt, $txt);
 $sth->bind_columns(\($srt, $txt));
 while ($sth->fetch) {
 print "[$srt][$txt] len=" . (length($txt)) . "\n";
 }
 $dbh->disconnect;
 return 0;
}

exit main();

Running it produces the following output :

au-stb-101-144:dev stbaldwin$ ./sb2.plx
[1][AA] len=2
[2][BBB] len=2
[3][] len=2

As you can see, even though the data in $txt looks correct, perl thinks the
internal length of the variable is whatever it was after fetching the first
row.  This screws up things like sprintf.

I'm pretty sure this behaviour is a DBD::Oracle thing rather than a DBI
thing.  I tried an equivalent script with DBD::SQLite and DBD::mysql and
they both returned the length correctly (imo).

This behaviour exists in 1.23 and 1.26.  I haven't tested any other
versions.

Thanks,

Steve


---
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.
---

RE: DBI Error: END failed--call queue aborted.

[John Scoles]

Ok that cleans it up a little for me.
 
Funny according to the OIC trace you are connecting
 
 see below
 

> To: dbi-users@perl.org
> From: mstrum...@gmail.com
> Subject: Re: DBI Error: END failed--call queue aborted.
> Date: Wed, 17 Nov 2010 07:50:30 -0800
> 
> On Nov 13, 10:19 am, sco...@pythian.com (John Scoles) wrote:
> > You DBD::ORacle is way out of data for you DBI try updating to DBD::Oracle
> > 1.26 ans see what happens
> >
> > cheers
> 
> The error remains the same :( Thanks for all your help. Not sure what
> else I can do here.
> 
> -bash-3.00$ cat test.db.pl
> use strict;
> use DBI;
> my $dbh = DBI->connect( 'dbi:Oracle:',
> '',
> '',
> {dbd_verbose => 15}
> ) || die "Database connection not made:
> $DBI::errstr";
> $dbh->disconnect;
> print "Done. :)\n";
> 
> -bash-3.00$ perl -w test.db.pl
> Environment variables:
> MANPATH=/opt/SUNWspro/man
> TERM=xterm
> SHELL=/usr/bin/bash
> SSH_CLIENT=172.28.2.66 3369 22
> OLDPWD=/export/home/webdev/.cpan
> SSH_TTY=/dev/pts/4
> USER=webdev
> LD_LIBRARY_PATH=/opt/instantclient10_1
> TNS_ADMIN=/opt/instantclient10_1
> MAIL=/var/mail//webdev
> PATH=/usr/bin:/opt/SUNWspro/bin:/opt/instantclient10_1:/usr/
> ccs/bin
> PWD=/export/home/webdev
> JAVA_HOME=/usr/java
> TZ=US/Eastern
> SHLVL=1
> HOME=/export/home/webdev
> LOGNAME=webdev
> SSH_CONNECTION=172.28.2.66 3369 172.28.1.40 22
> ORACLE_HOME=/opt/instantclient10_1
> _=/usr/bin/perl
> OCINlsEnvironmentVariableGet(1,0,93,0,2)=SUCCESS
> OCINlsEnvironmentVariableGet(1,0,94,0,2)=SUCCESS
> OCIEnvNlsEnvCreate(1b56bc,OBJECT,2,0,0,0,0,0,0,1,1)=SUCCESS
> OCIHandleAlloc(2500a0,1b56c0,OCI_HTYPE_ERROR,0,0)=SUCCESS
> OCIAttrGet(2500a0,OCI_HTYPE_ENV,fee78090,0,31,267a28)=SUCCESS
> OCIAttrGet(2500a0,OCI_HTYPE_ENV,fee78092,0,262,267a28)=SUCCESS
> charset id=1, name=US7ASCII, ncharset id=1, name=US7ASCII
> (csid: utf8=871 al32utf8=873)
 
The above set up you server context

> OCIHandleAlloc(2500a0,1b56c4,OCI_HTYPE_SERVER,0,0)=SUCCESS
> OCIHandleAlloc(2500a0,1b56c8,OCI_HTYPE_SVCCTX,0,0)=SUCCESS
> OCIServerAttach(267fb4, 267a28, "AHST90", 6, mode=DEFAULT, 0)=SUCCESS
> OCIAttrSet(2679b4,OCI_HTYPE_SVCCTX,267fb4,0,Attr=OCI_ATTR_SERVER,267a28)=SUCCESS
> OCIHandleAlloc(2500a0,1b56cc,OCI_HTYPE_SESSION,0,0)=SUCCESS
 
The above sets up all your handles

> OCIAttrSet(270ca8,OCI_HTYPE_SESSION,1a6380,3,Attr=OCI_ATTR_USERNAME,267a28)=SUCCESS
> OCIAttrSet(270ca8,OCI_HTYPE_SESSION,1a6390,5,Attr=OCI_ATTR_PASSWORD,267a28)=SUCCESS
> OCISessionBegin(2679b4,267a28,270ca8,1,mode=DEFAULT 0)=SUCCESS
> OCIAttrSet(2679b4,OCI_HTYPE_SVCCTX, 
> 270ca8,0,Attr=OCI_ATTR_SESSION,267a28)=SUCCESS
 
The above users your PW and UN and creates a session and it connects
 
 
The rest below is what happens when you disconnects 
> OCISessionEnd(2679b4,267a28,270ca8,mode=DEFAULT 0)=SUCCESS
> OCIServerDetach(267fb4,267a28,mode=DEFAULT,0)=SUCCESS
> Done. :)
> OCIHandleFree(270ca8,OCI_HTYPE_SESSION)=SUCCESS
> OCIHandleFree(2679b4,OCI_HTYPE_SVCCTX)=SUCCESS
> OCIHandleFree(267fb4,OCI_HTYPE_SERVER)=SUCCESS
> OCIHandleFree(267a28,OCI_HTYPE_ERROR)=SUCCESS
 
You are now our of OCI/DBD/Oracle

> Out of memory!
 
So this Out of memory error happens after you have left 
Can you run a query with your connection handle??
 

> Callback called exit.
> END failed--call queue aborted.
> 
> --
> i5mast
> 
  

Re: DBI Error: END failed--call queue aborted.

[John Scoles]

Well nothing in that pops out at me.

You DBD::ORacle is way out of data for you DBI try updating to DBD::Oracle
1.26 ans see what happens

cheers


On Fri, Nov 12, 2010 at 3:49 PM, i5mast  wrote:

> On Nov 12, 8:24 am, sco...@pythian.com (John Scoles) wrote:
> > If you can run this with
> >
> > dbd_verbose=>15
> >
> > on the connect string  the post the results it my help us find an answer.
> >
> > Cheers
> > John Scoles
>
> John, I added dbd_verbose but that didn't print any debugging info :(
> DBI->connect( 'dbi:Oracle:AHST90',
>'ahs',
>'ahsgc',
>{dbd_verbose => 15}
>  )
>
> I ran the script with DBI_TRACE=15 and got the following info. I
> edited the output below and removed user login and SID.
>
> I appreciate any help.
>
> -bash-3.00$ DBI_TRACE=15 perl -w test.db.pl
>DBI 1.53-nothread default trace level set to 0x0/15 (pid 28069)
> 0x2a00 DBI::db::get_info
> install_method DBI::db::get_info, flags 0x2a00,
>usage: min 2, max 2, '$info_type'
> 0x DBI::db::take_imp_data
> install_method DBI::db::take_imp_data,
>usage: min 1, max 1, ''
> 0x0c00 DBI::db::disconnect
> install_method DBI::db::disconnect  , flags 0x0c00,
>usage: min 1, max 1, ''
> 0x2000 DBI::db::selectrow_array
> install_method DBI::db::selectrow_array, flags 0x2000,
>usage: min 2, max 0, '$statement [, \%attr [, @bind_params ] ]'
> 0x2200 DBI::db::tables
> install_method DBI::db::tables  , flags 0x2200,
>usage: min 1, max 6, '$catalog, $schema, $table, $type [, \
> %attr ]'
> 0x0430 DBI::db::quote_identifier
> install_method DBI::db::quote_identifier, flags 0x0430,
>usage: min 2, max 6, '$name [, ...] [, \%attr ]'
> 0x DBI::db::clone
> install_method DBI::db::clone   ,
>usage: min 1, max 2, '[\%attr]'
> 0x0430 DBI::db::quote
> install_method DBI::db::quote   , flags 0x0430,
>usage: min 2, max 3, '$string [, $data_type ]'
> 0x2200 DBI::db::type_info
> install_method DBI::db::type_info   , flags 0x2200,
>usage: min 1, max 2, '$data_type'
> 0x2a00 DBI::db::statistics_info
> install_method DBI::db::statistics_info, flags 0x2a00,
>usage: min 6, max 7, '$catalog, $schema, $table, $unique_only,
> $quick, [, \%attr ]'
> 0x2000 DBI::db::selectrow_arrayref
> install_method DBI::db::selectrow_arrayref, flags 0x2000,
>usage: min 2, max 0, '$statement [, \%attr [, @bind_params ] ]'
> 0x0400 DBI::db::begin_work
> install_method DBI::db::begin_work  , flags 0x0400,
>usage: min 1, max 2, '[ \%attr ]'
> 0x2800 DBI::db::last_insert_id
> install_method DBI::db::last_insert_id, flags 0x2800,
>usage: min 5, max 6, '$catalog, $schema, $table_name, $field_name
> [, \%attr ]'
> 0x2a00 DBI::db::foreign_key_info
> install_method DBI::db::foreign_key_info, flags 0x2a00,
>usage: min 7, max 8, '$pk_catalog, $pk_schema, $pk_table,
> $fk_catalog, $fk_schema, $fk_table [, \%attr ]'
> 0x2200 DBI::db::primary_key
> install_method DBI::db::primary_key , flags 0x2200,
>usage: min 4, max 5, '$catalog, $schema, $table [, \%attr ]'
> 0x0c80 DBI::db::commit
> install_method DBI::db::commit  , flags 0x0c80,
>usage: min 1, max 1, ''
> 0x0404 DBI::db::ping
> install_method DBI::db::ping, flags 0x0404,
>usage: min 1, max 1, ''
> 0x2000 DBI::db::selectall_arrayref
> install_method DBI::db::selectall_arrayref, flags 0x2000,
>usage: min 2, max 0, '$statement [, \%attr [, @bind_params ] ]'
> 0x2a00 DBI::db::type_info_all
> install_method DBI::db::type_info_all, flags 0x2a00,
>usage: min 1, max 1, ''
> 0x3200 DBI::db::do
> install_method DBI::db::do  , flags 0x3200,
>usage: min 2, max 0, '$statement [, \%attr [, @bind_params ] ]'
> 0x2000 DBI::db::selectcol_arrayref
> install_method DBI::db::selectcol_arrayref, flags 0x2000,
>usage: min 2, max 0, '$statement [, \%attr [, @bind_params ] ]'
> 0x2200 DBI::db::prepare_cached
> install_method DBI::db::prepare_cached, flags 0x2200,
>usage: min 2, max 4, '$statement [, \%attr [, $if_active ] ]'
> 0x0004 DBI::db::rows
> install_method DBI::db::rows, flags 0x0004
> 0x0c80 DBI::db::rollback
> install_method DBI::db::rollback, flags 0x0c80,
>usage: min 1, max 1, ''
> 0x2a00 DBI::db::column_info
> install_method DBI::db::column_info , flags 0x2a00,
>usage: min 5, max 6, '$catalog, $schema, $table, $colu

Re: DBI Error: END failed--call queue aborted.

[John Scoles]

 On 10/11/2010 11:11 AM, i5mast wrote:
If you can run this with

dbd_verbose=>15

on the connect string  the post the results it my help us find an answer.

Cheers
John Scoles


I'm using Solaris 10 sparc, Perl 5.8.4, DBI 1.615, instantclient
10.1.0.5, DBD::Oracle 1.16. I compiled and installed the modules
successfully. I can connect to DB and fetch records just fine.
However, when my perl script finishes, it prints out the error.

I'm using Sun's compiler
cc: Sun C 5.8 Patch 121015-04 2007/01/10

I've tried DBD::Oracle 1.24 and DBI 1.53 versions but the error still
happens at the end :(

I've read about other people getting this message but no resolution. I
would appreciate any help.

use strict;
use DBI;
my $dbh = DBI->connect( 'dbi:Oracle:',
 'user',
 'passwd',
   ) || die "Database connection not made:
$DBI::errstr";
$dbh->disconnect;
print "Done. :)\n";

Done. :)
Out of memory!
Callback called exit.
END failed--call queue aborted.

--
  i5mast

Re: perl problem in solaris 5.10

[John D Groenveld]

In message , SOMN
ATH GANGULY writes:
>ora...@prdcmw  > perl TablespaceFree.pl
>Can't locate DBI.pm in @INC (@INC contains:

Where did you install DBI and DBD::Oracle?

John
groenv...@acm.org

Re: ANNOUNCE DBD Oracle 1.27 Release Candidate 1

[John Scoles]

On 11/5/2010 5:22 AM, Martin J. Evans wrote:

On 04/11/10 15:37, John Scoles wrote:

You can find Release Candidate 1 for DBD::Oracle 1.27 here

http://svn.perl.org/modules/dbd-oracle/branches/polute/DBD_ORACLE_1_27_RC_1.tar.zip

This release is limited to cleaning up Perl globals within the C and XS code.

Short version

PERL_POLLUTE is being dropped from perl 5.13.3 so drivers that have been around 
for a while (like DBD::Oracle)  may break!!

Check out this thread

http://www.nntp.perl.org/group/perl.dbi.dev/2010/08/msg6217.html

for the long version

I am announcing this on dbi-users as well to expand the group of people who may 
be using 5.13.3.

Cheers
John Scoles

--
The best compliment you could give Pythian for our service is a referral.


This is perl 5, version 13, subversion 6 (v5.13.6) built for i686-linux
Ubuntu 10.10
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
Oracle version 11.1.0.6 (11.1)
Using DBI 1.615

One compiler warning:
oci8.c:3898: warning: cast from function call of type ‘size_t’ to non-matching 
type ‘void *’

Ok that will be for 1.28

the usual 2 test failures:

t/26exe_array.t   (Wstat: 512 Tests: 17 Failed: 2)
   Failed tests:  11, 14


I think I have narrowed this one down some place else I will have to 
look at the email on it.


Martin are you testing on a 64bit box??

cheers
John Scoles

Martin

ANNOUNCE DBD Oracle 1.27 Release Candidate 1

[John Scoles]

You can find Release Candidate 1 for DBD::Oracle 1.27 here

http://svn.perl.org/modules/dbd-oracle/branches/polute/DBD_ORACLE_1_27_RC_1.tar.zip

This release is limited to cleaning up Perl globals within the C and XS 
code.


Short version

PERL_POLLUTE is being dropped from perl 5.13.3 so drivers that have been 
around for a while (like DBD::Oracle)  may break!!


Check out this thread

http://www.nntp.perl.org/group/perl.dbi.dev/2010/08/msg6217.html

for the long version

I am announcing this on dbi-users as well to expand the group of people 
who may be using 5.13.3.


Cheers
John Scoles

--
The best compliment you could give Pythian for our service is a referral.

Re: undefined references when installing DBD::Oracle on Windows Server 2008

[John Scoles]

On 10/29/2010 3:25 PM, Alexander Saip wrote:
Ok that sounds like it I will have to fix that if it is in the present 
release?

Thanks for pointing that out.
Cheers
John Scoles

I guess not... I eventually managed to build, test and install 
DBD::Oracle. The culprit turned out to be oci.def file in the CPAN 
folder. After I edited it by adding all those "undefined 
references", everything worked like a charm. Quick note: I switched to 
the 32-bit versions of Perl and Oracle IC, but I'm almost positive now 
that it would work in the 64-bit world as well.


Date: Fri, 29 Oct 2010 14:15:35 -0400
From: sco...@pythian.com
To: as...@msn.com
CC: dbi-users@perl.org
Subject: Re: undefined references when installing DBD::Oracle on 
Windows Server 2008


On 10/29/2010 1:13 PM, Alexander Saip wrote:

Might be a strawberry perl thing??

Can you give it a try with Activestate perl or with and eariler 
DBD::Oracle 1.24 or 1.23 or the 10 instant client??


Cheers
John Scoles

Sorry for the delayed response. I had to make Oracle connection
work ASAP yesterday, so I simply installed DBD::ODBC and used the
Oracle ODBC driver. Today, I checked the IC SDK 'include' folder
and found all those function headers (and many more) in ociap.h.
The compiler does include that folder in its list. What else may
cause those errors?
Date: Thu, 28 Oct 2010 13:03:52 -0400
From: sco...@pythian.com <mailto:sco...@pythian.com>
To: as...@msn.com <mailto:as...@msn.com>
CC: dbi-users@perl.org <mailto:dbi-users@perl.org>
Subject: Re: undefined references when installing DBD::Oracle on
Windows Server 2008

On 10/28/2010 12:18 PM, Alexander Saip wrote:

John,

thanks for your comments. I have successfully installed
DBD::Oracle on a Windows 7 (64-bit) box, but used both 32-bit
Strawberry Perl and Oracle instant client. That may be the way
for me to go, unless I can make the 64-bit stuff work together.

I do have the Oracle instant clent SDK folder in the
E:/app/Dna_Admin/product/11.2.0/client_1/instantclient_11_2
directory with all the goodies, which apparently doesn't help
much. Yes, the flags you mention (ORA_OCI_102 and ORA_OCI_112)
are set in the Makefile. Where should I look for those command
files that you refer to?

Thanks again,


They would or should be part of the .dll

You might be able to look at the dll and see if the commands are
there? I can't remember how to do that off hand long time ago
since my VB days.

The are usually in the .h files as well.  Try just a text search
of any .h files in that client.

You might want to try the 10 IC and see if it works with that one.

Cheers
John Scoles


Alexander Saip

> Date: Thu, 28 Oct 2010 11:51:54 -0400
> From: sco...@pythian.com <mailto:sco...@pythian.com>
> To: as...@msn.com <mailto:as...@msn.com>
> CC: dbi-users@perl.org <mailto:dbi-users@perl.org>
> Subject: Re: undefined references when installing
DBD::Oracle on Windows Server 2008
>
> On 10/28/2010 10:23 AM, Alexander Saip wrote:
> > Hi Everybody,
> >
> > Windows Server 2008 R2 Enterprise (64-bit) running
Strawberry Perl, as follows:
>
> Strawberry Perl
>
>
> never did DBD::Oracle to like it.
> myself had you installed it before on a box with Strawberry??
>
> Oracle.o:Oracle.c:(.text+0x34a2): undefined reference to
`OCIPing'
>
> > dbdimp.o:dbdimp.c:(.text+0xb29d): undefined reference to
`OCISessionRelease'
> > dbdimp.o:dbdimp.c:(.text+0xb6ea): undefined reference to
`OCISessionPoolDestroy'
> > dbdimp.o:dbdimp.c:(.text+0xe15a): undefined reference to
`OCISessionPoolCreate'
> > dbdimp.o:dbdimp.c:(.text+0xebf8): undefined reference to
`OCISessionGet'
> > dbdimp.o:dbdimp.c:(.text+0xedd2): undefined reference to
`OCISessionPoolDestroy'
> > collect2: ld returned 1 exit status
> > dmake: Error code 129, while making
'blib\arch\auto\DBD\Oracle\Oracle.dll'
> >
> The above means that the ORA_OCI_102 and the ORA_OCI_112
precompile
> flags are being set but your
>
> Oracle.dll
>
> does not have the the access to these commands files for
what ever reason
>
> Me I would get the rest of the instantclient (the SDK) from
Oracle and
> give it another try
>
> cheers
> John Scoles
> > --

Re: undefined references when installing DBD::Oracle on Windows Server 2008

[John Scoles]

On 10/29/2010 1:13 PM, Alexander Saip wrote:

Might be a strawberry perl thing??

Can you give it a try with Activestate perl or with and eariler 
DBD::Oracle 1.24 or 1.23 or the 10 instant client??


Cheers
John Scoles
Sorry for the delayed response. I had to make Oracle connection work 
ASAP yesterday, so I simply installed DBD::ODBC and used the Oracle 
ODBC driver. Today, I checked the IC SDK 'include' folder and found 
all those function headers (and many more) in ociap.h. The compiler 
does include that folder in its list. What else may cause those errors?

Date: Thu, 28 Oct 2010 13:03:52 -0400
From: sco...@pythian.com
To: as...@msn.com
CC: dbi-users@perl.org
Subject: Re: undefined references when installing DBD::Oracle on 
Windows Server 2008


On 10/28/2010 12:18 PM, Alexander Saip wrote:

John,

thanks for your comments. I have successfully installed
DBD::Oracle on a Windows 7 (64-bit) box, but used both 32-bit
Strawberry Perl and Oracle instant client. That may be the way for
me to go, unless I can make the 64-bit stuff work together.

I do have the Oracle instant clent SDK folder in the
E:/app/Dna_Admin/product/11.2.0/client_1/instantclient_11_2
directory with all the goodies, which apparently doesn't help
much. Yes, the flags you mention (ORA_OCI_102 and ORA_OCI_112) are
set in the Makefile. Where should I look for those command files
that you refer to?

Thanks again,


They would or should be part of the .dll

You might be able to look at the dll and see if the commands are 
there? I can't remember how to do that off hand long time ago since my 
VB days.


The are usually in the .h files as well.  Try just a text search of 
any .h files in that client.


You might want to try the 10 IC and see if it works with that one.

Cheers
John Scoles


Alexander Saip

> Date: Thu, 28 Oct 2010 11:51:54 -0400
> From: sco...@pythian.com <mailto:sco...@pythian.com>
> To: as...@msn.com <mailto:as...@msn.com>
> CC: dbi-users@perl.org <mailto:dbi-users@perl.org>
> Subject: Re: undefined references when installing DBD::Oracle on
Windows Server 2008
>
> On 10/28/2010 10:23 AM, Alexander Saip wrote:
> > Hi Everybody,
> >
> > Windows Server 2008 R2 Enterprise (64-bit) running Strawberry
Perl, as follows:
>
> Strawberry Perl
>
>
> never did DBD::Oracle to like it.
> myself had you installed it before on a box with Strawberry??
>
> Oracle.o:Oracle.c:(.text+0x34a2): undefined reference to `OCIPing'
>
> > dbdimp.o:dbdimp.c:(.text+0xb29d): undefined reference to
`OCISessionRelease'
> > dbdimp.o:dbdimp.c:(.text+0xb6ea): undefined reference to
`OCISessionPoolDestroy'
> > dbdimp.o:dbdimp.c:(.text+0xe15a): undefined reference to
`OCISessionPoolCreate'
> > dbdimp.o:dbdimp.c:(.text+0xebf8): undefined reference to
`OCISessionGet'
> > dbdimp.o:dbdimp.c:(.text+0xedd2): undefined reference to
`OCISessionPoolDestroy'
> > collect2: ld returned 1 exit status
> > dmake: Error code 129, while making
'blib\arch\auto\DBD\Oracle\Oracle.dll'
> >
> The above means that the ORA_OCI_102 and the ORA_OCI_112 precompile
> flags are being set but your
>
> Oracle.dll
>
> does not have the the access to these commands files for what
ever reason
>
> Me I would get the rest of the instantclient (the SDK) from
Oracle and
> give it another try
>
> cheers
> John Scoles
> > 
> >> perl Makefile.PL -s OCIPing
> > 
> > Using DBI 1.615 (for perl 5.012001 on
MSWin32-x64-multi-thread) installed in
c:/strawberry/perl/vendor/lib/auto/DBI/
> > Installing on a MSWin32, Ver#6.1
> > Using Oracle in
E:/app/Dna_Admin/product/11.2.0/client_1/instantclient_11_2
> > DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
> > Oracle version 11.2.0.1 (11.2)
> > Searching for symbol 'OCIPing' in
E:/app/Dna_Admin/product/11.2.0/client_1/instantclient_11_2 ...
> > The system cannot find the path specified.
> > Search done.
> > (Please only include the 'interesting' parts when mailing.)
> >
> > I wonder where these missing pieces are supposed to be, or
what may be wrong with my environment setup?
> >
> > Thank you in advance,
> >
> > Alexander Saip
>

Re: undefined references when installing DBD::Oracle on Windows Server 2008

[John Scoles]

On 10/28/2010 12:18 PM, Alexander Saip wrote:

John,

thanks for your comments. I have successfully installed DBD::Oracle on 
a Windows 7 (64-bit) box, but used both 32-bit Strawberry Perl and 
Oracle instant client. That may be the way for me to go, unless I can 
make the 64-bit stuff work together.


I do have the Oracle instant clent SDK folder in the 
E:/app/Dna_Admin/product/11.2.0/client_1/instantclient_11_2 directory 
with all the goodies, which apparently doesn't help much. Yes, the 
flags you mention (ORA_OCI_102 and ORA_OCI_112) are set in the 
Makefile. Where should I look for those command files that you refer to?


Thanks again,


They would or should be part of the .dll

You might be able to look at the dll and see if the commands are there? 
I can't remember how to do that off hand long time ago since my VB days.


The are usually in the .h files as well.  Try just a text search of any 
.h files in that client.


You might want to try the 10 IC and see if it works with that one.

Cheers
John Scoles


Alexander Saip

> Date: Thu, 28 Oct 2010 11:51:54 -0400
> From: sco...@pythian.com
> To: as...@msn.com
> CC: dbi-users@perl.org
> Subject: Re: undefined references when installing DBD::Oracle on 
Windows Server 2008

>
> On 10/28/2010 10:23 AM, Alexander Saip wrote:
> > Hi Everybody,
> >
> > Windows Server 2008 R2 Enterprise (64-bit) running Strawberry 
Perl, as follows:

>
> Strawberry Perl
>
>
> never did DBD::Oracle to like it.
> myself had you installed it before on a box with Strawberry??
>
> Oracle.o:Oracle.c:(.text+0x34a2): undefined reference to `OCIPing'
>
> > dbdimp.o:dbdimp.c:(.text+0xb29d): undefined reference to 
`OCISessionRelease'
> > dbdimp.o:dbdimp.c:(.text+0xb6ea): undefined reference to 
`OCISessionPoolDestroy'
> > dbdimp.o:dbdimp.c:(.text+0xe15a): undefined reference to 
`OCISessionPoolCreate'
> > dbdimp.o:dbdimp.c:(.text+0xebf8): undefined reference to 
`OCISessionGet'
> > dbdimp.o:dbdimp.c:(.text+0xedd2): undefined reference to 
`OCISessionPoolDestroy'

> > collect2: ld returned 1 exit status
> > dmake: Error code 129, while making 
'blib\arch\auto\DBD\Oracle\Oracle.dll'

> >
> The above means that the ORA_OCI_102 and the ORA_OCI_112 precompile
> flags are being set but your
>
> Oracle.dll
>
> does not have the the access to these commands files for what ever 
reason

>
> Me I would get the rest of the instantclient (the SDK) from Oracle and
> give it another try
>
> cheers
> John Scoles
> > 
> >> perl Makefile.PL -s OCIPing
> > 
> > Using DBI 1.615 (for perl 5.012001 on MSWin32-x64-multi-thread) 
installed in c:/strawberry/perl/vendor/lib/auto/DBI/

> > Installing on a MSWin32, Ver#6.1
> > Using Oracle in 
E:/app/Dna_Admin/product/11.2.0/client_1/instantclient_11_2

> > DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
> > Oracle version 11.2.0.1 (11.2)
> > Searching for symbol 'OCIPing' in 
E:/app/Dna_Admin/product/11.2.0/client_1/instantclient_11_2 ...

> > The system cannot find the path specified.
> > Search done.
> > (Please only include the 'interesting' parts when mailing.)
> >
> > I wonder where these missing pieces are supposed to be, or what 
may be wrong with my environment setup?

> >
> > Thank you in advance,
> >
> > Alexander Saip
>

Re: undefined references when installing DBD::Oracle on Windows Server 2008

[John Scoles]

On 10/28/2010 10:23 AM, Alexander Saip wrote:

Hi Everybody,

Windows Server 2008 R2 Enterprise (64-bit) running Strawberry Perl, as follows:


Strawberry Perl


never did DBD::Oracle to like it.
myself had you installed it before on a box with Strawberry??

Oracle.o:Oracle.c:(.text+0x34a2): undefined reference to `OCIPing'


dbdimp.o:dbdimp.c:(.text+0xb29d): undefined reference to `OCISessionRelease'
dbdimp.o:dbdimp.c:(.text+0xb6ea): undefined reference to `OCISessionPoolDestroy'
dbdimp.o:dbdimp.c:(.text+0xe15a): undefined reference to `OCISessionPoolCreate'
dbdimp.o:dbdimp.c:(.text+0xebf8): undefined reference to `OCISessionGet'
dbdimp.o:dbdimp.c:(.text+0xedd2): undefined reference to `OCISessionPoolDestroy'
collect2: ld returned 1 exit status
dmake:  Error code 129, while making 'blib\arch\auto\DBD\Oracle\Oracle.dll'

The above means that the ORA_OCI_102 and the ORA_OCI_112 precompile 
flags are being set but your


Oracle.dll

does not have the the access to these commands files for what ever reason

Me I would get the rest of the instantclient (the SDK)  from Oracle and 
give it another try


cheers
John Scoles



perl Makefile.PL -s OCIPing


Using DBI 1.615 (for perl 5.012001 on MSWin32-x64-multi-thread) installed in 
c:/strawberry/perl/vendor/lib/auto/DBI/
Installing on a MSWin32, Ver#6.1
Using Oracle in E:/app/Dna_Admin/product/11.2.0/client_1/instantclient_11_2
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
Oracle version 11.2.0.1 (11.2)
Searching for symbol 'OCIPing' in 
E:/app/Dna_Admin/product/11.2.0/client_1/instantclient_11_2 ...
The system cannot find the path specified.
Search done.
(Please only include the 'interesting' parts when mailing.)

I wonder where these missing pieces are supposed to be, or what may be wrong 
with my environment setup?

Thank you in advance,

Alexander Saip  

Re: Oracle: ORA-24327 need explicit attach before authenticating a user in perl script

[John Scoles]

 On 10/21/2010 1:11 PM, suuuper wrote:
Funny that should work.
What version of DBD Oracle are you using??

Can you connect using sqlplus??

If no it means there is some sort of TNSNAMES.ORA or other problems on 
the oracel network side of things



I would try using an oracle easy connect string like this

DBI->connect( "dbi:Oracle","testuser", "passw...@someoracle.sid")'

hope this helps
cheers
John Scoles

Hi,

i write the sequent script:

###
use DBI;

my $orachehost = "*";
my $oracleuser = "**";
my $orachepasswd = "**";
my $oraclesid = "*";
my $oracle_home = "/usr/lib/oracle/11.2/client";

$ENV{ORACLE_HOME}=$oracle_home;
$ENV{ORACLE_SID}=$oraclesid;
$ENV{PATH}="$oracle_home/bin";

my $dbh = DBI->connect( "dbi:Oracle:host=$orachehost:sid=$oraclesid",
$oracleuser, $orachepasswd, { AutoCommit =>
1,RaiseError=>1,PrintError=>1 }  ) ||   die("Errore nella connessione
al db: $DBI::errstr");

$dbh->disconnect;

exit 0;

###

When i run the script, it doesn't work and write:

DBI connect('host=**:sid=***','',...) failed:
ORA-24327: need explicit attach before authenticating a user (DBD
ERROR: OCISessionBegin) at /usr/local/sbin/myscript.pl

Any ideas?

Re: DBD::Oracle-1.26 fails test t/31lob.t on Oracle 11gR2 Ubuntu 10.04

[John Scoles]

 On 10/19/2010 2:33 PM, CAMPBELL, ED (ATTBST) wrote:

Ok I have seen this one before

Me thinks this is on the oracle side of things

I would just go ahead and install

The fail here will not effect you much as it is a rather obscure blob 
thing dealing with  the test of an insert empty blob returning blob to a 
var. it returns 0 for the lenght but it actully enters the data.


Might be a switch in the OCI client or an error on the DB??

I am working in it for the next release.

You might want to raise it with you Oracle rep and report it as a bug as 
it works for all version before 11


cheers
John Scoles



Thanks, I've been fortunate enough in my dealings with DBD or DBI that I
haven't had to run prove at this point..

The output from "prove -vb t/31lob.t" follows:
DBD::Oracle::st execute failed: ORA-24813: cannot send or receive an
unsupporte.

#   Failed test 'returned length via PL/SQL'
#   at t/31lob.t line 143.
#  got: undef
# expected: '4'
t/31lob.t ..
1..12
ok 1 - returned valid locator
ok 2 - inserted into BLOB successfully
ok 3 - got back what we put in
ok 4 - returned valid locator
ok 5 - returned valid locator
ok 6 - returned initialized locator
ok 7 - returned length
ok 8 - returned written value
not ok 9 - returned length via PL/SQL
Errors in file :
OCI-21500: internal error code, arguments: [kghufree_06], [0x0023CBDC8],
[0], []
)Or?)Or?Errors in file :

OCI-21500: internal error code, arguments: [kghufree_06], [0x0023CBDC8],
[0], []

Dubious, test returned 1 (wstat 256, 0x100)
Failed 4/12 subtests

Test Summary Report
---
t/31lob.t (Wstat: 256 Tests: 9 Failed: 1)
   Failed test:  9
   Non-zero exit status: 1
   Parse errors: Bad plan.  You planned 12 tests but ran 9.
Files=1, Tests=9,  0 wallclock secs ( 0.05 usr  0.01 sys +  0.23 cusr
0.06 csy)
Result: FAIL

It appears to be the same as output from "make test TEST_VERBOSE=1".


-Original Message-
From: Martin J. Evans [mailto:martin.ev...@easysoft.com]
Sent: Tuesday, October 19, 2010 2:23 PM
To: dbi-users@perl.org
Subject: Re: DBD::Oracle-1.26 fails test t/31lob.t on Oracle 11gR2
Ubuntu 10.04


   On 19/10/2010 18:17, CAMPBELL, ED (ATTBST) wrote:

I'm not sure exactly how to run the "prove" command.  When I try it as
"prove t/31lob.t" it gives:

Can't locate DBD/Oracle.pm in @INC (@INC contains: /etc/perl
/usr/local/lib/per.
BEGIN failed--compilation aborted at t/31lob.t line 5.
# Looks like your test exited with 2 before it could output anything.
t/31lob.t ..
Dubious, test returned 2 (wstat 512, 0x200)
Failed 12/12 subtests


You need prove -vb t/32lob.t if you run it from the untarred DBD::Oracle

distribution else you are attempting to run it using the installed
DBD::Oracle which clearly you do not have.

I think it adds -Iblib/lib and -Iblib/arch to the Perl command line.

I think unrelated but "/usr/local/lib/per" in INC looks strange.

Martin


Test Summary Report
---
t/31lob.t (Wstat: 512 Tests: 0 Failed: 0)
Non-zero exit status: 2
Parse errors: Bad plan.  You planned 12 tests but ran 0.
Files=1, Tests=0,  0 wallclock secs ( 0.06 usr  0.01 sys +  0.04 cusr
0.01 csy)
Result: FAIL

I assume I'm not running this correctly.  It appears to be looking for
an installed version of DBD which I haven't done at the point.  I just
attempting to install DBD and haven't actually done the "make

install".

Instead I ran the "make test TEST_VERBOSE=1", not all results shown,
just for t/31lob.t.  Hope this will shed some light.

. . . .
ok 479
ok
? ); END;" with ParamValues: :p1=undef,
:p2=OCILobLocatorPtr=SCALAR(0x1e8a0d8)].

#   Failed test 'returned length via PL/SQL'
#   at t/31lob.t line 143.
#  got: undef
# expected: '4'
t/31lob.t ...
1..12
ok 1 - returned valid locator
ok 2 - inserted into BLOB successfully
ok 3 - got back what we put in
ok 4 - returned valid locator
ok 5 - returned valid locator
ok 6 - returned initialized locator
ok 7 - returned length
ok 8 - returned written value
not ok 9 - returned length via PL/SQL
Errors in file :
OCI-21500: internal error code, arguments: [kghufree_06],

[0x001C2A838],

[0], []
Errors in file :
OCI-21500: internal error code, arguments: [kghufree_06],

[0x001C2A838],

[0], []

Dubious, test returned 1 (wstat 256, 0x100)
Failed 4/12 subtests
# create table dbd_ora__drop_me ( idx integer, x clob,  dt date )
# procedure p_DBD_Oracle_drop_me dropped
# table dbd_ora__drop_me dropped
t/31lob_extended.t ..
1..31
. . . .

-Original Message-
From: John Scoles [mailto:sco...@pythian.com]
Sent: Tuesday, October 19, 2010 12:03 PM
To: CAMPBELL, ED (ATTBST)
Cc: dbi-users@perl.org
Subject: Re: DBD::Oracle-1.26 fails test t/31lob.t on Oracle 11gR2
Ubuntu 10.04


On 10/19/2010 10:34 AM, CAMPBELL

Re: DBD::Oracle-1.26 fails test t/31lob.t on Oracle 11gR2 Ubuntu 10.04

[John Scoles]

 On 10/19/2010 10:34 AM, CAMPBELL, ED (ATTBST) wrote:

No I do not think you buggered anything up.

Might be a permissions error or alike or a change in Oracle 11gR2 sounds 
very new.


if you can do a

 prove (t/31lob.t)  on it so we can get the deatils of that test that 
will help narrow it down


cheers
John Scoles

When I do make test on DBD::Oracle-1.26 with Oracle 11gR2 on Ubuntu
10:04 x64 it fails t/31lob.t test 9.  Is this a known problem or am I
doing something wrong.

My perl info:
$ perl -V
Summary of my perl5 (revision 5 version 10 subversion 1) configuration:

   Platform:
 osname=linux, osvers=2.6.24-27-server,
archname=x86_64-linux-gnu-thread-muli
 uname='linux crested 2.6.24-27-server #1 smp fri mar 12 01:23:09 utc
2010 x'
 config_args='-Dusethreads -Duselargefiles -Dccflags=-DDEBIAN
-Dcccdlflags=-'
 hint=recommended, useposix=true, d_sigaction=define
 useithreads=define, usemultiplicity=define
 useperlio=define, d_sfio=undef, uselargefiles=define, usesocks=undef
 use64bitint=define, use64bitall=define, uselongdouble=undef
 usemymalloc=n, bincompat5005=undef
   Compiler:
 cc='cc', ccflags ='-D_REENTRANT -D_GNU_SOURCE -DDEBIAN
-fno-strict-aliasing,
 optimize='-O2 -g',
 cppflags='-D_REENTRANT -D_GNU_SOURCE -DDEBIAN -fno-strict-aliasing
-pipe -f'
 ccversion='', gccversion='4.4.3', gccosandvers=''
 intsize=4, longsize=8, ptrsize=8, doublesize=8, byteorder=12345678
 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16
 ivtype='long', ivsize=8, nvtype='double', nvsize=8, Off_t='off_t',
lseeksiz8
 alignbytes=8, prototype=define
   Linker and Libraries:
 ld='cc', ldflags =' -fstack-protector -L/usr/local/lib'
 libpth=/usr/local/lib /lib /usr/lib /lib64 /usr/lib64
 libs=-lgdbm -lgdbm_compat -ldb -ldl -lm -lpthread -lc -lcrypt
 perllibs=-ldl -lm -lpthread -lc -lcrypt
 libc=/lib/libc-2.11.1.so, so=so, useshrplib=true,
libperl=libperl.so.5.10.1
 gnulibc_version='2.11.1'
   Dynamic Linking:
 dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E'
 cccdlflags='-fPIC', lddlflags='-shared -O2 -g -L/usr/local/lib
-fstack-prot'


Characteristics of this binary (from libperl):
   Compile-time options: MULTIPLICITY PERL_DONT_CREATE_GVSV
 PERL_IMPLICIT_CONTEXT PERL_MALLOC_WRAP
USE_64_BIT_ALL
 USE_64_BIT_INT USE_ITHREADS USE_LARGE_FILES
 USE_PERLIO USE_REENTRANT_API
   Built under linux
   Compiled at Apr 23 2010 08:21:31
   @INC:
 /etc/perl
 /usr/local/lib/perl/5.10.1
 /usr/local/share/perl/5.10.1
 /usr/lib/perl5
 /usr/share/perl5
 /usr/lib/perl/5.10
 /usr/share/perl/5.10
 /usr/local/lib/site_perl
 .

My DBI info:
perl -MDBI -e 'print "$DBI::VERSION\n"'
1.609

The output of the 'make test':
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e"
"test_harness(0,t
# Test loading DBI, DBD::Oracle and version
t/01base.t .. ok
t/10general.t ... ok
t/12impdata.t ... ok
t/14threads.t ... ok
t/15nls.t ... ok
t/20select.t  ok
t/21nchar.t . ok
t/22nchar_al32utf8.t  ok
t/22nchar_utf8.t  ok
t/23wide_db.t ... skipped: Database character set is not Unicode
t/23wide_db_8bit.t .. skipped: Database character set is not Unicode
t/23wide_db_al32utf8.t .. skipped: Database character set is not Unicode
t/24implicit_utf8.t . ok
t/25plsql.t . ok
t/26exe_array.t . ok
t/28array_bind.t  ok
t/30long.t .. ok
t t/31lob.t line 126.
:

t/31lob.t ...
Dubious, test returned 1 (wstat 256, 0x100)
Failed 4/12 subtests
t/31lob_extended.t .. ok
t/32xmltype.t ... ok
t/34pres_lobs.t . ok
  Placeholder behaviour for ora_type=1 VARCHAR2 (the default) varies with
Oracle.
  Oracle 7 didn't strip trailing spaces, Oracle 8 did, until 9.2.x
  Your system doesn't. If that seems odd, let us know.
t/40ph_type.t ... ok
Can't determine open_cursors from v$parameter, so using default
t/50cursor.t  ok
t/51scroll.t  ok
t/55nested.t  ok
t/56embbeded.t .. ok
t/58object.t  ok
t/60reauth.t  skipped: ORACLE_USERID_2 not unique.
t/70meta.t .. ok
t/80ora_charset.t ... ok


Test Summary Report
---
t/31lob.t (Wstat: 256 Tests: 9 Failed: 1)
   Failed test:  9
   Non-zero exit status: 1
   Parse errors: Bad plan.  You planned 12 tests but ran 9.
Files=30, Tests=1166, 26 wallclock secs ( 0.46 usr  0.15 sys + 11.99
cusr  1.72)
Result: FAIL
Failed 1/30 test programs. 1/1166 subtests failed.
make: *** [test_dynamic] 

Re: DBD::Oracle version to use?

[John Scoles]

 On 10/13/2010 4:12 AM, Martin J. Evans wrote:

On 13/10/10 02:25, Carlson, John W. wrote:

What is the recommended DBD::Oracle version and which instant client
should I compile against for Oracle 11g databases, RedHat el5xen
Linux x86_64 (64bit) architecture?  There appears to be several
recent oddly named versions on CPAN. 1.26 is out there, but it
doesn't appear to be the latest release.  Thanks for your help!

John

The DBD-Oracle-1.25-withoutworldwriteables is because 1.25 was uploaded without 
world writeable dir (probably because it was packaged on Windows) and CPAN 
spots this and changes the name (amongst other things).

The DBD-Oracle-1.26 was I believe uploaded by Tim Bunce to fix the above 
problem.

I'm not sure what the DBD-Oracle-1.25_brokentar is unless it was uploaded to 
demonstrate a problem with CPAN.

Use 1.26.
I use Oracle Client 11.2 without any problems.

Don't forget to download the "sdk" as well as the "basic" and it does not hurt to get 
"sqlplus" at the same time as it can be useful.

Martin
Martin is spot on 1.26 would be the one to use and all of the instant 
client downloads.


Cheers
John Scoles

DBD::Oracle version to use?

[Carlson, John W.]

What is the recommended DBD::Oracle version and which instant client should I 
compile against for Oracle 11g databases, RedHat el5xen Linux x86_64 (64bit) 
architecture?  There appears to be several recent oddly named versions on CPAN. 
1.26 is out there, but it doesn't appear to be the latest release.  Thanks for 
your help!

John

Re: Fwd: Question regarding DBI.pm for Perl, v5.6.1 built for MSWin32-x86 on Windows 2003.

[John Scoles]

 On 10/8/2010 8:06 AM, Martin J. Evans wrote:

On 08/10/10 12:41, John Scoles wrote:

  On 10/7/2010 10:20 AM, Mayank Gupta wrote:

When in trouble with Activestate Perl for windows you best bet would be to

1) deinstall all of the Activestate products on your box
2) install only the free version of Perl  from here
http://www.activestate.com/activeperl/downloads
3) start again

I'm not sure this is the "best bet" in this case or even in general.
Mayank, I'm no ActiveState expert but it looks like you have a business addition of 
ActiveState your company may have paid for so I would not rush to deinstall it and 
replace with the "free" version.

Also if you have a proxy issue reinstalling is not going to make it go away.

Martin
True enough I get so use to people using the free edition it didn't even 
cross my mind that someone would pay for it.


Might be the case that the software has expired as it is over 6 years old.

Do what Martin suggests go back to activestate and ask.

Re: Fwd: Question regarding DBI.pm for Perl, v5.6.1 built for MSWin32-x86 on Windows 2003.

[John Scoles]

 On 10/7/2010 10:20 AM, Mayank Gupta wrote:

When in trouble with Activestate Perl for windows you best bet would be to

1) deinstall all of the Activestate products on your box
2) install only the free version of Perl  from here 
http://www.activestate.com/activeperl/downloads

3) start again




Hi Tim,

I very new to Perl and have been put in a situation where I am supposed to
figure out why a Perl code is not working. I have already written to
dbi-users@perl.org
  and am still awaiting a response. I just thought I would right to you too
with a hope that you might be able to help me.

Issue is with the DBI module for Perl, v5.6.1 built for
MSWin32-x86-multi-thread on Windows 2003. I am getting a syntax error: Can't
locate DBI.pm in @INC. I checked the directories in @INC array and both the
directories (C:\Perl\lib and C:\Perl\Site\lib) seem to miss DBI.pm. I also
did the search in parent directory c:\Perl but could not find DBI.pm
anywhere. Could be that DBI.pm module never got installed or got deleted by
some other user accidentally.

Can you please suggest how can I fix this problem? Do I need to install DBI
module again and if that is the case which version would it be and where can
I install it from? Or better would be installing absolute new version of
Perl?

*Also, following is what I tried in command line to install but didn't work:
*


PPM>  install dbi
Install package 'dbi?' (y/N): y
Installing package 'dbi'...
Downloading
http://ppm.activestate.com/PPMPackages/5.6/MSWin32-x86-multi-thread/
DBI-1.48.tar.gz ...
Error installing package 'dbi': Error reading
http://ppm.activestate.com/PPMPack
ages/5.6/MSWin32-x86-multi-thread/DBI-1.48.tar.gz
PPM>


*Version of Perl:*

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\mgupta>perl -v
This is perl, v5.6.1 built for MSWin32-x86-multi-thread


This is also a very very old version of Perl

(with 1 registered patch, see perl -V for more detail)
Copyright 1987-2001, Larry Wall
Binary build 638 provided by ActiveState Corp.
http://www.ActiveState.com
ActiveState is a division of Sophos.
Built Apr 13 2004 19:24:21
Perl may be copied only under the terms of either the Artistic License or
the
GNU General Public License, which may be found in the Perl 5 source kit.
Complete documentation for Perl, including FAQ lists, should be found on
this system using `man perl' or `perldoc perl'.  If you have access to the
Internet, point your browser at http://www.perl.com/, the Perl Home Page.


  Thanks much in advance for being so kind to help. I will appreciate a
response back.

Kind Regards,
Mayank Gupta.
703-677-6984.





-- Forwarded message --
From: Mayank Gupta
Date: Wed, Oct 6, 2010 at 11:15 PM
Subject: Question regarding DBI.pm for Perl, v5.6.1 built for MSWin32-x86 on
Windows 2003.
To: dbi-users@perl.org


Hello,


I have a question regarding DBI.pm and hoping you will be able to help me
with an answer. I am working with a perl script and when I tried executing
the script, I got a syntax error: Can't locate DBI.pm in @INC. I checked the
directories in @INC array and both the directories (C:\Perl\lib and
C:\Perl\Site\lib) seem to miss DBI.pm. I also did the search in parent
directory c:\Perl but could not find DBI.pm anywhere. Could be that DBI.pm
module never got installed or got deleted by some other user accidentally.
Can you please suggest how can I fix this problem? Do I need to install DBI
module again and if that is the case which version would it be and where can
I install it from? I am using Perl, v5.6.1 built for
MSWin32-x86-multi-thread on Windows 2003.



Thanks much in advance for being so kind to help.

Re: Question regarding DBI.pm for Perl, v5.6.1 built for MSWin32-x86 on Windows 2003.

[John R Pierce]

 On 10/07/10 8:18 AM, Mayank Gupta wrote:

PPM>  install dbi
Install package 'dbi?' (y/N): y
Installing package 'dbi'...
Downloading
http://ppm.activestate.com/PPMPackages/5.6/MSWin32-x86-multi-thread/DBI-1.48.tar.gz
 ...
Error installing package 'dbi': Error reading
http://ppm.activestate.com/PPMPackages/5.6/MSWin32-x86-multi-thread/DBI-1.48.tar.gz
PPM>




when I manually try and download from that URL, I get a request for a 
username and password, something about ActiveState Business Edition.


I'd contact ActiveState support for this, as PPM is their own creation, 
to support a CPAN like environment for MS Windows.

Re: Problem compiling DBD::Oracle on FreeBSD with Oracle 10g Instant Client

[John Scoles]

 On 10/5/2010 3:58 AM, Martin J. Evans wrote:

On 03/10/10 17:45, Eric Yellin wrote:


Hi,


I am trying to compile DBD::Oracle that will work with Oracle 10g on
FreeBSD 6.3


Here is what I have done so far:

1. Installed the 3 Oracle Instant Client ports:

- linux-oracle-instantclient-basic
- linux-oracle-instantclient-sdk
- linux-oracle-instantclient-sqlplus

These install as follows:
_The "basic files install in:_
/usr/compat/linux/usr/lib/Oracle/10.2.0.3/client/lib

_The sqlplus installes in _:
/usr/compat/linux/usr/lib/Oracle/10.2.0.3/client/bin

_The sdk split between:_
/usr/compat/linux/usr/include/Oracle/10.2.0.3/client/lib (the include
directory)
and:
/usr/compat/linux/usr/share/Oracle/10.2.0.3/client/lib (the demo directory)

2. Downloaded latest DBD::Oracle (1.25)

3.  Set Environment variables as follows:
setenv LD_LIBRARY_PATH /usr/compat/linux/usr/lib/oracle/10.2.0.3/client/lib
setenv ORACLE_HOME /usr/compat/linux/usr/lib/oracle/10.2.0.3/client

You normally don't need to set ORACLE_HOME for instant client.

Shouldn't that be /usr/compat/linux/usr/lib/oracle/10.2.0.3, i.e. no client/lib 
on the end?


4. Ran perl Makefile.PL for DBD:Oracle and recieved the follwoing error:
/"Unable to locate an oracle.mk, proc.mk or other suitable *.mk"/

5. changed the ORACLE_HOME variable to: setenv ORACLE_HOME
/usr/compat/linux/usr/lib/oracle/10.2.0.3/client/lib and ran "perl
Makefile.PL" again and now received the following message:
/"I can't find the header files I need in your Oracle installation.
You probably need to install some more Oracle components.
For Instant Client that means the SDK package"/

6. Copied the "sdk" files directly from the ports "work" directory
leaving them in the original structure and placing them under the
/usr/compat/linux/usr/lib/oracle/10.2.0.3/client/lib directory and ran
perl Makefile.PL again. Now it built the Makefile succesfully with a few
warnings:

/WARNING: If you have problems you may need to rebuild perl with
threading enabled.
WARNING: If you have problems you may need to rebuild perl with
-Uusemymalloc.
WARNING: META_MERGE is not a known parameter.
'META_MERGE' is not a known MakeMaker parameter name./

I guess the META_MERGE warning is because your ExtUtils::MakeMaker does not 
support it. If that is the case DBD::Oracle should only add META_MERGE after 
testing ExtUtils::MakeMaker version.


7. Ran 'make' and it compiled smoothly.

8. Ran 'make test'  and got the following error:
/Failed to load Oracle extension and/or shared libraries:
install_driver(Oracle) failed: Can't load
'/home/packages/Oracle/DBD-Oracle-1.25/blib/arch/auto/DBD/Oracle/Oracle.so'
for module DBD::Oracle: Shared object "libdl.so.2" not found, required
by "libclntsh.so.10.1" at /usr/local/lib/perl5/5.8.8/mach/DynaLoader.pm
line 230.
  at (eval 9) line 3
Compilation failed in require at (eval 9) line 3.
Perhaps a required shared library or dll isn't installed where expected
  at t/01base.t line 19
The remaining tests will probably also fail with the same error./

The libdl.so.2 file is located in "/usr/compat/linux/lib" and the
compiled DBD::Oracle module does 'know' not see that directory.

How can I move on from here to link the necessary shared libraries
during runtime ? (Tried doing this with symlinks, but kept getting
errors and eventually reached a dead end where symlinking did not help
anymore. I assume there must be a different way to do it)

Thanks,  Eric




Undo the changes you made and try again without client/lib in your 
LD_LIBRARY_PATH and without setting ORACLE_HOME.

Martin

Counter intuitive but that might work.

Pleas give it a try.

That is something that should go in the readme.

cheers
John

Re: Problem compiling DBD::Oracle on FreeBSD with Oracle 10g Instant Client

[John Scoles]

 On 10/3/2010 12:45 PM, Eric Yellin wrote:
I have yet to try an build DBD::Oracle for FreeBSD so the Makefile might 
not be working correctly  here are my comments





Hi,


I am trying to compile DBD::Oracle that will work with Oracle 10g on 
FreeBSD 6.3



Here is what I have done so far:

1. Installed the 3 Oracle Instant Client ports:

- linux-oracle-instantclient-basic
- linux-oracle-instantclient-sdk
- linux-oracle-instantclient-sqlplus

These install as follows:
_The "basic files install in:_
/usr/compat/linux/usr/lib/Oracle/10.2.0.3/client/lib

_The sqlplus installes in _:
/usr/compat/linux/usr/lib/Oracle/10.2.0.3/client/bin

_The sdk split between:_
/usr/compat/linux/usr/include/Oracle/10.2.0.3/client/lib (the include 
directory)

and:
/usr/compat/linux/usr/share/Oracle/10.2.0.3/client/lib (the demo 
directory)


2. Downloaded latest DBD::Oracle (1.25)

3.  Set Environment variables as follows:
setenv LD_LIBRARY_PATH 
/usr/compat/linux/usr/lib/oracle/10.2.0.3/client/lib

setenv ORACLE_HOME /usr/compat/linux/usr/lib/oracle/10.2.0.3/client


this looks ok till this point


4. Ran perl Makefile.PL for DBD:Oracle and recieved the follwoing error:
/"Unable to locate an oracle.mk, proc.mk or other suitable *.mk"/

Nomally in the linux IC the .so files are buried under/in another file 
and you have to make a ln to them like this


 libclntsh.so -> /export/home/ic_112/instantclient_11_2/libclntsh.so.11.1

Did the Makefile make a like like the above for you???




5. changed the ORACLE_HOME variable to: setenv ORACLE_HOME 
/usr/compat/linux/usr/lib/oracle/10.2.0.3/client/lib and ran "perl 
Makefile.PL" again and now received the following message:

/"I can't find the header files I need in your Oracle installation.
You probably need to install some more Oracle components.
For Instant Client that means the SDK package"/

6. Copied the "sdk" files directly from the ports "work" directory 
leaving them in the original structure and placing them under the 
/usr/compat/linux/usr/lib/oracle/10.2.0.3/client/lib directory and ran 
perl Makefile.PL again. Now it built the Makefile succesfully with a 
few warnings:


/WARNING: If you have problems you may need to rebuild perl with 
threading enabled.
WARNING: If you have problems you may need to rebuild perl with 
-Uusemymalloc.

WARNING: META_MERGE is not a known parameter.
'META_MERGE' is not a known MakeMaker parameter name./

7. Ran 'make' and it compiled smoothly.


That would be odd after the first few errors you got.

8. Ran 'make test'  and got the following error:
/Failed to load Oracle extension and/or shared libraries:
install_driver(Oracle) failed: Can't load 
'/home/packages/Oracle/DBD-Oracle-1.25/blib/arch/auto/DBD/Oracle/Oracle.so' 
for module DBD::Oracle: Shared object "libdl.so.2" not found, required 
by "libclntsh.so.10.1" at 
/usr/local/lib/perl5/5.8.8/mach/DynaLoader.pm line 230.

 at (eval 9) line 3


Mos likely related to the bad ln above.  now DBD oracle cannot find its 
.so files.


Hope this helps a little

Compilation failed in require at (eval 9) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at t/01base.t line 19
The remaining tests will probably also fail with the same error./

The libdl.so.2 file is located in "/usr/compat/linux/lib" and the 
compiled DBD::Oracle module does 'know' not see that directory.


How can I move on from here to link the necessary shared libraries 
during runtime ? (Tried doing this with symlinks, but kept getting 
errors and eventually reached a dead end where symlinking did not help 
anymore. I assume there must be a different way to do it)


Thanks,  Eric

Re: DBD::Oracle 1.25 and DRCP

[John Scoles]

 On 9/21/2010 4:05 PM, Cliff Martin wrote:

I am using the new DBD::Oracle module with DRCP. I am able to connect
to my connection pool, but the NUM_HITS does not increment, and the
connection class is always "SHARED" instead of whatever I specify
(using either the environment variable or the connection parameter)

What am I doing wrong?


Most likely nothing.  It is most likely the implementation will have to 
change.


We are only at the opening stages of getting DRCP to work with 
DBD::Oracle so this feedback is great.


I am not sure what  the 'connection class' and NUM_HITS are?

Can you give me an example of where you are getting these and how the 
DRCP should connect.


I will gladly change the interface if need be.

Cheers
John Scoles


I have the same problem with Python, but PHP works great. With PHP I
can set the connection class and the NUM_HITS goes up appropriately.

Using Perl 5.10, DBI 1.614, DBD::Oracle 1.25 and Oracle 11.1 on an HP-

UX 11v3 Itanium system.

Cliff

Re: DBI interfacing with Mysql

[John R Pierce]

 On 09/21/10 2:32 AM, SUNANDA SURYA KUMARI Y (ssuryaku) wrote:

Hi,



I am facing problem with the interfacing of Mysql with perl module . It
is showing me the following output.





"install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC
contains:

C:/Perl64/site/lib C:/Perl64/lib .) at (eval 5) line 3.

Perhaps the DBD::mysql perl module hasn't been fully installed,


  ^

I think that sums it up.   It appears DBD::mysql hasn't been installed 
on this system.   from your C: path, I'm assuming you're on some sort of 
Microsoft Windows...  Are you sure you installed DBD::mysql with your 
perl package?




or perhaps the capitalisation of 'mysql' isn't right.

Available drivers: CSV, DBM, ExampleP, File, Gofer, ODBC, Proxy, SQLite,
Sponge."


certainly sounds like DBD:mysql isn't installed.

if you're using ActivePerl, I believe you can use something like...

 ppm install DBD::mysql

(at a command line prompt) to install it from the ActivePerl library.  
if you're using another perl port to Windows, refer to their 
documentation on installing perl modules.

Re: DBD-Oracle on AIX

[John R Pierce]

 On 09/14/10 6:58 AM, John Scoles wrote:

 On 9/14/2010 9:17 AM, John R Pierce wrote:
Sorry,  I meant, the make test problems with t/58object on big endian 
machines like Sparc, IBM Power


Ok that clears it up a little.

I am not sure if it was  fixed yet.

Give the latest trunk version a try and see what happens.  Just a side 
note if you are not using VARRY, TABLE or OBJECT in your tables you 
can ignore this failure as you will never hit it.


yup, aware thats the case, and yes, we've been ignoring the failure as 
the perl programs in question are truly ancient and wouldn't know what 
these things are if they bit them :)


I'll try and find the time to get around to building this again from 
trunk and seeing whether or not it recurs.