PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle’s procedural extension for SQL. PL/SQL includes features such as conditional statements, loop processing and exception handling.

Thumbnail image for Referencing User Defined Types over DBLink: Problem and Alternatives

Referencing User Defined Types over DBLink: Problem and Alternatives

Understanding PLS-00453, the error that shows up when a UDT is used as a procedure parameter across DBLink, Plus a few ways to work around it.

Read the Full Article →
Thumbnail image for How to Cast PLSQL Function Output as View

How to Cast PLSQL Function Output as View

How to cast PLSQL function output as view, when a single SQL statement is not sufficient to get the result you want.

Read the Full Article →
Thumbnail image for Stop Overlapping Ranges in a Table

Stop Overlapping Ranges in a Table

Overlapping Ranges Blocker Toolkit: trigger-based solution to prevent overlapping ranges of data in an Oracle master table that stores range-based data, such as income tax brackets or sales commission rates.

Read the Full Article →
Thumbnail image for What is the Signature of a PLSQL Program?

What is the Signature of a PLSQL Program?

Interview FAQ: What is the signature of a PLSQL program? Read on for the answer: the definition of PLSQL signature and components that make the PLSQL signature.

Read the Full Article →
Thumbnail image for Find Number of Rows in Each Table in a Schema

Find Number of Rows in Each Table in a Schema

Ways to find out the number of rows in each table in a schema in Oracle – one via data dictionary lookup, the other via dynamic SQL.

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 An Easy Guide to PL/SQL Collections

An Easy Guide to PL/SQL Collections

Oracle PL/SQL collections are of three types: Index By Tables, Nested Tables, Varrays. Here’s a comparison chart of the types of PL/SQL collections based on parameters such as size, ease of modification, and persistence.

Read the Full Article →
Thumbnail image for The BOOLEAN Datatype in Oracle

The BOOLEAN Datatype in Oracle

Oracle database has a BOOLEAN datatype only in PL/SQL, not in SQL. There is no direct way to represent that a column value is TRUE or FALSE. However, the BOOLEAN datatype can be easily simulated in Oracle with these implementation ideas.

Read the Full Article →
Thumbnail image for ORA-06592: CASE_NOT_FOUND Exception

ORA-06592: CASE_NOT_FOUND Exception

Though CASE behaves like IF-THEN-ELSE, it differs in one important way – CASE requires a defined leg for each scenario. If an undefined leg is chosen, the exception ORA-06592: CASE not found while executing CASE statement is raised.

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