Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.

Report information
The Basics
Id: 61511
Status: resolved
Priority: 0/
Queue: DBD-Oracle

People
Owner: Nobody in particular
Requestors: kris.lemaire [...] dbxprt.com
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 1.24b
Fixed in: (no value)



Subject: ORA-00942 when inserting into a table with a LOB column over a synonym on HP-UX
Download (untitled) / with headers
text/plain 9.2k
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
>name[1]);
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
>name[1]);
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
> char new_tablename[100];
4070d4070 < char new_tablename[100];
Subject: oci8.c
Download oci8.c
text/x-csrc 153.1k

Message body is not shown because it is too large.

Download (untitled) / with headers
text/plain 167b
Ok I added the patch o Trunk with no other ill effects you can find it here http://svn.perl.org/modules/dbd-oracle/trunk It will be released with 1.28 cheers John


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.