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 Purchasing – Purchase Order FAQ

What is 2-way, 3-way, 4-way matching? How is this set-up?


2-way matching verifies that Purchase order and invoice information match within your tolerances as follows:

Quantity billed <= Quantity Ordered
Invoice price <= Purchase order price
(<= sign is used because of tolerances)

3-way matching verifies that the receipt and invoice information match with the quantity tolerances defined:

Quantity billed <= Quantity received

4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances defined:

Quantity billed <= Quantity accepted.
(Acceptance is done at the time of Inspecting goods).

Whether a PO shipment has 2-way, 3-way or 4-way matching can be setup in the Shipment Details zone of the Enter PO form (character)

Receipt required  Inspection required       Matching

Yes                            Yes                                         4-way

Yes                            No                                           3-way

No                              No                                           2-way

In GUI, Shipments block, alternative region, click on More…this brings up a region with ‘Invoice matching’ column where you can choose 2-way, 3-way or 4-way match.

How do you reflect discount from a supplier on a Purchase orders?
The legal document is the Purchase order not the quote and therefore the purchase order should reflect agreed upon price.  Secondly if the discount comes after the PO then AP should handle it when invoicing.  If you are using a blanket Purchase order then you can use price breaks.

How do you change the created date on a Purchase Order?
The 'Created' date on the Purchase order is not an updateable field, due to it being a legal document once approved.

What is the sorting order for requisition lines in autocreation of a Purchase Order?
Requisition lines first meet search criteria determined by "find" screen, and then are grouped together by item and item category, then by need-by date. After you choose an action, enter any search criteria to narrow the selection of requisition lines.  Oracle Purchasing chooses requisition lines from the requisition pool that meet your search criteria.  All requisition lines for the same item and item category are grouped together, but they are not necessarily in order alphabetically.  Within each grouping of item and item category, requisition lines are ordered by need-by date.

What does the error ‘APP-50022: Oracle Human Resources could not retrieve a value for User Type profile option’ mean?
The profile HR: User Type needs to be set. Set the profile option ‘HR: User Type’ at the Responsibility level

When do you see the status of a Purchase order as Archived?
You must approve or print a specific purchase order before you can see it in the history information.

In Oracle Purchasing, where is the automatic numbering for Purchase Order defined and maintained?
The navigation path for GUI is:
Setup --> Organizations --> Purchasing Options, Numbering alternate region

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

ERP and SCM Knowledge through books

Books are the best way to acquire knowledge about a suject that interests you. Even if you are new entrant to a field and you start with reading a good book, the learning curve will be much steeper for you. I was asked by a friend of mine whose organization has started taking baby steps towards implementing an enterprise wide supply chain solution and they want to acquire little knowledge prior to start engaging consultants in this regard. I suggested them to grab two of my favorite books on supply chain management and enterprise resource planning which are:
  •  Essentials of Supply Chain Management, 2nd Edition:- This is written by Michael H. Hugos and contains valuable tips, techniques, illustrative real-world examples, exhibits, and best practices in the are of SCM. This handy and concise paperback helps to stay up to date on the newest thinking, strategies, developments, and technologies in supply chain management. A quite useful possession indeed. This can be bought at most economical price here
  • Modern ERP: Select, Implement & Use Today’s Advanced Business Systems:- This book is written by Marianne Bradford and what is unique about this book is that it contains contributions from organizations like NetSuite, Microsoft, SAS, GlaxoSmithKline and Deloitte Consulting. This makes this book a much practical document to read and implement in rela life. Incidentally, this is one of the best selling books on ERP these days on Amazon
Just to make things easier for the readers, let me share an online store where both the books are available online so go ahead and have a look if they suit your objectives and interests.