lead-lag Take a table with the structure {<Category>, <Extent>}– say, a GRADE_MASTER table with two columns: {GRADE_CODE, SCORE_UPTO}. For examination scores in the range 0-100, GRADE_MASTER specifies the extent of score up to which a certain grade applies.

SQL> desc grade_master
 Name              Null?    Type
 ----------------- -------- -----------
 GRADE_CODE        NOT NULL VARCHAR2(2)
 SCORE_UPTO        NOT NULL NUMBER(3)

SQL> select * from grade_master;

GR SCORE_UPTO
-- ----------
F          59
D          69
C          79
B          89
A         100

To find the grade for a given the examination score, the SQL needs to compare the EXTENT values across *two* rows. Comparing a column’s value across more than one row can be tricky to implement – unless you turn to Oracle functions LEAD/LAG.

Continue Reading …

{ 0 comments }

Number of Rows in Each Table in a Schema

How will you find out the number of rows in each table in a schema in Oracle?

Here are two approaches with their pros and cons.

Continue Reading …

{ 0 comments }

Thumbnail image for What is the Signature of a PLSQL Program?

What is the Signature of a PLSQL Program?

In interviews for PLSQL developers, I usually ask a question of the form: If you write a procedure to do <specific operation>, what will be the signature of such a procedure? In response, the developer often veers off into describing the logic/body of the procedure, which is not what was asked. If you’re among those [...]

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 you have got Oracle Total Recall enabled on a table, and now want to truncate or drop the table, you are in for a problem. Clearly enough, Oracle does not allow you to do so because it has flashback archive enabled. How do you get around this problem?

Read the Full Article →
Oracle 11G 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 →
Thumbnail image for How to Perform 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 →
Thumbnail image for MULTISET Operations: Combining Nested Tables Made Easy

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 →
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 NOCOPY Parameter Hint in PL/SQL

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 →
Thumbnail image for 10 Important Lessons on Data Modeling

10 Important Lessons on 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 →