Debug a procedure - Oracle

Let’s introduce a compilation error into your procedure declaration.

Open your skeleton.sql file in Notepad. Replace the DBMS_OUTPUT.PUT_LINE procedure call with the NULLL statement (notice the three "l"s!), an invalid PL/SQL statement. Your program should look like this:

CREATE OR REPLACE PROCEDURE skeleton
IS
BEGIN
  NULLL;
END;
Save your file as skeleton.sql.

From SQL*Plus, open your skeleton.sql file.

SQL*Plus loads the contents of your skeleton.sql file into the SQL*Plus buffer or memory area and presents the SQL*Plus command prompt:

SQL>
  1  CREATE OR REPLACE PROCEDURE skeleton
  2  IS
  3  BEGIN
  4    NULLL;
  5* END;
SQL>

Execute the contents of the SQL*Plus buffer. Type a front slash and press <enter> like this:

SQL> /

Your procedure is compiled and saved on the database. However, SQL*Plus warns us of compilation errors:

Warning: Procedure created with compilation errors.

Let’s see the compilation errors. First, we need to run two SET commands to ensure the SQL*Plus buffer does not overflow.

At the SQL*Plus command prompt, type:

SQL> SET ARRAYSIZE 1
SQL> SET MAXDATA 60000
SQL>

Again, SQL*Plus remains secretive of the result. Let's see the errors. At the SQL*Plus command prompt, type:

SQL> SHOW ERRORS PROCEDURE skeleton

You should see the compilation error:

LINE/COL
----------------------------------------------
ERROR
----------------------------------------------
4/3
PLS-00201: identifier 'NULLL' must be declared

4/3
PL/SQL: Statement ignored

Oracle doesn't recognize the NULLL statement with the three "l"s. But Oracle won't hold it against you.

Change your procedure declaration in Notepad by inserting the proper NULL statement, and follow the steps to create your procedure again on the Oracle database.

What if you want to completely remove a procedure from your database? That's what we'll cover next.