retrieving CLOB from an Oracle stored function within perl

[andrewkl]

Hi,

I'm trying to retrieve data (CLOB, string, integer) from a stored function.
My example code looks like:

      $dbh = DBI->connect(....) or die "can't connect";

      $stmt = 'BEGIN :cursor := foo; END;';
      $sth = $dbh->prepare ($stmt);
      $sth->bind_param_inout(":cursor", \$refCursor, 0, { ora_type => ORA_RSET } );

       $sth->execute();
       while ( my ($clob, $str, $num)= $refCursor->fetchrow_array ) { 
          print "$clob, $str, $num\n";
         }
      $sth->finish();

When I run my perl script, I get:

     OCILobLocatorPtr=SCALAR(0x4cbdec), hello there, 1
     OCILobLocatorPtr=SCALAR(0x4cbe4c), bye now, 2

How can I get the value of the CLOB data?

The stored function foo() looks like:

     FUNCTION foo
     RETURN ref_cursor_type 
     IS
         /* ---- TYPE ref_cursor_type is REF CURSOR; */
        ref_cursor   ref_cursor_type; 
     BEGIN 
        OPEN  ref_cursor
        FOR   SELECT clob_col, string_col, int_col FROM foo_table ;
        RETURN ref_cursor;     
     END;

The foo_table looks like:

     CREATE TABLE foo_table
     (
       clob_col           CLOB,
       string_col         VARCHAR2 (100),
       int_col            NUMBER
     );

Contents of foo_table are:

     CLOB_COL         STRING_COL       INT_COL
     -----------------------------------------
     testing          hello there       1
     another test     bye now           2

Thanks
Andrew

[KevinADC]

$clob looks like a reference to a scalar, so try this:

print ${$clob},", $str, $num\n";