Showing posts with label PL/SQL. Show all posts
Showing posts with label PL/SQL. Show all posts

Wednesday, 31 October 2012

Debug Oracle Applications.

Below is the piece of code I have been using for quite a while for creating a good debug process in Oracle Applications development environment. It is good enough to be in PROD if we can convince the team too.


Steps:
1.Create a sequence
2.Create a debug table
3.Create a procedure
---
CREATE SEQUENCE APPS.XXC_SEQ
  START WITH 1
  MAXVALUE 99999999
  MINVALUE 1
  NOCYCLE
  CACHE 2
  NOORDER;
/

CREATE TABLE APPS.XXC_DEBUG_TBL(
  SEQ    NUMBER,  MESG   VARCHAR2(1000 BYTE),  DTIME  DATE)

/

CREATE OR REPLACE procedure APPS.XXC_debug(mesg1 in XXC_debug_tbl.mesg%type)
is
    PRAGMA AUTONOMOUS_TRANSACTION;
begin

    insert into XXC_debug_tbl(Seq,mesg,dtime)
    values(XXC_seq.nextval,mesg1,sysdate);
    commit;
      
end;



To call the procedure : xxc_debug('Test message')

And all debug messages are available in xxc_debug_tbl

Fetch Environment variables in PL/SQL

How to get the value of environment variables in PL/SQL code
-------------------------------------------------------------
1.For eBusiness Suite, you can also look at fnd_env_context table in your pl/sql code to get the path to the custom top variables as well.


2.A well coded program avoids hard-coding of paths and uses variables. If you want to get the value of unix environment variables in your programs, you can use the DBMS_SYSTEM.GET_ENV procedure. For applying it in E-Business Suite, you would need to make sure that the variables are defined in RDBMS $ORACLE_HOME/custom$CONTEXT_NAME.env

For example, I defined environment variable CUSTOM_TOP in a new file $ORACLE_HOME/custom$CONTEXT_NAME.env. This file is called automatically by your database environment file $CONTEXT_NAME.env.

To test, whether the value appears simply do this in an sql session:
SQL> set autoprint on
SQL> var CUSTOM_TOP varchar2(255)
SQL> exec dbms_system.get_env('CUSTOM_TOP',:CUSTOM_TOP);

PL/SQL procedure successfully completed.

Integrations Lead - Lessons learnt

 Integrations have been my passion for a while but like anything tech there is no credit given when things go right but always heaps of pres...