Posts

Showing posts from September, 2011

SQL TRACE FILE

Enable the Trace:

ALTER SESSION SET SQL_TRACE=TRUE;

Identify the trace file path:

select u_dump.value || '/' || db_name.value || '_ora_' || v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc' "Trace File" from v$parameter u_dump cross join v$parameter db_name cross join v$process join v$session on v$process.addr = v$session.paddr where u_dump.name = 'user_dump_dest' and db_name.name = 'db_name' and v$session.audsid=sys_context('userenv','sessionid'); In order to make a more informative trace file name, the following command can be used:

alter session set tracefile_identifier = here_is_my_session;
Generate TKPROF Output :


GOTO Statement In ORACLE

GOTO Statement
The GOTO statement branches to a label unconditionally. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. When executed, the GOTO statement transfers control to the labeled statement or block. BEGIN ... GOTO insert_row; ... <<insert_row>> INSERT INTO emp VALUES ... END; you go to a PL/SQL block farther up in a sequence of statements:
The label end_loop in the following example is not allowed because it does not precede an executable statement: DECLARE done BOOLEAN; BEGIN ... FOR i IN 1..50 LOOP IF done THEN GOTO end_loop; END IF; ... <<end_loop>> -- not allowed END LOOP; -- not an executable statement END;  In the below case  GOTO statement can branch to an enclosing block from the current block:

DECLARE
   my_ename  CHAR(10);
BEGIN
   <<get_name>>
   SELECT ename INTO my_ename FROM emp WHERE ...
   BEGIN
      ...
      GOTO get_name;…

Types of Triggers

the different types of triggers

Introduction to Data Blocks, Extents, and Segments

Image
Overview of Data BlocksOracle manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of data used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.

The standard block size is specified by the DB_BLOCK_SIZE initialization parameter


Data Block FormatThe Oracle data block format is similar regardless of whether the data block contains table, index, or clustered data. Figure 2-2illustrates the format of a data block.
Figure 2-2 Data Block Format PCTFREE, PCTUSED, and Row Chaining

The PCTFREE ParameterThe PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. For example, assume that you specify the following parameter within a CREATETABLE statement:
PCTFREE 20��…

Creating a Custom Application in Applications 11i

Custom Applications are required if you are creating new forms, reports, etc.
This allows you to segregate your custom written files from the standard seeded functionality that Oracle Applications provide.
Customizations can therefore be preserved when applying patches or upgrades to your environment.

Oracle recommneds to have your customized objects/files under custom top, PFB method to create and register CUSTOM TOP in oracle apps 11i:-

1) Make the directory structure for your custom application files.
cd $APPL_TOP
mkdir xxcustom
mkdir xxcustom/11.5.0
mkdir xxcustom/11.5.0/admin
mkdir xxcustom/11.5.0/admin/sql
mkdir xxcustom/11.5.0/admin/odf
mkdir xxcustom/11.5.0/sql
mkdir xxcustom/11.5.0/bin
mkdir xxcustom/11.5.0/reports
mkdir xxcustom/11.5.0/reports/US
mkdir xxcustom/11.5.0/forms
mkdir xxcustom/11.5.0/forms/US
mkdir xxcustom/11.5.0/$APPLLIB
mkdir xxcustom/11.5.0/$APPLOUT
mkdir xxcustom/11.5.0/$APPLLOG


2) Add the custom module into the environment
Customised environment variables ca…

Unix Shell Script For Oracle Apps

What is a Shell Script?
A shell is an environment in which we can run our commands, programs, and shell scripts. There are different flavors of shells, just as there are different flavors of operating systems. Each flavor of shell has its own set of recognized commands and functions.
The basic concept of a shell script is a list of commands, which are listed in the order of execution. A good shell script will have comments, preceded by a pound sign, #, describing the steps. Unix is case sensitive. Because Unix is case sensitive our shell scripts are also case sensitive.
Shell scripts and functions are both interpreted. This means they are not compiled. Both shell scripts and functions are ASCII text that is read by the Korn shell command interpreter. When we execute a shell script, or function, a command interpreter goes through the ASCII text line by line, loop by loop, test by test and executes each statement, as each line is reached from the top to the bottom.

Unix Special Characters
Al…