SQL Sequences | Nextval | Currval | Modifying and Dropping a Sequence | Basics of Oracle SQL | Oracle SQL | SQL

 Sequences

A sequence is a user created database object that can be shared by multiple users to generate unique integers.

A typical usage for sequences is to create a primary key value, which must be unique for each row.

The sequence is generated and incremented (or decremented) by an internal Oracle routine.

Sequence numbers are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.

A sequence Speeds up the efficiency of accessing sequence values when cached in memory

A sequence Replaces application code.

 

SQL Views | Types Of Views | Simple View | Complex View | Materialized View | Inline View

Views

What is a View?

•    A view is a logical table based on a table or another view.4
•    A view contains no data of its own but is like a window through which data from tables can          be viewed or changed.
•    The tables on which a view is based are called base tables. The view is stored as a SELECT                 statement in the data dictionary.
•     We can present logical subsets or combinations of data by creating views of tables.

SQL Clauses | Group By Clause | Having Clause | Order By Clause | Distinct Clause | With Clause | Asc | Desc | Oracle SQL | SQL

 CLAUSES

Clauses:

•       Group by Clause

•       Having Clause

•       Order by Clause

•       Distinct Clause

•       With Clause

SQL Sub-Queries| Types of Sub-Queries | Non-correlated Sub-Query | Correlated Sub-Query| Exists | Non-Exists | Oracle SQL | SQL

 Sub Queries

Using a Sub-query :

Main Query: Which employees have salaries greater than Abel’s salary?

Subquery: What is Abel’s salary?

 Sub-query syntax :

The subquery (inner query) executes once before the main query.

The result of the subquery is used by the main query (outer query).

Interfaces | Inbound | Outbound | Purchasing | EBS | Oracle Apps R12 | Oracle EBS

Interfaces

What is Interface?

          Interfaces are used in Oracle Applications to integrate external systems and Data Conversion.

          The interfaces are mainly used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.

          Interfaces in oracle apps r12 are ways to exchange data between Oracle Applications and external systems.

Single-Row Functions | SQL Functions | Number Functions | Character Functions | Date Functions | Conversion Functions

 

 SQL Functions:

What is a function?

Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments. This format enables them to operate on zero, one, two, or more arguments:

Syntax: function (argument, argument, ...)

SQL Loader | Basics of Oracle SQL | Oracle SQL | SQL

SQL Loader

SQL*Loader allows you to load data from an external file into a table in the database. It can parse many delimited file formats such as CSV, tab-delimited, and pipe-delimited.

SQL*Loader provides the following methods to load data:

•       Conventional path loads – construct INSERT statements from the contents of the input data file based on the predefined specification and execute the inserts.

•       Direct path loads – creates data blocks in Oracle database block format from the data file and directly write the data block to the database. This way is much faster than the conventional path but subject to some restrictions.

•       External table loads – create an external table for the data stored in the data file and execute INSERT statements to insert the data from the data file into the target table. The external table loads support parallel loading if data file is big enough.

 

SQL Pesudo Columns | Rowid |Rownum | Oracle SQL | SQL

 

Pseudo Columns

1. ROWID:

•       ROWID is nothing but the physical memory location on which that data/row is stored. ROWID basically returns address of row.

•       ROWID uniquely identifies row in database.

•       ROWID is combination of data object number, data block in data file, position of row and datafile in which row resides.