Page 1 of 1
ORA-24372 - invalid object for describe on Oracle 9i
Posted: Wed 11 Feb 2009 09:33
by PETER_99
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?
Posted: Thu 12 Feb 2009 11:38
by Plash
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;
Posted: Mon 16 Feb 2009 11:17
by PETER_99
I'm so sorry, In fact.
I was thinking about change the source code of ""OCL"".
How can I do that?
..........
initProcParams8(name, overload);
Posted: Mon 16 Feb 2009 11:48
by jfudickar
Hi Oleg,
isn't this change a little bit risky?
What about if you have no rights to do this?
Regards
Jens
Posted: Fri 20 Feb 2009 09:14
by Plash
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.
Posted: Fri 20 Feb 2009 13:02
by Shalex
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.
Posted: Fri 20 Feb 2009 13:08
by jfudickar
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
Posted: Mon 23 Feb 2009 02:19
by PETER_99
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