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
Showing posts with label PL/SQL. Show all posts
Showing posts with label PL/SQL. Show all posts
Wednesday, 31 October 2012
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.
-------------------------------------------------------------
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.
Subscribe to:
Posts (Atom)
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...
-
For most outbound interfaces bursting to content server and then picking the file from UCM is the best approach for large extracts. Default...
-
While developing Cloud integrations I could not find a single place for all details like InterfaceID or jobdefinitionname or job name etc. ...
-
Order to Cash flow starts with putting in an order in Order Management. 1. Enter an Sales order. A customer puts in a order for an Item. S...