13 Replies Latest reply on Sep 6, 2007 5:03 PM by 575370

    SQL Developer and NetBeans

    575370
      Is it possible to call a PL/SQL function from JDBC code running in NetBeans IDE and to set break points within the function code using SQL Developer?
      My intention is to debug PL/SQL code (using SQL Developer) which is being executed from a 3rd party IDE.

      Thank you,
      Mark.
        • 1. Re: SQL Developer and NetBeans
          -K-
          Yes, it is called remote debugging.
          Doesn't matter where called from. If you hook into the debugger from your remote session, you can step into your local code editor.
          See the item in the help contents: SQL Developer Concepts and Usage - Running and Debugging Functions and Procedures - Remote Debugging.

          Regards,
          K.
          • 2. Re: SQL Developer and NetBeans
            575370
            See the item in the help contents: SQL Developer Concepts and Usage - Running and Debugging Functions and Procedures - Remote Debugging.
            Do you mean NetBeans help or some of the Oracle documentation?

            Thanks.
            • 3. Re: SQL Developer and NetBeans
              d_wilhelm
              I doubt that there is something about the "SQL Developer Concepts and Usage" in the NetBeans help. Look directly in the help in SQL Developer application.
              • 4. Re: SQL Developer and NetBeans
                575370
                I’ve read the help, but I am missing something here.
                Here is the situation:
                1.     I am running Oracle XE database, SQL Developer and NetBeans on the same machine.
                2.     I succeeded to use SQL Developer to connect to the DB, to see PL/SQL code of a function I developed and to debug this function from SQL Developer environment.
                3.     NetBeans is opened with a Java projects (JDBC application) that calls the same function form the same DB. I created a CallableStatement and I can execute the function from my Java code, but I can’t step into the PL/SQL code when I call the following line of code: myCallableStatement.execute();
                     (Note: there is no doubt the PL/SQL function was called by the Java code – I      can see results in a DB table.)

                Here is what I want to do and I don’t know exactly how to do that:

                1.     Once myCallableStatement.execute() is being executed, I want to step into the PL/SQL code within NetBeans IDE or if it is not possible using NetBeans, to attach SQL Developer to the invocation of my PL/SQL function so the SQL      Developer will show me the PL/SQL code that is being executes, so I will be able to put      breakpoints in the PL/SQL code and debug it within SQL Developer.

                I am puzzled on how to attached SQL Developer to the process that is running in NetBeans IDE.

                Thanks for any help!!!

                Regard,
                Mark.

                Message was edited by:
                MarcoPolo
                • 5. Re: SQL Developer and NetBeans
                  -K-
                  Hi Mark,

                  I don't know about NetBeans, but I don't think it has a PL/SQL debugger, so quite logically you won't be able to debug inside it's IDE. As said before: remote debugging with sqldev is what you want.

                  Nothing to be puzzled about how sqldev attaches to the NetBeans' session: you explicitly call CONNECT_TCP to attach, don't you?

                  Just remember 1) to compile your PL/SQL for debug, and 2) to put your breakpoints before running, else you won't be able to step in there, will you?

                  If you're still unable to get it working, post the steps and details of what you're doing...

                  K.
                  • 6. Re: SQL Developer and NetBeans
                    575370
                    K.,

                    Here is what I am doing:
                    1. In the SQL Developer:
                    - Defined a connection with the DB. Worked great. I can see all my DB objects including the function I want to debug.
                    - Right click on my function and select 'Compile for Debug' in pop-up menu then select Edit and make some break points in the PL/SQL code.
                    - Right click on the connection and select 'Remote Debug' from the pop up menu. The fill up the dialog box (Listen to JPDA) with the values: 4000, 0, 127.0.0.1. Press Ok. Worked great. I can see in the Run Manager: 'Debug Listener (Port=4000 Timeout=1000 Local address = 127.0.0.01)' .
                    2. Open SQL Command Line. Then execute the following commands:
                    SQL> connect myUsername/myPassword
                    SQL> connected
                    SQL> EXEC DBMS_DEBUG_JDWP.CONNECT_TCP('127.0.0.1', '4000');
                    PL/SQL procedure successfully completed.

                    At this moment a 'Remote Debug Session' appeared in Run Manager tree view of the SQL Developer. So far so good -- I guess so :-(.

                    3. In NetBeans, run debugger up to the point in code where my PL/SQL function gets called.
                    Here I expect that once I execute myCallableStatement.execute(); in the NetBeans debugger, the SQL Developer will stop on the break point withing the PL/SQL function I called from Java. But it does not happen. The NetBeans debugger executes myCallableStatement.execute() without pausing and SQL Developer does not change at this moment. I would expect NetBeans debugger to freeze in myCallableStatement.execute() and SQL Developer to stop on a break point withing the PL/SQL function -- which does not happen.

                    Who do I do wrong? Thanks!!!

                    P.S
                    1. By the way, I use Oracle Express -- is it a problem? 2. When I execute same function using SQL Command line withing the Oracle Express Web GUI I get the same result -- nothing happened in SQL Developer.

                    Mark.

                    Message was edited by:
                    MarcoPolo
                    • 7. Re: SQL Developer and NetBeans
                      -K-
                      Let me try to explain that more clearly: you're really debugging a session, not just a function.
                      So if you connect to the debugger in sqlplus, you're debugging the session in sqlplus. You can verify that calling your function in sqlplus, which will break into the debugger.
                      What you want to do is hook into the debugger from within NetBeans, so that session bill be debugged.
                      Alternatively you could even wrap the call inside your called function, but I wouldn't recommend it, just as last resort.

                      Regards,
                      K.
                      • 8. Re: SQL Developer and NetBeans
                        575370
                        K.,
                        It makes more cense to me now. I will try it on Tuesday and will let you know what the result.

                        Thanks very much.

                        Mark.
                        • 9. Re: SQL Developer and NetBeans
                          575370
                          Finally I succeed!!!
                          Here is what I have done. I added the following code to my Java application:
                          // -----------------------------------------------------------------
                          // Call PL/SQL procedure to enable PL/SQL debug session.
                          // -----------------------------------------------------------------
                          CallableStatement cstmt1 = null;
                          cstmt1 = dbConnection.prepareCall("{CALL DBMS_DEBUG_JDWP.CONNECT_TCP(?, ?)}");

                          // Provide all of the required parameter
                          cstmt1.setString(1, "127.0.0.1");
                          cstmt1.setString(2, "4000");
                          cstmt1.execute();
                          cstmt1.close();
                          //------------------------------------------------------------------

                          Once executed (from withing NetBeans), it connected my application to the SQL Developer debug session. Then I was able to call my PL/SQL function from the same Java application and to debug the function in the SQL Developer.

                          Ideally, I would prefer to use the same tool for debugging my Java code and the PL/SQL code. I believe it can be worked out in JDeveloper and maybe in NetBeans, but I have spent much time finding this solution already, so I will proceeded with my codding rather than optimizing my debugging environment further.

                          Dear K., thanks VERY MUCH for the help -- it is highly appreciated! Me has ayudado mucho! :-)

                          Message was edited by:
                          MarcoPolo
                          • 10. Re: SQL Developer and NetBeans
                            -K-
                            My pleasure ;-)

                            As for using 1 tool for debugging Java & PL/SQL, SQL Developer will be made available as extension to JDeveloper, so you can probably use JDev in the near future to do them both.

                            Regards,
                            K.
                            • 11. Re: SQL Developer and NetBeans
                              575370
                              It is very good to know. :-)
                              Thank you,
                              Mark.
                              • 12. Re: SQL Developer and NetBeans
                                SueHarper
                                SQL Developer is in JDeveloper 11g Beta. Currently available for download.

                                Sue
                                • 13. Re: SQL Developer and NetBeans
                                  575370
                                  Oh, hi Sue!

                                  I was reviewing your blog yesterday while was searching the Internet for the information about PL/SQL debugging. Thanks for sharing with us this information.

                                  Mark.