Keywords

Keywords have a special meaning in the Oracle database, and a specific role to play in the context they appear. FUNCTION, MAX, COMMIT are some examples of Oracle keywords.

Thumbnail image for DETERMINISTIC Functions in Oracle

DETERMINISTIC Functions in Oracle

Deterministic functions always produce the same output for a given input. Learn how Oracle uses the DETERMINISTIC clause for optimizing function calls.

Read the Full Article →
Thumbnail image for Pagination Queries in Oracle: Analytics, Row Limiting Clause

Pagination Queries in Oracle: Analytics, Row Limiting Clause

How to implement pagination queries in Oracle, using (1) analytic functions (2) Oracle’s row limiting clause to fetch N rows from a given offset.

Read the Full Article →
Thumbnail image for DML Error Logging: Supersized DML Operations Made Easy

DML Error Logging: Supersized DML Operations Made Easy

Oracle DML error logging lets you manage very large DML operations with high performance and robust error handling, with the addition of a LOG ERRORS INTO clause with the DML statement. An explanation and working example.

Read the Full Article →
Thumbnail image for INNER JOIN and OUTER JOIN Explained

INNER JOIN and OUTER JOIN Explained

Overview of INNER JOIN and OUTER JOIN in Oracle, with examples, memory aids and a further drilldown of INNER JOIN and OUTER JOIN types.

Read the Full Article →
Thumbnail image for LISTAGG for Converting Rows to Comma-Separated String

LISTAGG for Converting Rows to Comma-Separated String

Oracle built-in function LISTAGG lets you concatenate multiple rows of data into a single delimiter-separated string. No more of those circuitous MAX(SYS_CONNECT_BY_PATH) or STRAGG solutions of the pre-Oracle 11G R2 days!

Read the Full Article →

How to Perform MULTISET Operations on Nested Table of Objects

PL/SQL with MULTISET operations on nested table of objects throws up the error ‘PLS-00306: wrong number or types of arguments…’. This workaround lets you use MULTISET operations successfully with complex types.

Read the Full Article →
Thumbnail image for MULTISET Operations: Combining Nested Tables Made Easy

MULTISET Operations: Combining Nested Tables Made Easy

Oracle 10G onwards, MULTISET operations using UNION, INTERSECT, MINUS can work as single-step set operations on PL/SQL nested tables without procedural code for row-by-row comparison. Here’s how.

Read the Full Article →
Thumbnail image for NOCOPY Parameter Hint in PL/SQL

NOCOPY Parameter Hint in PL/SQL

NOCOPY parameter hint in PL/SQL suggests to the compiler to pass OUT and IN OUT parameters by reference instead of by value. The use of NOCOPY saves on the processing and memory overhead of copying data from subprogram to calling program.

Read the Full Article →
Thumbnail image for PIVOT in Oracle 11G to Select Rows As Columns

PIVOT in Oracle 11G to Select Rows As Columns

SQL to PIVOT a result set in Oracle — that is, display rows as columns. Pre-11G solution and a simple example using the 11G+ PIVOT syntax.

Read the Full Article →
Thumbnail image for INSERT ALL: Insert Multiple Rows with a Single INSERT Statement

INSERT ALL: Insert Multiple Rows with a Single INSERT Statement

A quick tip to get rid of the plodding way of inserting data into a table with multiple INSERT statements. Know what I mean? Say you have a table COLORS with this structure: And you want to create this data in the table COLORS: Hands up all who write insert statements this way:

Read the Full Article →
Page 1 of 3
1
2
3