| 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
