Thumbnail image for Find Number of Rows in Each Table in a Schema

Find Number of Rows in Each Table in a Schema

Ways to find out the number of rows in each table in a schema in Oracle – one via data dictionary lookup, the other via dynamic SQL.

Read the Full Article →
Thumbnail image for ORA-55610: Invalid DDL statement on history-tracked table

ORA-55610: Invalid DDL statement on history-tracked table

If Oracle Total Recall is enabled on a table and want to truncate or drop the table, you are in for a problem: error “ORA-55610: Invalid DDL statement on history-tracked table”. How to work around this error.

Read the Full Article →
Thumbnail image for Oracle Total Recall for Tracking History

Oracle Total Recall for Tracking History

Most applications need an audit trail or history of changes for important entities in the database. This requirement is easily implemented with Oracle Total Recall, a tool that tracks all transactional changes to a table.

Read the Full Article →

How to Perform MULTISET Operations on Nested Table of Objects

PL/SQL with MULTISET operations on nested table of objects throws up the error ‘PLS-00306: wrong number or types of arguments…’. This workaround lets you use MULTISET operations successfully with complex types.

Read the Full Article →
Thumbnail image for MULTISET Operations: Combining Nested Tables Made Easy

MULTISET Operations: Combining Nested Tables Made Easy

Oracle 10G onwards, MULTISET operations using UNION, INTERSECT, MINUS can work as single-step set operations on PL/SQL nested tables without procedural code for row-by-row comparison. Here’s how.

Read the Full Article →
Thumbnail image for How to Delete Old Data from a Database Schema

How to Delete Old Data from a Database Schema

DELETE script or CTAS DROP-RENAME script: ways to purge old data from a database schema so that it retains only the current year’s rows in all the tables in the schema, and the rest of the data is erased.

Read the Full Article →
Thumbnail image for NOCOPY Parameter Hint in PL/SQL

NOCOPY Parameter Hint in PL/SQL

NOCOPY parameter hint in PL/SQL suggests to the compiler to pass OUT and IN OUT parameters by reference instead of by value. The use of NOCOPY saves on the processing and memory overhead of copying data from subprogram to calling program.

Read the Full Article →
Thumbnail image for 10 Important Lessons in Data Modeling

10 Important Lessons in Data Modeling

Follow these ten data modeling guidelines to make sure that your application database design is accurate, usable and scalable.

Read the Full Article →
Thumbnail image for The 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 sheds light on the difference between user and schema in Oracle.

Read the Full Article →
Thumbnail image for PIVOT in Oracle 11G to Select Rows As Columns

PIVOT in Oracle 11G to Select Rows As Columns

SQL to PIVOT a result set in Oracle — that is, display rows as columns. Pre-11G solution and a simple example using the 11G+ PIVOT syntax.

Read the Full Article →