Learn Oracle Applications

Oracle Apps Database Structure

2012-09-25T12:16:00.001-07:00

Database Structure:-

In this post we will be learning about Oracle Apps Database Structure ,

Schema: Schema is a place in database which contains the database objects like tables,

indexes, sequences, views and synonyms.

Oracle Apps DB Structure

In oracle applications database we have individual schemas for each module

application. These schemas contain the database objects of only that particular module.

When we connect any database schema we can access only that schema database

objects. We can’t access other database objects. For data integrity purpose the oracle

has deigned one more schema called Apps schema which contains only synonyms and

this schema have the rights to access any other module database objects.

Note: Apps Schema will not contain the tables it contain only synonyms.

Projects in Oracle Applications:

Implementation Projects: Implementation means we will develop the forms,

reports from the scratch. In this project we will be work in the areas of the forms,

reports, interface, customizations and interfaces.

Customization Projects: In this we customize some standard reports.

Migration / Up gradation Projects: This will be moving from the Old version to the

New version.

Support / Maintenance Projects: Solving the issues which are raised by the end

user while entering the data.

Post Implementation Projects: This will be done at free of cost. It’s a real time

testing.

Types of Docs in Oracle Apps:

MD 050 -- Module design by the functional consultant.
MD 070 -- Technical Document designed by the technical consultant.
MD 020 -- Technical document designed by the functional document.
MD 0120 -- Migration and user training document by the technical consultant.
CV 040 -- Conversion of the functional document by the functional consultant.
CV 060 -- Conversion of the technical document by the technical consultant.

Note: Conversion means moving the data from the legacy system to the apps system.

Introduction to Oracle Apps

2012-09-24T13:14:00.000-07:00

Introduction:

The Oracle Applications is an ERP, which was developed by the Oracle Corp and
was used by the several companies to utilize several resources contained with them.

Business Flow of Oracle Applications:

Oracle Applications is designed for the Financial and Manufacturing clients. The
categories in the modules are given below.

Financial Modules: Account Payables, Account Receivables, General Ledger and Fixed
Assets.
Manufacturing Modules: Purchasing, Inventory, Bill Of Materials, Work in Progress and
Order Management.

In HRMS we maintain all the details of the employee as well as the organization details.
Module: It is a collection of forms and reports which are related to particular business
process.
Purchase order module has the forms and reports which are related to the purchasing
business process. It contains nearly 50,000 tables they are accessed by only purchase
order module.

Getting started with Oracle Apps

2012-09-24T12:59:00.002-07:00

ERP & AIM METHODOLOGY :-

After learning SQL & PLSQL concepts now we will start learning Oracle Applications with this post,

ERP: Enterprise Resource Planning
Here Resource means 5 m’s
1. Money
2. Man power
3. Machinery
4. Material and
5. Management

ERP Purpose: Planning for proper utilization of resources for a business organization .

ERP as a Product : all objects(i.e. forms, reports )and all other programs are
developed and throwly tested and those will be provided with these ERP.
Methodology : the process used to implement ERP Product for a business Organization.

AIM (APPLICATION IMPLEMENTATION METHODOLOGY):
Methodology for Oracle Apps is AIM(Applications Implementation Methodology)
Phases in Aim Methodology:
1. Definition Phase
2. Operational Analysis Phase
3. Solution Design Phase
4. Build Phase
5. Transition Phase
6. Production Phase

1. Definition Phase: Here the functional consultants prepare the sample structure
(Blue Print) for the real business by gathering the information.

2. Operational Analysis phase: In this the functional consultant prepare BR100
(Business Requirement).

3. Solution Design Phase: here the functional consultant will prepare MD50 by using
BR100. MD50 is the functional document.

4. Build Phase: here the technical consultant converts MD50 to MD70. MD70 is the
technical document. Then the technical consultant prepare the final object and go
with testing, i.e. Unit Testing.

