A SQL script can contain one or more SQL statements or PL/SQL blocks, to do a logical unit of work. SQL scripts can be saved as files.

SQL: Compare Two Tables for Differences

SQL: Compare Two Tables for Differences

In a recent project, a shadow table had to be compared periodically with its main source table to identify the differences between the two tables. The nature of differences fell into one of these buckets: Insert Required [INS]: If the source had rows not present in the shadow table Update Required [UPD]: If one or […]

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 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 →
Delete Old Data from Database Schema

How to Delete Old Data from the Entire Schema

We may want to slim down a database schema, pruning old data from all tables, for a number of reasons. Let’s say to set up the test environment for a data migration project, a replica of the legacy database schema has been created. The legacy database schema has millions of records spanning multiple years, but […]

Read the Full Article →
Drop Recreate Foreign Keys

How to Drop and Recreate All Foreign Keys that Reference a Table in Oracle

The last post showed a workaround for the error "ORA-02449: unique/primary keys in table referenced by foreign keys", which blocks any attempt to drop an Oracle table if there are foreign keys from other tables referring to it. The caveat: if the dropped table has to be recreated, the dropped foreign keys must be recreated […]

Read the Full Article →
Auto-Increment Column

What’s the value of auto-increment column in this insert?

In the last post, we saw a neat way to implement auto-increment functionality in an Oracle table. The auto-incremented column gets its value populated in the background, without the issuer of the insert statement even getting to know about it. BUT – what if the issuer of the insert statement does want to know about […]

Read the Full Article →
Find out Oracle Database Name

Quick way to find out your database name

Oracle gives you a number of ways to know the name of the database you are connected to, from inside a SQL*Plus session. Through V$DATABASE: The above will work only if your login has access to V$DATABASE. This is generally accessible to DBA logins only. If you have a non-DBA login, use one of the […]

Read the Full Article →
Thumbnail image for SCOTT schema in Oracle

SCOTT schema in Oracle

What is SCOTT? Scott is a database user in Oracle, used for demos and examples. The SCOTT schema used to be installed by default in earlier versions of Oracle. Not anymore. Oracle 9i onwards, new sample schemas like HR and OE are available that are more suited for demoing newer Oracle features. How to install […]

Read the Full Article →