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.

Static SQL vs Dynamic SQL

Static SQL vs Dynamic SQL: Which to use?

Static SQL vs dynamic SQL: a comparison on metrics such as security, maintainability and performance, and a rule of thumb for choosing between the two.

Read the Full Article →
Thumbnail image for What are SQLCODE and SQLERRM?

What are SQLCODE and SQLERRM?

SQLCODE and SQLERRM are Oracle’s built-in error reporting functions in PL/SQL. See PL/SQL examples using SQLCODE and SQLERRM in various scenarios.

Read the Full Article →
Fuzzy Matching with UTL_MATCH

Fuzzy Matching with UTL_MATCH

Oracle supports fuzzy matching with UTL_MATCH functions based on Levenshtein Distance and Jaro-Winkler Distance. A look at UTL_MATCH functions in action.

Read the Full Article →
Thumbnail image for Oracle Partitioning: Range Interval Partitioning

Oracle Partitioning: Range Interval Partitioning

Create Oracle table partitions on-the-fly using range interval partitioning, a beautiful Oracle partitioning feature that obviates the need to pre-create database table partitions.

Read the Full Article →
Thumbnail image for How to Find out Your Oracle Database Version

How to Find out Your Oracle Database Version

Three ways to find out your Oracle database version in SQL and PL/SQL: using V$VERSION, V$INSTANCE, or DBMS_DB_VERSION constants.

Read the Full Article →
Thumbnail image for Access Control List (ACL) in Oracle 11G

Access Control List (ACL) in Oracle 11G

Access Control List or ACL is a security mechanism that defines which users or roles can perform which operations on which data. It is an essential setup in Oracle 11G for running network packages such as UTL_HTTP.

Read the Full Article →
UTL_HTTP to Call a Web Service from PL/SQL

UTL_HTTP to Call a Web Service from PL/SQL

You can call a web service from PL/SQL using the Oracle-supplied package UTL_HTTP. This article demos a PL/SQL script to invoke a web service and read its response into a CLOB. It also describes typical errors you may encounter in web service calls and how to resolve them.

Read the Full Article →
UTL_FILE: Write to file example in PL/SQL

UTL_FILE: Write To File Example and Debugging Common Errors

Example of using UTL_FILE in PL/SQL to write to a file. Plus, ORA errors and solutions to common problems faced with UTL_FILE.

Read the Full Article →
Thumbnail image for Oracle Collections and Object Types: Comparing for Equality

Oracle Collections and Object Types: Comparing for Equality

Oracle collection or object type comparison – how to check if two Oracle collections or object types are equal or not.

Read the Full Article →
Thumbnail image for The Smart Way to Check if an Element Exists in a Collection

The Smart Way to Check if an Element Exists in a Collection

Oracle 10G onwards, the set operator MEMBER OF can be applied to test if an element exists in a collection or not. You do not need to loop through all elements in the collection to find a match.

Read the Full Article →