Oracle Technical – Oracle SQL Statement Types and Usage

let me intraduce SQL by statements statements defines set of commands.

Generally types statements are as followed.

  1. Data definition Language(DDL)
  2. Data Retrieval or Query Language(DQL or DRL)
  3. Data Manipulation Language(DML)
  4. Data Control Language(DCL)
  5. Transaction Control Language(TCL)

let me continue in detail type.

DDL commands are used to define the data base objects.The following commands are used to define data base objects.

1. create
2. alter
3. truncate
4. rename
5. drop
1. create

create command defines a new database object, such as a database, user, table, view, trigger, index, macro, stored procedure, user-defined type, user-defined function, or user-defined macro, depending on the object of the CREATE request.

Syntax :

CREATE TABLE table_name
(column_1 column-definition,
column_2 column-definition,

column_n column_definition);
Examples :




2. alter

ALTER statement to add a column, modify a column, drop a column, rename a column or rename a table.
Syntax of Single Column Adding:

ALTER TABLE table_name
ADD column_name column-definition;

Example of Single Column Adding:

alter table dept add dept_type varchar2(10);

alter table emp add end_date date;

Syntax of Multiple Column Adding :

ALTER TABLE table_name
ADD (column_1 column-definition,
column_2 column-definition,

column_n column_definition);

Example of Multiple Column Adding :

alter table add(dob date,
gender varchar2(10));
Syntax of Single Column Modifying:

ALTER TABLE table_name
MODIFY column_name column_type;
Example of Single Column Modifying:

alter table dept modify dept_type varchar2(10) not null;
Syntax of multiple Column Modifying:

ALTER TABLE table_name
MODIFY (column_1 column_type,
column_2 column_type,

column_n column_type);

Example of multiple Column Modifying:

alter table modify(dob date NOT NULL,
gender varchar2(6));

Note : We can’t modify any column if table contains data means if you want to increase range of data then table should be empty.
Syntax of drop a column in an existing table :

ALTER TABLE table_name
DROP COLUMN column_name;

Example of drop a column in an existing table :

Syntax of rename a column in an existing table :

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

Example of rename a column in an existing table :

RENAME COLUMN dob to date_of_birth;
Syntax of To rename a table

ALTER TABLE table_name
RENAME TO new_table_name;

Example of To rename a table

RENAME TO employee;

3. truncate

By using this command , we can delete data of the table but definition of the object will remain same.

Syntax :

truncate table table_name;

Example :

truncate table emp;

5. drop

By using this command we can delete table permanently from database.

Syntax :

drop table table_name;

Example :

drop table emp;
Few Important Points:

What is result of the following statements?

1. create table xx_dept as select *from dept;

Answer : xx_dept table will be create same as dept except constraints,here not null will be applicable.

2. create table xx_dept 1 as select *from dept where 1=1;

Answer : xx_dept table will be create same as dept except constraints,here not null will be applicable.

3. create table xx_dept2 as select *from dept where 1=2;

Answer : xx_dept table will be create same as dept with structure only.

Data Retrieval or Query Language(DQL or DRL) :

here SELECT clause will come under this category,main usage of select to identify the result set column list.

Syntax :
select *
from table_name;

select column1,column2…
from table_name;

Example :
select *from

this statement will fetch all columns of dept table.but it is not suggestible approach.

select deptno,
from dept;

Data Manipulation Language(DML) 

These statements are used to manage the data with database objects.

Categorization of Commands :

insert data into a table :

Syntax :

INSERT INTO <table_name>

Example 1 :


