On 05/26/2010 10:46 AM, John Scoles wrote:
> Bob McGowan wrote:
>
> I am interested in this one Bob here are my words on it.
>
>
-- Deleted my code details --
>>
>>
> What I want you to do so I can properly debug this is
>
> 1) tell me what version of DBD::Oracle you are useing
> 2) what version of Oracle
> 3) what platform is it running on
> 4) what version of Perl
>
> finally
> If you are using a later version of DBD::Oracle I would like you to
> rerun your code but this time do this
>
> $dbd->{dbd_verbose}=15;
>
> after you connect and then send me all the output from a test run of you
> .PL
>
> cheers
> John Scoles
>
>
Hi, John,
So far as I can tell (I'm not a PL/SQL programmer) the append_comments
function does not explicitly create any temporary CLOB elements. I can
provide the code for the procedure, if you think it would help.
Is it possible that the procedure needs to know it's dealing with a CLOB
temporary element from the DBD?
Here's the info you requested:
1) DBD::Oracle is 1.24 (fyi, DBI is 1.611)
2) Oracle instantclient v. 10.2.0.3, connecting to Oracle server
10.2.0.1.0 - 64bit
3) DBD built on GNU/Linux (Debian 5.0.4), server running on a SPARC
sun4u with Solaris OS v. 5.10
4) perl v5.8.8 built for i686-linux
The output for the test run is attached.
Thanks again.
--
Bob McGowan
Symantec
dbd_preparse scanned 2 distinct placeholders
OCIHandleAlloc(82d07e8,8309df0,OCI_HTYPE_STMT,0,0)=SUCCESS
OCIStmtPrepare(82ef660,82e6378,'
BEGIN
append_comments(:incident, :comments);
END;
',179,1,0)=SUCCESS
OCIAttrGet(82ef660,OCI_HTYPE_STMT,8309dfc,0,OCI_ATTR_STMT_TYPE,82e6378)=SUCCESS
dbd_st_prepare'd sql BEGIN (pl1, auto_lob1, check_sql1)
dbd_describe skipped for BEGIN
dbd_bind_ph(): bind :incident <== '1199329' (type 0 (DEFAULT (varchar)))
dbd_rebind_ph() (1): rebinding :incident as '1199329' (not-utf8, ftype 1
(VARCHAR), csid 0, csform 0(0), inout 0)
dbd_rebind_ph_char() (1): bind :incident <== '1199329' (size 7/8/0, ptype
4(VARCHAR), otype 1 )
dbd_rebind_ph_char() (2): bind :incident <== ''119932' (size 7/8, otype
1(VARCHAR), indp 0, at_exec 1)
bind :incident as ftype 1 (VARCHAR)
OCIBindByName(82ef660,830a65c,82e6378,":incident",placeh_len=9,value_p=83089e0,value_sz=8,dty=1,indp=830a674,alenp=0,rcodep=830a66c,maxarr_len=0,curelep=0
(*=0),mode=DATA_AT_EXEC,2)=SUCCESS
OCIBindDynamic(82ef42c,82e6378,830a638,b7c79a50,830a638,b7c79c70)=SUCCESS
OCIAttrGet(82ef42c,OCI_HTYPE_BIND,830a648,0,31,82e6378)=SUCCESS
dbd_rebind_ph(): bind :incident <== '1199329' (in, not-utf8, csid 1->0->1,
ftype 1 (VARCHAR), csform 0(0)->0(0), maxlen 8, maxdata_size 0)
OCIAttrSet(82ef42c,OCI_HTYPE_BIND, bfffdce8,0,Attr=31,82e6378)=SUCCESS
dbd_bind_ph(): bind :comments <== 'this is a test
and more stuff.
' (type 0 (DEFAULT (varchar)), attribs: HASH(0x814d7f0))
dbd_rebind_ph() (1): rebinding :comments as 'this ...' (not-utf8, ftype 112
(SQLT_CLOB / long), csid 0, csform 0(0), inout 0)
OCIDescriptorAlloc(82d07e8,830a6e8,OCI_DTYPE_LOB,0,0)
OCIAttrSet(82e4b9c,OCI_DTYPE_LOB,
bfffdbb4,0,Attr=OCI_ATTR_LOBEMPTY,82e6378)=SUCCESS
OCILobCreateTemporary(82e6304,82e6378,82e4b9c,0,0,2,1,10)=SUCCESS
OCILobCharSetForm(82d07e8,82e6378,82e4b9c,1)=SUCCESS
OCILobCharSetId(82d07e8,82e6378,82e4b9c,1)=SUCCESS
calling OCILobWrite phs->csid=1 phs->csform=1 amtp=31
OCILobWrite(82e6304,82e6378,82e4b9c,bfffdbb0,1,8308958,31,0,0,0,1,1)=SUCCESS
bind :comments as ftype 112 (SQLT_CLOB / long)
OCIBindByName(82ef660,830a6e4,82e6378,":comments",placeh_len=9,value_p=830a6e8,value_sz=4,dty=112,indp=830a6fc,alenp=0,rcodep=830a6f4,maxarr_len=0,curelep=0
(*=0),mode=DEFAULT,0)=SUCCESS
OCIAttrSet(82ef2f4,OCI_HTYPE_BIND,
bfffdceb,0,Attr=OCI_ATTR_CHARSET_FORM,82e6378)=SUCCESS
OCIAttrGet(82ef2f4,OCI_HTYPE_BIND,830a6d0,0,31,82e6378)=SUCCESS
dbd_rebind_ph(): bind :comments <== 'this ...' (in, not-utf8, csid 1->1->1,
ftype 112 (SQLT_CLOB / long), csform 1(SQLCS_IMPLICIT)->1(SQLCS_IMPLICIT),
maxlen 4, maxdata_size 0)
OCIAttrSet(82ef2f4,OCI_HTYPE_BIND, bfffdce8,0,Attr=31,82e6378)=SUCCESS
dbd_st_execute BEGIN (out0, lob1)...
Statement Execute Mode is 0 (DEFAULT)
in ':incident' [0,0]: len 7, ind 0, value='1199329'
OCIStmtExecute(82e6304,82ef660,82e6378,1,0,0,0,mode=DEFAULT,0)=SUCCESS
OCIAttrGet(82ef660,OCI_HTYPE_STMT,bfffdd68,0,OCI_ATTR_ROW_COUNT,82e6378)=SUCCESS
OCIAttrGet(82ef660,OCI_HTYPE_STMT,bfffdd6e,0,OCI_ATTR_SQLFNCODE,82e6378)=SUCCESS
dbd_st_execute BEGIN returned (SUCCESS, rpc1, fn34, out0)
OCIHandleFree(82e4b9c,OCI_DTYPE_LOB)=INVALID_HANDLE
OCITransCommit(82e6304,82e6378,0)=SUCCESS
OCISessionEnd(82e6304,82e6378,8306a98,mode=DEFAULT 0)=SUCCESS
OCIServerDetach(82e6904,82e6378,mode=DEFAULT,0)=SUCCESS
dbd_st_destroy
OCIHandleFree(82ef660,OCI_HTYPE_STMT)=SUCCESS
OCILobIsTemporary(82d07e8,82e6378,82e4b9c,bfffda38)=SUCCESS
OCILobFreeTemporary SUCCESS
OCILobFreeTemporary(82e6304,82e6378,82e4b9c)=(UNKNOWN OCI STATUS 1041)
OCIErrorGet(82e6378,1,"<NULL>",bfffd9a8,"",1024,2)=NO_DATA
OCIDescriptorFree(82e4b9c,OCI_DTYPE_LOB)
(in cleanup) DBD::Oracle::st DESTROY failed: (DBD (UNKNOWN OCI STATUS
1041): OCILobFreeTemporary) [for Statement "
BEGIN
append_comments(:incident, :comments);
END;
"], <> line 2.
OCIHandleFree(8306a98,OCI_HTYPE_SESSION)=SUCCESS
OCIHandleFree(82e6904,OCI_HTYPE_SERVER)=SUCCESS
OCIHandleFree(82e6304,OCI_HTYPE_SVCCTX)=SUCCESS
OCIHandleFree(82e6378,OCI_HTYPE_ERROR)=SUCCESS
