Debugging

Even though there is no native debugger for PL/SQL you are still able to debug your code. PL/SQL, as well as any other procedural language, can fail on syntax/semantics checks and run-time errors. With the first two, you won’t be allowed to successfully compile an object.

The most common ways how you can track your code are:

  • DBMS_OUTPUT
  • SHOW ERRORS
  • Custom logging – find out more in the article about Logging

DBMS_OUTPUT

This one is my favorite and it is the simplest (and in my opinion the most powerful) form of debugging. You can easily print to the output all variables, steps, or progress in your application. You can even set the main variable that will determine whether the procedure or package should run in a “debug mode” or not; the best part is that it has almost zero effect on the performance 🙂 Let’s check an example of a simple (yet powerful) debugging:

-- package specification
CREATE OR REPLACE PACKAGE my_debug_package IS
  PROCEDURE run_ctl;
END;
  

-- package body
CREATE OR REPLACE PACKAGE BODY my_debug_package IS
/****************************************************************************************************************************
    NAME:      MY_DEBUG_PACKAGE
    PURPOSE:   To demonstrate how to debug with dbms_output
    REVISIONS:
    Ver        Date        Author           Description
    ---------  ----------  ---------------  ---------------------------------------------------------------------------------
    1.0        10/10/2018  OracleWorld      1. Created this package. 
****************************************************************************************************************************/
   

    v_debug_mode INT := 1; -- 1 - TRUE; 0 - FALSE  
    v_error_note VARCHAR2(255);

/****
 *   Print out data if Debug is on
 *****/
PROCEDURE p_debug ( v_input IN varchar2 )
IS
BEGIN

    IF v_debug_mode = 1 THEN
       dbms_output.put_line(v_input);
    END IF;
        
END;


PROCEDURE p_init_load (
    v_error_code OUT varchar2
    )
IS
    v_sql_rowcount number;
BEGIN
    
    p_debug('init load begins ...');
    
    -- insert data ....
    
    p_debug('load finished ...') ;
    

    v_sql_rowcount := sql%rowcount;
    
        
    -- check how many rows were records
    
    IF v_sql_rowcount >= 100000 THEN
    
        
        p_debug('more than 100k rows processed .. updating anotherTable');
        -- update another table
    ELSE
      
        p_debug('Only '||v_sql_rowcount||' rows were processed .. deleting myTableLog');
        
        -- delete my table log
    
    END IF;

    
        p_debug('finished');
        
    commit;

   
    EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        v_error_code := sqlcode ||';'|| sqlerrm;
        
        
END;


PROCEDURE run_ctl
IS

  v_error_code varchar2(1000);
  v_ex_custom EXCEPTION;  

BEGIN



        p_init_load (v_error_code);
        IF v_error_code IS NOT NULL THEN
            RAISE v_ex_custom;
        END IF;


    EXCEPTION

    WHEN v_ex_custom THEN
        v_error_note := 'ERROR - Custom exception';
        dbms_output.put_line(v_error_note);
        
    WHEN OTHERS THEN
        ROLLBACK;
        v_error_code := sqlcode ||';'|| sqlerrm;
        v_error_note := 'Unexpected error';
        dbms_output.put_line(v_error_code);

END run_ctl;

END;

This is a simple demonstration on how to use debugging. You can easily adapt it to your needs 🙂 If you are surprised about packages, I will write about them in another article called Packages.

SHOW ERRORS

There is another command that might show you errors. You can easily use a command SHOW ERRORS to see errors for that particular user/schema.

SHOW ERRORS;


No errors

To see the errors for a particular object, use the following syntax:

SHOW ERRORS [plsql_program] [program_name];

--examples
SHOW ERRORS VIEW V_EMP_LIST;
SHOW ERRORS PROCEDURE P_LOAD_DATA;

-- you can even specify the schema where the object resides (if not specified, current user's schema is used)
SHOW ERRORS VIEW HR_SCHEMA.V_EMP_LIST;

Errors for VIEW HR_SCHEMA.V_EMP_LIST;
LINE/COL ERROR
-------- -----------------------------------------
0/0      ORA-00942: table or view does not exist

If you prefer SQL queries, give DBA_ERRORS view a try 🙂 Alternatively use USER_ERRORS or ALL_ERRORS; depends on your privileges.

There are also features like DBMS_DEBUG and DBMS_PROFILER but I honestly don’t have much of an experience with them so I will refer you to the documentation 🙂 I find them too difficult to use and haven’t experienced any need to use them in my whole career.