Hi!
I'm trying to execute a report based on DB Stored Procedure.
If SP is rightly compiled, the report runs without problems but, if SP is uncompiled, the report shows the ORA-24372 invalid object for describe message.
Sometimes a SP related DB object is changed and it's causes this SP is uncompiled.
I need re-compile this SP at run-time while reports is executed.
How Can I do this?
====================================
Product: WF 7.1.1
DB: Oracle 9i

Giovanni,

You can create a focexec that calls a stored procedure that compiles objects that you specify.

In your focexec, you would have something like:

-REMOTE BEGIN
SQL SQLORA SET SERVER connect_string
SQL SQLORA
EX COMPILE_PROCEDURE
-REMOTE END

Then in the PL/SQL procedure called COMPILE_PROCEDURE, you would have code to retrieve the objects from the table user_objects. If the status column is 'INVALID', then perform something like

EXECUTE IMMEDIATE ('ALTER '|| cursorname.object_type || ' '|| cursorname.object_name || ' COMPILE');

Hope this helps.
Sean