Home » SQL & PL/SQL » SQL & PL/SQL » Display results from Oracle Stored Procedure (Toad 9.0, Oracle 9i)
Display results from Oracle Stored Procedure [message #338746] Tue, 05 August 2008 20:50 Go to next message
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 #338749 is a reply to message #338746] Tue, 05 August 2008 20:54 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
You lose.
I want to teach my pig to fly.
Tell me how to do this.

http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above


invoke sqlplus & do
SQL> SET TIME ON
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 Go to previous messageGo to next message
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 #338757 is a reply to message #338746] Tue, 05 August 2008 21:10 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
TheSingerman,
Messages: 33
>Otherwise, the folks here can be pretty brutal.
It appears you are a faster learner & will fit in well here.
BTW, I agree with your post.
Re: Display results from Oracle Stored Procedure [message #338770 is a reply to message #338755] Tue, 05 August 2008 21:31 Go to previous messageGo to next message
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 #338771 is a reply to message #338746] Tue, 05 August 2008 22:05 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
alter session set sql_trace=true;
& then invoke the procedure to produce the trace file.
Processing the trace file with TKPROF will show where time is being spent.

BTW - You really should have a test instance & be doing all this debugging against it.
Re: Display results from Oracle Stored Procedure [message #339136 is a reply to message #338746] Wed, 06 August 2008 21:09 Go to previous messageGo to next message
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.
Re: Display results from Oracle Stored Procedure [message #339249 is a reply to message #338746] Thu, 07 August 2008 02:44 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can use the supplied package DBMS_PROFILER to provide information on how long the various parts of a package take to execute, without changing the package.
Previous Topic: Insert Blob
Next Topic: Need help building my first Oracle Trigger
Goto Forum:
  


Current Time: Wed Dec 07 20:08:03 CST 2016

Total time taken to generate the page: 0.12098 seconds