Example 2:
(7369, ‘SMITH’, ‘CLERK’, 7902,
TO_DATE(’17-DEC-1980′, ‘DD-MON-YYYY’), 800, NULL, 20);
(7499, ‘ALLEN’, ‘SALESMAN’, 7698,
TO_DATE(’20-FEB-1981′, ‘DD-MON-YYYY’), 1600, 300, 30);
(7521, ‘WARD’, ‘SALESMAN’, 7698,
TO_DATE(’22-FEB-1981′, ‘DD-MON-YYYY’), 1250, 500, 30);
(7566, ‘JONES’, ‘MANAGER’, 7839,
TO_DATE(‘2-APR-1981’, ‘DD-MON-YYYY’), 2975, NULL, 20);
(7654, ‘MARTIN’, ‘SALESMAN’, 7698,
TO_DATE(’28-SEP-1981′, ‘DD-MON-YYYY’), 1250, 1400, 30);
(7698, ‘BLAKE’, ‘MANAGER’, 7839,
TO_DATE(‘1-MAY-1981’, ‘DD-MON-YYYY’), 2850, NULL, 30);
(7782, ‘CLARK’, ‘MANAGER’, 7839,
TO_DATE(‘9-JUN-1981’, ‘DD-MON-YYYY’), 2450, NULL, 10);
(7788, ‘SCOTT’, ‘ANALYST’, 7566,
TO_DATE(’09-DEC-1982′, ‘DD-MON-YYYY’), 3000, NULL, 20);
TO_DATE(’17-NOV-1981′, ‘DD-MON-YYYY’), 5000, NULL, 10);
(7844, ‘TURNER’, ‘SALESMAN’, 7698,
TO_DATE(‘8-SEP-1981’, ‘DD-MON-YYYY’), 1500, 0, 30);
(7876, ‘ADAMS’, ‘CLERK’, 7788,
TO_DATE(’12-JAN-1983′, ‘DD-MON-YYYY’), 1100, NULL, 20);
(7900, ‘JAMES’, ‘CLERK’, 7698,
TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 950, NULL, 30);
(7902, ‘FORD’, ‘ANALYST’, 7566,
TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 3000, NULL, 20);
(7934, ‘MILLER’, ‘CLERK’, 7782,
TO_DATE(’23-JAN-1982′, ‘DD-MON-YYYY’), 1300, NULL, 10);

Important Note :
We can Insert data by using select statement also.

Example :
first create table without data.

create table employee as select *from emp where 1=2;

then insert data from emp to employee table.

INSERT INTO employee
FROM emp;

this statemet entire data of emp into employee.

INSERT INTO employee(empno,ename,job,deptno)
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno=10;

But here it will insert only specified columns for deptno 10 only.
updates existing data within a table.

Syntax :
UPDATE <table_name>
SET <column_name> = <value>
WHERE <column_name> = <value>;


update emp
set sal=sal+2000;

it will update all records of emp,but it’s suggestible way in real time, surely updation can be done based on some condition.

update emp
set sal=sal+2000
where empno=7788;

update emp
set sal=sal+2000,
where empno=7788;

deletes all records from a table :

Syntax :
from table_name
where <column_name>=<value>;


from emp
where deptno=10;

Note : We can’t delete specific column from table, like the following way.

delete empno
from emp;
Primary usage merge when moving large amounts of data from one table to another table.this kind work can be done in data warehouse.

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;

Example :

CREATE TABLE employee (
employee_id NUMBER(5),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
dept_no NUMBER(2),
salary NUMBER(10));

INSERT INTO employee VALUES (1, ‘Dan’, ‘Morgan’, 10, 100000);
INSERT INTO employee VALUES (2, ‘Jack’, ‘Cline’, 20, 100000);
INSERT INTO employee VALUES (3, ‘Elizabeth’, ‘Scott’, 20, 50000);
INSERT INTO employee VALUES (4, ‘Jackie’, ‘Stough’, 20, 40000);
INSERT INTO employee VALUES (5, ‘Richard’, ‘Foote’, 20, 30000);
INSERT INTO employee VALUES (6, ‘Joe’, ‘Johnson’, 20, 70000);
INSERT INTO employee VALUES (7, ‘Clark’, ‘Urling’, 20, 90000);

CREATE TABLE bonuses (
employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses (employee_id) VALUES (1);
INSERT INTO bonuses (employee_id) VALUES (2);
INSERT INTO bonuses (employee_id) VALUES (4);
INSERT INTO bonuses (employee_id) VALUES (6);
INSERT INTO bonuses (employee_id) VALUES (7);

MERGE INTO bonuses B
SELECT employee_id, salary
FROM employee
WHERE dept_no =20) E
ON (B.employee_id = E.employee_id)
UPDATE SET B.bonus = E.salary * 0.1
INSERT (B.employee_id, B.bonus)
VALUES (E.employee_id, E.salary * 0.05);

Data Control Language(DCL) 

he Data Control Language (DCL) is a subset of the Structured Query Lanaguge (SQL) ,that allows database administrators to configure security access to relational databases.Two types of DCL commands are GRANT and REVOKE. Only Database Administrator’s or owner’s of the database object can provide/remove privileges on a database object.

SQL GRANT is a command used to provide access or privileges on the database objects to the users.

gives user’s access privileges to database.

The Syntax for the GRANT command is:

GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}

grant select on
emp to scott;

grant select,insert,delete on
emp to scott;

withdraw access privileges given with the GRANT command.

The Syntax for the REVOKE command is:

REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name} ;

