Multiple indexes on same column

In the past oracle allowed only one index for same columns. if you try to create another index (with same columns), Oracle throws an error ORA-01408 : such column list already indexed

With 12c, Oracle allows multiple indexes on same column(s). However, only one index is visible any time. It also allows having one function based index + one regular index to be visible.

Read Full Post »

How to display/return procedure name when there is error (PL/SQL Directive)

If you would like to return or display pl/sql program unit name when there is an error, you can use code some thing like below.

create or replace procedure test….

is…

begin…

exception

when others then

dbms_output.put_line($$PLSQL_UNIT||’ caused error’);

end;

Using the PL/SQL Directive $$PLSQL_UNIT displays the pl/sql unit name (in this case test, is the name of the procedure ), when exception raised.

Another directive is $$PLSQL_LINE can be used to identify the line number in the program unit.

Oracle 12c database introduced new directives to display additional information as below.

$$PLSQL_OWNER – Owner of the object

$$PLSQL_TYPE - Whether it is procedure, function etc.

This addition would be very useful for developers to identify the owner of the program unit and type.

Read Full Post »