Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: Retrieve return value from function

    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?
    Last edited by ne13918; 11-24-09 at 10:20.

  2. #2
    Join Date
    Nov 2009
    Posts
    2
    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();

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Thanks for the followup post.

    Code:
    "BEGIN BATCH_SQL." .$FILE_MASK_FUNCTION . "(:OUTPUTFILE); end;"
    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:

    Code:
    sub quote_symbol {
       my $a = shift; # Copy by value
       $a =~ s/"/""/g; # Double up any double-quotes
       return "\"$a\""; # And surround with double-quotes
    }
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •