plsql

Thumbnail image for 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

Oracle 11G brought with it a beautiful new twist to range partitioning – the ability to create partitions on-the-fly rather than having to pre-create all partitions. Hello interval partitioning.

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 →
Thumbnail image for UTL_FILE: Simple Write To File Example and Debugging Common Errors

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 →
Oracle Collections and Object Types: Comparing for Equality

Oracle Collections and Object Types: Comparing for Equality

Oracle can easily compare data items of scalar data types (those that hold a single data value with no internal components – e.g. NUMBER, DATE or VARCHAR2). So, if a and b are two NUMBER variables, all you need to do to check whether they are the same or not, is test "if (a=b)". Can […]

Read the Full Article →
Check if an Element Exists in a Collection

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

When you need to find out if a specific element exists in a collection or not, how do you go about it? Do you loop through the list till you find the value? If that’s what you do, this post is for you.

Read the Full Article →
Thumbnail image for Referencing User Defined Types over DBLink: Problem and Alternatives

Referencing User Defined Types over DBLink: Problem and Alternatives

Consider a PL/SQL call to a function which returns a user-defined type (UDT) – say, a SQL Type object. This works well as long as the called function resides in the same database as the calling PL/SQL, but if the called function is in a remote database, the PL/SQL call via DBLink end ups with […]

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

How to Cast PLSQL Function Output as View

An Oracle view is typically like a window to the output of a SQL statement, but sometimes, a single SQL isn’t sufficient to get you the result you want. You may need to do some procedural data manipulation before you get the output you want. Does this mean, you cannot use an Oracle view if […]

Read the Full Article →