perl DBI/DBD problem, Oracle stored procedure returning CLOB

From: Manuela Mueller <520040906697-0001_at_t-online.de>
Date: Sun, 27 Oct 2002 11:11:47 +0100
Message-ID: <3DBBBBE3.34A3476D@t-online.de>

OS: Linux 2.4.18, Suse 8.0
Perl: 5.6.1
DBI: 1.20
DBD: 1.12
RDBMS: Oracle Standard Edition 9.2

Dear All,
please be patient if this is slightly OT, but I tried the RTFM, different newsgroups metalink and google for many hours with no result that
fits my problem.
I'm trying to read a clob returned from a stored procedure. When I call the procedure via sqlplus, I can always sucessfully read the entire CLOB (Size variable, up to 1 MB). If I try to read the same CLOB via Perl, I'm only able to read CLOBs < 4K.

Body of procedure:

PROCEDURE tmarkup_document (

        p_document_id     IN varchar2,
        p_string          IN varchar2,
        p_search_table    IN varchar2,
        p_plaintext       IN number,
        p_hitlist_id      IN number, 
        --out params
        p_wwwhost_id     OUT number,
        p_url            OUT varchar2,
        p_date_fetched   OUT date,
        p_date_checked   OUT date,
        p_date_lastmod   OUT date,
        p_language       OUT varchar2,
        p_content_type   OUT varchar2,
        p_content_length OUT number,
        p_mklob          OUT clob

)
IS

        my_sqlerrm        varchar2(150);
        --declare all out parameters
        o_wwwhost_id      number(10);
        o_url             varchar2(2000);
        o_date_fetched    date;
        o_date_checked    date;
        o_date_lastmod    date;
        o_language        varchar2(4);
        o_content_type    varchar2(40);
        o_content_length  number(10);
        o_mklob           clob;
        --declare the params for ctx_doc.markup
        l_p_hitlist_id    number         := p_hitlist_id;
        l_p_string        varchar2(4000) := p_string;
        l_p_search_table  varchar2(20)   := p_search_table;
        l_p_document_id   number         := p_document_id;
        l_p_plaintext     boolean;
        --alter following for pure perl calls
        mklob             clob;

BEGIN
--a) select stuff in pl/sql variables

        select wwwhost_id, url, date_fetched,
               date_checked, date_lastmod,
               language, content_type, content_length
          into o_wwwhost_id, o_url, o_date_fetched,
               o_date_checked, o_date_lastmod,
               o_language, o_content_type, o_content_length
          from guide_docs
         where id = l_p_document_id;

        --give each out param a value
        p_wwwhost_id     := o_wwwhost_id;
        p_url            := o_url;
        p_date_fetched   := o_date_fetched;
        p_date_checked   := o_date_checked;
        p_date_lastmod   := o_date_lastmod;
        p_language       := o_language;
        p_content_type   := o_content_type;
        p_content_length := o_content_length;

--b) markup the document
if p_plaintext = 1 then

l_p_plaintext := TRUE; --p_plaintext => 1 (plaintext) else

l_p_plaintext := FALSE; --p_plaintext => 0 (HTML) end if;

 ctx_doc.markup(index_name => ''||l_p_search_table||'',
                textkey    => ''||l_p_document_id||'',
                text_query => ''||l_p_string||'',
                plaintext  => l_p_plaintext,
                starttag   => 'STARTTEXT',
                endtag     => 'ENDTEXT',
                restab     => mklob);


 -- give o_mklob a value
        p_mklob := mklob;

de-allocate the temp lob dbms_lob.freetemporary(mklob);
- for all other exceptions: warning with error message, exit procedure. EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('sqlerrm: ' || sqlerrm);

end;

Perl Script:
<start snipped, db connection checked, issue a sucessful "select sysdate from dual">
my($wwwhost_id, $url, $date_fetched, $date_checked, $date_lastmod,
$language, $content_type, $content_length, $mklob);

# specify max size of the lob
$dbh->{LongReadLen} = 2.5 * 1024 * 1024;

##set trace level to 2 (up to 4), specify trace file DBI->trace( 4 , 'tmarkup_document_trace4.txt');

my $sth1 = $dbh->prepare("BEGIN tmarkup_document(:p_document_id,

:p_string, :p_search_table, :p_plaintext, :p_hitlist_id , :p_wwwhost_id,
:p_url, :p_date_fetched, :p_date_checked, :p_date_lastmod, :p_language,
:p_content_type, :p_content_length, :p_mklob); END;")

