Scripts

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.

Convert Relational Data to JSON

Convert Relational Data to JSON in Oracle: PL/JSON Method

How to convert relational data to JSON in the Oracle database, using PL/JSON.

Read the Full Article →
Thumbnail image for SQL: Compare Two Tables for Differences in Data

SQL: Compare Two Tables for Differences in Data

How to compare two tables that share the same structure, to identify data differences between the two tables: missing rows, extra rows, or mismatch in attribute values.

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

Access Control List or ACL is a security mechanism that defines which users or roles can perform which operations on which data. It is an essential setup in Oracle 11G for running network packages such as UTL_HTTP.

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 PL/SQL using the Oracle-supplied package UTL_HTTP. This article demos a PL/SQL script to invoke a web service and read its response into a CLOB. It also describes typical errors you may encounter in web service calls and how to resolve them.

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 →
Thumbnail image for Drop and Recreate All Foreign Keys on Oracle Table

Drop and Recreate All Foreign Keys on Oracle Table

You might need to reistate a dropped table in Oracle, and in doing so, restore the foreign keys as well. Here’s how to drop and recreate all foreign keys on an Oracle table.

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 →
Thumbnail image for How to Find Out Your Oracle Database Name

How to Find Out Your Oracle Database Name

Oracle gives you easy ways to find out your Oracle database name from inside a SQL*Plus session. Here are three ways: using V$DATABASE, GLOBAL_NAME, and DBMS_UTILITY.GET_PARAMETER_VALUE.

Read the Full Article →