Debug Oracle PL/SQL scripts

To debug PL/SQL scripts, I have found two commands are extremely helpful, "set echo on" and "show errors". "Set echo on" prints the scripts in a file along with the line number. "show errors" displays the compilation errors. By combining the outputs from the above two commands, we can easily identify where the problems are and fix them. When we are done debugging, we can turn off the display using "set echo off". In the example below, I put my "create or replace procedure" in a script file called debug_proc.sql.

SQL> set echo on
SQL> @debug_proc.sql
SQL> create or replace procedure
  2  proc_test_ins(
  3  p1 number,
  4  p2 date)
  5
  6  is
  7
  8  begin
  9
 10  insert into tbl_test_x(
 11  id, dt)
 12
 13  select
 14  p1, p2 from dua;
 15  commit;
 16
 17
 18  end;
 19
 20  /

Warning: Procedure created with compilation errors.

The "set echo on" show the line number for each line of scripts. To see the detailed compilation errors, we run "show errors" command.

SQL> show errors
Errors for PROCEDURE PROC_TEST_INS:

LINE/COL
--------
ERROR
--------------------------------------------------------------
--------------------------------------------------------------
------------------------------------------------------------
10/1
PL/SQL: SQL Statement ignored

14/13
PL/SQL: ORA-00942: table or view does not exist

As we see, there is a typo on line 14, "from dua" should be "from dual".

SQL> create or replace procedure
  2  proc_test_ins(
  3  p1 number,
  4  p2 date)
  5
  6  is
  7
  8  begin
  9
 10  insert into tbl_test_x(
 11  id, dt)
 12
 13  select
 14  p1, p2 from dual;
 15
 16  commit;
 17
 18
 19  end;
 20
 21  /

Procedure created.

To turn off the display of the scripts and the line numbers, I run "set echo off".

SQL> set echo off
SQL> @debug_proc.sql

Procedure created.

Now we can run the procedure.

SQL> exec proc_test_ins(1, sysdate);
SQL> select * from  tbl_test_x;

     ID DT
------- ---------
      1 09-MAR-15