scripts

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 →
Thumbnail image for How to Delete Old Data from the Entire 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 →
Thumbnail image for How to Drop and Recreate All Foreign Keys that Reference a Table in Oracle

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

In the last post you saw a workaround for the error "ORA-02449: unique/primary keys in table referenced by foreign keys", which blocks our 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 […]

Read the Full Article →
Thumbnail image for What’s the value of auto-increment column in this insert?

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