3 Replies Latest reply on Mar 23, 2018 2:53 PM by rp0428

    Trace stored proc with Oracle SQL developer?

    BeckyJohn

      Hi,

       

      I use 'Oracle SQL developer' tool to test the backend of the application.

       

      I would need tracing of the stored proc with SQL developer tool. Does anyone has expirience with this on how can I set up to start tracing?

       

      On my previous project, I was using SQL visual studio management and Profiler was part of this tool. So, I used Profieler to track real time the stored proc real time.

       

      Now I'm trying to find something simular for Oracle SQL developer.

       

      If anyone has expirience with this, let me know.

       

      Thanks!

        • 1. Re: Trace stored proc with Oracle SQL developer?
          BluShadow

          *** Moderator Note: I've moved your question over to the SQL Developer space.  Whilst the Getting Started group is a great place to introduce yourself and get to learn about the community, it's not the best place to ask product related questions, as you're unlikely to find the specific product experts hanging around there.  Instead, use the search functionality, or the Browser burger menu to locate the space that is most appropriate for your technology/product and ask there.  You'll find more experts willing to help in those spaces.

          • 2. Re: Trace stored proc with Oracle SQL developer?
            BPeaslandDBA

            SQL Server's Profiler lets you see what a session(s) is doing and watch what it executes. Oracle has a few products that do similar. Products like Enterprise Manager and its functionality that does this requires you to license the optional Diagnostics Pack. In Oracle, you can also start a SQL Trace in a session. https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

             

            If all you're looking for is to trace the execution of a stored proc, then Oracle let's you compile that proc for Debug and then you can step through the proc just like you can with any programming languages IDE. You can do this in SQL Dev by right-clicking on the proc and selecting Compile for Debug. The object will now have a green lady bug symbol on it indicating such. Then right click and choose Debug. Supply input values and you're off and running. When you're done, right click and choose Compile to turn off the Debug option.

             

            Another things you can do in SQL Dev is click on View --> DBA. Then add one of your connections to the DBA panel and connect to your database. Then go to Database Status --> Instance Viewer. Once that's up, double click on the Sessions panel. This will show you the current sessions in the instance. Click on one and the bottom panel will show you what its up to. There is a refresh pull down to select how often to refresh that info.

             

            Cheers,
            Brian

            • 3. Re: Trace stored proc with Oracle SQL developer?

              I would need tracing of the stored proc with SQL developer tool. Does anyone has expirience with this on how can I set up to start tracing?

              Yes - Oracle has experience with it and they wrote all about it in the Sql Developer User Guide.

               

              That doc covers ALL of the Sql Dev functionality and how to use it.