Hi *,
I'm using DBD::Oracle (code checked onCPAN and verified in version 1.21) and encountered a p
roblem (DB is Oracle 10gR2) related to LOBs and synonyms:

Table TTHUMBNAIL (DOCUMENT_ID NUMBER(12), THUMBNAIL BLOB NOT NULL) is owned by user DMSSGADM
. User DMSSGDP has a synonym TTHUMBNAIL on the table and all necessary rights granted.

When executing the following code

$sthInsertTTHUMBNAIL = prepareStatement($dbh,
                                        'INSERT ' .
                                        'INTO    tthumbnail ' .
                                        '       (document_id, ' .
                                        '        thumbnail) ' .
                                        'VALUES (document_id_seq.CURRVAL, ' .
                                        '        ?)');
$sthInsertTTHUMBNAIL-> bind_param(1, $thumbnail, { ora_type => ORA_BLOB });
$sthInsertTTHUMBNAIL-> execute();

I get the following error:

ORA-04043: object DMSSGADMTTHUMBNAIL.TTHUMBNAIL does not exist (DBD SUCCESS: OCIDescribeAny(
view)/LOB refetch)

Currently I tracked down the error to the following place in oci8.c, but since I got no comp
iler here, I can't verify further:

01 #ifdef OCI_ATTR_OBJ_NAME /* not in 8.0.x */
02     OCIDescribeAny_log_stat(imp_sth-> svchp, errhp, tablename, strlen(tablename),
03 		(ub1)OCI_OTYPE_NAME, (ub1)1, (ub1)OCI_PTYPE_SYN, dschp, status);
04     if (status == OCI_SUCCESS) { /* There is a synonym, get the schema */
05       char new_tablename[100];
06       char *syn_schema=NULL,  *syn_name=NULL;
07       OCIAttrGet_log_stat(dschp,  OCI_HTYPE_DESCRIBE,
08				  &parmhp, 0, OCI_ATTR_PARAM, errhp, status);
09       OCIAttrGet_log_stat(parmhp, OCI_DTYPE_PARAM,
10			  &syn_schema, 0, OCI_ATTR_SCHEMA_NAME, errhp, status);
11       OCIAttrGet_log_stat(parmhp, OCI_DTYPE_PARAM,
12			  &syn_name, 0, OCI_ATTR_OBJ_NAME, errhp, status);
13       strcpy(new_tablename, syn_schema);
14       strcat(new_tablename, ".");
15       strcat(new_tablename, syn_name);
16       tablename=new_tablename;
17       if (DBIS-> debug >= 3)
18 	PerlIO_printf(DBILOGFP, "       lob refetch synonym, schema=%s, name=%s, new tablename=%
s\n", syn_schema, syn_name, tablename);
19     }
20 #endif /* OCI_ATTR_OBJ_NAME */

First, lines 05 & 16 constitute a very bad situation, since the pointer refers to a stack va
riable that is freed when the block is closed :-( So this buffer should be declared outside 
the block, anyway...

And from what I know about OCIAttrGet, if you retrieve a text attribute, you have to pass a 
pointer to receive the length of the string, as it is not '\0' terminated! After that you wo
uld call something like

strncpy(new_tablename, syn_schema, syn_schema_length);
strcpy (new_tablename, ".");
strncpy(new_tablename, syn_name, syn_name_length);

I'm looking forward to hearing from you about the issue, since I'm really stuck on the topic
.

Sincerely,

  Mirko Kraft

UBS AG
Global Wealth Management & Business Banking
Information Technology
eRMS - electronic Records Management Solutions
Viaduktstr. 31-35, PO Box 4473, CH-4051 Basel
Tel. +41-61-288 53 72
Fax +41-61-288 71 91
www.ubs.com

Based on previous e-mail correspondence with you and/or an agreement reached with you, UBS c
onsiders itself authorized to contact you via unsecured e-mail.
Warning:
(a) E-mails can involve SUBSTANTIAL RISKS, e.g. lack of confidentiality, potential manipulat
ion of contents and/or sender's address, incorrect recipient (misdirection), viruses etc. UB
S assumes no responsibility for any loss or damage resulting from the use of e-mails. UBS re
commends in particular that you do NOT SEND ANY SENSITIVE INFORMATION, that you do not inclu
de details of the previous message in any reply, and that you enter e-mail address(es) manua
lly every time you write an e-mail.
(b) As a matter of principle, UBS does NOT accept any ORDERS, revocations of orders or autho
rizations, blocking of credit cards, etc., sent by e-mail. Should such an e-mail nevertheles
s be received, UBS is not obliged to act on or respond to the e-mail.
Please notify UBS immediately if you received this e-mail by mistake or if you do not wish t
o receive any further e-mail correspondence. If you have received this e-mail by mistake, pl
ease completely delete it (and any attachments) and do not forward it or inform any other pe
rson of its contents.