Hi,

I am calling a Oracle function from within Perl but I cannot figure out how to retrieve return value. The function does execute fine with no errors.

Here is my code;

my $Proc = "DECLARE sResult JOB_MAPS.OUTPUTFILE%TYPE; begin sResult:= BATCH_SQL.DATE_BEGINNING('$OUTPUTFILE'); end;";
$sth = $db->prepare($Proc);
$sth->execute();
my $rows = $sth->rows;

This function returns a varchar2.
If I check the row count after it is 1.
If I check the value of $sth it gives me DBI::st=HASH(0x30583890)

Anyone know how I can get my return value back?

Hi - just thought I'd let everyone know that I changed my function to a procedure with a IN/OUT parameter and it works great. Below is an example. Thanks to anyone who read my post.

$Proc = "BEGIN BATCH_SQL." .$FILE_MASK_FUNCTION . "(:OUTPUTFILE); end;";
$sth = $db->prepare($Proc);
$sth->bind_param_inout(":OUTPUTFILE", \$OUTPUTFILE, 200);
$sth->execute();

Thanks for the followup post.

Just for safety's sake, you should quote symbol names. Unfortunately, DBI (to my knowledge) doesn't provide a function to do this, it can only quote string values.

The rules for quoting a symbol name in Oracle are similar to quoting a value. This code should work:

There is a hitch, in that quoted symbols are case sensitive but unquoted symbols are not. This means that foo is the same as FOO and both are the same as "FOO", but they're all different from "foo". Got that?