Display results from Oracle Stored Procedure [message #338746] |
Tue, 05 August 2008 20:50 |
skas502
Messages: 2 Registered: August 2008
|
Junior Member |
|
|
I have a stored procedure in Oracle and I want to display the results to debug without changing anything in the stored procedure. The procedure is running in production. Also I would like to monitor the performance and execution time. Tool I am using is TOAD 9.0.
This is how I am calling the stored procedure from Edit window:
DECLARE
O_REPORT rptCursor.ReportCursor;
BEGIN
PKGREPORT.GETMONTHLYBALANCE ( O_REPORT );
END;
|
|
|
|
Re: Display results from Oracle Stored Procedure [message #338755 is a reply to message #338746] |
Tue, 05 August 2008 21:02 |
TheSingerman
Messages: 49 Registered: April 2008 Location: Brighton, Michigan
|
Member |
|
|
You put a stored procedure in production and now you want to debug it?
In this case, I would assume that you are getting some error message. Since AMD has pushed back the date of the "Read Mind" instruction, it would help if you furnished it.
It's against my religion to use Toad, but I think it is possible to use it to step through a stored procedure. You may not be allowed to use that functionality in production however.
I have some strong guesses as to what is syntactically wrong with your debugging attempt - but - why should I guess, when all you have to do is reveal the package heading of your stored procedure? Along with the definitions of any object types used in it.
Follow these suggestions, and you might get some help. Otherwise, the folks here can be pretty brutal.
|
|
|
|
Re: Display results from Oracle Stored Procedure [message #338770 is a reply to message #338755] |
Tue, 05 August 2008 21:31 |
skas502
Messages: 2 Registered: August 2008
|
Junior Member |
|
|
Stored procedure is working fine in production except it is taking longer to return results.
also i just want to find out how one can debug stored procedure without inserting anything directly into stored procedure. if there is a scenario when you cannot change the stored proc directy in prod but want to debug or display the results after running it. when i run the stored proc it only shows that procedure completed successfully.
here is my stored procedure:
PROCEDURE GETMONTHLYBALANCE(
o_Report OUT ReportCursor)
IS
cbCursor ReportCursor;
V_SQL VARCHAR2(2000);
BEGIN
OPEN cbCursor FOR 'SELECT DISTINCT ........';
o_Report := cbCursor;
END GETMONTHLYBALANCE
|
|
|
|
Re: Display results from Oracle Stored Procedure [message #339136 is a reply to message #338746] |
Wed, 06 August 2008 21:09 |
TheSingerman
Messages: 49 Registered: April 2008 Location: Brighton, Michigan
|
Member |
|
|
Quote: | Stored procedure is working fine in production except it is taking longer to return results.
|
As a rough guess, 95% of all sites have a much greater volume of data in their production system than they do in development. Of those 95%, 100% will find queries and functions and procedures which take longer to run in production than development. Either get a development system which has a load approximating that of production (you can call it "quality assurance; management types like that) or learn to live with queries taking longer in production.
If you are one of the 5% who have a proper QA environment, then please accept my appologies for being flippant. You have a deeper problem, and will need to use trace and TKPROF.
Quote: | also i just want to find out how one can debug stored procedure without inserting anything directly into stored procedure...
|
I put these words:
Quote: | how to debug an oracle ref cursor in sqlplus
|
into Google, and got over 1,000 hits. The fourth one was a very good article by Kevin Meade right on Orafaq. I think it will answer you questions here.
|
|
|
|