Oracle Total Recall

Oracle Total Recall for Tracking History

Oracle 11g Flashback Data Archive (Oracle Total Recall), essentially meant as a DBA tool to recover from logical corruptions and human errors, can be put to good use for another standard requirement in applications. Most applications need an audit trail or history of changes for important entities – say, the trail of changes to customer’s […]

Read the Full Article →
MULTISET Operations on Nested Table of Objects

How to Perform MULTISET Operations on Nested Table of Objects

We’ve seen the easy working of MULTISET operations on nested tables of simple types (i.e. collections of text, numbers). And we’ve seen the problem with extending MULTISET operations to nested tables of complex types (i.e. those based on OBJECTs with multiple attributes). PL/SQL code when run with MULTISET operations on complex collections throws up the […]

Read the Full Article →
MULTISET Operations

MULTISET Operations: Combining Nested Tables Made Easy

Set operators (UNION, INTERSECT, MINUS) have long been available in basic SQL to process data in tables, but for data in PL/SQL nested tables, we’d earlier have to go through the ritual of traversing through the collections in a loop, doing a row-by-row comparison. Oracle 10G onwards, MULTISET features have made possible single-step set operations […]

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 →
Passing Parameter by Value

NOCOPY Parameter Hint in PL/SQL

Before we get to understanding the NOCOPY parameter hint in PL/SQL, it would help to first look at PL/SQL parameter modes and the ways in which they pass values in and out of a subprogram. PL/SQL subprograms have three parameter modes:

Read the Full Article →
Important Lessons in Data Modeling

10 Important Lessons in Data Modeling

Are you building the database for a custom OLTP application starting from scratch, with the business requirements as only initial input? No matter what the size, domain or location of your project, the following lessons will help to ensure that your physical data modeling process is smooth and that you end up with a database […]

Read the Full Article →
Difference between User and Schema in Oracle

The Difference between User and Schema in Oracle

As an Oracle developer, you have probably wondered at some point: do user and schema in Oracle really mean the same thing? And if they do, why does Oracle have two names for it? This post helps to clarify the sameness – or otherwise – of Oracle user and schema.

Read the Full Article →
PIVOT in Oracle to Select Rows As Columns

PIVOT in Oracle 11G to Select Rows As Columns

A frequent requirement in SQL is to "pivot" a result set – that is, display rows as columns. Before 11G, the developer had to do the equivalent of jumping through hoops to pivot the data. In 11G, with the introduction of the new PIVOT syntax, the task of transposing rows to columns has become a […]

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 →
ORA-02449: unique/primary keys in table referenced by foreign keys

ORA-02449: Solving the DROP TABLE Dilemma

Sometimes you want to drop and recreate an Oracle table, and are in a fix because when you issue the DROP command Oracle responds with ORA-02449: Disabling constraints has no effect on ORA-02449, the only resolution is to drop constraints. Which presents another dilemma: the error does not specify which foreign keys are causing this […]

Read the Full Article →