Go Premium for a chance to win a PS4. Enter to Win


sql trace for debugging?

Posted on 2011-09-06
Medium Priority
Last Modified: 2012-05-12
Has anybody ever used sql trace for debugging?
For example if I run a PL/SQL block and get a "no data found" error then
can I run the same pl/sql block with trace on this time to find which statement
the error is occuring at?
Thanks in advance.
Question by:subratoc
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 1600 total points
ID: 36492800
I typically add my own debug statements for pl/sql code.

I've never used this but check out: DBMS_DEBUG

Assisted Solution

sivaprakasam earned 200 total points
ID: 36499608
I dont think so ... TKprof does show the parsing errors  but not the other exceptions. It's easy to handle the exceptions inside the pl/sql block ...
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 200 total points
ID: 36503158
For debug use
set serveroutput on

and in your procedure use the following where needed.

Tracing is mainly used for other problems such as procedure runs slow and you  want to know where oracle has spend the time, execution plan etc. however you can take littlebit of idea that how many times particular query got executed, whether particular query ran or not (if not run then query will not be there).
But I would say, you can put your own comments for output to see where the problem is.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Screencast - Getting to Know the Pipeline

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question