FAQ
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

Search Discussions

  • Martin J. Evans at Sep 3, 2011 at 8:44 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedSep 3, '11 at 6:50a
activeSep 3, '11 at 8:44a
posts2
users2
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase