Re: Perl DBI libraries for connecting 12c

[Furst, Carl]

I believe you have to go to Oracle to get those.. You can get DBI and even 
DBB::Oracle but it won’t build. You won’t have the Oracle C libs to link off 
of. You’ll need to install an oracle client library.

--
Carl Yamamoto-Furst


From: Michael Radakovich 
Date: Monday, October 9, 2017 at 5:46 PM
To: "dbi-users@perl.org" 
Subject: Perl DBI libraries for connecting 12c

  Hello,

  I need to install the Perl DBI libraries that are 
used by Perl scripts for connecting to an Oracle 12c database. The server is 
running Solaris 10 sparc
  Any help would be greatly appreciated.

# perl -v

This is perl, v5.8.4 built for sun4-solaris-64int
(with 37 registered patches, see perl -V for more detail)

Copyright 1987-2004, Larry Wall

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.

Michael Radakovich
Engineer, Infrastructure II
Family Guy, Sports Junkie, Music Man
WOW! internet, cable & phone
--
desk: 630-536-3163mobile: 630-803-9048fax: 630-536-3108












[http://corpweb.mlb.com/MLB_COM_Disclaimed1.png]

Re: Bundle::DBI and DBD::Oracle failing

[Furst, Carl]

It means you don¹t have the Oracle DBD installed. Or its installed to a
different place.

Did you install it? Where?

Carl Yamamoto-Furst
-- 





From:  Tony D'Alfonso 
Date:  Wednesday, June 22, 2016 at 2:00 PM
To:  "dbi-users@perl.org" 
Subject:  RE: Bundle::DBI and DBD::Oracle failing


Confirming this email made it through.  Slicing the output into multiple
messages.  Can you help me with this error message?
 
perl /stl/bin/test.pl
install_driver(Oracle) failed: Can't load
'/root/perl5/lib/perl5/x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so'
 for module DBD::Oracle:
 libstdc++.so.5: cannot open shared object file: No such file or directory
at /usr/lib64/perl5/DynaLoader.pm line 190.
at (eval 4) line 3.
Compilation failed in require at (eval 4) line 3.
Perhaps a required shared library or dll isn't installed where expected
at /stl/bin/test.pl line 3.
 




**

MLB.com  - Where Baseball is Always On.

Re: DBD::CSV

[Furst, Carl]

What system, version of Excel?
I’m using Mac Office on OS 10.9.5


Carl Furst


From: Matthew Musgrove mr.musk...@gmail.commailto:mr.musk...@gmail.com
Date: Wednesday, February 25, 2015 at 2:31 PM
Cc: Perl DBI mailing list dbi-users@perl.orgmailto:dbi-users@perl.org
Subject: Re: DBD::CSV

Carl,
When I tried it just now (first time using DBD::CSV) it just worked as I would 
expect.

First, here's my script that I named test.plhttp://test.pl:
#!/bin/env perl
use strict;
use warnings;

use Data::Dumper;
use DBI;
use Try::Tiny qw( try catch );

my $dbh = DBI-connect( 'DBI:CSV:', '', '', {
f_schema = undef,
f_dir = '.',
f_ext = '.csv/r',
RaiseError = 1,
PrintError = 0,
}) or die Cannot connect: $DBI::errstr;

my $test = try {
return $dbh-selectall_arrayref( 'select * from test' );
}
catch {
my $err = $_;
if ( $err =~ m!^.*DBD::CSV::db.*?Execution ERROR: (.*?) at 
.*?DBI/DBD.*?line \d+.*?called from (.*? at \d+)\..*?$!s )
{
my ( $msg, $where ) = ( $1, $2 );
warn $msg -- $where\n;
}
else
{
warn $err\n;
}
return {};
};

print Dumper( $test );
__END__

Here is my test.csv file:
id,value
a,1
b,2
c,3

When I run it with test.csv (whether or not it is open in Excel) I get this 
output:
$VAR1 = [
  [
'a',
'1'
  ],
  [
'b',
'2'
  ],
  [
'c',
'3'
  ]
];

When I run it from a network drive I get this output:
Cannot obtain shared lock on /export/home/mmusgrove/test.
csv: No locks available -- ./test.plhttp://test.pl at 18
$VAR1 = {};

HTH,
Matt


On Wed, Feb 25, 2015 at 11:25 AM, Furst, Carl 
carl.fu...@mlb.commailto:carl.fu...@mlb.com wrote:
I think someone wrote about this before, but I just wanted to bring it up
again.

Is there a way to have DBD::CSV produce a warning or a message (like when
printError and raise error is set) to say that it’s waiting for a file
lock??

For example if I’m using my oh so beloved MS Excel and it locks the csv
file and at the same time I’m trying to also run my even more beloved perl
script which is trying to open the file for read. The script of course
hangs when trying to execute any statements because it’s waiting for the
flock put there by MS Excel..Is there a lock timeout? Is that what f_lock
= 2 is for??

At least I’m pretty sure that’s what’s happening because when I open it in
things that put the whole file into memory (like my other beloved text
editor) I have no problem, but excel likes to lock things, it seems. When
I close the file in Excel, of course, the perl runs like a charm.

Thanks,
Carl Furst

**

MLB.com: Where Baseball is Always On

**

MLB.com: Where Baseball is Always On

Re: make test fails installing DBI-1.632 (linux, perl5.8.8)

[Furst, Carl]

Yeah, I agree,  it would save time if there was a check during make test
itself and a die with a version incompatible message.


Carl Furst
CMS Developer
MLB Advanced Media





On 1/8/15, 11:20 AM, John Wiersba jrw32...@gmail.com wrote:

Adding dbi-users

-- Forwarded message --
From: John Wiersba jrw32...@gmail.com
Date: Thu, Jan 8, 2015 at 11:19 AM
Subject: Re: make test fails installing DBI-1.632 (linux, perl5.8.8)
To: Furst, Carl carl.fu...@mlb.com

Thanks, Carl.  Well, I see that now, but I did not see it before, even
after looking for it.  And it's only a warning; it did not cause a
failure in the Makefile.PL step (exit status = 0).  Additionally, it's
nowhere near the point of failure, which occurred in make test.

Maybe I'm dreaming, but if the wrong version of Test::Simple will
cause test failures, then detecting the wrong version should trigger a
failure and it should be loud.  I spent probably an hour of time
scanning the docs for any mention of the problem, debugging the
failing test, then guessing the cause (maybe there's a more recent
version of Test::More needed to supply the note() function), and
searching on google, to end up deducing that Test::Simple needed to be
updated.

Or, alternatively, some comment in INSTALL or README.md mentioning the
correlation of the symptoms (failed test) with the version of
Test::Simple installed.

On Thu, Jan 8, 2015 at 10:53 AM, Furst, Carl carl.fu...@mlb.com wrote:
 But it did say:

 Warning: prerequisite Test::Simple 0.90 not found. We have 0.62.


 Which is  the same thing, really.


 Carl Furst
 CMS Developer
 MLB Advanced Media





 On 1/8/15, 10:48 AM, John Wiersba jrw32...@gmail.com wrote:

After installing the latest version of Test::Simple, all tests passed,
and the installation (and a test script) succeeded.  Problem #3 is
that the make (or make test) didn't fail with something like
dependency on Test::Simple 0.90 not satisfied.

On Thu, Jan 8, 2015 at 8:46 AM, Tim Bunce tim.bu...@pobox.com wrote:
 On Wed, Jan 07, 2015 at 04:50:52PM -0500, John Wiersba wrote:

 I see in META.{json,yaml} that a later version of Test::Simple is
 required, but that doesn't seem to cause a noticable error message or
 abort the Makefile.PL/make/make test process.  Installing the latest
 version of Test::Simple allows make test to succeed.

 So there's no problem now? Or did that just fix t/06attr.t?

 Tim.

 **

 MLB.com: Where Baseball is Always On

**

MLB.com: Where Baseball is Always On

Re: make test fails installing DBI-1.632 (linux, perl5.8.8)

[Furst, Carl]

But it did say:

Warning: prerequisite Test::Simple 0.90 not found. We have 0.62.


Which is  the same thing, really.


Carl Furst
CMS Developer
MLB Advanced Media





On 1/8/15, 10:48 AM, John Wiersba jrw32...@gmail.com wrote:

After installing the latest version of Test::Simple, all tests passed,
and the installation (and a test script) succeeded.  Problem #3 is
that the make (or make test) didn't fail with something like
dependency on Test::Simple 0.90 not satisfied.

On Thu, Jan 8, 2015 at 8:46 AM, Tim Bunce tim.bu...@pobox.com wrote:
 On Wed, Jan 07, 2015 at 04:50:52PM -0500, John Wiersba wrote:

 I see in META.{json,yaml} that a later version of Test::Simple is
 required, but that doesn't seem to cause a noticable error message or
 abort the Makefile.PL/make/make test process.  Installing the latest
 version of Test::Simple allows make test to succeed.

 So there's no problem now? Or did that just fix t/06attr.t?

 Tim.

**

MLB.com: Where Baseball is Always On

Re: First time DBI user crashing perl

[Furst, Carl]

Is the driver you are using the one you used to install the DBD libs with?

If you installed the DBD libs with one driver and then, say, upgraded the
driver, you would have to re-install the DBD libs.


Carl Furst






On 10/30/14, 5:37 PM, Tim Bunce tim.bu...@pobox.com wrote:

On Thu, Oct 30, 2014 at 03:33:05PM -0500, Peter Shabino wrote:
Here is the environment variables:
env DB2INSTANCE = DB2
env DB2LIB = C:/Program Files/IBM/SQLLIB/lib/Win32
env LD_LIBRARY_PATH = C:/Program Files/IBM/SQLLIB/lib/Win32
env LIBPATH = C:/Program Files/IBM/SQLLIB/lib/Win32
and the output from trace 15
New 'DBI::dr' (for DBD::DB2::dr, parent='', id=undef)

dbih_setup_handle(DBI::dr=HASH(0x2e4c7c0)=DBI::dr=HASH(0x2e4c868),
DBD::DB2::dr, 0, Null!)
dbih_make_com(Null!, 0, DBD::DB2::dr, 176, 0) thr#98a6f8
dbih_setup_attrib(DBI::dr=HASH(0x2e4c868), Err, Null!)
SCALAR(0x2acff18) (already defined)
dbih_setup_attrib(DBI::dr=HASH(0x2e4c868), State, Null!)
SCALAR(0x2e11358) (already defined)
dbih_setup_attrib(DBI::dr=HASH(0x2e4c868), Errstr, Null!)
SCALAR(0x2acff60) (already defined)
dbih_setup_attrib(DBI::dr=HASH(0x2e4c868), TraceLevel, Null!) 0
(already defined)
dbih_setup_attrib(DBI::dr=HASH(0x2e4c868), FetchHashKeyName,
Null!) 'NAME' (already defined)
- install_driver= DBI::dr=HASH(0x2e4c7c0)
 data_sources DISPATCH (DBI::dr=HASH(0x2e4c7c0) rc1/3 @1 g3
ima801 pid#9476) at
c:/strawberry/perl/site/lib/DBI.pm line 1066 via  at db2.pl line 35
!! The warn '0' was CLEARED by call to data_sources method
- data_sources for DBD::DB2::dr
(DBI::dr=HASH(0x2e4c7c0)~0x2e4c868) thr#98a6f8
Any clues in there on what I have set up wrong?

Nope. Sure looks like a crash within the DBD::DB2 driver.
Hopefully someone here can help but you could also try
http://www.software.ibm.com/data/db2/perl

Tim.

Here is my perl version
This is perl 5, version 18, subversion 1 (v5.18.1) built for
MSWin32-x64-multi-thread
Thanks!
Peter
 
My projects:
http://www.wire2wire.org/
 Date: Thu, 30 Oct 2014 12:17:19 +
 From: tim.bu...@pobox.com
 To: three1...@gmail.com
 CC: wi...@hotmail.com; dbi-users@perl.org
 Subject: Re: First time DBI user crashing perl

 Turning on tracing is usually a good idea when odd things are
happening.
 Read https://metacpan.org/pod/DBI#TRACING
 and set the DBI_TRACE environment variable.

 Tim.

 On Wed, Oct 29, 2014 at 09:56:27PM -0600, Jon wrote:
  Hello Peter,
 
  Well, that's kinda what I figured but never hurts try for the
easy answer.
 
  I cleaned up your test script a bit.
 
  Can you give this version a try:
[1]https://gist.github.com/three18ti/c37587ef7d5fb9ee5748
 
  I'm downloading DB2 as we speak, but I wanted to ask for your
DB2_HOME environment variable. In your
  instructions it has you set it, but the instructions are clearly
for a linux server, what did you do
on
  your windows server?
 
  Honestly, I have very limited DB2 experience, so it's kinda the
blind leading the blind here. I'm
  really just curious myself. you're more than welcome to ignore
me and wait for one of the more
  experienced guys :)
 
  Best Regards,
  Jon A
  On Wed, Oct 29, 2014 at 8:42 PM, Peter Shabino
[2]wi...@hotmail.com wrote:
 
  I am running it from a command prompt and I am getting some of
the output so perl is running fine.
  Just looks like any command that trys and uses the DB2 interface
causes a crash.
  Thanks,
  Peter
 
  My projects:
  [3]http://www.wire2wire.org/
 
 

-
---
 
  Date: Wed, 29 Oct 2014 20:26:15 -0600
  Subject: RE: First time DBI user crashing perl
  From: [4]three1...@gmail.com
  To: [5]wi...@hotmail.com
  CC: [6]dbi-users@perl.org
 
  Hello Peter,
 
  How are you invoking the script? Do you have a perl script that
you double click?
 
  If so, can you try opening a command/powershell window and
running your program? You'll likely have
to
  specify the full path to the perl binary then pass your script
as the first parameter.
 
  E.g.: \strawberry\perl\bin\perl.exe [7]my_script.pl
 
  I have not encountered this particular problem with db2 but this
is my first step when trying to get
  one of my scripts to run in Windows (I'm a Linux enginner by the
Windows engineers steal a lot of my
  applications), perhaps someone has more specific experience than
myself.
 
  Best Regards,
  Jon A
 
  On Oct 29, 2014 5:50 PM, Peter Shabino [8]wi...@hotmail.com
wrote:
 
  Try this again I apparently found a keyboard shortcut to send
the message in hotmail..
 
  Trying to set up a connection to 

DBD::Oracle installation

[Furst, Carl]

What libs from oracle are exactly required to install this..

We just installed the client libs, including sql plus on Centos and it seems 
that Makefile.pl is having trouble finding the version:

WARNING: I could not determine Oracle client version so I'll just
default to version 8.0.0.0. Some features of DBD::Oracle may not work.
Oracle version based logic in Makefile.PL may produce erroneous results.
You can use perl Makefile.PL -V X.Y.Z to specify a your client version.

Now if I had to specify that manually is that the sqlplus version?? How is it 
determining that version?? We have 12.4 installed.

Thanks,
Carl Furst

Re: DBD::Oracle installation

[Furst, Carl]

Thanks! We got it going with an oracle client rpm, sqlplus and demo.mk
probably from SDK packages. Seems to be working.

However, I'm wondering why this information is not in the INSTALL file at
least? Would be a good hint so people know what they would need to get
going.

Thanks,
Carl Furst





On 12/5/13 6:57 PM, Bruce Johnson john...@pharmacy.arizona.edu wrote:


On Dec 5, 2013, at 4:06 PM, Furst, Carl carl.fu...@mlb.com wrote:

 What libs from oracle are exactly required to install this..
 
 We just installed the client libs, including sql plus on Centos and it
seems that Makefile.pl is having trouble finding the version:
 
 WARNING: I could not determine Oracle client version so I'll just
 default to version 8.0.0.0. Some features of DBD::Oracle may not work.
 Oracle version based logic in Makefile.PL may produce erroneous results.
 You can use perl Makefile.PL -V X.Y.Z to specify a your client
version.
 
 Now if I had to specify that manually is that the sqlplus version?? How
is it determining that version?? We have 12.4 installed.
 
 Thanks,
 Carl Furst

If you’re using the Instant Client, you need to install the Basic Client
and the SDK packages; most likely it’s breaking because of the missing
SDK stuff or your environment variables aren’t set. These cause 99% of
the problems I’ve ever had getting DBD::Oracle running.


http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

Then set up the $ORACLE_HOME, $TNS_ADMIN and $LD_LIBRARY_PATH environment
variables:


ORACLE_HOME=/usr/lib/oracle/11.2/client64
TNS_ADMIN=/usr/lib/oracle/11.2/client64/admin
LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib

This is on a RHEL server, but it’s in the same place on a CentOS server.

-- 
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Re: Error appears in web log but script runs ok on the command line

[Furst, Carl]

So did you build DBD::Oracle with that LD_LIBRARY_PATH?
And against that install?

Maybe Oracle.so is using LD_LIBRARY_PATH set at build time instead of
runtime?



 
Carl Furst





On 10/21/13 4:03 PM, Bruce Johnson john...@pharmacy.arizona.edu wrote:


On Oct 20, 2013, at 1:03 PM, Bruce Johnson john...@pharmacy.arizona.edu
wrote:

 Oh it turned out to be even simpler than that, and for once it wasn't
MY fat fingers that fat fingered it this time :-)