5. Transition Phase: here SIT(System Integration Testing) and UAT(User
Acceptance Testing) is done. Functional consultant does SIT. After this the
functional consultant will train the user in oracle apps product and user does UAT.
If it is satisfied by the user, it is sign off and product will be given to the user.

6. Production phase: loading the project in the client place. Where the server of
oracle apps in the client place.

Triggers in Oracle PLSQL

2012-09-23T11:39:00.001-07:00

Triggers:

In this post we will learn about Triggers in Oracle PLSQL,

A Trigger is a procedure that will fire automatically by the database. When a

specified DML statement is run against the specified table. Triggers are useful for doing

things like advanced auditing of changes made to a column values in a table. The syntax

for creating the trigger is given below.

Triggers in PL/SQL

Syntax:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF} trigger_event

ON table_name

[FOR EACH ROW [WHEN trigger_condition]]

BEGIN

Trigger_body;

END;

Ex: This example is when we insert into a table t1 then the same data has to be inserted

into the table t2. For that we require two tables of one same column.

SQL> CREATE TABLE t1(

Eno number);

SQL> CREATE TABLE t2(

Eno number,

Ins_date date);

SQL> CREATE OR REPLACE TRIGGER razia_trigger

BEFORE INSERT ON TABLE t1

FOR EACH ROW

BEGIN

INSERT INTO TABLE t2

VALUES(:new.eno,sysdate);

END;

Note: For every insert operation on t1 it will insert the eno col values to t2 along with the current

sysdate in the other column.

Disabling and Enabling Trigger:

We can stop the trigger from firing by disabling the

trigger by using the ALTER TRIGGER statement. The syntax for enabling the trigger and

disabling the trigger is given below.

Syntax: ALTER TRIGGER trigger_name DISABLE;

ALTER TRIGGER trigger_name ENABLE;

Dropping a Trigger:

If we want to drop the trigger from the database then we have to

use DROP TRIGGER statement. The syntax for dropping the trigger is given below.

Syntax: DROP TRIGGER trigger_name;

PLSQL Packages

2012-09-23T11:31:00.001-07:00

Packages:

In this post we will be learning concepts of PLSQL Packages,

Tutorial for learning concepts of PLSQL Packages :-

Package is declarative part of the functions and procedures which are stored

in that package. There are two blocks in defining a package.

_ Package Specification

_ Package body

Package Specification: The package specification contains information about the

contents of the package. It does not contain code for any subprograms.

Syntax: CREATE [OR REPLACE] PACKAGE package_name {IS | AS}

Type_definition |

Procedure_specification |

Function_specification |

Variable_declaration |

Exception_declaration |

Cursor_declaration |

Pragma_declaration

END [package_name];

Package Body: The package body is separate data dictionary object from the package

header. It cannot be successfully compiled unless the package header is already been

successfully compiled.

Syntax: CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}

….

BEGIN

Initialization_code;

END package_name;

Ex: SQL> create or replace package vamsi_pack as

procedure razia_proc(mynum in number);

end vamsi_pack;

-- Declaration of Package Body and passing value to the procedure

create or replace package body vamsi_pack as

procedure razia_proc(mynum in number) as

cursor mycursor is select ename,empno,sal from emp where empno=mynum;

begin

dbms_output.put_line(' NAME ' || ' NUMBER ' || ' SALARY ');

for i in mycursor

loop

dbms_output.put_line(' '||i.ename||' '|| i.empno ||' '|| i.sal);

end loop;

exception

when others then

dbms_output.put_line('YOU HAVE DONE AN ILLEGAL OPERATION ');

end myproc;

end vamsi_pack;

Execution: SQL> exec vamsi_pack.razia_proc(7788);

Functions in Oracle PLSQL

2012-09-23T11:25:00.000-07:00

Functions:

In this post we will be learning Oracle Functions,

Tutorial for Functions in Oracle PLSQL

Function is similar to a procedure except that a function must return a value

to the statement from which it is called. The syntax for creating a function is given

below.

Syntax: SQL> CREATE [OR REPLACE] FUNCTION function_name

