I think the cursor binding should be more like:
$sth->bind_param_inout(":curref", \$curref, 1, {ora_type => ORA_RSET});
from perldoc DBD::Oracle
The only special requirement is the use of `bind_param_inout()' with an
attribute hash parameter that specifies `ora_type' as `ORA_RSET'. If you
don't do that you'll get an error from the `execute()' like: "ORA-06550:
line X, column Y: PLS-00306: wrong number or types of arguments in call to
...".
-----Original Message-----
From: Rozengurtel, Daniel [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 20, 2002 7:05 AM
To: 'Fox, Michael'; '[EMAIL PROTECTED]'
Subject: RE: Stored Procs
Hello everybody,
I was wondering if I can get anybody's help about my error that I get while
running the following code:
I use DBI version 1.15 ("$Date: 2001/03/30 14:35:41 $"), Perl 5.6.1 and on
Oracle 8i.
...........
...........
...........
my $dbh = DBI->connect('dbi:Oracle:'.$DBNAME,$DBUSER,$DBPSWD,
{RaiseError => 1, AutoCommit
=> 1 }) or die "Unable to connect: $DBI::errstr";
my $sql = qq(CREATE OR REPLACE PACKAGE plsql_example
is
/* Declare cursor specifications */
TYPE IEDF_IEVP_IPDF IS REF CURSOR RETURN
JOIN_IEDF_IEVP_IPDF%ROWTYPE;
PROCEDURE OPEN_INCOME_EVENTS (
instr_id IN
FT_T_ISID.INSTR_ID%TYPE,
ie_ref_cur IN OUT
IEDF_IEVP_IPDF
);
PROCEDURE CLOSE_INCOME_EVENTS (ie_ref_cur IN
OUT IEDF_IEVP_IPDF);
END plsql_example;
);
my $rv = $dbh->do($sql);
print "The package has been created...\n";
$sql = qq(CREATE OR REPLACE PACKAGE BODY plsql_example
IS
PROCEDURE OPEN_INCOME_EVENTS (
instr_id IN
FT_T_ISID.INSTR_ID%TYPE,
ie_ref_cur IN OUT
IEDF_IEVP_IPDF
) IS
BEGIN
OPEN ie_ref_cur FOR
SELECT * FROM JOIN_IEDF_IEVP_IPDF WHERE
INSTR_ID=instr_id
ORDER BY EV_RATE_TYP, START_TMS_IPDF DESC;
END OPEN_INCOME_EVENTS;
PROCEDURE CLOSE_INCOME_EVENTS (ie_ref_cur IN IEDF_IEVP_IPDF) IS
BEGIN
CLOSE ie_ref_cur;
END CLOSE_INCOME_EVENTS;
END plsql_example;
);
$rv = $dbh->do($sql);
print "These are the results from the ref cursor:\n";
$sql = qq(
BEGIN
USER.PLSQL_EXAMPLE.OPEN_INCOME_EVENTS(:instr_id,
:curref);
END;
);
my $curref;
my $instr_id='0001A5PWAw';
my $sth = $dbh->prepare($sql);
$sth->bind_param(":instr_id", $instr_id);
$sth->bind_param_inout(":curref", \$curref, 1);
$sth->execute;
$curref->dump_results;
$sql = qq(
BEGIN
USER.PLSQL_EXAMPLE.CLOSE_INCOME_EVENTS(:curref);
END;
);
$sth = $dbh->prepare($sql);
$sth->bind_param(":curref", $curref);
$sth->execute;
$dbh->disconnect;
exit;
I get this freaky message:
DBD::Oracle::st execute failed: ORA-06550: line 3, column 4:
PLS-00306: wrong number or types of arguments in call to
'OPEN_INCOME_EVENTS'
ORA-06550: line 3, column 4:
PL/SQL: Statement ignored (DBD ERROR: OCIStmtExecute) at package_call.pl
line 116.
Thanx to all of you .
daniel
> -----Original Message-----
> From: Fox, Michael [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, August 08, 2002 7:33 PM
> To: 'Rozengurtel, Daniel'
> Subject: RE: Stored Procs
>
>
> Daniel, this code is in the Oracle.ex directory in the DBD-Oracle-1.12
> distribution
>
> With regard to moving stuff to stored procs instead of Perl/DBD, I think
> you
> have to look at it on a case by case basis.
>
> We have lots of SQL developers who write SQL and PL/SQL eg to generate
> reports. They don't want to or can't write perl. They do use temp tables
> to
> get the data they want to report when the query gets really complex. I
> get
> them to return me a cursor from the select on their last temp table, so I
> can process the output in Perl, rather than them trying to do in in
> SQLPLUS.
>
>
> If I was doing it all myself, I probably would never create a temp table,
> but suck down the data that was going to the temp tables into perl arrays
> instead, then use perl to 'join', clean/transorm and report. You
> certainly
> get performance benefits by using SQL to limit the amount of data you pull
> down, and maybe doing simple joins, but I think it ends there. And if you
> have ever tried to use arrays in PLSQL you would switch to Perl in an
> instant.
>
> <start code>
>
> #!/usr/bin/perl
> #
> # curref.pl - by Geoffrey Young
> #
> # for this example, we create a package that contains
> # two procedures:
> # emp_cursor - returns a specific cursor reference
> # ref_cursor_close - closes any cursor reference
> #
> # to actually run this example as is, you will need the
> # oracle demo tables. otherwise, it's just sample code...
>
> use DBI;
> use DBD::Oracle qw(:ora_types);
>
> use strict;
>
> # Set trace level if '-# trace_level' option is given
> DBI->trace( shift ) if 1 < @ARGV && $ARGV[0] =~ /^-#/ && shift;
>
> die "syntax: $0 [-# trace] base user pass" if 3 > @ARGV;
> my ( $inst, $user, $pass ) = @ARGV;
>
> # Connect to database
> my $dbh = DBI->connect( "dbi:Oracle:$inst", $user, $pass,
> { AutoCommit => 0, RaiseError => 1, PrintError => 0 } )
> or die $DBI::errstr;
>
> my $sql = qq(
> CREATE OR REPLACE PACKAGE curref_test
> IS
> TYPE cursor_ref IS REF CURSOR;
> PROCEDURE emp_cursor (job_in IN VARCHAR2, curref IN OUT cursor_ref);
> PROCEDURE ref_cursor_close (curref IN cursor_ref);
> END;
> );
> my $rv = $dbh->do($sql);
> print "The package has been created...\n";
>
> $sql = qq(
> CREATE OR REPLACE PACKAGE BODY curref_test
> IS
> PROCEDURE emp_cursor (job_in IN VARCHAR2, curref IN OUT cursor_ref)
> IS
> BEGIN
> OPEN curref FOR select ename, job from emp where job = job_in;
> END;
>
> PROCEDURE ref_cursor_close (curref IN cursor_ref)
> IS
> BEGIN
> close curref;
> END;
> END;
> );
> $rv = $dbh->do($sql);
> print "The package body has been created...\n";
>
> print "These are the results from the ref cursor:\n";
> $sql = qq(
> BEGIN
> curref_test.emp_cursor(:job_in, :curref);
> END;
> );
> my $curref;
> my $sth = $dbh->prepare($sql);
> $sth->bind_param(":job_in", "CLERK");
> $sth->bind_param_inout(":curref", \$curref, 0, {ora_type => ORA_RSET});
> $sth->execute;
> $curref->dump_results;
> open_cursors();
>
> $sql = qq(
> BEGIN
> curref_test.ref_cursor_close(:curref);
> END;
> );
> $sth = $dbh->prepare($sql);
> $sth->bind_param(":curref", $curref, {ora_type => ORA_RSET});
> $sth->execute;
>
> print "The cursor is now closed\n";
> print "just to prove it...\n";
> open_cursors();
>
> $dbh->disconnect;
>
> sub open_cursors {
> print "Here are the open cursors:\n";
> $sth = $dbh->prepare('select user, sql_text from V$OPEN_CURSOR');
> $sth->execute;
> $sth->dump_results;
> }
>
> <end code>
>
> -----Original Message-----
> From: Rozengurtel, Daniel [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, August 08, 2002 10:58 PM
> To: 'Fox, Michael'; '[EMAIL PROTECTED]'
> Subject: RE: Stored Procs
>
>
> Hi,
> thank you all for your help.
> Mike, I could not locate the that perl you specified. There is nothing in
> the DBD dir except for *.pm. (if its not a trouble for you could you
> please
> cut and paste it?)
> I have another question though (for anybody who have experienced in the
> following). Steve Baldwin (thanx man!) has suggested using temp tables (or
> create normal tables and then when not needed anymore ,drop them) to
> populate with a data. I believe its going to be denormolized ones (for
> fast
> retrieval) with a prime key and maybe some index.
> Now all of you gurus: do you think if I create couple of those
> tables,link
> them with a prime key and only after this is done by joining them retrieve
> all the data I need for formatting purposes will be fast performance wise?
> We have a lot of tables and a very ,very normalized Oracle database, which
> makes it very difficult to retrieve this data. I personally love perl and
> its DBD/DBI bundle and use it all over the place but everybody suggest I
> start using store-procs and functions that sit on Oracle side for
> performance reasons.
>
> Than you all for you comments and replies.
>
>
>
> > -----Original Message-----
> > From: Fox, Michael [SMTP:[EMAIL PROTECTED]]
> > Sent: Wednesday, August 07, 2002 6:37 PM
> > To: '[EMAIL PROTECTED]'
> > Subject: RE: Stored Procs
> >
> >
> > Maybe you should look at curref.pl in the Oracle.ex directory of the
> > DBD::Oracle distribution. This shows how to use a cursor return from a
> > stored proc, which is then just like using a 'normal' DBI cursor.
> >
> > The example creates the Oracle proc in the code, but you would normally
> > have
> > the stored procs or packages done elsewhere and saved in the database.
> We
> > do this to separate the SQL from the perl program, which is useful if
> you
> > have a lot of SQL programmers who don't want to write Perl code (some
> > people
> > are strange!). It is also sometimes useful where the stored proc is
> doing
> > something more complicated than a single SQL statement - it just returns
> > the
> > cursor as the last step in its processing.
> > The PL/SQL part of interest in the example quoted above is:
> >
> > PROCEDURE emp_cursor (job_in IN VARCHAR2, curref IN OUT cursor_ref)
> > IS
> > BEGIN
> > OPEN curref FOR select ename, job from emp where job = job_in;
> > END;
> >
> > The example shows how to use the returned cursor in Perl.
> >
> > -----Original Message-----
> > From: Rozengurtel, Daniel [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, August 07, 2002 5:51 AM
> > To: '[EMAIL PROTECTED]'
> > Subject: Stored Procs
> >
> >
> > Hello all,
> >
> > I need to create couple of stored procedures that return values. As far
> as
> > I
> > understand store-procs don't return values as Oracle functions do, but a
> > function cannot return more than one value at a time. (by the way I
> might
> > be
> > mistaken with that statement since I don't fully understand the 'in out'
> > stor-proc parameter. If it has 'out' then it does return value doesn't
> > it?)
> > Here is a question: If I had a perl code similar to this:
> >
> > my $sth_isid = $dbh->prepare_cached(q{SELECT * FROM FT_T_ISID WHERE
> > INSTR_ID=?
> > AND END_TMS IS NULL
> > ORDER BY ID_CTXT_TYP, LAST_CHG_TMS
> > DESC}) || die $dbh->errstr;
> > ..........................
> > ..........................
> > foreach my $instr_id (blah blah)
> > {
> >
> > $sth_isid->execute( $instr_id );
> > my $hash_ref_isid;
> > my %hash_ids;
> >
> > while ($hash_ref_isid = $sth_isid->fetchrow_hashref)
> > {
> >
> > #### loop through all possible hash values that are returned for
> each
> > record
> > #### and do some stuff
> >
> > } # end while
> >
> > } # end foreach
> >
> > Is it possible (if yes, how or at least what direction should I look to
> )
> > to
> > create something similar using functions and /or store-procs that
> > CONTINUOSLY return values back to a client (a perl program in my case
> for
> > formatting reasons)? The whole idea is to have some reusable procs
> sitting
> > on Oracle instead of doing embedded SQL's. Now I have some examples that
> I
> > followed but nothing really tells me or steer me to the right solution.
> > here is what I looked at as a call to Stor_proc.:
> >
> > my $return_val = "";
> > my $csr = $dbh->prepare(q{
> > BEGIN
> > :return_val :=
> > USERID.RETURN_FLAG('459200101', 'NULL', 'NULL' );
> > END;
> > });
> > $csr->bind_param_inout(":return_val", \$return_val, 20);
> > $csr->execute;
> > print "Return Value: $return_val\n";
> >
> > thanx a lot for any help that you could provide.
> >
> >
> > Daniel
> >
> >
> >
> > _____________________________________________________________________
> > IMPORTANT NOTICES:
> > This message is intended only for the addressee. Please notify
> > the
> > sender by e-mail if you are not the intended recipient. If you are not
> the
> > intended recipient, you may not copy, disclose, or distribute this
> message
> > or its contents to any other person and any such actions may be
> unlawful.
> >
> > Banc of America Securities LLC("BAS") does not accept time
> > sensitive, action-oriented messages or transaction orders, including
> > orders
> > to purchase or sell securities, via e-mail.
> >
> > BAS reserves the right to monitor and review the content of all
> > messages sent to or from this e-mail address. Messages sent to or from
> > this
> > e-mail address may be stored on the BAS e-mail system.
> >
> >
> > Australia Post is committed to providing our customers with excellent
> > service. If we can assist you in any way please either telephone 13 13
> 18
> > or visit our website www.auspost.com.au.
> >
> > CAUTION
> >
> > This e-mail and any files transmitted with it are privileged and
> > confidential information intended for the use of the addressee. The
> > confidentiality and/or privilege in this e-mail is not waived, lost or
> > destroyed if it has been transmitted to you in error. If you have
> received
> > this e-mail in error you must (a) not disseminate, copy or take any
> action
> > in reliance on it; (b) please notify Australia Post immediately by
> return
> > e-mail to the sender; and (c) please delete the original e-mail.
> >
> >
> _____________________________________________________________________
> IMPORTANT NOTICES:
> This message is intended only for the addressee. Please notify
> the
> sender by e-mail if you are not the intended recipient. If you are not the
> intended recipient, you may not copy, disclose, or distribute this message
> or its contents to any other person and any such actions may be unlawful.
>
> Banc of America Securities LLC("BAS") does not accept time
> sensitive, action-oriented messages or transaction orders, including
> orders
> to purchase or sell securities, via e-mail.
>
> BAS reserves the right to monitor and review the content of all
> messages sent to or from this e-mail address. Messages sent to or from
> this
> e-mail address may be stored on the BAS e-mail system.
>
>
> Australia Post is committed to providing our customers with excellent
> service. If we can assist you in any way please either telephone 13 13 18
> or visit our website www.auspost.com.au.
>
> CAUTION
>
> This e-mail and any files transmitted with it are privileged and
> confidential information intended for the use of the addressee. The
> confidentiality and/or privilege in this e-mail is not waived, lost or
> destroyed if it has been transmitted to you in error. If you have received
> this e-mail in error you must (a) not disseminate, copy or take any action
> in reliance on it; (b) please notify Australia Post immediately by return
> e-mail to the sender; and (c) please delete the original e-mail.
>
>
_____________________________________________________________________
IMPORTANT NOTICES:
This message is intended only for the addressee. Please notify the
sender by e-mail if you are not the intended recipient. If you are not the
intended recipient, you may not copy, disclose, or distribute this message
or its contents to any other person and any such actions may be unlawful.
Banc of America Securities LLC("BAS") does not accept time
sensitive, action-oriented messages or transaction orders, including orders
to purchase or sell securities, via e-mail.
BAS reserves the right to monitor and review the content of all
messages sent to or from this e-mail address. Messages sent to or from this
e-mail address may be stored on the BAS e-mail system.
Australia Post is committed to providing our customers with excellent service. If we
can assist you in any way please either telephone 13 13 18 or visit our website
www.auspost.com.au.
CAUTION
This e-mail and any files transmitted with it are privileged and confidential
information intended for the use of the addressee. The confidentiality and/or
privilege in this e-mail is not waived, lost or destroyed if it has been transmitted
to you in error. If you have received this e-mail in error you must (a) not
disseminate, copy or take any action in reliance on it; (b) please notify Australia
Post immediately by return e-mail to the sender; and (c) please delete the original
e-mail.
