OracleMine

FROM Clause in Oracle

From clause is one of the important clauses of Oracle SQL query.

It helps to retrieve data from a single table or view, multiple tables or views and in-line queries.

One table is mandatory to be mentioned in the from clause of the query.

If there are multiple tables, they are joined either using standard Oracle standards or ANSI standards.

FROM CLAUSE Syntax

SELECT * FROM…

Flashback Table in Oracle With Examples

Flashback table restores the existing tables to earlier versions using timestamp and SCN number. It is available in and after Oracle 10g version.

It also helps to retrieve removed tables from the database, dropped using DROP and TRUNCATE commands.

Tables altered using DDL statements can also not be restored to prior state with flashback operation.

One thing you must remember of flashback…

Delete Statement in Oracle

Delete statement in Oracle is used to delete existing records of the table.

You can delete a specific number of rows or all rows from the table.

Data from multiple tables cannot be deleted at once. Only single table data removal is possible using delete statement.

Delete is classified as DML (Data Manipulation Language) statement by Oracle.

DELETE Statement Syntax

DELETE FROM…

Update Statement in Oracle

Update statement in Oracle is used to update existing records of the table.

You can modify one or more than one columns using update statement.

Update statement is classified as DML (Data Manipulation Language) statement by Oracle.

UPDATE Statement Syntax

UPDATE [schema_name.]table_name SET column_1 = value_1 | expression_1, column_2 = (SELECT value_2 | expression_2 FROM table_name2 WHERE…

Insert Statement in Oracle

Insert statement in Oracle is used to insert a single or multiple records in the table.

There are two ways to perform an insertion of record/records in Oracle.

Direct Insert into the table

Insert into one table using other table

Below are the syntax and examples of both the kind of Insert statements.

INSERT Statement Syntax

Direct insert into the table

INSERT INTO [schema_name.]table_name…

SELECT statement in Oracle

The SELECT statement in Oracle is used to retrieve data from tables, views, materialized views and object tables.

SELECT Statement in Oracle Syntax

SELECT expression FROM [schema_name.]table_name WHERE condition_1 AND condition_2;

Parameters Explanation

schema_name

Name of the schema to which the table belongs. It is optional.

table_name

Name of the table you want to delete. It is…

TRUNCATE TABLE in Oracle

                                                                                                   There have been plenty of situations when you needed to empty the table data and fill it with fresh set of data. To perform this action truncate table statement is very useful.

Oracle classifies TRUNCATE as DDL (Data Definition Language) statement.

Syntax of TRUNCATE TABLE

TRUNCATE TABLE…

DROP TABLE in Oracle

Drop table in Oracle removes the table to the recycle bin or entirely from Oracle database. It is classified as DDL (Data Definition Language) statement by Oracle.

FLASHBACK TABLE statement released from Oracle 10g onward recovers the dropped table.

DROP TABLE Syntax

DROP TABLE table_name [CASCADE CONSTRAINTS] [PURGE];

Parameters Explanation

table_name

Table name to drop

CASCADE CONSTRAINTS

All…

ALTER TABLE in Oracle

ALTER TABLE in Oracle is used to modify table structure by adding or deleting columns, add and drop constraints to the table, change the length of column datatypes, drop and rename table columns and rename the table. It is classified as DDL (Data Definition Language) statement.

ALTER TABLE Syntax of ALTER TABLE

ALTER TABLE table_name  ADD column_name1 datatype | ADD CONSTRAINT constraint_name…

CREATE TABLE in Oracle

CREATE TABLE is used to create a new table in Oracle database. Oracle classifies it as DDL (Data Definition Language).

Syntax

CREATE TABLE table_name   (      columnname_1 datatype [ NOT NULL ],     columnname_2 datatype [ NOT NULL ],     ...     columnname_n datatype [ NOT NULL ]   );

Parameters Explanation

table_name

Name of the table you want to create

columnname_1, columnname_2……

Query to Get Employee Details in Oracle Apps

In Oracle HRMS, it becomes necessary to fetch details of employees for some tasks.

So it is extremely helpful to have such query easily available in the bookmark.

Below is the query to get employee details in Oracle Apps which you can add as favorites in your browser.

Query to Fetch Employee Details

SELECT pad.primary_flag, papf.employee_number "Employee Number", papf.title…

Standard Key Flexfields List of Oracle EBS R12 – OracleMine.com

Oracle E-Business Suite R12 or Oracle EBS R12 (cool name among Oracle Applications programmers) comprises of wide variety of modules such as Oracle HRMS, Oracle Receivables, Oracle Payables, Oracle Order Management, Oracle Purchasing, Oracle Finance etc.

As a programmer of Oracle Applications, I struggle many times to search for particular Key Flexfield in Oracle EBS R12 modules. Definitely, I…

Difference Between IN and EXISTS in Oracle

What is the difference between in and exists in Oracle? How can we know which is more cost efficient? The answer to all such questions lies in this post.

To understand the difference between IN and EXISTS, you must understand the definition of both first.

Check out other difference posts here >> Other Difference Posts

IN Condition in Oracle

IN condition is one of the widely used conditions in…

Query to Find Concurrent Program Responsibilities in Oracle Apps

With the help of below query, programmer can find concurrent program responsibilities attached to it in Oracle E-Business Suite.

Query to Find Concurrent Program Responsibilities

SELECT fcp.user_concurrent_program_name, frg.request_group_name, frtl.responsibility_name FROM fnd_responsibility fres, fnd_responsibility_tl frtl, fnd_request_groups frg, fnd_request_group_units…

Purchase Order and Types of Purchase Orders in Oracle Purchasing

What is Purchase Order?

Purchase Order is a commercial document generated by a buyer company to record the purchase of goods or services from a seller company. The Purchase Order number is then utilized in entire Procure to Pay Cycle till the invoice creation.

Check out >> Procure to Pay Cycle

Types of Purchase Orders

There are four types of Purchase Orders, a buyer can generate in Oracle…

List of Oracle HRMS APIs for Oracle E-Business Suite Consultants

Oracle HRMS APIs (Application Program Interface) are essential pieces of standard Oracle codes to perform modification, creation or deletion of Human Resource module data. The direct DML operations could be risky as programmers are sometimes unaware of other linked tables that require the same kind of operations to be performed. To avoid such anomalies Oracle has provided standard APIs for HRMS…

Difference between Form Personalization and CUSTOM.PLL in Oracle EBS

Form Personalization and CUSTOM.PLL are two utilities provided by Oracle E-Business suiteto alter the standard behaviour of user interfaces. They are handy to perform tasks such as disabling the fields, implement zoom functionality, change caption (prompt) values, display custom message and create custom menus for a particular form. CUSTOM.PLL and form personalization both are equally capable to…