Re: can i run a stored procedure from Reports?

bmlam wrote:

> Since the figures I have to calculate require pretty complex
> calculation, I wrote a stored procedure in PL/SQL to compute the figures
> and store them in a table . Then I create a query with Reports to get
> the data in the report sheet . Now the point is we want to run the
> calculating procedure (1) immediately before the report is run (2).
>
> I know I can do it with a script to synchronize the 2 tasks. But a
> better way would be to imbed step 2 in the report itself, sort of in one
> of the report trigger. The trouble is you can not issue "execute
> calcuation_procedure;" in the trigger.
>
> So is there a solution to this?

Yes. I'm afraid I'm at home at the moment, so don't have access to the code we used, but we did exactly this kind of thing with several reports we have (it can simplify and speed up reports enormously).

We call the procedure from, I think, the after parameter form trigger, so just running the report does everying.

The DB procedure (we use packages with procedures contained with them) is called directly, so if the procedure is named 'pr_fred', we just use the code

pr_fred

adding, of course any parameters. I don't believe you need to include the word 'execute'.

You need to make sure that appropriate grants are given to the procedure/package and that a public synonym is created for it (or the schema named prefixed to it).

[Quoted] I hope this helps. My main point is that it CAN be done. Received on Sun Aug 08 1999 - 13:12:37 CEST