ORA-24372 - invalid object for describe on Oracle 9i

PETER_99 · Post by **PETER_99** » Wed 11 Feb 2009 09:33

During the process to execute a stored procedure using ODAC and TOraStoredProcedure component, the OCI function OCIDescribeAny() is called to retrieve the procedure parameters. This function returns an ORA-24372 error if the stored procedure is invalid for any reason. Oracle releases 10 and 11 forces the recompilation of the object when describe is executed but that is not the behavior on Oracle 9i.
I was thinking about change the source code of ODAC to force a recompilation and another call to OCIDescribeAny before the error was raised. How can I do that?
Any other ideas?

Plash · Post by **Plash** » Thu 12 Feb 2009 11:38

You can replace the following lines in the TOCICommand.CreateProcCall procedure:

Code: Select all

  if NeedDescribe then
    InitProcParams(ProcName, Overload);

with the following code:

Code: Select all

var
  Command: TOCICommand;
...
 
  if NeedDescribe then
    try
      InitProcParams(ProcName, Overload);
    except
      on E: EOraError do begin
        if E.ErrorCode = 24372 then begin
          FConnection.CheckCommand;
          Command := FConnection.FCommand;
          Command.SetSQL('ALTER PROCEDURE ' + ProcName + ' COMPILE');
          Command.Execute;
 
          InitProcParams(ProcName, Overload);
        end
        else
          raise;
      end;

PETER_99 · Post by **PETER_99** » Mon 16 Feb 2009 11:17

I'm so sorry, In fact.

I was thinking about change the source code of ""OCL"".
How can I do that?
..........
initProcParams8(name, overload);

jfudickar · Post by **jfudickar** » Mon 16 Feb 2009 11:48

Hi Oleg,

isn't this change a little bit risky?

What about if you have no rights to do this?

Regards
Jens

Plash · Post by **Plash** » Fri 20 Feb 2009 09:14

To jfudickar:
I think that this is not a good solution too. User application should not recompile stored procedures.
But if Peter needs such functionallity, we will try to help.

To Peter:
I have sent your question to our OCL team. Please wait for reply.

Shalex · Post by **Shalex** » Fri 20 Feb 2009 13:02

Automatical procedure compilation occurs during an execution of the query with invoking the stored procedure only. But there is no compilation when you are getting parameters. In this case make the compilation of procedure manually. We think that the source code should not be changed. The user has to decide whether he needs to make compilation or not.

jfudickar · Post by **jfudickar** » Fri 20 Feb 2009 13:08

An other way is to execute a procedure of the package.

For example the packages in my company named "module_version" which returns a constant version name and number.

This procedure could be called without any side effects.

So this procedure could be called, but this is not a general solution.

Regards
Jens

PETER_99 · Post by **PETER_99** » Mon 23 Feb 2009 02:19

Dear Plash,
I need this function.
This is an urgent issue in my company,
please help me to slove this problem as soo as possible.

Regards
Peter