Ask the Expert

Checking PL/SQL packages

How to check if the data populated into a table is correct when a PL/SQL package is executed? How to check whether data populated by executing an old package and a modified package is the same?

    Requires Free Membership to View

It sounds like you are asking how to debug your PL/SQL code. There are several ways to debug a procedure: 1) use TOAD's debug procedure; 2) use RAISE_APPLICATION_ERROR procedure; 3) use DBMS_OUTPUT package.

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*       /

The results:

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: