TOracleObject and synonym

[Ralf]

When I try to create a TOracleObject with a type-name 'bindparam_table' that remains in another schema I get:
object 'bindparam_table' does not exist.

Session logs in to schema A and type exists in schema B. And we created a public synonym in schema A for that type and a grant execute from schema B.

I thought (from the help) that this should work.

What am I doing wrong???

=====================

Script:

From schema B:

create or replace type bindparam_table as table of number
/

create public synonym bindparam_table for bindparam_table;

grant execute on bindparam_table to A;

From Delphi:

TOracleObject.Create(session,'bindparam_table','');

 

[Marco Kalter]

It does indeed seem that Oracle does not perform the synonym translation. You would have to do this yourself, or explicitly supply the schema name.

 

[Ralf]

But using the objectname 'bindparam_table' from stored procedures, etc in schema A does work without the prefix! Isn't that kind of the same situation??

 

[Marco Kalter]

Apparently the synonym translation is performed by Oracle when referencing an object type from PL/SQL on the server, but it is not done when referencing the object type from the Oracle Call Interfance (OCI) on the client. This is inconsistent even with other OCI functions, and I consider it an OCI bug. It's something you will have to explicitly work around though.

 

[Helene]

I thought I had a similar problem, but it didn't seem to help even if prefixing the type name.

In user A:
create or replace type OBJ AS object
(
a Char(20),
b Date,
c Date
);
create or replace type objTable
AS Table of Obj;
Grant execute on obj to B;
Grant execute on objTable to B;

From Delphi, logged in as B:
TOracleObject.Create(Session, '"A.OBJTABLE"', '');

fails, with the error message ORA-04043

(This is code which was originally generated using the package wizard, and then changed adding the prefix A. )

However, if I remove the "" around the name, it works:

TOracleObject.Create(Session, 'A.OBJTABLE', '');

If I now remove the prefix, however (thus trying to use B's private synonym to this type), another error message appears: "ORA-24372: Invalid object for describe".

So the code generated by the package wizard in this case is not valid.

With regards, Helene

 

[Marco Kalter]

If you add quotes, you need to add them to both the owner and the name:

TOracleObject.Create(Session, '"A"."OBJTABLE"', '');

 

[Helene]

OK - but is there any way I can set up DOA so that the package wizard generates correct code for me in this case? It was rather confusing when the generated code didn't work. As you say in a previous answer, this seems to be bug in the OCI - could it maybe be fixed for oracle 10G?

For now, I will simply copy the generated code into another unit after adding the owner reference.

Helene

 

[csteinme]

Hello Mark,

I opened a SR on Oracle for this subject and after a lot of questions/answers they respond to me that this is not an OCI pb.
I have oci sample which works with synonym on types.

It seems that using object types you should pass with OCI_PTYPE_UNK and not OCI_PTYPE_TYPE...

We need a fix about that because all our model is based on a schema holding only synonyms.

Thank you for your response.

 

[Marco Kalter]

We'll have to look into that.

 

[csteinme]

We have the Object Version 4.0.7.1.
Do you use Oracle DLL or your own dll for doing that ?
If it is your own DLL then i can close my Service Request.. Oracle can't do anything but if it's not the case, then it's on Oracle to rectify their DLL.
Can you answer me quickly ?
Thank you

 

[Marco Kalter]

I'm not sure I understand the question, but Direct Oracle Access directly talks to the Oracle Client Interface library (oci.dll).

 

[csteinme]

When using the method TOracleObject, are you calling self written oci code or you're just calling an oracle oci method ?

We are hitten the bug when we try to instantiate an objet type called T_TAB_VARCHAR2 wich is a synonym of a type declared in oracle like this :
create or replace type T_TAB_VARCHAR2 is table of VARCHAR2(1000)
/

TOracleObject *l_obj2 = new TOracleObject(l_ses,"T_TAB_VARCHAR2","");

I can send you the entire testcase if you want.
Thank you
Catherine

 

[Marco Kalter]

Direct Oracle Access always uses OCI to access Oracle. In this case OCIObjectNew() is called for the given type name.

 

[csteinme]

I'm sorry but i'm really not familiar with OCI, but that means that you do not write your own OCI code, here you just make a call to the OCIobjectnew() method delivered by oci.dll ?

Thank you Marko.

 

[Marco Kalter]

Yes, that is correct.

 

[csteinme]

OK ! That's exactly what i will say to Oracle.
The pb is in oci.dll
Thank you very much for your quick answer !
Catherine

 

[csteinme]

Sorry,
Oracle does not agree.
Please find here enclosed the sample they provide me.

Their answer :
Here's the analysis on bug filed from our sample code, cdemodsc.c

Please have your developers review the solution suggested and review the revised code, bde.c

a) User P_USER
- owns collection type TP_TAB_VARCHAR2
- owns package PKL_TEST referencing this type as a return from a
function and parameter to a procedure

b) User C_USER
- has execute granted on type TP_TAB_VARCHAR2
- has execute granted on package PKL_TEST
- owns synonym TP_TAB_VARCHAR2 for P_USER.TP_TAB_VARCHAR2
- owns synonym PKL_TEST for P_USER.PKL_TEST

In cdemodsc.c it calls OCIDescribeAny with type OCI_PTYPE_TYPE - ie type.So
it wouldn't be expected to find it as a synonym which is type OCI_PTYPE_SYN.
If you want it to allow for any type of object you must pass the type as
OCI_PTYPE_UNK then write code to switch on the OCI_ATTR_PTYPE attribute
returned accordingly in order to decide how to proceed.In the case of a
synonym that would be to access OCI_ATTR_SCHEMA_NAME, OCI_ATTR_NAME,
OCI_ATTR_LINK then call OCIDescribeAny again passing those values.

If I run:

cdemodsc C_USER C_USER P_USER.TP_TAB_VARCHAR2

I get the type attributes whereas:

cdemodsc C_USER C_USER TP_TAB_VARCHAR2(--> synonym)

and:

cdemodsc C_USER C_USER P_USER.PKL_TEST(--> package)

both fail as neither can be found as a type.

Uploaded bde.c which is cdemodsc.c but allows you to pass either a type or
synonym to a type by calling OCIDescribeAny with OCI_PTYPE_UNK.If it gets
back a type it proceeds as before.If it gets back a synonym then it
extracts the details and calls OCIDescribeAny again with OCI_PTYPE_TYPE and
those details.

I will post you this file bde.c.
Thank you
Catherine

 

[csteinme]

Do you think it's possible to send the piece of your code calling OCIObjectNew() ?
Thank you
Catherine STEINMETZ

 

[Marco Kalter]

If you can send an e-mail to support@allroundautomations.com we can arrange this.

 

[Pierretim]

Is there an update on this problem ? If I understand the problem is in the DOA code, not in the Oracle's OCI code and so Oracle won't fix ? Is there a fix from allroundautomations then?

We can workaround the issue by prefixing all TYPE with the owner schema, but of course if there is a fix it would be better

Thanks and regards

Pierre