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 } );

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

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 
         /* ---- TYPE ref_cursor_type is REF CURSOR; */
        ref_cursor   ref_cursor_type; 
        OPEN  ref_cursor
        FOR   SELECT clob_col, string_col, int_col FROM foo_table ;
        RETURN ref_cursor;     

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


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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.