Cue the sad trombone, because I was wrong, again.

It turns out the issue seems to have something to do with mod_perl.

It works find from the command line, it works fine when the script is
executed as a classic CGI script, it fails when run as a mod_perl handler
with an error that indicates that LD_LIBRARY_PATH is wrong or missing.

I can recreate the error in CGI mode by commenting out the server
directive 'SetEnv LD_LIBRARY_PATH /usr/lib/oracle/11.2/client64/lib'

When I found the errors in the conf file, I didn't know that my co-worker
had tried that because he'd read that in some web page somewhere and he
was starting to get desperate, because nothing was working.

I have, however managed to isolate the problem, and it's got to do with
mod_perl 

I'm chasing this on the mod_perl list, but without much luck and am
hoping if anyone else has run into this issue using mod_perl and
DBD::Oracle.

We've set a directory to be handled by mod_perl as follows:

Alias /card_access /home/allwebfiles/perl/catcard
Directory /home/allwebfiles/perl/catcard
SetHandler perl-script
PerlResponseHandler ModPerl::Registry
PerlOptions +ParseHeaders
Options +ExecCGI
PerlSetEnv LD_LIBRARY_PATH /usr/lib/oracle/11.2/client64/lib
PerlSetEnv ORACLE_HOME /usr/lib/oracle/11.2/client64
/Directory