Example :
revoke insert, delete on
emp from scott;
Transaction Control Language(TCL) :

TCL statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

permanent save to work we can’t roll back once applied commit.

Example :

identify a point in a transaction and which you can later roll back also.

Example :

save point to sp1;

roll back to sp1;

restore database to original since the last COMMIT

Example :


Oracle A.I.M. Methodology – template list

Oracle A.I.M. Methodology encompasses a project management methodology with documentation templates that support the life cycle of an implementation. The life cycle methodology and documentation templates allows A.I.M. to be a very useful tool for managing implementation projects successfully. This is a depiction of the A.I.M. methodology life cycle:

Application Implementation Method is a proven approach for all the activities required to implement oracle applications. there are eleven processes of implementation.

1. Business Process Architecture [BP] – This phase outlines:

  • Existing Business Practices
  • Catalog change practices
  • Leading practices
  • Future practices
BP.010 Define Business and Process StrategyBP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop High-Level Process Vision

BP.070 Develop High-Level Process Design

BP.080 Develop Future Process Model

BP.090 Document Business Procedure

2. Business Requirement Definition [RD] This phase explains about the initial baseline questionnaire and gathering of requirements.

RD.010 Identify Current Financial and Operating Structure RD.020 Conduct Current Business Baseline RD.030 Establish Process and Mapping Summary RD.040 Gather Business Volumes and Metrics RD.050 Gather Business Requirements RD.060 Determine Audit and Control Requirements RD.070 Identify Business Availability Requirements RD.080 Identify Reporting and Information Access Requirements

3. Business Requirement Mapping [BR]In this phase the requirements of business are matched with the standard functionality of the oracle applications.

BR.010 Analyze High-Level GapsBR.020 Prepare mapping environment
BR.030 Map Business requirements
BR.040 Map Business Data
BR.050 Conduct Integration Fit Analysis
BR.060 Create Information Model

BR.070 Create Reporting Fit Analysis

BR.080 Test Business Solutions

BR.090 Confirm Integrated Business Solutions

BR.100 Define Applications Setup

BR.110 Define security Profiles

4. Application and Technical Architecture [TA]This outlines the infrastructure requirements to implement oracle applications.

TA.010 Define Architecture Requirements and StrategyTA.020 Identify Current Technical Architecture
TA.030 Develop Preliminary Conceptual Architecture
TA.040 Define Application Architecture
TA.050 Define System Availability Strategy
TA.060 Define Reporting and Information Access Strategy

TA.070 Revise Conceptual Architecture

TA.080 Define Application Security Architecture

TA.090 Define Application and Database Server Architecture

TA.100 Define and Propose Architecture Subsystems

TA.110 Define System Capacity Plan

TA.120 Define Platform and Network Architecture

TA.130 Define Application Deployment Plan

TA.140 Assess Performance Risks

TA.150 Define System Management Procedures

5. Build and Module Design [MD]This phase emphasizes the development of new functionality (customization) required by the client. It mainly details how to design the required forms, database and reports.

MD.010 Define Application Extension StrategyMD.020 Define and estimate application extensions
MD.030 Define design standards
MD.040 Define Build Standards
MD.050 Create Application extensions functional design
MD.060 Design Database extensions

MD.070 Create Application extensions technical design

MD.080 Review functional and Technical designs

MD.090 Prepare Development environment

MD.100 Create Database extensions

MD.110 Create Application extension modules

MD.120 Create Installation routines

6. Data Conversion [CV]Data Conversion is the process of converting or transferring the data from legacy system to oracle applications. Ex. Transferring customer records from the legacy to the Customer Master.

CV.010 Define data conversion requirements and strategyCV.020 Define Conversion standards
CV.030 Prepare conversion environment
CV.040 Perform conversion data mapping
CV.050 Define manual conversion procedures
CV.060 Design conversion programs

CV.070 Prepare conversion test plans

CV.080 Develop conversion programs

CV.090 Perform conversion unit tests

CV.100 Perform conversion business objects

CV.110 Perform conversion validation tests

CV.120 Install conversion programs

CV.130 Convert and verify data

7. Documentation [DO]Documentation prepared per module that includes user guides and implementation manuals.

DO.010 Define documentation requirements and strategyDO.020 Define Documentation standards and procedures
DO.030 Prepare glossary
DO.040 Prepare documentation environment
DO.050 Produce documentation prototypes and templates
DO.060 Publish user reference manual

DO.070 Publish user guide

DO.080 Publish technical reference manual

DO.090 Publish system management guide

8. Business System Testing [TE]A process of validating the setup’s and functionality by QA(functional consultant) to certify status.

