If you don't have access to TOAD, then you can use either DBMS_OUTPUT or RAISE_APPLICATION_ERROR in SQL*Plus. When using RAISE_APPLICATION_ERROR, and once the code reaches that area in your procedure, it will stop and display the message from the RAISE_APPLICATION_ERROR procedure. Therefore, I recommend using DBMS_OUTPUT to debug your PL/SQL code. DMBS_OUTPUT package displays your debugged results when your procedure has completed.
To use the DBMS_OUTPUT package in SQL*Plus, you must set serveroutput on. Below is an example of a procedure called Baz, which uses the dbms_putput package:
SQL> set serveroutput on SQL> create or replace PROCEDURE baz (ind number) IS 2 counter number; 3 BEGIN 4 FOR counter IN 0..ind LOOP 5 DBMS_OUTPUT.PUT_LINE('Inside baz'); 6 END LOOP; 7 END; 8* /
SQL> execute baz(10); Inside baz0 Inside baz1 Inside baz2 Inside baz3 Inside baz4 Inside baz5 Inside baz6 Inside baz7 Inside baz8 Inside baz9 Inside baz10 PL/SQL procedure successfully completed.
For more information see Oracle9i Supplied PL/SQL Packages and Types reference Release 2 (9.2).
This was first published in August 2006