Home » Developer & Programmer » Precompilers, OCI & OCCI » OciDescribeAny on procedure in package gives error ORA-04043 (Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production)
OciDescribeAny on procedure in package gives error ORA-04043 [message #684495] Tue, 15 June 2021 00:36 Go to next message
ovri
Messages: 4
Registered: June 2021
Junior Member
I use OCI 19.11 (also tried 12.2), Oracle instant client light 32-bit with Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production.

I am trying to run OciDescribeAny on an procedure in a package. The object name is of the form <package>.<procedure>. The resulting error is ORA-04043: object <package>.<procedure> does not exist.

And yes: The procedure does exist in the database, and can be listed using sql plus.

The call is:

OCIDescribeAny(fdptr->svchp, fdptr->errhp, (void *)objnam, (ub4) strnlen((const char *)objnam, OSIZE-1), (ub1)OCI_OTYPE_NAME, (ub1)OCI_DEFAULT, (ub1) OCI_PTYPE_PROC, dschp);

I am in the process of upgrading from ORA7 and ORA8 to ORA12 OCI calls. This worked in the past using odessp from ORA7.

Is a new approach required with OciDescribeAny? Is there any example of this somewhere?
Re: OciDescribeAny on procedure in package gives error ORA-04043 [message #684615 is a reply to message #684495] Thu, 08 July 2021 10:34 Go to previous messageGo to next message
NevemTeve
Messages: 25
Registered: November 2017
Junior Member
Does it work if the parameter the package itself? Elements of a package aren't schema-objects on their own.
Re: OciDescribeAny on procedure in package gives error ORA-04043 [message #684616 is a reply to message #684615] Fri, 09 July 2021 00:49 Go to previous messageGo to next message
ovri
Messages: 4
Registered: June 2021
Junior Member
Thank you very much for responding, I am really stuck on this issue and I very much appreciate your help!
Yes, there is no error message if I set the object name to just the package name, and the object type to OCI_PTYPE_PKG.
Does this mean I cannot use OCIDescribeAny tho get the same information about the procedure that I was able to get with odessp for Oracle 7?
If not, how can I do it?
The following information about the procedure was retrieved with odessp - ovrld, radix, mode etc, I think pretty much all outputs of odessp are used in the code.
Re: OciDescribeAny on procedure in package gives error ORA-04043 [message #684617 is a reply to message #684616] Fri, 09 July 2021 06:09 Go to previous messageGo to next message
NevemTeve
Messages: 25
Registered: November 2017
Junior Member
Maybe it is possible, but schema-name is mandatory in this context? Try these:
schema.package           - I suppose it will work
package.procedure        - I suppose it won't work
schema.package.procedure - I think it might work
Re: OciDescribeAny on procedure in package gives error ORA-04043 [message #684626 is a reply to message #684617] Tue, 13 July 2021 06:13 Go to previous messageGo to next message
ovri
Messages: 4
Registered: June 2021
Junior Member
Thank you for making these suggestions!
It does not seem to matter if the schema name is included or not.
Both just package and schema.package work when the object type is OCI_PTYPE_PKG.
Neither package.procedure nor schema.package.procedure work when the object type is OCI_PTYPE_PROC.
Maybe the schema name is included in the service context structure so it does not need to be explicitly set?
Re: OciDescribeAny on procedure in package gives error ORA-04043 [message #684666 is a reply to message #684626] Mon, 19 July 2021 01:40 Go to previous messageGo to next message
NevemTeve
Messages: 25
Registered: November 2017
Junior Member
Did you try "schema.package.procedure" with OCI_PTYPE_PKG?
Re: OciDescribeAny on procedure in package gives error ORA-04043 [message #684667 is a reply to message #684666] Mon, 19 July 2021 01:52 Go to previous message
ovri
Messages: 4
Registered: June 2021
Junior Member
I tried that too, without any luck.
I gave up on this approach, and I am not sure how well it would work with overloaded procedures anyway. The old odessp call would return information for all instances of the procedure (or function), while I think OciDescribeAny could only return a handle to one instance anyway? I would be interested to hear if somebody has experience with this.
I had more luck with a nested approach, where I run OciDescribeAny on the package, then get a list of all procedures. I then go through the list and do a string compare with the procedure name, and get a handle to each matching procedure. From these I can get all the information I need about the procedure instances, and build output that mimics the odessp output. I have not figured out how to differentiate between functions and procedures, but luckily I only need procedures.
Supplementary information and information about alternate approaches is welcome. Thank you for your help, Neve - the ideas for the alternate approach came to me while trying out your suggestions Smile
Previous Topic: Pro C compile error
Next Topic: sqlnet.log: No message file for product=network, facility=TNS
Goto Forum:
  


Current Time: Thu Mar 28 04:47:58 CDT 2024