utl_http

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.

Continue Reading …

{ 0 comments }

UTL_FILE

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.

Continue Reading …

{ 2 comments }

Thumbnail image for SQL to Find Master Records with Identical Detail Records

SQL to Find Master Records with Identical Detail Records

The query to identify duplicate records in Oracle in a single table is fairly straightforward. Things get tricky in a two-table (master-detail) scenario, in which the master table holds the header information (id, name, etc) and the detail table has sets of values associated with the master records. What if we need to find those […]

Read the Full Article →
Replace Duplicate Ids with Unique Values

Replace Duplicates in ID Column with Unique Values

A common scenario: in the development environment, a table’s ID column is missing its unique key constraint, and developers have been creating their own data for testing. Suddenly someone realizes that the ID column has duplicates. The DBA is summoned. "Enable the unique key", the DBA is told, "but do not delete our data." Can […]

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 →
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

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 The Curious Case of The Missing ORA-00904

The Curious Case of The Missing ORA-00904

Here’s a puzzle for you to solve. You’re given these two tables – empl and dept – in which column emp_dept_id of table empl references dept_id of table dept. Note that the foreign key column names in the two tables is not identical. It’s called dept_id in table dept, emp_dept_id in table empl.

Read the Full Article →
Thumbnail image for Flatten Hierarchical Data using a Single SQL

Flatten Hierarchical Data using a Single SQL

Oracle has some neat ways of handling hierarchical data. A while back I wrote about how Oracle SQL can find the entire tree of data above or below a node in a hierarchy using the CONNECT BY clause. What if the requirement is to flatten hierarchical data? For example, given a table containing the employee […]

Read the Full Article →
Thumbnail image for LISTAGG for Converting Rows to Comma-Separated String

LISTAGG for Converting Rows to Comma-Separated String

In an earlier post, we saw how Oracle SQL can be used to transpose data from rows to columns. It is a similar, though simpler, exercise to transpose data from rows to a comma-seperated list.

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 →