With those directives in place, I get the error every time. (I've added
ORACLE_SID and TNS_ADMIN to the PerlSetEnv declarations, but it makes no
difference.

LD_LIBRARY_PATH is set, this is the contents of %ENV and %INC according
to the server, with that mod_perl directive in place:

DOCUMENT_ROOT -- /home/allwebfiles/static
GATEWAY_INTERFACE -- CGI/1.1
HTTPS -- on
HTTP_ACCEPT -- 
text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
HTTP_ACCEPT_ENCODING -- gzip,deflate,sdch
HTTP_ACCEPT_LANGUAGE -- en-US,en;q=0.8
HTTP_CONNECTION -- keep-alive
HTTP_COOKIE -- __qca=P0-1946018635-1381167733063;
SESS360e9fa4a6458358b044501f2b5b21b9=4862153113fc157562a1fc7691eecb36;
__utma=219252696.1214622818.1382045504.1382045504.1382045504.1;
__utmz=219252696.1382045504.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(no
ne); CASAUTHOK=1; COPInt=ghZxjY9mJ74QPBgWE13I8cGha
HTTP_HOST -- x.pharmacy.arizona.edu
HTTP_USER_AGENT -- Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_5)
AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36
LD_LIBRARY_PATH -- /usr/lib/oracle/11.2/client64/lib
MOD_PERL -- mod_perl/2.0.4
MOD_PERL_API_VERSION -- 2
ORACLE_HOME -- /usr/lib/oracle/11.2/client64
PATH -- /sbin:/usr/sbin:/bin:/usr/bin
QUERY_STRING -- 
REMOTE_ADDR -- 128.196.45.237
REMOTE_PORT -- 49295
REQUEST_METHOD -- GET
REQUEST_URI -- /card_access/envvars.pl
SCRIPT_FILENAME -- /home/allwebfiles/perl/catcard/envvars.pl
SCRIPT_NAME -- /card_access/envvars.pl
SERVER_ADDR -- 150.135.124.49
SERVER_ADMIN -- root@localhost
SERVER_NAME -- x.pharmacy.arizona.edu
SERVER_PORT -- 443
SERVER_PROTOCOL -- HTTP/1.1
SERVER_SIGNATURE --
Apache/2.2.15 (Red Hat) Server at x.pharmacy.arizona.edu Port 443

SERVER_SOFTWARE -- Apache/2.2.15 (Red Hat)
SSL_TLS_SNI -- .pharmacy.arizona.edu
INC 
INC- /usr/local/lib64/perl5
INC- /usr/local/share/perl5
INC- /usr/lib64/perl5/vendor_perl
INC- /usr/share/perl5/vendor_perl
INC- /usr/lib64/perl5
INC- /usr/share/perl5
INC- .
INC- /etc/httpd

But attempting to create a database handle results in:

[Mon Oct 21 10:10:37 2013] [error] install_driver(Oracle) failed: Can't
load '/usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so' for module
DBD::Oracle: libocci.so.11.1: cannot open shared object file: No such
file or directory at /usr/lib64/perl5/DynaLoader.pm line 200.\n at (eval
11) line 3\nCompilation failed in require at (eval 11) line 3.\nPerhaps a
required shared library or dll isn't installed where expected\n at
/home/allwebfiles/perl/catcard/oratest.pl line 9\n

EVEN THOUGH libocci.so.11.1 is right where it's supposed to be, in
$LD_LIBRARY_PATH.

# cd $LD_LIBRARY_PATH
# pwd
/usr/lib/oracle/11.2/client64/lib
# ls -al
total 185024
drwxr-xr-x 2 root root  4096 May 20 12:07 .
drwxr-xr-x 5 root root  4096 May 20 14:12 ..
-rw-r--r-- 1 root root   368 Sep 17  2011 glogin.sql
lrwxrwxrwx 1 root root17 May 20 12:07 libclntsh.so -
libclntsh.so.11.1
-rw-r--r-- 1 root root  52761218 Sep 17  2011 libclntsh.so.11.1
-rw-r--r-- 1 root root   7955322 Sep 17  2011 libnnz11.so
lrwxrwxrwx 1 root root15 May 20 12:07 libocci.so -
libocci.so.11.1
-rw-r--r-- 1 root root   1971762 Sep 17  2011 libocci.so.11.1
-rw-r--r-- 1 root root 

