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 an often asked question, but I tried the
RTFM, different newsgroups and google for many hours with no result that
fits my problem.
I'm trying to read a clob returned from a stored porcedure. 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, --for exeception handling
--out params for all other values guide_docs
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 (uses automatically bind
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;
-- have to de-allocate the temp lob
dbms_lob.freetemporary(mklob);
-- for all other exceptions: warning with error message, exit
procedure.
--check errors other way: insert into hitlists(m_error)
EXCEPTION
WHEN OTHERS
THEN
my_sqlerrm := SUBSTR(SQLERRM, 1, 150);
update hitlists
set m_error = my_sqlerrm,
status = 'error'
where id = l_p_hitlist_id;
commit;
--final version: NO dbms_output!!!
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
<snipped the successfully params>
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 BLOB via Perl, 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
