TOracleObject and synonym
|
Joined: Feb 2004
Posts: 41
Member
|
OP
Member
Joined: Feb 2004
Posts: 41 |
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','');
|
|
|
Re: TOracleObject and synonym
|
Joined: Aug 1999
Posts: 22,221
Member
|
Member
Joined: Aug 1999
Posts: 22,221 |
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.
Marco Kalter Allround Automations
|
|
|
Re: TOracleObject and synonym
|
Joined: Feb 2004
Posts: 41
Member
|
OP
Member
Joined: Feb 2004
Posts: 41 |
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??
|
|
|
Re: TOracleObject and synonym
|
Joined: Aug 1999
Posts: 22,221
Member
|
Member
Joined: Aug 1999
Posts: 22,221 |
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.
Marco Kalter Allround Automations
|
|
|
Re: TOracleObject and synonym
|
Joined: Nov 1999
Posts: 108 Bod
Member
|
Member
Joined: Nov 1999
Posts: 108 Bod |
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
|
|
|
Re: TOracleObject and synonym
|
Joined: Aug 1999
Posts: 22,221
Member
|
Member
Joined: Aug 1999
Posts: 22,221 |
If you add quotes, you need to add them to both the owner and the name:
TOracleObject.Create(Session, '"A"."OBJTABLE"', '');
Marco Kalter Allround Automations
|
|
|
Re: TOracleObject and synonym
|
Joined: Nov 1999
Posts: 108 Bod
Member
|
Member
Joined: Nov 1999
Posts: 108 Bod |
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
|
|
|
Re: TOracleObject and synonym
|
Joined: Feb 2011
Posts: 8
Member
|
Member
Joined: Feb 2011
Posts: 8 |
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.
|
|
|
Re: TOracleObject and synonym
|
Joined: Aug 1999
Posts: 22,221
Member
|
Member
Joined: Aug 1999
Posts: 22,221 |
We'll have to look into that.
Marco Kalter Allround Automations
|
|
|
Re: TOracleObject and synonym
|
Joined: Feb 2011
Posts: 8
Member
|
Member
Joined: Feb 2011
Posts: 8 |
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
|
|
|
Re: TOracleObject and synonym
|
Joined: Aug 1999
Posts: 22,221
Member
|
Member
Joined: Aug 1999
Posts: 22,221 |
I'm not sure I understand the question, but Direct Oracle Access directly talks to the Oracle Client Interface library (oci.dll).
Marco Kalter Allround Automations
|
|
|
Re: TOracleObject and synonym
|
Joined: Feb 2011
Posts: 8
Member
|
Member
Joined: Feb 2011
Posts: 8 |
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
|
|
|
Re: TOracleObject and synonym
|
Joined: Aug 1999
Posts: 22,221
Member
|
Member
Joined: Aug 1999
Posts: 22,221 |
Direct Oracle Access always uses OCI to access Oracle. In this case OCIObjectNew() is called for the given type name.
Marco Kalter Allround Automations
|
|
|
Re: TOracleObject and synonym
|
Joined: Feb 2011
Posts: 8
Member
|
Member
Joined: Feb 2011
Posts: 8 |
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.
|
|
|
Re: TOracleObject and synonym
|
Joined: Aug 1999
Posts: 22,221
Member
|
Member
Joined: Aug 1999
Posts: 22,221 |
Marco Kalter Allround Automations
|
|
|
Re: TOracleObject and synonym
|
Joined: Feb 2011
Posts: 8
Member
|
Member
Joined: Feb 2011
Posts: 8 |
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
|
|
|
Re: TOracleObject and synonym
|
Joined: Feb 2011
Posts: 8
Member
|
Member
Joined: Feb 2011
Posts: 8 |
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
|
|
|
Re: TOracleObject and synonym
|
Joined: Feb 2011
Posts: 8
Member
|
Member
Joined: Feb 2011
Posts: 8 |
Do you think it's possible to send the piece of your code calling OCIObjectNew() ? Thank you Catherine STEINMETZ
|
|
|
Re: TOracleObject and synonym
|
Joined: Aug 1999
Posts: 22,221
Member
|
Member
Joined: Aug 1999
Posts: 22,221 |
If you can send an e-mail to support@allroundautomations.com we can arrange this.
Marco Kalter Allround Automations
|
|
|
Re: TOracleObject and synonym
|
Joined: Apr 2013
Posts: 1
Member
|
Member
Joined: Apr 2013
Posts: 1 |
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
|
|
|
|
|