Trick to Debug Oracle Procedure

In the Production Environment an oracle procedure can lead to Exception or Error which cannot be backtrack if a proper logging machanisam not adopted.

Take the following Example Procedure,

create or replace
PROCEDURE DEPOSIT_INSRT
(
  APLICTON_ID IN VARCHAR2
, DEP01_ID IN  VARCHAR2
, tempdbexc IN OUT INTEGER
) AS
BEGIN

--APLICTON_ID:='1383';
--DEP01_ID :='1234567v';
  tempdbexc :=1;
 
  INSERT INTO CF_FXD_DEPOSIT ("APPLCTION_ID",  "DEP01_ID",)
  VALUES (APLICTON_ID, DEP01_ID);

  INSERT INTO CF_FXD_DEP_RECPT ("APPLCTION_ID") VALUES (APLICTON_ID);
  DBMS_OUTPUT.put_line('COMMITTED INSERT');
  COMMIT;

END DEPOSIT_INSRT;

In a testing Environment, Example I will use the SQLDeveloper as the web Client to Oracle ,,We can run and verify weather produce is error free for the values given hard corded values i have commented (APLICTON_ID:='1383', DEP01_ID :='1234567v';)

However when procedure executing form the external system ,there can mismatch of values ,there order or datatypes which gives errors in database level .IF external system unable to rice exact exception ,then error identification method should be adopted.

As trick I created following table to  save the exception raised from database.

CREATE TABLE "SYSTEM"."ERROR_TB"
  (
    "ERROR_COL" VARCHAR2(4000 BYTE)
  )

And Update the Procedure as fallows.

PROCEDURE DEPOSIT_INSRT
(
  APLICTON_ID IN VARCHAR2
, DEP01_ID IN  VARCHAR2
, tempdbexc IN OUT INTEGER
, dberror IN OUT  VARCHAR2
) AS
BEGIN

--APLICTON_ID:='1383';
--DEP01_ID :='1234567v';
  tempdbexc :=1;

  INSERT INTO CF_FXD_DEPOSIT ("APPLCTION_ID",  "DEP01_ID",)
  VALUES (APLICTON_ID, DEP01_ID);

  INSERT INTO CF_FXD_DEP_RECPT ("APPLCTION_ID") VALUES (APLICTON_ID);
  DBMS_OUTPUT.put_line('COMMITTED INSERT');
  COMMIT;

 EXCEPTION
    when NO_DATA_FOUND THEN tempdbexc := 0;
    WHEN OTHERS THEN  dberror :=SQLERRM; insert INTO  ERROR_TB (ERROR_COL) VALUES (POST_ADDRS01);

END DEPOSIT_INSRT;

IT will insert the error to ERROR_TB  created above wen and exception rise and allows us to correct error by going trough the latest record of the table.