RE: EXT :Re: Trying to use perl to call oracle stored proc to return an object.
Thanks for the reply Robert.
PROCEDURE FetchTrackObjbyID ( p_ID IN TRACK.ID%TYPE, p_TARGET OUT TARGET )
PROCEDURE is defined with an IN and OUT param. May have also tried it with an
in out vs simply an out param and this made no difference.
$sth->bind_param_inout(":p_Target",\$p_Target, 20) or die "bind(target) err is
$DBI::errstr"; # Should I specify a bind type and if so, what type?
$sth->bind_param(":p_ID",$i) or die "bind(ID) err is $DBI::errstr\n";
Do a bind_param_inout with the OUT and a bind_param with the IN param.
E. Scott Stricker
703-561-3671
-Original Message-
From: Robert Durgin [mailto:rdur...@texterity.com]
Sent: Wednesday, May 02, 2012 12:19 PM
To: Stricker, Scott (IS)
Cc: dbi-users@perl.org
Subject: EXT :Re: Trying to use perl to call oracle stored proc to return an
object.
Did you specify the direction for each parameter?
- Original Message -
From: "Scott Stricker (IS)"
To: dbi-users@perl.org
Sent: Wednesday, May 2, 2012 12:06:05 PM
Subject: Trying to use perl to call oracle stored proc to return an object.
All:
I'm trying to use perl to invoke a stored procedure to return an object.
Oracle Procedure is defined as
PROCEDURE FetchTrackObjbyID ( p_ID IN TRACK.ID%TYPE , p_TARGET OUT TARGET )
Where p_ID is numeric and p_TARGET is the TARGET object type.
When I 'execute', I get
DBD::Oracle::st execute failed: ORA-06550: line 2, column 5:
PLS-00306: wrong number or types of arguments in call to 'FETCHTRACKOBJBYID'
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicator at char
10 in 'BEGIN
<*>FetchTrackObjbyID(:p_id, :p_target); END;
') [for Statement "BEGIN
FetchTrackObjbyID(:p_ID, :p_Target); END; " with ParamValues: :p_id=1,
:p_target=undef] at
So far, I've got
my $i = 5;
my $p_Target; # should this be either @p_Target or %p_Target
my $sqlstmt= <<"END_SQL";
BEGIN
FetchTrackObjbyID(:p_ID, :p_Target); END; END_SQL
my $sth = $dbh->prepare($sqlstmt) or die "Can't prepare
statement\n$sqlstmt\n$DBI::errstr\n";
$sth->bind_param_inout(":p_Target",\$p_Target, 20) or die "bind(target)
err is $DBI::errstr"; # Should I specify a bind type and if so, what type?
$sth->bind_param(":p_ID",$i) or die "bind(ID) err is $DBI::errstr\n";
$sth->execute();
Hoping it is something really simple. Thanks for any help
E. Scott Stricker @ Work
--
Robert Durgin
Sr. Software Engineer
Texterity • 144 Turnpike Road
Southborough , MA 01772
rdur...@texterity.com
www.texterity.com
Trying to use perl to call oracle stored proc to return an object.
All:
I'm trying to use perl to invoke a stored procedure to return an object.
Oracle Procedure is defined as
PROCEDURE FetchTrackObjbyID ( p_ID IN TRACK.ID%TYPE , p_TARGET OUT TARGET )
Where p_ID is numeric and p_TARGET is the TARGET object type.
When I 'execute', I get
DBD::Oracle::st execute failed: ORA-06550: line 2, column 5:
PLS-00306: wrong number or types of arguments in call to 'FETCHTRACKOBJBYID'
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicator at char
10 in 'BEGIN
<*>FetchTrackObjbyID(:p_id, :p_target);
END;
') [for Statement "BEGIN
FetchTrackObjbyID(:p_ID, :p_Target);
END;
" with ParamValues: :p_id=1, :p_target=undef] at
So far, I've got
my $i = 5;
my $p_Target; # should this be either @p_Target or %p_Target
my $sqlstmt= <<"END_SQL";
BEGIN
FetchTrackObjbyID(:p_ID, :p_Target);
END;
END_SQL
my $sth = $dbh->prepare($sqlstmt) or die "Can't prepare
statement\n$sqlstmt\n$DBI::errstr\n";
$sth->bind_param_inout(":p_Target",\$p_Target, 20) or die "bind(target)
err is $DBI::errstr"; # Should I specify a bind type and if so, what type?
$sth->bind_param(":p_ID",$i) or die "bind(ID) err is $DBI::errstr\n";
$sth->execute();
Hoping it is something really simple. Thanks for any help
E. Scott Stricker @ Work
RE: Having a problem conneting to an Oracle 11gr2 RAC
Found the issue.
Had been connecting as
my $dbh = DBI->connect("dbi:Oracle:host=edc-dbserver;sid=HCSMDA",
"ais_dbo", "PASSWORD",...
and getting the error. Changed it to
my $dbh = DBI->connect('dbi:Oracle:',
'ais_...@edc-dbserver/HCSMDA','PASSWORD',...
and all is well.
Thanks for the help.
E. Scott Stricker
Senior Software Engineer
Northrop Grumman - Information Systems - C2SD
MailStop 4S02
2340 Dulles Corner Blvd.
Herndon, VA 20171
703-561-3671
703-713-1601 (fax)
-Original Message-
From: Stricker, Scott (IS) [mailto:scott.stric...@ngc.com]
Sent: Friday, April 30, 2010 8:49 AM
To: John Scoles; dbi-users@perl.org
Subject: RE: Having a problem conneting to an Oracle 11gr2 RAC
Thanks for the info John. Agreed that it is probably an issue with the
connect string. More details on the environment
The oracle RAC environment is 64bit 11gR2 running on windows server
2003.
The client is currently a 32 bit 10.2 oracle client running on a
different 64 bit windows server 2003 box. There is no TNSNAMES.ORA file
on this box. Also, from this box, I can connect to a standalone 64 bit
11gR1 oracle server running on another box.
Host environment
XYZ-3 - 64bit windows 2003 server client box running 32 bit 10.2 oracle
client and activestate perl with DBI 1.609 and DBD-Oracle 1.21
XYZ-1 - 64bit windows 2003 server running 64bit standalone 11gR1 oracle
(host alias dbserver)
XYZ-2 & XYZ-10 64bit windows 2003 servers running 64bit 11gR2 oracle RAC
(host alias edc-dbserver)
Can connect with perl to XYZ-1 (dbserver) box
Error trying to connect with perl to XYZ-2 & XYZ-10 (edc-dbserver) box
E. Scott Stricker
Senior Software Engineer
Northrop Grumman - Information Systems - C2SD
MailStop 4S02
2340 Dulles Corner Blvd.
Herndon, VA 20171
703-561-3671
703-713-1601 (fax)
From: John Scoles [mailto:byter...@hotmail.com]
Sent: Thursday, April 29, 2010 6:11 PM
To: Stricker, Scott (IS); dbi-users@perl.org
Subject: RE: Having a problem conneting to an Oracle 11gr2 RAC
It is somthing in you connection string or you do not have the
TNS_ADMIN env car set
TNS_ADMIN should point to your TNSNAMES.ORA file
> Subject: Having a problem conneting to an Oracle 11gr2 RAC
> Date: Thu, 29 Apr 2010 15:03:18 -0500
> From: scott.stric...@ngc.com
> To: dbi-users@perl.org
>
> Don't know if anyone has worked with the Oracle RAC environment and
> perl.
>
> Using sqlplus, I can do the following
>
> C:\>sqlplus ais_dbo/passw...@edc-dbserver/HCSMDA
>
> SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 29 11:55:35 2010
>
> Copyright (c) 1982, 2005, Oracle. All rights reserved.
>
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
> Production
> With the Partitioning, Real Application Clusters, Automatic Storage
> Management, OLAP,
> Data Mining and Real Application Testing options
>
> And then execute queries, updates ...
>
> From ActiveState perl running on a windows 2003 server box with
> DBD-Oracle 1.21 and DBI 1.609
>
> C:\>perl -v
>
> This is perl, v5.8.9 built for MSWin32-x86-multi-thread (with 9
> registered patches, see perl -V for more detail)
>
> Copyright 1987-2008, Larry Wall
>
> Binary build 825 [288577] provided by ActiveState
> http://www.ActiveState.com
> Built Dec 14 2008 21:07:41
>
> Perl code follows
>
> #!/usr/bin/perl -I .
> require 5.001;
> use strict;
> use DBI;
>
> MAIN:
> {
> print "Started\n";
>
> my $dbh = DBI->connect("dbi:Oracle:host=edc-dbserver;sid=HCSMDA",
> "ais_dbo","PASSWORD",
> {RaiseError=>1, PrintError=>0});
> my $sqlstmt = "SELECT COUNT(*) FROM VESSEL_IDENTIFIERS";
> my($no_of_ships) = $dbh->selectrow_array($sqlstmt);
> print "Got $no_of_ships ships\n"
> }
>
> Executing the above, I get
>
> Started
> DBI connect('host=edc-dbserver;sid=HCSMDA','ais_dbo',...) failed:
> ORA-12505: TNS:listener does not currently know of SID given in
connect
> descriptor (DBD ERROR: OCIServerAttach) at C:\adbtest.pl line 15
>
> I've got a standalone oracle database (11gr1) pointed to by host alias
> dbserver. If I use dbserver above instead of edc-dbserver, the script
> works fine.
>
> Also, tnsping gives the following
>
> C:\>tnsping edc-dbserver
>
> TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production
on
> 29-APR-2010 12:57:46
>
> Copyright (c) 1997, 2005, Oracle. All rights reserved.
>
> Used parameter files:
> C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora
>
> Used HOSTNAME adapter to resolve the alias
> Attempting to contact
&
RE: Having a problem conneting to an Oracle 11gr2 RAC
Thanks for the info John. Agreed that it is probably an issue with the
connect string. More details on the environment
The oracle RAC environment is 64bit 11gR2 running on windows server
2003.
The client is currently a 32 bit 10.2 oracle client running on a
different 64 bit windows server 2003 box. There is no TNSNAMES.ORA file
on this box. Also, from this box, I can connect to a standalone 64 bit
11gR1 oracle server running on another box.
Host environment
XYZ-3 - 64bit windows 2003 server client box running 32 bit 10.2 oracle
client and activestate perl with DBI 1.609 and DBD-Oracle 1.21
XYZ-1 - 64bit windows 2003 server running 64bit standalone 11gR1 oracle
(host alias dbserver)
XYZ-2 & XYZ-10 64bit windows 2003 servers running 64bit 11gR2 oracle RAC
(host alias edc-dbserver)
Can connect with perl to XYZ-1 (dbserver) box
Error trying to connect with perl to XYZ-2 & XYZ-10 (edc-dbserver) box
E. Scott Stricker
Senior Software Engineer
Northrop Grumman - Information Systems - C2SD
MailStop 4S02
2340 Dulles Corner Blvd.
Herndon, VA 20171
703-561-3671
703-713-1601 (fax)
From: John Scoles [mailto:byter...@hotmail.com]
Sent: Thursday, April 29, 2010 6:11 PM
To: Stricker, Scott (IS); dbi-users@perl.org
Subject: RE: Having a problem conneting to an Oracle 11gr2 RAC
It is somthing in you connection string or you do not have the
TNS_ADMIN env car set
TNS_ADMIN should point to your TNSNAMES.ORA file
> Subject: Having a problem conneting to an Oracle 11gr2 RAC
> Date: Thu, 29 Apr 2010 15:03:18 -0500
> From: scott.stric...@ngc.com
> To: dbi-users@perl.org
>
> Don't know if anyone has worked with the Oracle RAC environment and
> perl.
>
> Using sqlplus, I can do the following
>
> C:\>sqlplus ais_dbo/passw...@edc-dbserver/HCSMDA
>
> SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 29 11:55:35 2010
>
> Copyright (c) 1982, 2005, Oracle. All rights reserved.
>
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
> Production
> With the Partitioning, Real Application Clusters, Automatic Storage
> Management, OLAP,
> Data Mining and Real Application Testing options
>
> And then execute queries, updates ...
>
> From ActiveState perl running on a windows 2003 server box with
> DBD-Oracle 1.21 and DBI 1.609
>
> C:\>perl -v
>
> This is perl, v5.8.9 built for MSWin32-x86-multi-thread (with 9
> registered patches, see perl -V for more detail)
>
> Copyright 1987-2008, Larry Wall
>
> Binary build 825 [288577] provided by ActiveState
> http://www.ActiveState.com
> Built Dec 14 2008 21:07:41
>
> Perl code follows
>
> #!/usr/bin/perl -I .
> require 5.001;
> use strict;
> use DBI;
>
> MAIN:
> {
> print "Started\n";
>
> my $dbh = DBI->connect("dbi:Oracle:host=edc-dbserver;sid=HCSMDA",
> "ais_dbo","PASSWORD",
> {RaiseError=>1, PrintError=>0});
> my $sqlstmt = "SELECT COUNT(*) FROM VESSEL_IDENTIFIERS";
> my($no_of_ships) = $dbh->selectrow_array($sqlstmt);
> print "Got $no_of_ships ships\n"
> }
>
> Executing the above, I get
>
> Started
> DBI connect('host=edc-dbserver;sid=HCSMDA','ais_dbo',...) failed:
> ORA-12505: TNS:listener does not currently know of SID given in
connect
> descriptor (DBD ERROR: OCIServerAttach) at C:\adbtest.pl line 15
>
> I've got a standalone oracle database (11gr1) pointed to by host alias
> dbserver. If I use dbserver above instead of edc-dbserver, the script
> works fine.
>
> Also, tnsping gives the following
>
> C:\>tnsping edc-dbserver
>
> TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production
on
> 29-APR-2010 12:57:46
>
> Copyright (c) 1997, 2005, Oracle. All rights reserved.
>
> Used parameter files:
> C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora
>
> Used HOSTNAME adapter to resolve the alias
> Attempting to contact
>
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=edc-dbserver.nais.org))(ADDRESS
>
=(PROTOCOL=TCP)(HOST=10.166.135.110)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(
> HOST=10.166.135.112)(PORT=1521)))
> OK (0 msec)
>
> Any help would be greatly appreciated.
>
> E. Scott Stricker
> Senior Software Engineer
> Northrop Grumman - Information Systems - C2SD
> MailStop 4S02
> 2340 Dulles Corner Blvd.
> Herndon, VA 20171
> 703-561-3671
> 703-713-1601 (fax)
>
>
Got a phone? Get Hotmail & Messenger for mobile!
<http://go.microsoft.com/?linkid=9724457>
Having a problem conneting to an Oracle 11gr2 RAC
Don't know if anyone has worked with the Oracle RAC environment and
perl.
Using sqlplus, I can do the following
C:\>sqlplus ais_dbo/passw...@edc-dbserver/HCSMDA
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 29 11:55:35 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, Real Application Clusters, Automatic Storage
Management, OLAP,
Data Mining and Real Application Testing options
And then execute queries, updates ...
>From ActiveState perl running on a windows 2003 server box with
DBD-Oracle 1.21 and DBI 1.609
C:\>perl -v
This is perl, v5.8.9 built for MSWin32-x86-multi-thread (with 9
registered patches, see perl -V for more detail)
Copyright 1987-2008, Larry Wall
Binary build 825 [288577] provided by ActiveState
http://www.ActiveState.com
Built Dec 14 2008 21:07:41
Perl code follows
#!/usr/bin/perl -I .
require 5.001;
use strict;
use DBI;
MAIN:
{
print "Started\n";
my $dbh = DBI->connect("dbi:Oracle:host=edc-dbserver;sid=HCSMDA",
"ais_dbo","PASSWORD",
{RaiseError=>1, PrintError=>0});
my $sqlstmt = "SELECT COUNT(*) FROM VESSEL_IDENTIFIERS";
my($no_of_ships) = $dbh->selectrow_array($sqlstmt);
print "Got $no_of_ships ships\n"
}
Executing the above, I get
Started
DBI connect('host=edc-dbserver;sid=HCSMDA','ais_dbo',...) failed:
ORA-12505: TNS:listener does not currently know of SID given in connect
descriptor (DBD ERROR: OCIServerAttach) at C:\adbtest.pl line 15
I've got a standalone oracle database (11gr1) pointed to by host alias
dbserver. If I use dbserver above instead of edc-dbserver, the script
works fine.
Also, tnsping gives the following
C:\>tnsping edc-dbserver
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on
29-APR-2010 12:57:46
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora
Used HOSTNAME adapter to resolve the alias
Attempting to contact
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=edc-dbserver.nais.org))(ADDRESS
=(PROTOCOL=TCP)(HOST=10.166.135.110)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(
HOST=10.166.135.112)(PORT=1521)))
OK (0 msec)
Any help would be greatly appreciated.
E. Scott Stricker
Senior Software Engineer
Northrop Grumman - Information Systems - C2SD
MailStop 4S02
2340 Dulles Corner Blvd.
Herndon, VA 20171
703-561-3671
703-713-1601 (fax)