TE.010 Define testing requirements and strategyTE.020 Develop unit test script
TE.030 Develop link test script
TE.040 Develop system test script
TE.050 Develop systems integration test script
TE.060 Prepare testing environments

TE.070 Perform unit test

TE.080 Perform link test

TE.090 perform installation test

TE.100 Prepare key users for testing

TE.110 Perform system test

TE.120 Perform systems integration test

TE.130 Perform Acceptance test

9. Performance Testing [PT] Performance testing is the evaluation of transactions saving time, transaction retrieval times, workflow background process, database performance, etc

PT.010 – Define Performance Testing StrategyPT.020 – Identify Performance Test Scenarios
PT.030 – Identify Performance Test Transaction
PT.040 – Create Performance Test Scripts
PT.050 – Design Performance Test Transaction Programs
PT.060 – Design Performance Test Data

PT.070 – Design Test Database Load Programs

PT.080 – Create Performance Test TransactionPrograms

PT.090 – Create Test Database Load Programs

PT.100 – Construct Performance Test Database

PT.110 – Prepare Performance Test Environment

PT.120 – Execute Performance Test

10. Adoption and Learning [AP]This phase explains the removal of the legacy system and oracle application roll out enterprise wide.

AP.010 – Define Executive Project StrategyAP.020 – Conduct Initial Project Team Orientation
AP.030 – Develop Project Team Learning Plan
AP.040 – Prepare Project Team Learning Environment
AP.050 – Conduct Project Team Learning Events
AP.060 – Develop Business Unit Managers’Readiness Plan

AP.070 – Develop Project Readiness Roadmap

AP.080 – Develop and Execute CommunicationCampaign

AP.090 – Develop Managers’ Readiness Plan

AP.100 – Identify Business Process Impact onOrganization

AP.110 – Align Human Performance SupportSystems

AP.120 – Align Information Technology Groups

AP.130 – Conduct User Learning Needs Analysis

AP.140 – Develop User Learning Plan

AP.150 – Develop User Learningware

AP.160 – Prepare User Learning Environment

AP.170 – Conduct User Learning Events

AP.180 – Conduct Effectiveness Assessment

11. Production Migration [PM]The process of “decommissioning” of legacy system and the usage(adoption) of oracle application system.

PM.010 – Define Transition Strategy

PM.020 – Design Production Support Infrastructure

PM.030 – Develop Transition and Contingency Plan

PM.040 – Prepare Production Environment

PM.050 – Set Up Applications

PM.060 – Implement Production Support Infrastructure

PM.070 – Verify Production Readiness

PM.080 – Begin Production

PM.090 – Measure System Performance

PM.100 – Maintain System

PM.110 – Refine Production System

PM.120 – Decommission Former Systems

PM.130 – Propose Future Business Direction

PM.140 – Propose Future Technical Direction

thanks – Shivmohan Purohit

Oracle Financials – A Case Study

Here sharing recent communication on options to implement oracle Apps, i like you all to share you thoughts on this below scenario

I need the list of points or concerns  we need to take care of at the time of migration from 11.5.9 to

