7 Replies Latest reply: Dec 13, 2012 10:56 AM by bencol RSS

    debug plsql in sqlplus

    976379
      Hi,
      can any one let me know if i can debug the plsql code in sqlplus ( windows sqlplus )?
      if yes then also let me know the process.

      Thanks.
        • 1. Re: debug plsql in sqlplus
          LPS
          Place dbms_output.Put_line('I am in Condition1); in your pl/sql code and then run the below statements in SQL plus to debug ....
          This is one way of debugging.....


          SQL > SET SERVEROUTPUT ON

          SQL> EXEC proc_name(Parameters);
          • 2. Re: debug plsql in sqlplus
            976379
            Hello sir,
            I know the mentioned method, but by this way i can not check the values in each and every step.
            also in production environment, its not possible to change the procedure and add the DBMS_OUTPUT for each step.

            Is there any other way for debugging the procedure like we can do in TOAD ?

            Thanks in advance.
            • 3. Re: debug plsql in sqlplus
              Mahir M. Quluzade
              You do debug with TOAD or SQL Developer tools .
              • 4. Re: debug plsql in sqlplus
                976379
                yes , i can
                But in production environment , we dont have access of TOAD or any other tool. we can only connect by sqlplus.
                So if any method avilable then let me know.
                Thanks
                • 5. Re: debug plsql in sqlplus
                  BluShadow
                  KriC wrote:
                  yes , i can
                  But in production environment , we dont have access of TOAD or any other tool. we can only connect by sqlplus.
                  So if any method avilable then let me know.
                  Not really. once the code is compiled into the database, it has been compiled to a code that is not exactly like the source code (and if Native compilation is used, then it's definitely nothing like the source code). So when this code actually executes it is executing the compiled version of the code, not the source code itself. This is the difference between compiled code and interpreted code. To step through in a debug fashion this is typically done using an interpreted code, or some very clever techniques employed by some tools. You're certainly not going to achieve step by step debugging in SQL*Plus.
                  • 6. Re: debug plsql in sqlplus
                    John Spencer
                    To the best of my knowledge, the tools like TOAD or SQL Developer that do debugging, just provide a GUI interface to the dbms_debug supplied package. You can (and I have) use it directly in sqplus, but it is a bit of a pain. If you need to debug something, even in production code I would definitely use a tool.

                    However, if you really cannot use tools to do your job, then check out the dbms_debug section in the PL/SQL Packages and Types Reference documentation for your version.

                    John
                    • 7. Re: debug plsql in sqlplus
                      bencol
                      http://tkyte.blogspot.co.uk/2005/06/instrumentation.html

                      I realise that in you case this will not help now, but it is still worth a read.