Skip to Main Content
  • Questions

  • Best debuging steps for PLSQL procedure AND SQL Querys

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, kanaka.

Asked: July 05, 2019 - 1:11 pm UTC

Last updated: July 11, 2019 - 2:56 pm UTC

Version: oracle 11g

Viewed 1000+ times

You Asked

Hi Team,

In my project, we have a 100 of the procedure and those procedures we have 1000 to 5000 lines of codes. we need to debugging those procedures.
Is there any best methods, techniques for debugging plsql procedure and SQL queries.


this website is very usefully for me and thanks you mush for providing knowledge.

and Connor said...

I'd look at using Logger - powerful and free.

https://github.com/OraOpenSource/Logger

Basically you populate your code with calls to the logger, and it provides many mechanisms for capturing and monitoring that information from other sessions.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

That is not really an answer

Steef D., July 08, 2019 - 9:16 pm UTC

The question was how to debug all those procedures the best way, so what are the best tools for debugging, not for logging.
Chris Saxon
July 11, 2019 - 2:56 pm UTC

Adding instrumentation - logging - is a great way to debug your code. Provided you capture all the relevant information, you can walk back through the log to see what happened.

A reader, July 09, 2019 - 8:46 am UTC

Use third party tool PLSQL Developer and SQL Developer ( Oracle) and Toad for Oracle.

on Debugging from SQL Developer

Rajeshwaran Jeyabal, July 10, 2019 - 7:33 am UTC

The Oracle SQL Developer Product manager "Jeff smith" has done some blog post and video's about how to debug PL/SQL using SQL Developer.

https://www.thatjeffsmith.com/archive/2014/02/how-to-start-the-plsql-debugger/

https://www.thatjeffsmith.com/archive/2019/02/video-pl-sql-debugger-demo/

https://www.youtube.com/watch?v=rBA8gL8NyXE

https://www.youtube.com/watch?v=Ybe04HkE6Bs

Hope this helps.
Chris Saxon
July 11, 2019 - 2:52 pm UTC

Thanks for sharing these.

May be you are looking for...

Imran, July 18, 2019 - 6:48 am UTC

You may use DBMS Profiler which not only gives you info on the flow of code but also stats on each procedure/function and line of code , no. of times a line is executed and time taken. Based on these stats you may take decision n change code appropriately.
More info on how to configure and run this utility can be found at http://www.dba-oracle.com/t_dbms_profiler.htm


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library