[(parameter_name[IN | OUT | IN OUT] type [,….])]

RETURN type;

{IS | AS}

BEGIN

Function_body

END function_name;

Ex: create or replace function haritha_func(p_empno number)

return varchar2 is

v_ename varchar2(20);

v_sal number;

begin

select ename,sal into v_ename, v_sal from emp where empno=p_empno;

if v_sal>=6000 then

return 'TRUE';

else

return 'FALSE';

end if;

end haritha_func;

Execution: SQL> select haritha_func(7788) from dual;

Ex: SQL> create or replace function haritha_fun(p_radius number)

return number as

v_pi number := 3.141;

v_area number;

begin

v_area := v_pi * POWER(p_radius,2);

return v_area;

end haritha_fun;

Execution: SQL> select haritha_fun(7) from dual;

Dropping Function and Procedure: When we want to drop the function or procedure

then we use this DROP statement the syntax for the DROP is given below.

Syntax for Dropping Function:

DROP FUNCTION function_name;

Syntax for Dropping Procedure:

DROP PROCEDURE procedure_name;

Procedures in PL/SQL

2012-09-23T10:53:00.000-07:00

Procedures:

In this post post we will be learning PL/SQL procedure,

Tutorial for Procedures in PL/SQL :

Procedures are also known as subprograms. Given below is the syntax for

the creation of the procedure.

Syntax:
CREATE [OR REPLACE] PROCEDURE procedure_name

