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.
{ 0 comments }
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.
{ 2 comments }
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.
{ 0 comments }
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.
{ 2 comments }
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.
{ 0 comments }
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.
{ 1 comment }
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.
{ 5 comments }
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.
{ 22 comments }
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.
{ 3 comments }
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.
{ 2 comments }
Oratable is for Oracle newbies. You'll find here tips, tricks and tutorials about cool things you can do with the Oracle database.
