Xref: alice comp.databases.oracle.tools:29812 Path: alice!news-feed.fnsi.net!newsfeed.icl.net!btnet-peer!btnet!diablo.theplanet.net!news.theplanet.net!newspost.theplanet.net!not-for-mail From: "Simon Hedges" Newsgroups: comp.databases.oracle.tools Subject: Re: can i run a stored procedure from Reports? Date: Sun, 8 Aug 1999 12:12:37 +0100 Organization: Customer of Planet Online Lines: 38 Message-ID: <7ojomg$dn4$1@news6.svr.pol.co.uk> References: <37AB4A63.A4C89830@online.de> X-Trace: news6.svr.pol.co.uk 934110736 14052 62.136.157.242 (8 Aug 1999 11:12:16 GMT) NNTP-Posting-Date: 8 Aug 1999 11:12:16 GMT X-Complaints-To: abuse@theplanet.net X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2014.211 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2014.211 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). I hope this helps. My main point is that it CAN be done.