Re: DBD::Oracle Schema different than User question

[Furst, Carl]

Switching schemas in Oracle makes no sense, because schemas are tied to
the user. In fact you don't login to oracle with a username but with a
schema name. Schema and username are the same things. It is not like MySQL
where you have Databases that denote these object groups and are separate
from users. 

However, this is not something that is confined. If you track which
schemas you want to access, and have the proper grants issued.. You can
always access any object with the following notion:

schema_name.object_name

This is irregardless of which schema you are logged into. However, the
proper grants need to be issued and can only be issued if you are logged
into the schema that owns those objects.

For example.. 

Table A is in Schema X and Table B is in schema Y.. X wants to access B
and Y wants to access A..

So you have to login as Y and

GRANT SELECT,UPDATE,INSERT,DELETE ON B TO X;

Then login as X and

GRANT SELECT,UPDATE,INSERT,DELETE ON A TO Y;

Then if you are logged in as X you can do

SELECT * FROM Y.B;

And logged in as Y

SELECT * FROM X.A;

Hope that helps,
Carl Furst






On 11/12/12 3:32 PM, John Scoles byter...@hotmail.com wrote:




 Date: Mon, 12 Nov 2012 19:04:56 +
 From: martin.ev...@easysoft.com
 To: dbi-users@perl.org
 CC: kevin.k...@gmail.com
 Subject: Fwd: DBD::Oracle Schema different than User question

 Hi Kevin,

 I've forwarded your email on to the dbi-users list. See
 http://dbi.perl.org and look at the support page. Sorry for top posting
 but my email client is having some sort of fit with your email. I don't
 have any issue with any well formed patch to set the schema but I'll
 wait to see what others say as personally I've never had to change it.

 Martin


  Original Message 
 Subject: DBD::Oracle Schema different than User question
 Date: Mon, 12 Nov 2012 13:04:05 -0500
 From: Kevin L. Kane kevin.k...@gmail.com
 To: Tim Bunce t...@cpan.org, Yanick Champoux yan...@cpan.org,
 Martin J. Evans mjev...@cpan.org

 Hi all,
 I am running into a problem and was planning on modifying my local
 DBD::Oracle to add support for a schema=mySchema construct in the
 connect string. Specifically, I want to connect as user X but set
 current_schema to Y.

Sounds like someone tried to creata a MySQL type DB schema on Oracle
again;)

Anyway if you want to log in wiht 1 generic user then change the 'schema'
 just issue
an sql something like this

exec sql execute immediate  ALTER SESSION SET CURRENT_SCHEM=sss



 Another solution i've toyed with is having a
 trigger that switches my schema when I log in but I need to do this
 for a lot of different schemas and I will always be the same user. It
 seems weird to me that support for this isn't included in DBD::Oracle
 currently.

 If I do this in a sane way are you at all interested in the patch?

IF if you come up with a patch we will have a look at it.

 Am I just missing something and this functionality is already there? Or
 should this functionality not exist in the first place and why?


I don't think it should be there because what oracle thinks a schema is
different thatn other DB??
A schema to an Oracle DB is the set of all tables and other objects owned
by a user account if I am not mistaken
not the grouping of all the table under an app or alike

Anyway off the top of head I do not think there is a way with OCI to
change the shcema without loging in again as there
 is only OCISession begin OCIlogin2 neither take any shcema params???


Cheers
John
 Thanks,
 Kevin
 --
 Kevin L. Kane
 kevin.kane at gmail.com












**

MLB.com: Where Baseball is Always On

Re: What is the oldest community supported versions for PERL DBI and DBD::ORACLE?

[Furst, Carl]

I'm sorry I should clarify.. When I say work I mean you can build it
against that version of the OCI.

What you should do is see what the highest version of the DBD::Oracle can
build against your OCI client.


Carl Furst





On 1/23/12 10:32 AM, Furst, Carl carl.fu...@mlb.com wrote:

If I remember what the docs told me (we recently went through an upgrade
from 9i to 10g), any thing pre 1.26 can work with anything up to 9i. 1.26
and later can work 9i and 10g however 11g and 12 will not work unless you
upgrade further. 

I THINK I have that right.. However the DBD::Oracle documentation is where
I got all this so, have a look.

Thanks,
Carl Furst
CMS Developer
carl.fu...@mlb.com
AIM: furstmlb
work: 212-485-4502
cell: 917-837-4558




On 1/23/12 10:20 AM, John Scoles byter...@hotmail.com wrote:


