Home » SQL & PL/SQL » SQL & PL/SQL » Procedure executed from where??????
icon8.gif  Procedure executed from where?????? [message #351846] Fri, 03 October 2008 07:47 Go to next message
Derek N
Messages: 80
Registered: September 2002
Member
I have a report that has a stored procedure, create_balances, that was run from the 'Before Report' trigger. The procedure stores data in a temporary table. The report uses this temporary table. The customer complained that the report does not balance. On opening the report I discovered that the procedure was commented out. I searched the report and then the server using ultra-edit looking for occurances of the procedure named 'create_balances'. The only occurance returned is the report with the commented out procedure.

I then searched the database using PL/SQL developer and it only returned the stored procedure. I have asked the user to run the report again on a different day and it still picks up different values. This is the original report. Where else an I look to see where this procedure is being triggered off from??

Re: Procedure executed from where?????? [message #351859 is a reply to message #351846] Fri, 03 October 2008 08:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If the report is based on the temporary table, and that it a true temporary table, it is of no interest who else calls the procedure, since temporary tables only show content inserted through the current session.
Re: Procedure executed from where?????? [message #351867 is a reply to message #351846] Fri, 03 October 2008 09:06 Go to previous messageGo to next message
Derek N
Messages: 80
Registered: September 2002
Member
That's the issue. The procedure runs a few statements and inserts into the temporary table. This is the only stored proc that inserts into this table. Call to this proc in the report has been commented out. So, from where is this procedure being run???
Re: Procedure executed from where?????? [message #351870 is a reply to message #351867] Fri, 03 October 2008 09:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is this a genuine Global Temporary Table or just a regular table that you use to store temporary data in?
Re: Procedure executed from where?????? [message #351872 is a reply to message #351846] Fri, 03 October 2008 09:27 Go to previous messageGo to next message
Derek N
Messages: 80
Registered: September 2002
Member
Its a normal database table used to store data thats deleted each time the report is run?
Re: Procedure executed from where?????? [message #351873 is a reply to message #351872] Fri, 03 October 2008 09:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If the data is deleted each time the report is run, it should not be so hard to monitor which process is inserting on the table, unless running the report takes a considerable amount of time.
Re: Procedure executed from where?????? [message #351875 is a reply to message #351846] Fri, 03 October 2008 09:43 Go to previous message
Derek N
Messages: 80
Registered: September 2002
Member
Thanks. Would get our DBA's to monitor.

Previous Topic: Error in Trigger
Next Topic: migrating data from different tables into a single table
Goto Forum:
  


Current Time: Sat Dec 10 00:55:43 CST 2016

Total time taken to generate the page: 0.17107 seconds