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','');

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.

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??

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.

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

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

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

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

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.

We'll have to look into that.

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