I would have to agree with Martin.  The bigest jump, code-wise was the
jump between 16~17 so you are very much out of luck if you want to get
anything fixed in 16 or eariler. I you update to 17 first and see what
happens you will get a good idea if you can update any futher.  I also
depends on the version of oracle DB and client you are using.
CheersJohn 
  Date: Mon, 23 Jan 2012 15:14:24 +
 From: martin.ev...@easysoft.com
 To: dbi-users@perl.org
 Subject: Re: What is the oldest community supported versions for PERL
DBI and DBD::ORACLE?
 
 On 23/01/12 14:13, TIDWELL, GARY wrote:
  Hi all, we are currently using DBD::ORACLE 1.16 and DBI 1.46, and I'm
having a tough time
  trying to determine if either of these are still supported by the
open source community.
 
  All help is appreciated.
 
  Thanks,
  Gary
 
 
 
 
 DBI 1.46 isn't even on cpan now - it has been moved to backpan. It was
released 16 Nov 2004 and has been superseded by 19 releases on CPAN.
 
 DBD::Oracle 1.16 was released 22 Oct 2004 and has been superseded by 23
releases.
 
 So you are using 7 year code both of which has moved on a lot since
then.
 
 I help out with both of those modules and if you had a problem with
either of them that old my response would likely be to upgrade. However,
it depends on what you mean by support - what do you mean?
 
 Presumably because you are using really old perl modules either a) the
code was written ages ago or b) you need to use a really old version of
Perl newer DBI and DBD::Oracle modules no longer support? If it is (a)
then so long as you don't change anything why would you need support? If
it is (b) and you seriously cannot move to a newer Perl you've got the
same problem with Perl itself.
 
 With DBD::Oracle, the latest version is so different from the one you
are using (code-wise) I doubt even if you found a bug which was still
present in the newest release you'd persuade anyone to also fix it in
1.16.
 
 Perhaps you need to explain why you've asked the question.
 
 Martin
 -- 
 Martin J. Evans
 Easysoft Limited
 http://www.easysoft.com








**

MLB.com: Where Baseball is Always On

RE: DB2-DBD error messages

[Furst, Carl]

It could be that there is a POSIX issue with fastcwd() on your machine. 

Does your machine support getcwd(3)? and is it the POSIX version?

It could be that the dangerous portion of the description below is in play
here and that fastcwd() is trying to muck around with chdir(). You could
easily dismiss that, but you don't know how many levels of eval{} or eval()
exist between you and the message you see. So can you be sure if you are
seeing the error as it happened?

HTH,

Carl Furst

-Original Message-
From: Peter J. Holzer [mailto:h...@wsr.ac.at] 
Sent: Friday, March 18, 2011 7:28 AM
To: dbi-users@perl.org
Subject: Re: DB2-DBD error messages

On 2011-03-17 23:47:13 -0700, Jonathan Leffler wrote:
 On Thu, Mar 17, 2011 at 04:03,
computacenter.geisselbre...@daimler.comwrote:
 
  do anyone has experience with such messages when i try to install
DB2-DBD
  driver  ? It happens after perl Makefile.PL command was executed.
 
  zwtxbt05@videv263perl Makefile.PL
 
  Usage: Cwd::fastcwd() at
  /usr/opt/perl5/lib/5.8.8/aix-thread-multi/DynaLoader.pm line 253.
  BEGIN failed--compilation aborted at
  /usr/opt/perl5/lib/site_perl/5.8.8/aix-thread-multi/DBI.pm line 268.
  Compilation failed in require at Makefile.PL line 13.
  BEGIN failed--compilation aborted at Makefile.PL line 13.
 
  DB2_Home was exported to /home/zwtxbt05/sqllib
 
 
 
 I've not seen that message, but it appears that something tried to use the
 function 'fastcwd()' from a package 'Cwd' that is not available with your
 Perl.  You appear to be using Perl 5.8.8.  The module is part of the Perl
 core (and fastcwd() is part of the module) according to
 http://perldoc.perl.org/Cwd.html.  However, was it in 5.8.8?

% corelist Cwd

Cwd was first released with perl 5

Cwd::fastcwd has also existed for a long time and I see no indication in
the docs that it may not be available on some platforms.

