DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

As much as I like coding in PL\SQL I always hated those exceptions in the code. Whenever the code fails all I get is the line number of the error on the last exception section of the code and finding out on which line the error really occured would invlove removing the exception block which sometimes is really wacky when
the process invloves calling multiple program units.
But today I came across this new utility in 10g DBMS_UTILITY.FORMAT_ERROR_BACKTRACE which can be used to trace the line numbers of all the programs in which the error occured.

I am really ashamed of myself for not knowing it this long. 10g was released long back and 11g is also soon becoming matured enough for many applications to be upgraded to it. But I am glad I learned about it neverthless. I guess its high time I read the new features document.

Example Code:
--------------
declare
l_emp_id number(10);
begin
select EMPNO
into l_emp_id
from emp
where 1=2;
exception when no_data_found then
dbms_output.put_line('sqlcode-->'sqlcode);
dbms_output.put_line('DBMS_UTILITY.FORMAT_ERROR_BACKTRACE-->'DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
end;
/

Escape in SQL

Below is one example to use escape in SQL(Oracle).

select * from all_tables where table_name like '%TEST/_%TABLE%' ESCAPE '/';

Using Bulk collect in PL\SQL

I am used to using bulk collect in cursor variables. But I guess i have'nt worked on it enough to master its usage. Yesterday's question on plsqlchallenge really made me thing of them some more. I am reproducing the examples here for my reference in the future.

You can use BULK COLLECT with both implicit and explicit cursors, in both static and dynamic SQL, and with cursor variables. The following blocks demonstrate each approach.
With an explicit cursor:

DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
TYPE employees_aat IS TABLE OF employees%ROWTYPE
INDEX BY BINARY_INTEGER;
l_employees employees_aat;
BEGIN
OPEN employees_cur;
FETCH employees_cur BULK COLLECT INTO l_employees;
CLOSE employees_cur;
END;

With an implicit cursor:

DECLARE
TYPE employees_aat IS TABLE OF employees%ROWTYPE
INDEX BY BINARY_INTEGER;
l_employees employees_aat;
BEGIN
SELECT * BULK COLLECT INTO l_employees FROM employees;
END;

With a dynamic SQL statement (Oracle 9i Release 2 and above):

DECLARE
TYPE employees_aat IS TABLE OF employees%ROWTYPE
INDEX BY BINARY_INTEGER;

l_employees employees_aat;
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM employees' BULK COLLECT INTO l_employees;
END;

And here's an example of using BULK COLLECT with a cursor variable:

DECLARE
l_cursor sys_refcursor;
l_list DBMS_SQL.varchar2s;
BEGIN
OPEN l_cursor FOR
SELECT last_name
FROM employees;

LOOP
FETCH l_cursor
BULK COLLECT INTO l_list
LIMIT 100;

EXIT WHEN l_list.COUNT = 0;
END LOOP;

CLOSE l_cursor;
END;