Q

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?

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?
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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close