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.

XMLTABLE to convert XML to relational data

XMLTABLE: Convert XML Data to Relational Form

Using the SQL/XML function XMLTABLE in Oracle database 12c, to map XML data into relational rows and columns.

Read the Full Article →
When should we use dynamic SQL

When Should We Use Dynamic SQL?

We know that, if there exists a choice between static SQL vs dynamic SQL, static SQL is the way to go. In that case, why and when should we use dynamic SQL?

Read the Full Article →
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 →
SQLCODE and SQLERRM

What are SQLCODE and SQLERRM?

SQLCODE and SQLERRM are Oracle’s built-in error reporting functions in PL/SQL. When an error occurs in PL/SQL at runtime: SQLCODE returns the number of the last encountered error. SQLERRM returns the  message associated with its error-number argument. The error-number argument is optional: if omitted, SQLERRM returns the message associated with the current value of SQLCODE.

Read the Full Article →
Fuzzy Matching with UTL_MATCH

Fuzzy Matching with UTL_MATCH

Oracle’s UTL_MATCH package contains functions to perform fuzzy matching between two strings based on one of these algorithms: Levenshtein Distance Jaro-Winkler Distance Let’s understand the algorithms and see UTL_MATCH subprograms in action.

Read the Full Article →
Thumbnail image for Range Interval Partitioning

Range Interval Partitioning

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

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

How to Find out Your Oracle Database Version

Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your database version. Here’s a rundown of ways to identify the Oracle database version using SQL and PL/SQL.

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

Access Control List (ACL) in Oracle 11G

I recently upgraded Oracle XE from 10G to 11G, and found that none of the PL/SQL code using UTL_HTTP was working after upgrade. The code failed with the error: Oracle 10G used to be happy as long as the user running network packages like UTL_HTTP had execute permission on the package. Oracle 11G and above […]

Read the Full Article →
Thumbnail image for 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 within PL/SQL using the Oracle-supplied package UTL_HTTP. Here’s a demo of calling a public web service that returns latest city weather by ZIP.

Read the Full Article →
UTL_FILE: Simple write to file example

UTL_FILE: Simple Write To File Example and Debugging Common Errors

A simple example of using UTL_FILE in PLSQL to create a file and write into it. Plus, the common ORA errors you encounter with the use of UTL_FILE, and how to resolve them.

Read the Full Article →