≡ Menu

The Curious Case of The Missing ORA-00904


Here’s a puzzle for you to solve. You’re given these two tables – empl and dept – in which column emp_dept_id of table empl references dept_id of table dept.

SQL> desc dept
 Name              Null?    Type
 ----------------- -------- ------------
 DEPT_ID                    NUMBER(2)
 DEPT_NAME                  VARCHAR2(6)

SQL> desc empl
 Name              Null?    Type
 ----------------- -------- ------------
 EMP_ID                     NUMBER(2)
 EMP_NAME                   VARCHAR2(6)
 EMP_DEPT_ID                NUMBER(2)

Note that the foreign key column names in the two tables is not identical. It’s called dept_id in table dept, emp_dept_id in table empl.

[click to continue…]


Flatten Hierarchical Data using a Single SQL

Oracle has some neat ways of handling hierarchical data. A while back I wrote about how Oracle SQL can find the entire tree of data above or below a node in a hierarchy using the CONNECT BY clause.

What if the requirement is to flatten hierarchical data?

hierarchy-levels For example, given a table containing the employee reporting hierarchy of an organization (image alongside), get a single SQL to return the 4 types of employee roles as columns:

    • DBA (Level 2)
    • TEAM_LEAD (Level 2)
      • TEAM_MEMBER (Level 3)

This is trickier than transposing rows as columns in a non-hierarchical table, and needs a little extra to get the result. But it is nowhere near as tough as it appears. No multiple self-joins, no recursive CTE.

This post will show you how to flatten hierarchical data using a single SQL in Oracle.

[click to continue…]


listagg In an earlier post, we saw how Oracle SQL can be used to transpose data from rows to columns. It is a similar, though simpler, exercise to transpose data from rows to a comma-seperated list.

[click to continue…]



Consider a PL/SQL call to a function which returns a user-defined type (UDT) – say, a SQL Type object. This works well as long as the called function resides in the same database as the calling PL/SQL, but if the called function is in a remote database, the PL/SQL call via DBLink end ups with the following error:

Error: PLS-00453: remote operations not permitted on object tables or user-defined type columns

This has often led to issues discovered too late for developers, who successfully tested function calls over multiple schemas simulating remote calls, but when the function databases were separated in later stages of testing, the PL/SQL call no longer worked.

[click to continue…]


How to Cast PLSQL Function Output as View


An Oracle view is typically like a window to the output of a SQL statement, but sometimes, a single SQL isn’t sufficient to get you the result you want. You may need to do some procedural data manipulation before you get the output you want.

Does this mean, you cannot use an Oracle view if the underlying code cannot be framed as a single SQL? Not at all! This post will show you how to use PLSQL instead of tables inside a view.

[click to continue…]


Stop Overlapping Ranges in a Table


Range-based definition of values is a common business scenario, as in the case of mortgage pre-payment penalty charges depending on "pre-payment amount" slabs, or incentives for a dealer depending on "business generated" slabs.

A necessary validation is such cases is that the slabs should have no overlaps: each discrete value should match no more than a single row in the range definition.

How can overlapping ranges be prevented from getting defined? Read on to find out the solution for an Oracle database.

[click to continue…]


Using LEAD/LAG to Place a Value within Extents

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

SQL> select * from grade_master;

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

[click to continue…]


Find Number of Rows in Each Table in a Schema

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.

[click to continue…]


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 likely to answer that question similarly, this article will clarify for you the concept of "signature" of a PLSQL stored procedure.

[click to continue…]


ora-55610 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.

SQL> truncate table customer;
truncate table customer
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

SQL> drop table customer;
drop table customer
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

Clearly enough, Oracle does not allow you to do so because it has flashback archive enabled.

SQL> -- Check history tracking
SQL> select flashback_archive_name
  2  from dba_flashback_archive_tables
  3  where table_name = 'CUSTOMER';


How do you get around this problem?

[click to continue…]