6 Replies Latest reply: Mar 28, 2013 9:32 AM by Jeff Smith Sqldev Pm-Oracle RSS

    SQL Server compilation and Debugging

    IQ
      Hello,
      I am trying to compile a SQL Server procedure but looks like the SQL Developer 3.2.20.09, I am using which is the latest treats the SQL Server code as read only, there is no option for compiling the code as is available for Oracle PLSQL. Am I missing something or doing something incorrectly ?
      Also I would like to debug an Oracle Procedure using SQL Developer which is called from a Java apllication , can someone please let me know how this can be done or forward me links to some useful documentation on this.

      Thanks
      fm
        • 1. Re: SQL Server compilation and Debugging
          Jeff Smith Sqldev Pm-Oracle
          Read only connections for anything non-Oracle, correct. The connectivity is there primarily to assist in migrations. To step into the world of multi-RDBMS tools would be beyond our scope. Supporting Oracle Database is our primary charter.

          As for your debugging question, yes you can do that. Barry has a quick post on the subject
          http://barrymcgillin.blogspot.com/2012/04/remote-debugging-with-sql-developer.html?q=debug

          I talk about it a bit here too
          http://www.thatjeffsmith.com/archive/2012/05/using-sql-developer-to-debug-your-anonymous-plsql-blocks/
          • 2. Re: SQL Server compilation and Debugging
            IQ
            Thanks Jeff, I have read the articles you have directed me to, one question though, I am trying to debug through a Java Application which calls PLSQL code, can I do this ?
            If yes is the same process as mentioned in the links you suggested or is there something else that needs to be done. Please note my Java application is not web-based.


            Thanks
            fm
            • 3. Re: SQL Server compilation and Debugging
              IQ
              I tried running the Debugger from SQL Developer. I passed the requested run-time parameters and clicked on Debug, I get the following error. What could be wrong ?

              Connecting to the database Oracle Dev.
              Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
              Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '132.31.2.84', '51904' )
              ORA-30683: failure establishing connection to debugger
              ORA-12570: TNS:packet reader failure
              ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
              ORA-06512: at line 1
              Process exited.
              Disconnecting from the database Oracle Dev.
              • 4. Re: SQL Server compilation and Debugging
                Jeff Smith Sqldev Pm-Oracle
                The error message pretty much lays it out, the db can't reach 132.31.2.84 on port 51904.

                Is there a firewall in the way?
                • 5. Re: SQL Server compilation and Debugging
                  IQ
                  I disabled the firewalls on my PC and tried debugging , it again fails with the fllowing message

                  Connecting to the database Oracle Dev.
                  Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
                  Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '172.31.2.84', '1500' )
                  Debugger accepted connection from database on port 1500.
                  Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()

                  Process exited.
                  Disconnecting from the database Oracle Dev.
                  Debugger disconnected from database.

                  Edited by: IQ on Mar 28, 2013 10:01 AM
                  • 6. Re: SQL Server compilation and Debugging
                    Jeff Smith Sqldev Pm-Oracle
                    There's no error message there, it's working!

                    You just didn't give the debugger an opportunity to stop.

                    'DEBUGGER – START DEBUGGING OPTION
                    Set this to ‘Step Over.’ This allows you to start and run a debug session sans breakpoints. If you leave it at the default, starting a debug session will appear to ‘not work.’ This is because SQL Developer will enter debug mode and run until it finds a breakpoint, breaking condition, or exception. If you have none, it will execute to the end of your program. The ‘Step Over’ option will tell SQL Developer to stop execution at line 1 auto-magically.'

                    http://www.thatjeffsmith.com/archive/2011/12/top-10-preferences-to-tweak-when-using-sql-developer/