[(argument [{IN | OUT | IN OUT}] type,

…

[(argument [{IN | OUT | IN OUT}] type) ] {IS | AS}

BEGIN

procedure_body

END procedure_name;

Ex: SQL> create or replace procedure razia_proc as

v_sal number;

cursor razia_cursor is select * from emp order by deptno;

begin

v_sal := &v_s;

for i in razia_cursor

loop

if(i.sal > v_sal) then

dbms_output.put_line( 'Employee Name: ' || i.ename);

end if;

end loop;

exception

when others then

dbms_output.put_line( 'YOU HAVE PERFORMED AN ILLEGAL OPERATION');

dbms_output.put_line( 'THE PROGRAM MAY TERMINATE NOW');

end razia_proc;

Execution: exec razia_proc;

Passing IN Parameter to the Procedures:

Ex: SQL> create or replace procedure kanthi_proc(p_inpar in number) as

v_name varchar2(30);

begin

select ename into v_name from emp where empno=p_inpar;

dbms_output.put_line( 'Employee Name: ' || v_name);

exception

when others then

dbms_output.put_line( 'YOU HAVE PERFORMED ILLEGAL OPERATION');

end kanthi_proc;

Using IN and OUT parameters to Procedures:

Ex: SQL> create or replace procedure

shabbir_proc(p_outpar out varchar2,p_inpar in number) as

begin

select ename into p_outpar from emp where empno=p_inpar;

end;

--To Catch the Output variable out side the procedure in the SQL

declare

v_name varchar2(20);

v_num number;

begin

mypro(v_name,&v_num);

dbms_output.put_line( 'Employee Name: ' || v_name);

end shabbir_proc;

Exceptions in PL/SQL

2012-09-23T10:33:00.003-07:00

Exceptions:

In this post we will be learning Exceptions in PL/SQL,

An Exception is a error handling statement. It is used when ever our

program terminates abnormally by using the exception we can execute the program

from abnormal termination to normal termination. There are mainly two types of

exceptions they are given below.

Predefined Exceptions.
User defined Exceptions.

Exception in PL/SQL

Predefined Exceptions: Oracle has predefined

several exceptions that correspond to

the most common oracle errors. Some predefined exceptions are given below.

LOGIN_DENIED

NO_DATA_FOUND

ZERO_DIVIDE

TOO_MANY_ROWS

STORAGE_ERROR

ROWTYPE_MISMATCH

INVALID_CURSOR

CURSOR_ALREADY_OPEN

Ex: SQL> declare

cursor test_cursor is select e.ename, d.deptno,

d. dname from scott.emp e, scott.dept d

where e.deptno=20 and d.dname='RESEARCH';

begin

for i in test_cursor

loop

dbms_output.put_line(i.ename || ' ' || i.deptno ||' '|| i.dname);

end loop;

exception

when no_data_found then

dbms_output.put_line('NO DATA FOUND EXCEPTION RAISED');

when others then

dbms_output.put_line(' An Error Raised ' || sqlerrm);

end test_cursor;

User Defined Exceptions: A user defined exception is an error defined by the

programmer. User defined exceptions are declared in the declarative section of the

PL/SQL block just like variables.

Syntax: DECLARE

E_myexception EXCEPTION;

Ex: SQL> create or replace procedure razia_proc(v_sal in number) as

v_sl number;

razia_excep exception;

begin

if(v_sal >= 5000)

then

raise razia_excep;

else

select sal into v_sl from emp order by deptno;

end if;

exception

when razia_excep then

dbms_output.put_line( 'YOU HAVE PERFORMED AN ILLEGAL OPERATION');

end razia_proc;

Learning Cursors in PL/SQL

2012-09-23T09:48:00.000-07:00

PL/SQL Cursors:

In this post we will be Learning Cursors in PL/SQL,

Cursor is a private SQL area provided by the oracle engine. It is mainly used to

retrieve the data from more than one column. There are two types of cursors they are

given below.

Implicit Cursors
Explicit Cursors

Implicit Cursors:
Implicit cursor raises implicitly when we use INSERT, UPDATE,DELETE and SELECT..INTO statements. Because the Implicit cursor is opened and closed by the PL/SQL engine.

Ex: SQL> declare

v_edata emp%ROWTYPE;

begin

select * into v_edata from emp where empno=7788;

if sql%notfound then

dbms_output.put_line('Record Not Found');

else

dbms_output.put_line('Record Found');

end if;

end;

Processing Explicit Cursors: The four PL/SQL steps necessary for explicit cursor

processing are as follows.

Declare the cursor.
Open the cursor for a Query.
Fetch the results into PL/SQL variables.
Close the Cursor.

Declaration of Cursor: The declaration of the cursor is associated with the select

statement. The syntax for the cursor is given below.

Syntax: CURSOR cursor_name IS select_statement;

Ex: -- To print the Cumulative SAL of the Employee table order by empno

SQL>declare

v_sal number;

v_dno number;

l_count number;

cursor test_cursor is select * from scott.emp order by empno;

begin

v_sal:=0;

v_dno:=0;

for i in test_cursor

loop

if(i.deptno!=v_dno) then

v_sal:= 0;

end if;

v_sal:=v_sal+i.sal;

dbms_output.put_line('Ename: ' || i.ename||' Sal: '|| i.sal || '_

Cum_sal: '|| v_sal || 'Deptno: ' || i.deptno);

l_count:=test_cursor%rowcount;

end loop;

dbms_output.put_line(l_count||' Row(s) Fetched by the Cursor');

Oracle Applications 11.5.9 CS – Software Solutions

end test_cursor;

Cursor Attributes: There are four cursors available in PL/SQL that can be applied to

cursors. Those are given below.

%FOUND
%NOTFOUND
%ISOPEN
%ROWCOUNT

%FOUND: %FOUND is a Boolean attribute. It returns TRUE if the previous FETCH

returned a row and FALSE if it didn’t.

%NOTFOUND: %NOTFOUND is also a Boolean attribute. It returns FALSE if the

previous FETCH returned a row and TRUE if it didn’t. It behaves opposite to the

%FOUND.

%ISOPEN: %ISOPEN is a Boolean attribute. It returns TRUE if the associated cursor is

open other wise it will return FALSE.

%ROWCOUNT: %ROWCOUNT is a Numeric attribute. It returns number of rows

returned by the cursor so far.

Declaring, Opening Fetching and Closing the Cursor:

Ex: SQL> declare

v_eno number;

v_ename varchar2(20);

l_count number;

cursor razia_cursor is select empno,ename from scott.emp;

begin

open razia_cursor;

loop

fetch razia_cursor into v_eno, v_ename;

exit when razia_cursor%notfound;

l_count:=razia_cursor%rowcount;

dbms_output.put_line('Ename: ' || v_ename||' ENUM: '|| v_eno);

end loop;

close razia_cursor;

dbms_output.put_line(l_count||' Row(s) Fetched by the Cursor');

end;

Cursors in PL/SQL Tutorial :

Ex: SQL> -- Example to print the odd rows in the Table EMP

declare

n number;

l_count number;

cursor razia_cursor is select empno,ename,rownum from scott.emp;

begin

for i in razia_cursor

loop

n:=mod(i.rownum,2);

if (n>0) then

dbms_output.put_line('Empno: ' || i.empno||' NAME: '|| i.ename||'

ROWNUM: '|| i.Rownum);

end if;

l_count:=razia_cursor%rowcount;

end loop;

dbms_output.put_line(l_count||' Number of Row(s) Fetched by the Cursor');

end razia_cursor;

PL/SQL CONCEPTS

2012-09-23T09:25:00.004-07:00

Learn PL/SQL CONCEPTS:-

After learning SQL Concepts, Its time to move on PL/SQL,

Oracle added a procedural programming language known, as PL/SQL (Procedural

Language/SQL). PL/SQL is a third generation language and contains the standard

programming constructs.

PL/SQL

Anonymous Block:

An Unnamed block which will not store any where in the database is

know as Anonymous block.

Block Structure: PL/SQL programs are divided up into structures known as blocks, with

each block containing the PL/SQL and SQL statements. The syntax for the structure isgiven below.

Syntax:

DECLARE

Variable declaration

Begin

Executable_statements

[Exception

Exception_handling_statements]

End;

Ex: SQL> SET SERVEROUT ON;

SQL> Declare

v_1 number;

v_2 number;

v_3 number;

Begin

v_1:=&v_1;

v_2:=&v_2;

v_3:=&v_3;

if v_1 < v_2 and v_1 < v_3 then

dbms_output.put_line('1st is small');

elsif v_2 < v_3 then

dbms_output.put_line('2nd is small');

else

dbms_output.put_line('3rd is small');

end if;

Exception

When zero_divide then

DBMS_OUTPUT.PUT_LINE(‘Division by ZERO’);

End;

O/p: It will ask for the three input values and will display the smallest number in that.

Difference between PL/SQL and SQL: In PL/SQL there is no buffer to hold the values

so we pass into variables. Here in SQL there is buffer to hold the data temporarily.

Ex: SQL> Select ename from emp where is empno = 7788;

SQL> Declare

V_name varchar2(10);

Begin

Select ename into v_name from emp where empno = 7788;

Oracle Applications 11.5.9 CS – Software Solutions

End;

SQL Trace & TKPROF Concepts

2012-09-23T02:34:00.000-07:00

SQL Trace:-

Now we will learn about SQL Trace & TKPROF :-

SQL Trace gives a wide range of information & statistics that used to tune a

group of SQL operations. We do the Sequel Trace at three levels.

1. SQL

2. Reports

3. Forms

_ Trace the Sequel Statements. How much time it was taking, how many rows it was

fetching, all the information was given from SQL Trace.

Steps for generating Trace file:

_ Enable the Trace.

_ Run the DML statements.

_ Disable the Trace.

_ Get the Trace file.

_ Convert the Trace File to Readable Format.

_ The Trace file was generated with he extension .TRC. Oracle has give specified

directory for trace files. To get the path use the query below.

EX: SQL> Select value from

V$PARAMETER

Where name = ‘USER_DUMP_DEST’;

_ To get the name of the Trace file also we have to use the Query.

SQL> Select c.value || ‘\ORA’ || TO_CHAR(a.spid, ‘FM00000’) || ‘.TRC’

From

V$PROCESS a,

V$SESSION b,

V$PARAMETER c

Where

a.addr = b.paddr

and b.ausid = userenv(‘sessionid’)

and c.name = ‘USER_DUMP_DEST’;

TKPROF: Copy the trace file, which was generated earlier, and paste it in your custom

directory.

Syntax: CMD> TKPROF d:\siri_0016.trc siri_0016.txt

_ The Chaitu_0016.txt was created in the same drive where the Trace file was located.

Ex: --Query to Print the Cumulative Salary of the Employee table order by DEPTNO

SQL> Select deptno, ename, sal, sum(sal) over(partition by

deptno order by deptno, ename) “CUM_SAL”

from

scott.

Learn SQL Sequence

2012-09-23T02:06:00.002-07:00

SQL Sequence :-

We will be learning SQL Sequences in this post,

Sequence: A sequence is a database item that generates a sequence of integers. We

create the sequence using the CREATE SEQUENCE statement.

Sequence

Syntax: Create Sequence Sequence_name

[Start with Start_num]

[Increment by increment_num]

[ {MAXVALUE max_num | NOMAXVALUE} ]

[ {MINVALUE min_num | NOMINVALUE} ]

[ {CYCLE | NOCYCLE} ]

[ {ORDER | NOORDER} ];

Ex: SQL> Create Sequence medha_seq_0016

Start with 100 Increment by 1;

Sequence Created.

Using the Sequence:

Ex: SQL> Select medha_seq_0016.currval ”Currval”,

medha_seq_0016.nextval “Nextval”

From

Dual;

Output: Currval Nextval

--------- ----------

101 101

Modifying the Sequence: If we want to modify the sequence by using the ALTER

SEQUENCE we can do that.

Ex: SQL> Alter Sequence medha_seq_0016

Start with 1000 Increment by 2;

Sequence Altered.

Dropping a Sequence: If we want to drop the sequence then we use this DROP

STATEMENT.

Ex: SQL> Drop Sequence medha_seq_0016;

Sequence Dropped.

Learn SQL Views

2012-09-23T00:11:00.002-07:00

SQL Views:-

In this post we will be learning about SQL Views.

Views: A view is basically a predefined query on one or more tables. Retrieving

information from the view is done in the same manner as retrieving from the table.

Creating a View: by using the create view statement we can create a view.

SQL Views

Syntax: Create [Or Replace]

View view_name

[(allias_name[,allias_name….])]

AS subquery

[WITH {CHECK OPTION | READ ONLY} CONSTRAINT

Constraint_name];

Ex: SQL> Create view medha_0016_view AS

Select ename, eno, address from

Chaitu_0016;

View Created.

Performing an Insert using a View: we can also perform DML operations using the

views. Given below is the example for that.

Ex: SQL> Insert into medha_0016_view(

Ename, eno, address) Values (CHAITU, 0016, HYD);

1 Row Created.

Modifying a View: we can modify the view using the REPLACE. If there any view

existing with that name then it was modified with the current one.

Ex: SQL> Create or Replace

view medha_0016_view

Select a.ename, a.eno, a.address, b.city from

Chaitu_0016 a, chaitu_0011 b

Where a.eno = b.eno;

Dropping a View: when want to drop the view we use this statement. Only the view

will be dropped from the database the table was not effected.

Ex: SQL> Drop view chaitu_0016_view;

In next post we will be learning SQL Sequence, So keep learning and start sharing :) Happy Blogging!!

Learning SQL Tables

2012-09-22T17:05:00.000-07:00

SQL Tables: -

After learning types of SQLstatements in my last post, Now we will learn about SQL Tables,

Table is a database object which holds the data and contains one or more

columns associated with its datatypes

Table Structure

Creating a Table: we use the create table statement to create a table. The simple

syntax for the creation of table.

Syntax: CREATE Table table_name

(column_name type [CONSTRAINT constraint_def DEFAULT default_exp],

column_name type [CONSTRAINT constraint_def DEFAULT default_exp],

column_name type [CONSTRAINT constraint_def DEFAULT default_exp]…

)

[ON COMMIT {DELETE | PRESERVE} ROWS]

TABLESPACE tab_space;

Ex: SQL> Create table chaitu_0016(ename varchar2(10),

Eno number unique);

Table Created.

Altering a Table: we can alter the table using the alter statement. The alter table

statement perform such tasks given below.

_ Add, modify, or drop a column.

_ Add or drop a constraint.

_ Enable or Disable a constraint.

Ex: SQL> Alter table chaitu_0016

Add address varchar2(10);

Modifying a Column:

Ex: SQL> Alter table chaitu_0016

Modify address varchar2(20);

Dropping a Column:

Ex: SQL> Alter table chaitu_0016

Drop address;

Renaming a Table: If we want to change the name of the table then we use this

RENAME statement.

Ex: SQL> Rename table chaitu_0016

Chaitu_0015;

Truncating a Table: If we want to delete all the rows existing in the table then we use

the TRUNCATE Statement.

Ex: SQL> Truncate table chaitu_0015;

Dropping a Table: If we want to drop the total structure along with the records existing

in the table we use this DROP statement.

Ex: SQL> Drop table chaitu_0015;

Thanks a lot for reading.. Happy Blogging!

Learn SQL Basic Concepts

2012-09-22T16:00:00.002-07:00

SQL CONCEPTS:-

Hello Friends,

Before Starting with Oracle Apps we will brush up some SQL & PL/SQL concepts.

Today we will discuss SQL basics.

Learn SQL Concepts

Structured Query Language(SQL) is the standard language designed to access

relational databases. SQL is pronounced either as the word Sequel or as the letters SQL.

SQL uses simple syntax that is easy to learn and use there are five types of SQL

statements given below.

Query Statements: Allow you to retrieve the rows stored in the database tables. You

write a query using the SQL SELECT statement.

Data Manipulation Language (DML) Statements: Allows you to modify the contents

of the tables. There are three DML statements.

_ INSERT - Allows you to Insert row(s) into the Table.

_ UPDATE - Allows you to change a Row.

_ DELETE - Allows you delete the rows from the Table.

Data Definition Language (DDL): Allows you to define the Data structures, such as

tables that make up a database. There are five basic types of DDL statements.

_ CREATE – Allows you to Create Database structure.

Ex: Create Table – Used to create a Table in the Database.

Create User – Used to create the database user.

_ ALTER – Allows you to modify a database structure.

_ DROP – Allows you to remove a database structure.

_ RENAME – Allows you to change the name of the table.

_ TRUNCATE – Allows you to delete the entire contents of the table.

Transaction Control (TC) Statements: Allows you to permanently record the changes

made to rows stored in a table or undo the changes. There are three TC Statements.

_ COMMIT – Allows you to permanently record the changes made to the rows.

_ ROLLBACK – Allows you to undo the changes made to the rows.

_ SAVEPOINT – Allows you to set a SAVEPOINT to which you can rollback changes

you made.

Data Control Language (DCL): Allows you to change the permissions on the database

structures. There are two DCL statements.

_ GRANT – Allows you to give another user access to your database structures,

such as tables.

_ REVOKE – Allows you to prevent another user from accessing to your database

structures, such as tables.

Welcome to Oracle Apps With Me Blog!

2012-09-22T14:41:00.000-07:00

Start Learning Oracle Apps With Me

"Sharing knowledge is not about giving people something, or getting something from them. That is only valid for information sharing. Sharing knowledge occurs when people are genuinely interested in helping one another develop new capacities for action; it is about creating learning processes."
- Peter Senge

Above quote inspired me to start my own blog on Oracle Apps technology.

Share with World

Let us share our knowledge to the world of Oracle Apps.

I am trying my best to do so and I request every one to contribute their part.

If you have any thing useful related to Oracle Apps, do send me I will post in my blog on behalf of you.

Thanks a lot for reading.. Happy Blogging!