Simple Debugging Mechanism for PL/SQL Code Pack.

This article would help us while we trying to debug any existing package or trying to understand the functionality of a existing PL/SQL Object.

Though its very simple but this 3 steps would easy guide you to debug when asked to do so.

— Table Creation Script
CREATE TABLE apps.xx_log_tbl
(
name VARCHAR2 (100),
msg VARCHAR2 (500),
id NUMBER
);
/

— Sequence Creation
CREATE SEQUENCE xx_log_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
/

— Procedure when called will insert the records to the custom table
CREATE OR REPLACE PROCEDURE apps.xx_log (lname VARCHAR2,lmsg VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO xx_log_tbl (name, msg, id)
VALUES (lname, lmsg, xx_log_seq.NEXTVAL);

COMMIT;
END xx_log;
/

— Grant if required
GRANT ALL ON xx_log TO <SCHEMA_NAME>;

GRANT ALL ON xx_log_tbl TO <SCHEMA_NAME>;
/

Now just add a simple call to the Procedure “xx_log” in the code by passing any custom message, check on the table to understand the flow.

Ensure you drop them if not required.

Its a simple mechanism and I am sure you all would be already aware of it but wanted to share here to keep it handy to use.

Thanks…

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: