Home » Open Source » Programming Interfaces » retrieving CLOB from an Oracle stored function within perl (perl, v5.8.6 built for sun4-solaris)
retrieving CLOB from an Oracle stored function within perl [message #278313] Fri, 02 November 2007 17:49
andrewkl
Messages: 9
Registered: November 2007
Junior Member
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

Previous Topic: ora-106556
Next Topic: Passing Tables from PHP to PLSQL
Goto Forum:
  


Current Time: Thu Mar 28 05:19:43 CDT 2024