. Everything works fine when I am binding them to normal PL/SQL variable types such as
NUMBER, VARCHAR2, etc. The real gotcha comes when I try to bind parameters to data types defined in an Oracle
package as:
For example, say I create the ultra-simplistic package:
CREATE OR REPLACE PACKAGE foo AS
PROCEDURE getDept(dept_id IN NUMBER, dept_name OUT VARCHAR2);
END foo;
CREATE OR REPLACE PACKAGE BODY foo AS
PROCEDURE getDept(dept_id IN NUMBER, dept_name OUT VARCHAR2) IS
BEGIN
SELECT dept INTO getDept.dept_name
FROM Dept
WHERE idDept = getDept.dept_id;
END getDept;
END foo;
The Perl code to execute this and pull out the name of the department (
dept_name) is simple and works
just fine:
my $dbh =DBI->connect('dbi:Oracle:host=hostname;sid=theSID',
'user',
'pass',
{
RaiseError => 1,
AutoCommit => 0
}) || die "Database connection not made because:\n\t$DBI::errs
+tr\n";
my $deptName;
eval {
my $func = $dbh->prepare(q{
BEGIN
foo.getDept
(
:parameter1,
:parameter2
);
END;
});
$func->bind_param(":parameter1", 9);
$func->bind_param_inout(":parameter2", \$deptName, 4); # bind this
+ parameter as an "inout"
$func->execute;
};
if( $@ ) {
warn
"Execution of stored procedure failed because:\n\t$DBI::errstr
+\n$@";
}
else
{
print "$deptName\n";
}
$dbh->disconnect;
That code correctly prints out the department name. Now, comes the kicker, let's say I want to write a procedure
that returns all of the departments. That is, it returns the whole department table. Something like:
CREATE OR REPLACE PACKAGE foo AS
-- PROCEDURE getDept(dept_id IN NUMBER, dept_name OUT VARCHAR2);
/*
This is a package wide variable type that can hold the contents
of the entire department table
*/
TYPE deptTable IS TABLE OF dept%rowtype INDEX BY BINARY_INTEGER;
PROCEDURE getAllDept(dept OUT deptTable);
END foo;
CREATE OR REPLACE PACKAGE BODY foo AS
PROCEDURE getAllDept(dept OUT deptTable) IS
CURSOR cursDept IS
SELECT * FROM dept;
i NUMBER := 0;
BEGIN
OPEN cursDept;
LOOP
FETCH cursDept INTO getAllDept.dept(i); /* put all of the
+dept table's records in the variable */
i := i + 1;
EXIT WHEN cursDept%NOTFOUND;
END LOOP;
CLOSE cursDept;
END getAllDept;
END foo;
It's a fairly simple procedure and works just fine. Now, I write the Perl code to execute it and gather the results.
use Data::Dumper;
my $dbh =DBI->connect('dbi:Oracle:host=hostname;sid=theSID',
'user',
'pass',
{
RaiseError => 1,
AutoCommit => 0
}) || die "Database connection not made because:\n\t$DBI::errs
+tr\n";
my $deptRecords;
eval {
my $func = $dbh->prepare(q{
BEGIN
foo.getAllDept
(
:parameter1
);
END;
});
$func->bind_param_inout(":parameter1", \$deptRecords, 4096);
$func->execute;
};
if( $@ ) {
warn
"Execution of stored procedure failed because:\n\t$DBI::errstr
+\n$@";
}
else
{
print Dumper($deptName);
}
$dbh->disconnect;
This returns the error that I am not using the right data type.
Execution of stored procedure failed because:
ORA-06550: line 3, column 4:
PLS-00306: wrong number or types of arguments in call to 'GETALLDEPT'
ORA-06550: line 3, column 4:
PL/SQL: Statement ignored (DBD: oexec error)
So, my question is, how (assuming there is a way) can you bind Perl scalars to Oracle PL/SQL procedure OUT variables
that are of a package-defined type? Can you? I have read over
DBD::Oracle's POD examples,
and I have read all the other pages I could find (
Perl DBI
Examples,
DBI DBD::Oracle and OraPerl FAQ, etc.); and I can't
find anything. Is there any Perl/Oracle-guru lurking around the
Monastery?
Thanks,
enoch