Bug #61511 for DBD-Oracle: ORA-00942 when inserting into a table with a LOB column over a synonym on HP-UX

This problem seems OS related. We have the problem on HP-UX and not on Sun Solaris. We get an ORA-00942 when we insert into a table with a LOB column over a synonym. The problem occures in init_lob_refetch(SV *sth, imp_sth_t *imp_sth). In this function a pointer is declared that holds the address of the array that contains the tablename. The problem on HP-UX is : - the address of new_tablename is stored in the tablename pointer - the new_tablename array has a small scope and is released quickly - the same memory is reused for the sql_field array - this results in a select statement where the tablename has the value of sql_field. The attatched oci8.c file is a patched 1.24b oci8.c file. (Moved the declaration of new_tablename to the top of the function so the memory is not released and the pointer in tablename is always a reference to the right array) ----------------------------------------------------------------------- - - Investigation details - ----------------------------------------------------------------------- Version info : -------------- DBD version : DBD-Oracle-1.24b OS : HP-UX B.11.31 U ia64 GCC version : gcc -v Using built-in specs. Target: ia64-hp-hpux11.31 Configured with: ../gcc/configure Thread model: posix gcc version 4.2.3 Database setup : ---------------- - Version : Oracle 10.2.0.4 - 2 users : KRIS2 --> owns the following table desc TB_ORA00902 Name Null? Type ----------------------------------------- ----- --- ---------------------------- SRV_HOST_NM NOT NULL VARCHAR2(64) SID NOT NULL VARCHAR2(64) HOME_DRT NOT NULL VARCHAR2(255) PTH_SWR_VSN NOT NULL VARCHAR2(20) PTH_INF NOT NULL CLOB KRIS as a synonym : TB_ORA00902 -> KRIS2.TB_ORA00902 Perl code to reproduce the problem : ------------------------------------ Perl code : #!/usr/local/bin/perl use DBI; use DBD::Oracle qw(:ora_types); DBI->trace($ARGV[0]) if (defined $ARGV[0]); print "DBD::Oracle Version : $DBD::Oracle::VERSION\n"; my $hDb = DBI->connect("DBI:Oracle:tnsalias" ,"KRIS" ,"password" ,{ RaiseError => 0 ,AutoCommit => 0 } ); my $hStat; my $sStat; my $sResult; $sStat = "Insert Into TB_ORA00902 (SRV_HOST_NM ,SID ,HOME_DRT ,PTH_SWR_VSN ,PTH_INF ) Values (:hostname,:sid,:homedir,:opatchversion,:opatchinfo) "; $hStat = $hDb->prepare($sStat) or die "Prepare error = $DBI::err with text = $DBI::errstr"; $hStat->bind_param(":hostname", "hostname"); $hStat->bind_param(":sid", "sid"); $hStat->bind_param(":homedir", "homedir"); $hStat->bind_param(":opatchversion", "version"); $hStat->bind_param(":opatchinfo", "opatchinfo" , { ora_type => ORA_CLOB }); $sResult = $hStat->execute() or die "Execute error = $DBI::err with text = $DBI::errstr"; $hDb->commit; Error message : --------------- Execute error = 942 with text = ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 12 in 'Insert Into <*>TB_ORA00902 Debug information : ------------------- dbd_st_execute INSERT (out0, lob1)... in ':hostname' [0,0]: len 8, ind 0 in ':sid' [0,0]: len 3, ind 0 in ':homedir' [0,0]: len 7, ind 0 in ':opatchversion' [0,0]: len 7, ind 0 OCIStmtExecute(403638b8,4036c7d0,40363930,1,0,0,0,0)=SUCCESS OCIAttrGet(4036c7d0,OCI_HTYPE_STMT,7fffeb80,0,9,40363930) =SUCCESS OCIAttrGet(4036c7d0,OCI_HTYPE_STMT,7fffeb84,0,10,40363930) =SUCCESS dbd_st_execute INSERT returned (SUCCESS, rpc1, fn3, out0) OCIHandleAlloc(4034b5f0,7fffea64,OCI_HTYPE_DESCRIBE,0,0)=SUCCESS OCIDescribeAny(403638b8,40363930,403cd190,11,1,1,7,4036ae80) =SUCCESS OCIAttrGet(4036ae80,OCI_HTYPE_DESCRIBE,7fffea68,0,124,40363930) =SUCCESS OCIAttrGet(4036ae58,OCI_DTYPE_PARAM,7fffea80,0,9,40363930) =SUCCESS OCIAttrGet(4036ae58,OCI_DTYPE_PARAM,7fffea84,0,134,40363930) =SUCCESS lob refetch synonym, schema=KRIS2, name=TB_ORA00902, new tablename=KRIS2.TB_ORA00902 OCIDescribeAny(403638b8,40363930,7fffea8c,17,1,1,1,4036ae80) =SUCCESS OCIAttrGet(4036ae80,OCI_HTYPE_DESCRIBE,7fffea68,0,124,40363930) =SUCCESS OCIAttrGet(4036ae30,OCI_DTYPE_PARAM,7fffea60,0,102,40363930) =SUCCESS OCIAttrGet(4036ae30,OCI_DTYPE_PARAM,7fffea6c,0,103,40363930) =SUCCESS lob refetch from table KRIS2.TB_ORA00902, 5 columns: OCIParamGet(4036ae08,53,40363930,7fffea70,1)=SUCCESS OCIAttrGet(4036ade0,OCI_DTYPE_PARAM,7fffea80,0,2,40363930) =SUCCESS OCIAttrGet (4036ade0,OCI_DTYPE_PARAM,7fffea84,7fffea88,4,40363930)=SUCCESS lob refetch table col 1: 'SRV_HOST_NM' otype 1 OCIParamGet(4036ae08,53,40363930,7fffea70,2)=SUCCESS OCIAttrGet(4036adb8,OCI_DTYPE_PARAM,7fffea80,0,2,40363930) =SUCCESS OCIAttrGet (4036adb8,OCI_DTYPE_PARAM,7fffea84,7fffea88,4,40363930)=SUCCESS lob refetch table col 2: 'SID' otype 1 OCIParamGet(4036ae08,53,40363930,7fffea70,3)=SUCCESS OCIAttrGet(4036ad90,OCI_DTYPE_PARAM,7fffea80,0,2,40363930) =SUCCESS OCIAttrGet (4036ad90,OCI_DTYPE_PARAM,7fffea84,7fffea88,4,40363930)=SUCCESS lob refetch table col 3: 'HOME_DRT' otype 1 OCIParamGet(4036ae08,53,40363930,7fffea70,4)=SUCCESS OCIAttrGet(4036ad68,OCI_DTYPE_PARAM,7fffea80,0,2,40363930) =SUCCESS OCIAttrGet (4036ad68,OCI_DTYPE_PARAM,7fffea84,7fffea88,4,40363930)=SUCCESS lob refetch table col 4: 'PTH_SWR_VSN' otype 1 OCIParamGet(4036ae08,53,40363930,7fffea70,5)=SUCCESS OCIAttrGet(4036ad40,OCI_DTYPE_PARAM,7fffea80,0,2,40363930) =SUCCESS OCIAttrGet (4036ad40,OCI_DTYPE_PARAM,7fffea84,7fffea88,4,40363930)=SUCCESS lob refetch table col 5: 'PTH_INF' otype 112 lob refetch :opatchinfo param: otype 112, matched field 'PTH_INF' by type (PTH_INF "opatchinfo") OCIDescriptorAlloc(4034b5f0,40032e10,OCI_DTYPE_LOB,0,0) lob refetch sql: select PTH_INF "opatchinfo" from PTH_INF "opatchinfo" where rowid = :rid for update OCIHandleAlloc(4034b5f0,403d0480,OCI_HTYPE_STMT,0,0)=SUCCESS OCIStmtPrepare(40369710,40363930,'select PTH_INF "opatchinfo" from PTH_INF "opatchinfo" where rowid = :rid for update',83,1,0)=SUCCESS OCIDescriptorAlloc(4034b5f0,403d0488,OCI_DTYPE_ROWID,0,0) OCIBindByName (40369710,403d0484,40363930,":rid",placeh_len=4,value_p=403d0488,value_s z=4,dty=104,indp=0,alenp=0,rcodep=0,maxarr_len=0,curelep=0 (*=0),mode=0) =SUCCESS lob refetch 1 for ':opatchinfo' param: ftype 112 setup OCIDefineByPos(40369710,7fffea88,40363930,1,40032e10,- 1,112,403df280,0,403df2c0,0)=SUCCESS OCIAttrGet(4036c7d0,OCI_HTYPE_STMT,4036df48,0,19,40363930) =SUCCESS OCIStmtExecute(403638b8,40369710,40363930,1,0,0,0,0)=ERROR OCIAttrGet(4036c7d0,OCI_HTYPE_STMT,7fffe9f4,0,129,40363930) =SUCCESS OCIErrorGet(40363930,1,"<NULL>",7fffe5f0,"ORA-00942: table or view does not exist ",1024,2)=SUCCESS Added some extra debugging info in C-code (oci8.c) : ---------------------------------------------------- if (DBIS->debug >= 3 || dbd_verbose >= 3 ) PerlIO_printf(DBILOGFP, "Kris Debugging: %s %s %p %p %s \n", tablename, sql_field,(void *) tablename,(void *) sql_field, &phs- Show quoted text
matched = 1; sprintf(sql_field, "%s%s \"%s\"", (SvCUR(sql_select)>7)?", ":"", p, &phs->name [1]); if (DBIS->debug >= 3 || dbd_verbose >= 3 ) PerlIO_printf(DBILOGFP, "Kris Debugging: %s %s %p %p %s \n", tablename, sql_field,(void *) tablename,(void *) sql_field, &phs- Show quoted text
Output of the extra debug information : --------------------------------------- OCIParamGet(403c2d68,53,403bb890,7fffe9ec,5,OCI_DTYPE_PARAM) =SUCCESS OCIAttrGet (403c2ca0,OCI_DTYPE_PARAM,7fffea04,0,OCI_ATTR_OBJECT,403bb890)=SUCCESS OCIAttrGet (403c2ca0,OCI_DTYPE_PARAM,7fffea08,7fffea0c,OCI_ATTR_SQLCODE,403bb890) =SUCCESS lob refetch table col 5: 'PTH_INF' otype 112 Kris Debugging: KRIS2.TB_ORA00902 KRIS2.TB_ORA00902 7fffea74 7fffea74 opatchinfo Kris Debugging: PTH_INF "opatchinfo" PTH_INF "opatchinfo" 7fffea74 7fffea74 opatchinfo lob refetch :opatchinfo param: otype 112, matched field 'PTH_INF' by type (PTH_INF "opatchinfo") OCIDescriptorAlloc(403a3550,402bbd60,OCI_DTYPE_LOB,0,0) lob refetch sql: select PTH_INF "opatchinfo" from PTH_INF "opatchinfo" where rowid = :rid for update OCIHandleAlloc(403a3550,40351580,OCI_HTYPE_STMT,0,0)=SUCCESS OCIStmtPrepare(403c1670,403bb890,'select PTH_INF "opatchinfo" from PTH_INF "opatchinfo" where rowid = :rid for update',83,1,0)=SUCCESS The address of tablename and sql_field are the same! The sprintf copies 'PTH_INF "opatchinfo"' into sql_field and tablename (same address). Solution that seems to work for me : ------------------------------------ Move the declaration of new_tablename to the top of the function so we are sure that the address in not reused when a new array is allocate. $ diff oci8.c.orig oci8.c 4037a4038 Show quoted text
4070d4070 < char new_tablename[100];