Hi,
We are using : DBD-Oracle-1.24b
on HP-UX B.11.31 U ia64
compiled with :
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 :
#!/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 :
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_sz=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
Problem : The value of tablename has changed.
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->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->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).
Cause of the problem :
static int
init_lob_refetch(SV *sth, imp_sth_t *imp_sth)
{
...
STRLEN tablename_len;
char *tablename;
...
switch (imp_sth->stmt_type) {
case OCI_STMT_UPDATE:
tablename = find_ident_after(imp_sth->statement,
"update", &tablename_len, 1);
break;
case OCI_STMT_INSERT:
tablename = find_ident_after(imp_sth->statement,
"into", &tablename_len, 1);
break;
default:
return oci_error(sth, errhp, OCI_ERROR,
"LOB refetch attempted for unsupported statement
type (see also ora_auto_lob attribute)");
}
...
if (status == OCI_SUCCESS) { /* There is a synonym, get the schema
*/
char *syn_schema=NULL;
char syn_name[100];
char new_tablename[100];
ub4 tn_len = 0, syn_schema_len = 0;
...
tablename=new_tablename;
/* address of new_tablename is stored in tablename pointer */
}
/* new_tablename array is released (out of scope) */
while( (sv = hv_iternextsv(lob_cols_hv, &p, &j)) != NULL ) {
char sql_field[200];
/* The new array (sql_field) gets the same address as new_tablename and the
address that is stored in the tablename pointer */
...
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->name
[1]);
matched = 1;
/* sql_field gets a new value but tablename also gets the same value since
it is pointing to the same array*/
/* Kris Debugging: KRIS2.TB_ORA00902 KRIS2.TB_ORA00902 7fffea74 7fffea74
opatchinfo */
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->name
[1]);
/* Kris Debugging: PTH_INF "opatchinfo" PTH_INF "opatchinfo" 7fffea74
7fffea74 opatchinfo */
...
}
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
> char new_tablename[100];
4070d4070
< char new_tablename[100];
Or :
static int
init_lob_refetch(SV *sth, imp_sth_t *imp_sth)
{
dTHX;
SV *sv;
SV *sql_select;
HV *lob_cols_hv = NULL;
sword status;
OCIError *errhp = imp_sth->errhp;
OCIParam *parmhp = NULL, *collisthd = NULL, *colhd = NULL;
ub2 numcols = 0;
imp_fbh_t *fbh;
int unmatched_params;
I32 i,j;
char *p;
lob_refetch_t *lr = NULL;
STRLEN tablename_len;
char *tablename;
char new_tablename[100];
switch (imp_sth->stmt_type) {
Result :
OCIHandleAlloc(403a3550,40351580,OCI_HTYPE_STMT,0,0)=SUCCESS
OCIStmtPrepare(403c1670,403bb890,'select PTH_INF "opatchinfo" from
KRIS2.TB_ORA00902 where rowid = :rid for update',80,1,0)=SUCCESS
OCIDescriptorAlloc(403a3550,40351588,OCI_DTYPE_ROWID,0,0)
OCIBindByName
(403c1670,40351584,403bb890,":rid",placeh_len=4,value_p=40351588,value_sz=4,dty=104,indp=0,alenp=0,rcodep=0,maxarr_len=0,curelep=0
(*=0),mode=DEFAULT,0)=SUCCESS
Could you please give some feedback :
- is this a valid workaround?
- or is there a better solution for this issue?
Thanks in advance,
Kris Lemaire, Oracle DBA
This e-mail and any attached files are confidential and may contain
information which is protected by intellectual property rights. If you
are not the addressee named above any disclosure, reproduction, copying,
distribution, or other dissemination or use of this communication is
prohibited. If you have received this transmission in error, please
notify the sender immediately and destroy this e-mail.
This e-mail does not contain any professional advice and does not
constitute an offer regarding any financial, banking, insurance or other
product service toward the addressee. If you like to obtain specific
information, professional advice, an offer, or want to contract you have
to contact the KBC company mentioned above, its branch or agent.
E-mail transmission cannot be guaranteed to be secure or error free as
information could be intercepted, corrupted, lost, destroyed, arrive
late or incomplete, or contain viruses. The sender therefore does not
accept liability for any errors or omissions in the contents of this
message, and shall have no liability for any loss or damage suffered by
the user, which arise as a result of e-mail transmission.
