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.
Some questions do not have definitive answers. "Is a full table scan bad? Should this design be denormalized? Will partitioning this table help?" The answers vary widely depending on the specifics on the problem.
Fortunately, "Should I use static SQL or dynamic SQL?" is not one of those questions.
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.
Oracle’s UTL_MATCH package contains functions to perform fuzzy matching between two strings based on one of these algorithms:
Let’s understand the algorithms and see UTL_MATCH subprograms in action.
Oracle partitioning features in 11G brought with it a beautiful new twist to range partitioning — the ability to create partitions on-the-fly.
Hello range interval partitioning.
Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your Oracle database version.
Here’s a rundown of ways to identify the Oracle database version using SQL and PL/SQL.
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:
declare * ERROR at line 1: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-24247: network access denied by access control list (ACL) ORA-06512: at line 47
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 are not so easy to please (and rightly so!) — they enforce extra security, which means you need more access control configuration to get this working.
You can call a web service from 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.
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.
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 we extend this simple equality check to instances of PL/SQL collections or object types (UDTs)? Let’s find out.
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.