|| die "Can't prepare: $DBI::errstr\n";

# all in params, this fails if CLOB > 4K
$sth1->bind_param(":p_document_id", 15672044);
$sth1->bind_param(":p_string", "benzene");
$sth1->bind_param(":p_search_table", "SEARCH_2_CH_IDX");
$sth1->bind_param(":p_plaintext", 0);
$sth1->bind_param(":p_hitlist_id", 3355);

#note: third param to bind_param_in_out is the maximum size of the variable.
# make it slighlty larger than the necessary size. Memory for necessary size
# is always allocated, even if actual size is smaller.

# the out params
$sth1->bind_param_inout(":p_wwwhost_id", \$wwwhost_id, 11);
$sth1->bind_param_inout(":p_url", \$url, 2100);
$sth1->bind_param_inout(":p_date_fetched", \$date_fetched, 10);
$sth1->bind_param_inout(":p_date_checked", \$date_checked, 10);
$sth1->bind_param_inout(":p_date_lastmod", \$date_lastmod, 10);
$sth1->bind_param_inout(":p_language", \$language, 4);
$sth1->bind_param_inout(":p_content_type", \$content_type, 40);
$sth1->bind_param_inout(":p_content_length", \$content_length, 11);

# ok with clob < 4K
$sth1->bind_param_inout(":p_mklob", \$mklob, 2048000, { ora_type => 112
} );

$sth1->execute || die "Can't execute: $DBI::errstr\n";

print '$wwwhost_id: ', $wwwhost_id, "\n";
print '$url: ', $url, "\n";
print '$date_fetched: ', $date_fetched, "\n";
print '$date_checked: ', $date_checked, "\n";
print '$date_lastmod: ', $date_lastmod, "\n";
print '$language: ', $language, "\n";
print '$content_type: ', $content_type, "\n";
print '$content_length: ', $content_length, "\n";
print '$mklob: ', $mklob, "\n";

$dbh->disconnect || warn "Let me out: $DBI::errstr\n";
exit;

I tried different syntax for $sth1->bind_param_inout, with and without specifying ora_type, but
the result is always the same.

<snip to trace file, trying to read a CLOB > 4k:

-> bind_param_inout for DBD::Oracle::st (DBI::st=HASH(0x84a908c)~0x84a9254 ':p_content_length' SCALAR(0x84a2b58) 11)

bind :p_content_length <== undef (type 0, inout 0x84a2b58, maxlen 11)

bind :p_content_length <== undef (NULL, size 0/0/11, ptype 4, otype 1, inout)

bind :p_content_length <== '' (size 0/28, otype 1, indp -1, at_exec 1)

bind :p_content_length done with ftype 1 <- bind_param_inout= 1 at tmarkup_document.pl line 85 -> bind_param_inout for DBD::Oracle::st (DBI::st=HASH(0x84a908c)~0x84a9254 ':p_mklob' SCALAR(0x84a33d4) 2048000 HASH(0x84a92
3c))

bind :p_mklob <== undef (type 0, inout 0x84a33d4, maxlen 2048000, attribs: HASH(0x84a923c))

bind :p_mklob done with ftype 112 <- bind_param_inout= 1 at tmarkup_document.pl line 99 -> execute for DBD::Oracle::st (DBI::st=HASH(0x84a908c)~0x84a9254) dbd_st_execute BEGIN (out9, lob1)...

bind :p_mklob done with ftype 112

out ':p_content_length' [0,0]: alen 29, piece 0 dbd_st_execute BEGIN returned (SUCCESS, rpc1, fn34, out9) ERROR EVENT 1403 'ORA-01403: no data found (DBD ERROR: LOB refetch attempted for unsupported statement type)' on DBI::st=HAS H(0x84a9254)

!! ERROR: 1403 'ORA-01403: no data found (DBD ERROR: LOB refetch attempted for unsupported statement type)'

<- execute= undef at tmarkup_document.pl line 101 <Rest snipped>

I got the impression DBD and/or DBI is interpreting the CLOB as a VARCHAR2 with maxsize of 4000 characters.

When I try to read a CLOB/BLOB via "select x from table Y", it is always sucessfully read.

Maybe I'm on the wrong way, trying to read a CLOB returned from a stored procedure?

Any suggestions are welcome, TIA and have a nice weekend. Manuela Mueller Received on Sun Oct 27 2002 - 04:11:47 CST