On 03/09/2011 07:50, Shobha Deepthi V (sobv) wrote:
Hi,
I am calling a stored procedure that returns ref cursor from perl. But
it fails with the following error,
DBD::Oracle::st execute failed: ( DBD ERROR: LOB refetch attempted for
unsupported statement type )
I searched for this error, and all the results show how to read a CLOB
return type but not a ref cursor. Any help is much appreciated.
Here's my perl code,
sub run_proc{
...
...
...
$sth->bind_param_inout(":p1", \$args->[0],length($args->[0]), {
ora_type => ORA_CLOB});
$sth->bind_param_inout(":p2", \$args->[1],length($args->[1]));
$sth->bind_param_inout(":p3", \$args->[2],1);
$sth->bind_param_inout(":p4", \$res,0,{ ora_type => ORA_RSET});
$sth->execute();
....
}
And the stored procedure looks like,
PROCEDURE p_search_eol_data(
pid_string IN CLOB,
where_clause IN VARCHAR2,
role_flag IN NUMBER,
results OUT searchCursor)
IS
queryString LONG;
BEGIN
queryString := '';/* dynamic query */
open results for queryString;
END;
Thanks,
Shobha Deepthi V
Just some observations that might help you.
You don't need to bind the input parameters with bind_param_inout, just
use bind_param (first 3 parameters).
Which lob are you talking about, the input lob pid_string or a lob
returned in the reference cursor? Bind the input lob with bind_param and
take the reference cursor out of the procedure to make sure you are
getting the lob in first then put the reference cursor back in. The
output reference cursor ends up creating a new DBI statement handle for
you (your $res) and you need to bind parameters on that and call fetch.
If one of the columns returned from the reference cursor is a lob too
you'll need to bind it as a lob then use one of the lob fetching
mechanisms to fetch it - personally I prefer to turn off ora_auto_lob
and use ora_lob_length and ora_lob_read.
Martin