But the error message is Usage: Cwd::fastcwd() so I guess that the
function is available but called incorrectly (probably with a parameter
although it doesn't expect one). 

hp

-- 
   _  | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR   | eingeprägt stehen: Ein ewig Rätsel will ich
| |   | h...@wsr.ac.at  | bleiben, mir und andern.
__/   | http://www.hjp.at/ |-- Wolfram Heinrich in desd


smime.p7s
Description: S/MIME cryptographic signature





**

MLB.com: Where Baseball is Always On

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

[Furst, Carl]

Then, I guess, the text was not utf8, you set the flag, but did you actually
convert the text to a UTF-8 format, perhaps an explicit encode('utf8',
$text) call?

Just a thought.

Also some more food for thought about utf8 and UTF-8

http://search.cpan.org/~dankogai/Encode-2.42/Encode.pm#UTF-8_vs._utf8_vs._UT
F8

Thanks,

Carl Furst
o/~ What a difference a byte makes... o/~

-Original Message-
From: Bobak, Mark [mailto:mark.bo...@proquest.com] 
Sent: Friday, February 18, 2011 11:56 AM
To: Nelson, Erick [HDS]; dbi-users@perl.org
Subject: RE: Problem with UTF8 and array binding

Thanks for the suggestion, Erick.

In my case, I already had NLS_LANG set to AMERICAN_AMERICA.AL32UTF8 (which
is my db character set).

I didn't have anything set for ORA_NLS, but I tried setting it to
$ORACLE_HOME/nls/data (which is my guess as to the 11.2 equivalent of the
directory you had specified for 9.2).

It didn't make any difference.

On another note, I've discovered that if we set the UTF8 flag on the Perl
string, it gives me the PLS-418 error.  If we do not set the UTF8 flag, it
works fine, data does not get munged, and we are good.

But, my developers are telling me that's wrong, since the strings *are*, in
fact, UTF8, they *should* have the UTF8 flag set.

So, I have a workaround, albeit one that makes my developers uncomfortable.

-Mark

-Original Message-
From: Nelson, Erick [HDS] [mailto:erick.nel...@hdsupply.com] 
Sent: Thursday, February 17, 2011 5:19 PM
To: dbi-users@perl.org
Subject: RE: Problem with UTF8 and array binding

I had this problem quite awhile ago with an old Oracle 9.2 database.
I don't remember why but the solution involved setting a couple of
environment vars.

Looking back at those ancient, no longer used, problems I see

$ENV{NLS_LANG} = american_america.we8iso8859p1;
$ENV{ORA_NLS}  = $ENV{ORACLE_HOME}/ocommon/nls/admin/data;



-Original Message-
From: John Scoles [mailto:sco...@pythian.com] 
Sent: Thursday, February 17, 2011 1: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',

RE: One of us can't count....

[Furst, Carl]

Are you calling $csr-finish at the end of each iteration? Might not be the
issue but indeed good practice.

Carl Furst
o/~ What a difference a byte makes... o/~

-Original Message-
From: Bruce Johnson [mailto:john...@pharmacy.arizona.edu] 
Sent: Friday, February 18, 2011 3:07 PM
Cc: DBI Users List (dbi-users@perl.org)
Subject: Re: One of us can't count

No, this is done as:

$csr=$dbh-prepare($sql)

then as $csr-execute(@parms) inside of a loop.

On Feb 18, 2011, at 12:33 PM, Bill Ward wrote:

 You're probably calling do($sql, @args) when you should call do($sql,
undef,
 @args)
 
 On Fri, Feb 18, 2011 at 11:31 AM, Bruce Johnson 
 john...@pharmacy.arizona.edu wrote:
 
 I'm getting the following error: (some data has been change to protect
the
 bystanders.)
 
 DBD::Oracle::st execute failed: called with 18 bind variables when 19 are
 needed [for Statement insert into edsbase

(cn,emplId,employeeType,employeeTitle,employeeStatus,employeeTotalAnnualRate
,sn,givenName,netid,mail,employeeBldgName,employeeBldgNum,employeeCity,emplo
yeeFTE,employeePhone,employeePoBox,employeeRoomNum,employeeState,employeeZip
)
 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
  with ParamValues: :p1='Werner S Noname', :p10='some@here', :p11='COLL
OF
 PHARMAC', :p12='00207', :p13='TUCSON', :p14='0', :p15='5205114411',
:p16='PO
 BOX 210207', :p17='344', :p18='AZ', :p19='85721-0207', :p2='1',
 :p3='N', :p4='nosal', :p5='A', :p6='0', :p7='Zimmt', :p8='Werner S',
 :p9='wsz'] at ./edsload.pl line 97, DATA line 532.
 
 I've counted three times: I have 19 fields in the db, 19 '?'s, and 19
param
 values being passed, why am I getting this error?
 
 
 
 --
 Bruce Johnson
 University of Arizona
 College of Pharmacy
 Information Technology Group
 
 Institutions do not have opinions, merely customs
 
 
 
 
 
 -- 
 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

-- 
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs




smime.p7s
Description: S/MIME cryptographic signature





**

MLB.com: Where Baseball is Always On

RE: One of us can't count....

[Furst, Carl]

OK, scratch my last this indeed has changed... 
http://search.cpan.org/~timb/DBI-1.616/DBI.pm#finish

Carl Furst
o/~ What a difference a byte makes... o/~

-Original Message-
From: Bruce Johnson [mailto:john...@pharmacy.arizona.edu] 
Sent: Friday, February 18, 2011 3:07 PM
Cc: DBI Users List (dbi-users@perl.org)
Subject: Re: One of us can't count

No, this is done as:

$csr=$dbh-prepare($sql)

then as $csr-execute(@parms) inside of a loop.

On Feb 18, 2011, at 12:33 PM, Bill Ward wrote:

 You're probably calling do($sql, @args) when you should call do($sql,
undef,
 @args)
 
 On Fri, Feb 18, 2011 at 11:31 AM, Bruce Johnson 
 john...@pharmacy.arizona.edu wrote:
 
 I'm getting the following error: (some data has been change to protect
the
 bystanders.)
 
 DBD::Oracle::st execute failed: called with 18 bind variables when 19 are
 needed [for Statement insert into edsbase

(cn,emplId,employeeType,employeeTitle,employeeStatus,employeeTotalAnnualRate
,sn,givenName,netid,mail,employeeBldgName,employeeBldgNum,employeeCity,emplo
yeeFTE,employeePhone,employeePoBox,employeeRoomNum,employeeState,employeeZip
)
 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
  with ParamValues: :p1='Werner S Noname', :p10='some@here', :p11='COLL
OF
 PHARMAC', :p12='00207', :p13='TUCSON', :p14='0', :p15='5205114411',
:p16='PO
 BOX 210207', :p17='344', :p18='AZ', :p19='85721-0207', :p2='1',
 :p3='N', :p4='nosal', :p5='A', :p6='0', :p7='Zimmt', :p8='Werner S',
 :p9='wsz'] at ./edsload.pl line 97, DATA line 532.
 
 I've counted three times: I have 19 fields in the db, 19 '?'s, and 19
