Simple debugging tool Oracle

Most of the cases you can use dbms_output.put_line to log and debug the Oracle program units like Packages,Procedure, Function etc. But there are some situations we cannot use dbms_output.put_line. For example Oracle stored procedure is called from Java based applications. Java application will invoke the procedure and there is no place to see the debug message. In these type of situations you can use below tool

Tool consists of a Table and a small Package.

Pre-requisites:

Create table privilege

Create and execute privilege for Package

Step 1: Create a log table

CREATE TABLE LOG_TABLE
(
ERROR_NO        NUMBER,
ERROR_TEXT  VARCHAR2(4000),
ERROR_DATE  TIMESTAMP (6) DEFAULT systimestamp
);

Step 2 : Create an Package with Autonomous Procedure

create or replace
package trc
is

/*
Author   :  http://www.sqlandplsql.com
Date     :  09-Aug-2012
Version  :  1.0
*/

procedure put_line(v_text varchar2);
procedure reset;
end trc;
/

create or replace package body trc
is
/*
Author : http://www.sqlandplsql.com
Date : 09-Aug-2012
Version : 1.0
*/
procedure put_line(v_text varchar2) is
pragma autonomous_transaction;
n_error_no  integer;
begin
Begin
select nvl(max(error_no),0)+1 into n_error_no
from LOG_TABLE;
exception
when no_data_found then
n_error_no := 1;
end;

insert into LOG_TABLE(error_no,error_text)
values(n_error_no,substr(v_text,1,4000));
commit;

exception
when others then
raise_application_error(-20001,sqlerrm);
end put_line;

Procedure reset
is
begin
delete from LOG_TABLE;
commit;
end reset;
end trc;
/

Step 3: Testing

Run the below PL/SQL block

begin
trc.put_line(‘line1 here’);
end;
/

Now select from table and see

SQL>select * from LOG_TABLE;

SQL> select * from log_table;

ERROR_NO        ERROR_TEXT            ERROR_DATE
—————————————————————————
1                    line1 here                       08-AUG-12 09.29.21.465000 PM

Advantages

1. Easy to install and use.

2. Logged data persists in table

3. Can be used with any type of application like Java, .NET, PHP etc

Limitations

1. Need to maintain a table

2. Message length limit 4000 character

Enjoy the tool ! Share your experience.

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s