Calling a stored procedure through a view/query/etc

[rinogo]

Hello!

I'm using Crytal Reports to generate reports out of our Oracle-backed app. The problem that I've run into is that one of our stored procedures needs to be executed before a particular report will return accurate data.

Typically, one would simply configure Crystal Reports to execute the query. Unfortunately, due to special circumstances (or a misconfiguration), this is not possible.

I need to execute the stored procedure indirectly, via a view, query, or something else. Thanks for any suggestions!

-Rich :rolleyes:

[slimdave]

http://support.businessobjects.com/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf

[gamyers]

A stored proceudre cannot be called from a SELECT, but a function can be.
Wrap your stored procedure in a dummy function and a
SELECT dummy FROM dual will execute it.
Almost certainly you'll need to define the function as an AUTONOMOUS_TRANSACTION since otherwise Oracle wouldn't allow any stored procedure called by it to update the database.

FUNCTION dummy RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
procedure_call_here;
COMMIT;
RETURN NULL;
END dummy;