param
 values being passed, why am I getting this error?
 
 
 
 --
 Bruce Johnson
 University of Arizona
 College of Pharmacy
 Information Technology Group
 
 Institutions do not have opinions, merely customs
 
 
 
 
 
 -- 
 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

-- 
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs




smime.p7s
Description: S/MIME cryptographic signature





**

MLB.com: Where Baseball is Always On

RE: DBD::Oracle 'make test' fails - ORA-24327: need explicit attach before authenticating a user

[Furst, Carl]

I'm wondering if just using the username@SIDSTRING would have worked without
TWO_TASK being set? It did for me on Solaris Sparc. If TNS_ADMIN or
ORACLE_HOME is set correctly, it should lookup SIDSTRING in tnsnames.ora for
connection info.



Carl Furst
-Original Message-
From: Alexander Saip [mailto:as...@msn.com] 
Sent: Wednesday, February 02, 2011 9:45 AM
To: martin.ev...@easysoft.com
Cc: dbi-users@perl.org
Subject: Re: DBD::Oracle 'make test' fails - ORA-24327: need explicit attach
before authenticating a user


Martin,

setting the TWO_TASK environment variable did the trick, thanks for your
advice. For some strange reason, the EZconnect format didn't work, so I had
to specify the fully qualified connection string, but that got me through.
  


smime.p7s
Description: S/MIME cryptographic signature





**

MLB.com: Where Baseball is Always On

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

[Furst, Carl]

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.

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



smime.p7s
Description: S/MIME cryptographic signature





**

MLB.com: Where Baseball is Always On

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

[Furst, Carl]

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

Yes, that's correct we have ours set to about 500k

It looks like the solution John suggested worked. By taking out the ORA_CLOB
attributes and issuing a 

GRANT SELECT_CATALOG_ROLE TO user;

we are now inserting editing and reading CLOBS correctly.

Thanks everyone for the help!


Carl Furst
CMS Developer
MLB Advanced Media

-Original Message-
From: John Scoles [mailto:sco...@pythian.com] 
Sent: Tuesday, December 21, 2010 11:13 AM
To: dbi-users@perl.org
Subject: Re: DBD::Oracle, 10g Lob Refetch problem

  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



smime.p7s
Description

DBD::Oracle, 10g Lob Refetch problem

[Furst, Carl]

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)

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. 

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?
2) Has anyone else experienced this; building again lib32 client libs.
3) What role does oraperl have in all this? If oraperl fails to compile, is
that a blocker for DBD?

Thanks in advance,

Carl Furst
CMS Developer
MLB Advanced Media


smime.p7s
Description: S/MIME cryptographic signature





**

MLB.com: Where Baseball is Always On

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

[Furst, Carl]

Hey Michael,

Thanks for the reply!

So, I'd like to update this after further investigation. It would seem that
this is related to binding params and the ORA_CLOB value.

When I execute an insert passing attributes:

{ora_type = ORA_CLOB} it fails with the Lob Refetch error. However, if I
remove this, it works fine. 

I guess, yes, it would be time to update the perl... However, the code at
its oldest is also 2004 so, I'm worried about backward compatibility issues.
It's a rabbit hole I don't have time to go down. It would require
recompiling all the other libs we use under the new perl plus mod_perl plus
apache.. Just for one module??? 

In any case, The documentation says that to update multiple and or large
CLOB fields I need to pass these parameters, however how large is large? I
haven't even delved into the update part of the equation.

Thanks,
Carl.

Carl Furst
CMS Developer
MLB Advanced Media
212-485-4502 (W)
917-837-4558(M)
(505) MLB-CMS-5(CMS General Phone)
aim: furstmlb
o/~ What a difference a byte makes... o/~

-Original Message-
From: Michael Ludwig [mailto:mil...@gmx.de] 
Sent: Monday, December 20, 2010 6:25 PM
To: dbi-users@perl.org
Subject: Re: DBD::Oracle, 10g Lob Refetch problem

Moin Carl,

Furst, Carl schrieb am 20.12.2010 um 15:17 (-0500):
 
 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 

Perl 5.8.5 is from mid-2004, so six and a half years old. Time to
upgrade to 5.8.9. (And then on to 5.10.1, and 5.12.2, if you want.
If you deal with Unicode, the newer, the better.)

   DBD::Oracle::st execute failed: ORA-00903: invalid table name (DBD
 ERROR: OCIStmtExecute/LOB refetch)
 
 We think it's the LOB refetch that's causing the issue. 

But it says invalid table name. What does your SQL look like?

 1) do we need an actual Oracle server to build the DBD

No.

 if so what libs would we need to link against?

You need the Oracle client lib. The easiest should be to pick the Oracle
Instant Client, which you can find by doing a web search. The Basic or
even Basic Lite should do for your purposes. SQL*Plus is offered, too,
so I'd install that as well.

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

Experienced what exactly?

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

It's all in the docs, straight at the top:

  Oraperl is an extension to Perl which allows access to Oracle
  databases.

  The original oraperl was a Perl 4 binary with Oracle OCI
  compiled into it. The Perl 5 Oraperl module described here is
  distributed with DBD::Oracle (a database driver what operates
  within DBI) and adds an extra layer over DBI method calls. The
  Oraperl module should only be used to allow existing Perl 4
  oraperl scripts to run with minimal changes; any new development
  should use DBI directly.

http://search.cpan.org/~timb/DBD-Oracle-1.16/Oraperl.pm

Hope this helps!

Best,
-- 
Michael Ludwig


smime.p7s
Description: S/MIME cryptographic signature





**

MLB.com: Where Baseball is Always On