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

Normally DBI handles CLOBs as big scalars. It has some attributes that let you truncate them so that really big ones don't take up all your memory, but aside from that, it doesn't return references.

Here's the relevant manual page on DBD::Oracle. It looks confusing to me, but if you go down a bit there are some helpful looking examples.