Also I am in need information for M&A (merger and acquisition) perspective.
Say Company A acquires Company B. Company A is on and Company B is on 11.5.9
Now A wants to get B on the same instance ( as a separate operating unit. In this case what are the areas we need to concentrate on.
e.g. Common Item Master, Customer base.., B’s interfaces with some 3rd party system, Transaction between A and B…… etc

It would be very great if you could help me with this information …


Here if we consider this M&A , where Company A on 11.10 is target/ parent and company B is on 11.9 as legacy / secondary.

This case is like adding new operating unit in 11.10 for Company A , where it has advantage that Company B is already on Oracle with a lower version. Now here I think few thought to start with
GL Chart Of Accounts – This need to review the COA structure between A and B , having more different will make things more complex , having same will be great benefit. COA structure will drive the complexity of whole exerercise.
SOB and MRC – need to review the kind of SOB and MRC setup in both A & B , this will drive the kind of impact it will have and complexity of whole exercise.

As point 1 and 2 will form the foundation for merging B in A , and will outline the base architecture for remaining transaction and mater data to move in Company A.
Supplier/ Party / Customer / Employee/ Item / Inventory – all these master data need to review for company B and see if it duplicating or it need some mapping/ merging / cleaning or translation etc ,
GL transaction / GL history / GL Balance – this need to bring in A , this will depend on GL COA and GL SOB structure changes happened from B to A
AP ( open Invoice, payment history ) – this will business decision if they like to bring , the strong advice is to close all open invoice and keep history transaction in kind of archinve database for Company B till legally it require, This need to review if these are really require to bring in Company A where they may or may not useful.
AR ( Transaction , receipts  , history etc ) – This is recommended to bring in A , because this is surely impact the cash flow and allow lot of benefit to make future analysis and drive cash operations. Company A having any kind of Datawahrehouse , then I would recommend to bring all AP and AR transaction and history there directly rather bringing them in Apps and then send to DWH.
For fixed assets – I don’t see it could be much issue, all assets need to bring in A , and surely this can be manually if they are less in volume or  in automated way if large enough.

Another aspect to look for is Third party integration, Customization , Extension, Custom Reporting – As company B is moving to higher version so it need to consider as upgrade for them , from that point of view, you need to review if any changes needed in integration, or enhance/decommission customization, any more reports or less report need to migrate from B to A.

All above thoughts are surely very high level and brief , based on complete study , it will be more than 200 action items or more will outline only for fianancials to bring from Company  B to A , so it is very much driven from GL Structure between B and A and IT strategy of organization.

Thanks – Please share you thoughts to help in outline better approach

Oracle 11g – PL SQL – New features — Reference

I found a nice article, contain reference DocID from Metalink to know latest and new features of Oracle Database 11g PL/SQL

All the new features available with 11.1 release of oracle are listed together in
Note 869084.1 : New PL/SQL Features for 11g Release 1 (11.1).
The contents of the article is pasted below.

New PL/SQL Features for 11g Release 1 (11.1)

  • PL/SQL Native Compilation
    In Oracle 11g, PL/SQL native compilation requires no C compiler, no DBA intervention and is fully supported in a RAC environment. By setting the PLSQL_CODE_TYPE to a value of NATIVE , rather than the default value of INTERPRETED , code is compiled directly to machine code . When the code is called, it is loaded into shared memory, making it accessible for all sessions in that instance. The %_PLSQL_OBJECT_SETTINGS views include the current PLSQL_CODE_TYPE setting for each PL/SQL object.
    Note 462449.1 : PL/SQL Native Compilation on Oracle 11G


  • PL/SQL Function Result Cache
    The cross-session PL/SQL function result cache provides a simple way to boost the performance of PL/SQL functions by saving the results of function calls for specific combinations of input parameters in the SGA. These results can be reused by any session calling the same function with the same parameters. This can result in a significant performance boost when functions are called for each row in an SQL query, or within a loop in PL/SQL. This article describes the usage and administration of the function result cache.
    Note 430887.1  : 11g New Feature PL/SQL Function Result Cache


  • PL/SQL Fine-Grained Dependencies
    Prior to Oracle11g, modifying the structure of a table would make dependent views invalid even thought the change to table has nothing to do with view.That is why starting from Oracle11g a change to table that does not effect view logically, will NOT make view invalid. So, if you add a column to table T1 then because of fine grained dependency tracking system applied by Oracle 11g, it doesn’t make view invalid.
    Note 430725.1 : 11g New Feature Fine-Grained Dependencies


  • PL/SQL Continue Statement
    In Oracle Database 11g PL/SQL has a new construct called CONTINUE , which is used in a loop. The statement moves the logic to the end of the loop and then to the beginning of the loop.The CONTINUE statement jumps out of the current loop iteration and starts the next one. It can be used on its own, or as part of a CONTINUE WHEN statement. This type of processing has always been possible using IF statements either on their own or with exceptions or GOTO statements, but the CONTINUE statement is neater and brings PL/SQL in line with other languages.
    Note 469584.1 : 11g New Feature PLSQL CONTINUE Statement


  • PL/SQL Automatic Subprogram Inlining
    Every call to a procedure or function causes a slight, but measurable, performance overhead, which is especially noticeable when the subprogram is called within a loop. Avoiding procedures and functions is not an option, as it goes against the concept of modular programming, making programs bulky and difficult to manage. Automatic subprogram inlining can reduce the overheads associated with calling subprograms, whilst leaving your original source code in its normal modular state. This is done by replacing the subprogram calls with a copy of the code in the subprogram at compile time.
    The process of subprogram inlining is controlled by the PLSQL_OPTIMIZE_LEVEL parameter and the INLINE pragma. When PLSQL_OPTIMIZE_LEVEL=2 (the default), the INLINE pragma determines whether the following statement or declaration should be inlined or not. When PLSQL_OPTIMIZE_LEVEL=3, the optimizer may inline code automatically. In this case the INLINE pragma can turn it off inlining for a statement, or increase the likelihood that the optimizer will choose to inline a statement.


    The SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE data types are predefined subtypes of PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE, respectively. Each subtype has the same range as its base type and has a NOT NULL constraint.
    SIMPLE_INTEGER differs significantly from PLS_INTEGER in its overflow semantics, but SIMPLE_FLOAT and SIMPLE_DOUBLE are identical to their base types, except for their NOT NULL constraint.
    You can use SIMPLE_INTEGER when the value will never be NULL and overflow checking is unnecessary. You can use SIMPLE_FLOAT and SIMPLE_DOUBLE when the value will never be NULL. Without the overhead of checking for nullness and overflow, these subtypes provide significantly better performance than their base types when PLSQL_CODE_TYPE=’NATIVE’, because arithmetic operations on SIMPLE_INTEGER values are done directly in the hardware. When PLSQL_CODE_TYPE=’INTERPRETED’, the performance improvement is smaller.
    Note 433091.1 : 11g New Features SIMPLE_INTEGER Datatype

  • Named and Mixed Notation in PL/SQL Subprogram Invocations
    Before Release 11.1, a SQL statement that invoked a PL/SQL subprogram had to specify the actual parameters in positional notation. As of Release 11.1, named and mixed notation are also allowed. This improves usability when a SQL statement invokes a PL/SQL subprogram that has many defaulted parameters, and few of the actual parameters must differ from their default values.
    Note 433070.1 : 11g New Feature Using Named and Mixed notation in PL/SQL subprograms


  • Enhancements to Regular Expression Built-in SQL Functions
    The regular expression built-in functions REGEXP_INSTR and REGEXP_SUBSTR have increased functionality. A new regular expression built-in function, REGEXP_COUNT, returns the number of times a pattern appears in a string. These functions act the same in SQL and PL/SQL.


  • Sequences in PL/SQL Expressions
    The pseudocolumns CURRVAL and NEXTVAL make writing PL/SQL source code easier for you and improve run-time performance and scalability. You can use sequence_name.CURRVAL and sequence_name.NEXTVAL wherever you can use a NUMBER expression.
    Note 433068.1 : Sequences in PL/SQL Expressions
  • Dynamic SQL Enhancements
    Both native dynamic SQL and the DBMS_SQL package have been enhanced.Native dynamic SQL now supports a dynamic SQL statement larger than 32 KB by allowing it to be a CLOB.
    In the DBMS_SQL package:
    • All data types that native dynamic SQL supports are supported.
    • The DBMS_SQL.PARSE function accepts a CLOB argument, allowing dynamic SQL statements larger than 32 KB.
    • The new DBMS_SQL.TO_REFCURSOR Function enables you to switch from the DBMS_SQL package to native dynamic SQL.
    • The new DBMS_SQL.TO_CURSOR_NUMBER Function enables you to switch from native dynamic SQL to the DBMS_SQL package.
    Note 435251.1   : How Native Dynamic Sql Supports Statements > 32K Characters
    Note 567856.1 : New Feature in 11g , DBMS_SQL.TO_REFCURSOR and DBMS_SQL.TO_CURSOR_NUMBER.


  • Compound Trigger
    A compound trigger allows different blocks within a trigger to be executed at different timing points. It has a declaration section and a section for each of its timing points. All of these sections can access a common PL/SQL state.
    Note 430847.1 : 11g NEW PL/SQL COMPOUND Trigger

Oracle Interview Questions – Oracle Forms – Custom Development – FAQ

What is the ICX: Forms Launcher system profile option for?

This profile option is used by the Self Service Web Applications Personal Home Page (also known as ICX) to determine the base URL needed to launch an application, which in this case is a Forms application.

What should ICX: Forms Launcher be set to?

ICX: Forms Launcher –is set to ?http://machine_name:port/dev60cgi/f60cgi?. In Apps release 11i you can add some parameters to this URL to enable some Forms features like tracing.

FORMS60_TIMEOUT is an environment setting that determines the maximum idle time (in minutes) before f60webmx shuts down.  Note that it will only terminate an idle middle tier process, i.e. one which is waiting for the "next message" from the client. If the middle tier process is running a transaction or waiting for a query to complete it will not have take effect.

How do you enable Forms Runtime Diagnostics (FRD) in Apps?
To enable Forms Runtime Diagnostics (FRD):

– login to Apps as sysadmin
– click on Profile/System
– add the following to the end of its value at user level:

Note that like any Apps profiles you can do this on various levels e.g. site, user, etc. so that you target your FRD accordingly. Also be aware that FRD incurs significant overhead to Apps processing so disable it when not in use

How do you generate Apps Forms modules or FMB files?
Oftentimes as part of upgrading Forms or modifying a Form module or applying a patch modifying a Form module, you would encounter an issue that would necessitate manually regenerating the Forms module executable or FMX file. To do this you issue the following command:

f60gen module=form_name.fmb userid=apps/apps output_file=form_name.fmx module_type=form batch=yes compile_all=special

How do you generate Apps Forms library modules or PLL files?
To do this you issue the following command:

f60gen module=library_name.pll userid=apps/apps module_type=library batch=yes compile_all=special

How do you generate Apps Forms menu modules or MMB files?
Note that there is only one menu for Apps so it is rare that you need to regenerate the menu. To do this you issue the following command:

f60gen module=FNDMENU.mmb userid=apps/apps output_file=FNDMENU.mmx module_type=menu batch=yes compile_all=special

What is the sequence in which Forms resolves program unit calls?
Forms will attempt to resolve a program unit call in the following sequence:

Program Units node
Forms Library

Where can you find the source Forms modules for Apps?
Apps places all its source (FMB) files in the $AU_TOP/forms/<language> directory whereas the FMX files reside in their respective $PRODUCT_TOP/forms/<language> directory. On the other hand, all menu (MMB, MMX) and library (PLL and PLX) files are copied to the $AU_TOP/resource directory.

How can you find the version of Forms in Apps?
Once you are in an Apps form …

click on Help
click on About Oracle Applications

information is under the Current Form section. The Form Name corresponds to the FMB and FMX file for that form.  The Form Version corresponds to the release level of that form.

How can I open an Apps form in Forms Builder

If you want to open a product specific form, make sure you have all its associated forms and libraries copied over.  Since it is quite difficult to pinpoint exactly what this is, its best to just copy over the entire contents of $AU_TOP/forms/<language> and $AU_TOP/resource.

How can you confirm the version of the FMX or FMB file?
Oftentimes, once you determine the version and name of the current form you want to peek into the internal workings of the actual form itself. To ensure that you are peeking at the right release of the FMB file you can do the following:

$adident Header FNDSCSGN.fmb

If you simply copy the form over to a a machine that does not have Apps installed, you will run into is a lot of FRM-18108: "Failed to load the following objects" errors.  This is because an Apps form is usually dependent on a host of other forms, libaries and menus.

If you want to open the TEMPLATE.fmb form, make sure you have the following forms, libraries, copied over as well from the $AU_TOP/forms/<language> and $AU_TOP/resource directories:


What is a Forms Builder node?

    A Forms Builder node refers to the leaves and branches of its Object Navigator. The Object Navigator provides a hierarchical display of the objects in a Form modules.

       +Attached Libraries
       +Data Blocks
       +Object Groups
       +Pop Up Menus
       +Program Units
       +Property Classes
       +Record Groups
       +Visual Attributes
    +PL/SQL Libraries
    +Object Libraries
    +Built-In Packages
    +Database Objects

    Objects are grouped under their respective nodes. For example, all of the windows defined in a form module appear under the Windows node; all the LOVs defined in the form module appearunder the LOVs node and all the program units appear under the Program Units node.  It is important to familiarize yourself with this in case you need to navigate within an Apps form during the course of debugging a problem.



How To Setup A Custom Concurrent Host Program

1. Create UNIX Shell script using naming convention filename.prog
2. Place in the custom PRODUCT_TOP/bin directory-
3. Set protection on the Host Program
4. Link with program fndcpesr: Ex: link -s $FND_TOP/bin/fndcpesr $XXX_TOP/bin/hostprog Note: Exclude the .prog extension
5. Register Concurrent Program Executable
6. Register Concurrent Program Define
7. Add program to request group for Responsibility

Oracle Application – Top useful SQL Queries

Friends, here some of quite useful regular sql queries in oracle applications.

Query 1: Select responsibility name along with application name

SELECT application_short_name ,

frt.responsibility_id, frt.responsibility_name

FROM apps.fnd_responsibility_tl frt , fnd_application fa

WHERE fa.application_id = frt.application_id;

Query 2: Get Menu name for Responsibility ID , You can find out responsibility_id from Query 1

SELECT DISTINCT a.responsibility_name, c.user_menu_name

FROM apps.fnd_responsibility_tl a,

apps.fnd_responsibility b,

apps.fnd_menus_tl c,

apps.fnd_menus d,

apps.fnd_application_tl e,

apps.fnd_application f

WHERE a.responsibility_id(+) = b.responsibility_id

AND a.responsibility_id = &resp_id

AND b.menu_id = c.menu_id

AND b.menu_id = d.menu_id

AND e.application_id = f.application_id

AND f.application_id = b.application_id


Query 3: Get User name and related assigned responsibilities

SELECT distinct u.user_id, u.user_name user_name,

r.responsibility_name responsiblity,

a.application_name application

FROM fnd_user u,

fnd_user_resp_groups g,

fnd_application_tl a,

fnd_responsibility_tl r

WHERE g.user_id(+) = u.user_id

AND g.responsibility_application_id = a.application_id

AND a.application_id = r.application_id

AND g.responsibility_id = r.responsibility_id

order by 1;

Query 4: Get Request Group associate with Responsibility Name

SELECT responsibility_name responsibility, request_group_name,


FROM fnd_request_groups frg, fnd_responsibility_vl frv

WHERE frv.request_group_id = frg.request_group_id

ORDER BY responsibility_name

Query 5: Gets Form personalization listing

Personalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tables

applsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes

SELECT ffft.user_function_name “User Form Name”, ffcr.SEQUENCE,

ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event,

ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query

FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft

WHERE ffcr.ID = ffft.function_id


Query 6: Query to view the patch level status of all modules

SELECT a.application_name,

DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status,


FROM apps.fnd_application_vl a, apps.fnd_product_installations b

WHERE a.application_id = b.application_id;

Query 7: SQL to view all request who have attached to a responsibility

SELECT responsibility_name , frg.request_group_name,

fcpv.user_concurrent_program_name, fcpv.description

FROM fnd_request_groups frg,

fnd_request_group_units frgu,

fnd_concurrent_programs_vl fcpv,

fnd_responsibility_vl frv

WHERE frgu.request_unit_type = ‘P’

AND frgu.request_group_id = frg.request_group_id

AND frgu.request_unit_id = fcpv.concurrent_program_id

AND frv.request_group_id = frg.request_group_id

ORDER BY responsibility_name;

Query 8: SQL to view all requests who have attached to a responsibility

SELECT responsibility_name , frg.request_group_name,

fcpv.user_concurrent_program_name, fcpv.description

FROM fnd_request_groups frg,

fnd_request_group_units frgu,

fnd_concurrent_programs_vl fcpv,

fnd_responsibility_vl frv

WHERE frgu.request_unit_type = ‘P’

AND frgu.request_group_id = frg.request_group_id

AND frgu.request_unit_id = fcpv.concurrent_program_id

AND frv.request_group_id = frg.request_group_id

ORDER BY responsibility_name;

Query 9: SQL to view all types of request Application wise

SELECT fa.application_short_name,



DECODE (fcpv.execution_method_code,

‘B’, ‘Request Set Stage Function’,

‘Q’, ‘SQL*Plus’,

‘H’, ‘Host’,

‘L’, ‘SQL*Loader’,

‘A’, ‘Spawned’,

‘I’, ‘PL/SQL Stored Procedure’,

‘P’, ‘Oracle Reports’,

‘S’, ‘Immediate’,


) exe_method,

output_file_type, program_type, printer_name,


minimum_length, concurrent_program_name,


FROM fnd_concurrent_programs_vl fcpv, fnd_application fa

WHERE fcpv.application_id = fa.application_id

ORDER BY description

Query 10: SQL to view concurrent request processing time, quite useful

SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name

, f.actual_start_date actual_start_date

, f.actual_completion_date actual_completion_date,


|| ‘ HOURS ‘ ||

floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) –


|| ‘ MINUTES ‘ ||

round((((f.actual_completion_date-f.actual_start_date)*24*60*60) –

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 –

(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) –

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))

|| ‘ SECS ‘ time_difference


DECODE(p.concurrent_program_name,’ALECDC’,p.concurrent_program_name||'[‘||f.description||’]’,p.concurrent_program_name) concurrent_program_name

, decode(f.phase_code,’R’,’Running’,’C’,’Complete’,f.phase_code) Phase

, f.status_code

FROM apps.fnd_concurrent_programs p

, apps.fnd_concurrent_programs_tl pt

, apps.fnd_concurrent_requests f

WHERE f.concurrent_program_id = p.concurrent_program_id

and f.program_application_id = p.application_id

and f.concurrent_program_id = pt.concurrent_program_id

and f.program_application_id = pt.application_id

AND pt.language = USERENV(‘Lang’)

and f.actual_start_date is not null

ORDER by f.actual_completion_date-f.actual_start_date desc;