Error Handling : Back Trace

PL/SQL offers a powerful and flexible exception architecture. Of course, there is always room for improvement, and in Oracle Database 10g, exception handling takes a big step forward with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves and how best to use it.

Who Raised That Exception?

When an exception is raised, one of the most important pieces of information a programmer would like to uncover is the line of code that raised the exception. Prior to Oracle Database 10g, one could obtain this information only by allowing the exception to go unhandled.

Let's revisit the error-handling behavior available to programmers in Oracle9i Database. Consider this simple chain of program calls in Listing 1: procedure proc3 calls proc2 calls proc1 , at which point proc1 raises the NO_DATA_FOUND exception. Notice that there is no error handling in any of the procedures; it is most significantly lacking in the top-level proc3 procedure. If I run proc3 in SQL*Plus, I will see the following results:

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SCOTT.PROC1", line 4
ORA-06512: at "SCOTT.PROC2", line 6
ORA-06512: at "SCOTT.PROC3", line 4
ORA-06512: at line 3

Code Listing 1: A stack of procedures

CREATE OR REPLACE PROCEDURE proc1 IS
BEGIN
   DBMS_OUTPUT.put_line ('running proc1');
   RAISE NO_DATA_FOUND;
END;
/
CREATE OR REPLACE PROCEDURE proc2 IS
   l_str VARCHAR2(30) 
         := 'calling proc1';
BEGIN
   DBMS_OUTPUT.put_line (l_str);
   proc1;  
END;
/
CREATE OR REPLACE PROCEDURE proc3 IS
BEGIN
   DBMS_OUTPUT.put_line ('calling proc2');
   proc2;
END;
/

This is the error trace dump of an unhandled exception, and it shows that the error was raised on line 4 of proc1. On the one hand, we should be very pleased with this behavior. Now that we have the line number, we can zoom right in on the problem code and fix it. On the other hand, we got this information by letting the exception go unhandled. In many applications, however, we work to avoid unhandled exceptions.

Let's see what happens when I add an exception section to the proc3 procedure and then display the error information (the simplest form of error logging). Here is the second version of proc3 :

CREATE OR REPLACE PROCEDURE proc3
IS
BEGIN
  DBMS_OUTPUT.put_line ('calling proc2');
  proc2;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

Notice that I call DBMS_UTILITY.FORMAT_ERROR_STACK , because it will return the full error message. 

Having compiled the new proc3 , when I run it inside SQL*Plus I see the following output:

SQL> SET SERVEROUTPUT ON
SQL> exec proc3
calling proc2
calling proc1
running proc1
ORA-01403: no data found

In other words, DBMS_UTILITY.FORMAT_ERROR_STACK does not show the full error stack with line numbers; SQLERRM acts in the same manner.

Backtrace to the Rescue

In Oracle Database 10g, Oracle added DBMS_UTILITY.FORMAT_ERROR_BACKTRACE , which can and should be called in your exception handler. It displays the call stack at the point where an exception was raised, even if the function is called in a PL/SQL block in an outer scope from that where the exception was raised. Thus, you can call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE within an exception section at the top level of your stack and still find out where the error was raised deep within the call stack.

Code Listing 2: proc3 rewritten with FORMAT_ERROR_BACKTRACE

CREATE OR REPLACE PROCEDURE proc3
IS
BEGIN
  DBMS_OUTPUT.put_line ('calling proc2');
  proc2;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line ('Error stack at top level:');
    DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

And now when I run proc3 , I will see the following output:

SQL> SET SERVEROUTPUT ON
SQL> exec proc3
calling proc2
calling proc1
running proc1
Error stack at top level:
ORA-06512: at "SCOTT.PROC1", line 4
ORA-06512: at "SCOTT.PROC2", line 6
ORA-06512: at "SCOTT.PROC3", line 4

In other words, the information that had previously been available only through an unhandled exception is now retrievable from within the PL/SQL code.

Impact of Multiple RAISEs

An exception often occurs deep within the execution stack. If you want that exception to propagate all the way to the outermost PL/SQL block, it will have to be re-raised within each exception handler in the stack of blocks. Listing 3 shows an example of such an occurrence.

Code Listing 3: Re-raising exceptions to the outermost block in the stack

CREATE OR REPLACE PROCEDURE proc1 IS
BEGIN
   DBMS_OUTPUT.put_line ('running proc1');
   RAISE NO_DATA_FOUND;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line (
         'Error stack in block where raised:');
      DBMS_OUTPUT.put_line (
         DBMS_UTILITY.format_error_backtrace);
      RAISE;
END;
/
CREATE OR REPLACE PROCEDURE proc2
IS
   l_str VARCHAR2 (30) := 'calling proc1';
BEGIN
   DBMS_OUTPUT.put_line (l_str);
   proc1;
END;
/
CREATE OR REPLACE PROCEDURE proc3 IS
BEGIN
   DBMS_OUTPUT.put_line ('calling proc2');
   proc2;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error stack at top level:');
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
     
END;
/

When I run the code in Listing 3, I see the following output:

SQL> exec proc3
calling proc2
calling proc1
running proc1
Error stack in block where raised:
ORA-06512: at "SCOTT.PROC1", line 4
Error stack at top level:
ORA-06512: at "SCOTT.PROC1", line 11
ORA-06512: at "SCOTT.PROC2", line 6
ORA-06512: at "SCOTT.PROC3", line 4

Program owner = SCOTT
Program name = PROC1
Line number = 11

When I call the backtrace function within the lowest-level program, it correctly identifies line 4 of proc1 as the line in which the error is first raised. I then re-raise the same exception using the RAISE statement. When the exception propagates to the outermost block, I call the backtrace function again, and this time it shows that the error was raised on line 11 of proc1.

From this behavior, we can conclude that DBMS_UTILITY.FORMAT_ERROR_BACKTRACE shows the trace of execution back to the last RAISE in one's session. As soon as you issue a RAISE of a particular exception or re-raise the current exception, you restart the stack that the backtrace function produces. This means that if you want to take advantage of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE , take one of the following two approaches:

• Call the backtrace function in the exception section of the block in which the error was raised. This way you have (and can log) that critical line number, even if the exception is re-raised further up in the stack.
• Avoid exception handlers in intermediate programs in your stack, and call the backtrace function in the exception section of the outermost program in your stack.

Schema level Comparison with TOAD

Go to DATABASE à COMPARE à SCHEMAS

Select the Source Schema

Add the Target Schema

Click Compare

Sync script will be generated.

Issue: (If Toad is not DBA Version)

By making a scheme level comparison between two schemes in toad. it 
gives the difference with a Note, but  unable to copy the same in the 
notepad and execute it in the SQL PLUS. 

The message is 

"Script is view only because you do not have access to dba module functions 
in Toad" 

Solution :

Press

Ctrl + A ( Select All)

Ctrl + F ( Search )

Complete text will be copied to the test box on search dialog box.

Ctrl + C ( copy )

Ctrl + v ( paste ) in the editor. 

TRUNC ON DATE FIELD

TRUNC ( DATAFIELD,'<format>')

Default is 'dd' -- Truncate at the date level , meaning start of the day (Time: 00:00:00)


1. 'dd'  (First hour of the day) 

-- Start of the day

Example:
Select TRUNC(SYSDATE,'dd') from dual;

2. 'mm' (First day of the month) + (First hour of the day)

-- Start of the month

Example:
Select TRUNC(SYSDATE,'mm') from dual;


3. 'yyyy' (First month of the year With First Date) + (First day of the month) + (First hour of the day)

-- Start of the year

Example:
Select TRUNC(SYSDATE,'yyyy') from dual;


4. 'hh' ( Minutes part will be truncated)


 -- Start of the hour

Example:
Select to_char(TRUNC(SYSDATE,'hh'),'mm/dd/yyyy hh24:mi:ss') from dual;

5. 'mi'  (Seconds part will be truncated) 

-- Start of the minute

Example:
Select to_char(TRUNC(SYSDATE,'mi'),'mm/dd/yyyy hh24:mi:ss') from dual;

DBMS_APPLICATION_INFO (Package)

The DBMS_APPLICATION_INFO package allows programs to add information to the V$SESSION to make tracking of session activities more accurate.

Select module, action from v$session;


procedure set_module(module_name varchar2, action_name varchar2);

--  Sets the name of the module that is currently running to a new module.

procedure set_action(action_name varchar2);

-- Sets the name of the current action within the current module.

procedure read_module(module_name out varchar2, action_name out varchar2);

--  Reads the values of the module and action fields of the current session.

procedure set_client_info(client_info varchar2);

-- Sets the client info field of the v$session.

procedure read_client_info(client_info out varchar2);

-- Reads the value of the client_info field of the current session.


Example:

BEGIN
  DBMS_APPLICATION_INFO.set_module(module_name => 'add_order',
                                   action_name => 'insert into orders');
 
  -- Do insert into table.
END;
/


BEGIN
  DBMS_APPLICATION_INFO.set_action(action_name => 'insert into orders');
  DBMS_APPLICATION_INFO.set_client_info(client_info => 'Issued by Web Client');
 
  -- Do insert into ORDERS table.
END;
/

SQL DEVELOPER TOOL (FREE)

Oracle has released the free SQL DEVELOPER TOOL & can be download from oracle website.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b31695/intro.htm

Installation:

SQL DEVELOPER installs by simple "unzipping" the file downloaded from oracle.

Note: Not an windows installed program.


Run: Execute the sqldeveloper.exe in the unzipped folder.


1. Connection:

We can open multiple connections in a single instance.

Click New connection & then enter the connection name, host,port,sid,username,password.


2. After connecting to a schema (user):

we see three subpanels

1. Expandable list of objects associated with the user.
2. SQL Worksheet.
3. Results


Snippets: Drag & drop or code completion.


DBMS_OUTPUT : Will be displayed in DBMS_OUTPUT tab.


SQL Developer allows:

Setting and removal of breakpoints.

Complie for Debug : Must be executed to make an object available for debugging.

Once compiled for Debugging,When executed in Debug mode the code will stop at the break point.


ShortCut Keys:

F5 -- Execute as a script.

F9 -- Execute statement.

F10 -- Explain Plan

F11 -- Commit

F12 -- Rollback

Ctrl + F7 -- Formating.

F6 -- Auto Trace.

F7 -- Step by Step execution.

Shift + f4 -- Describe object in popup.

Ctrl + D -- Clear SQL WorkSheet.

Execute immediate - Dynamic SQL

NATIVE DYNAMIC SQL (NDS) - Execute immediate


DDL's must be executed via dynamic sql within the pl/sql.


Syntax:

EXECUTE IMMEDIATE <SQL_string>
   [INTO {define_variable[, define_variable]... | record}]
   [USING [IN | OUT | IN OUT] bind_argument
       [, [IN | OUT | IN OUT] bind_argument]...];



Note: <SQL_string> can be an SQL statement or PL/SQL block.



If <SQL_string> is a pl/sql block, then it ends with a semi colon(;).

If <SQL_string> is a sql statement(DML or DDL), then don't end with a semi colon(;).

Note: DDL statement via execute immediate will issue an implicit commit.


Method 1:

No queries; just DDL statements and UPDATEs, INSERTs, or DELETEs, which have no bind variables.

Example:
Begin
  Execute immediate ('create table test_table(rno number,name varchar2(100))');
End;
/




Method 2: (USING clause)

No queries; just UPDATEs, INSERTs, or DELETEs, with a fixed number of bind variables.

Example:


Begin

  Execute immediate 'update scott.emp set sal=sal*:percent where deptno=:deptno' using 0.2,20;
  Dbms_output.put_line(sql%rowcount);

End;
/



Method 3: 

A.  Single row data (INTO & USING clauses)

  Queries (SELECT statements) with a fixed numbers of columns and bind variables, retrieving a single row of data


Example:

Declare
  p_deptno number;
  p_sal number;
  p_hike number;
Begin
  Execute immediate 'select deptno,max(sal),:hike_pct * 100 from scott.emp
                               where deptno=:deptno
                               group by deptno'
                         into p_deptno,p_sal,p_hike
                         using 0.2,20;
   Dbms_output.put_line('Deptno:'|| p_deptno || ' Max salary:'|| p_sal || ' Hike perct:' || p_hike);

End;
/


B.  Multiple row data  ( BULK COLLECT INTO & USING clauses) / (OPEN FOR)

  Queries (SELECT statements) with a fixed numbers of columns and bind variables, retrieving or more rows of data.

Example:

Declare
  Type emp_tab_col is table of scott.emp%rowtype;
  my_emp_tab emp_tab_col:=emp_tab_col();
Begin
 Execute immediate 'select * from scott.emp
                   where deptno=:deptno'
 Bulk collect into my_emp_tab
 using 20;

 For i in my_emp_tab.first..my_emp_tab.last
 loop
  dbms_output.put_line(my_emp_tab(i).empno || ':' || my_emp_tab(i).sal || ':' || my_emp_tab(i).deptno);
 end loop;
End;
/


Method 4:


A statement in which the numbers of columns selected (for a query) or the number of bind variables set are not known until runtime.

(will be executing a dynamic PL/SQL block, rather than a SQL string).

Example:


CREATE OR REPLACE PROCEDURE
get_rows (
   table_in   IN   VARCHAR2,
   where_in   IN   VARCHAR2
                )
IS
BEGIN
   EXECUTE IMMEDIATE
      'DECLARE
          l_row ' || table_in || '%ROWTYPE;
       BEGIN
          SELECT * INTO l_row
            FROM ' || table_in || ' WHERE ' || where_in || ';
       END;';
END get_rows;



BIND VARIABLES:

We can bind into our SQL statement only those expressions (literals, variables, complex expressions) that replace placeholders for data values inside the dynamic string. We cannot bind in the names of schema elements (tables, columns, etc.) or entire chunks of the SQL statement (such as the WHERE clause).

For those parts of our string, we must use concatenation.

IN / OUT / IN OUT : (Argument types)

When we are executing a dynamic query, all bind arguments must be of mode IN,except when we are taking advantage of the RETURNING clause & dynamic PL/SQL.

Example:

PROCEDURE analyze_new_technology (
   tech_name IN VARCHAR2,
   analysis_year IN INTEGER,
   number_of_adherents IN OUT NUMBER,
   projected_revenue OUT NUMBER,
   ) ... ;


DECLARE
   devoted_followers NUMBER;
   est_revenue NUMBER;
BEGIN
   EXECUTE IMMEDIATE
      'BEGIN
          analyze_new_technology (:p1, :p2, :p3, :p4);       END;'
   USING 'oracle', 2010, devoted_followers, est_revenue;
END;


NOTE: OUT & IN OUT variable type and procedure parameters must be of the same data type.


Duplicate Placeholders:

When we are executing a dynamic SQL string (DML or DDLin other words, the string does not end in a semicolon), we must supply an argument for each placeholder, even if there are duplicates.



Example:

CREATE OR REPLACE PROCEDURE updnumval (
   col_in     IN   VARCHAR2,
   start_in   IN   DATE, end_in  IN   DATE,
   val_in     IN   NUMBER)
IS
   dml_str VARCHAR2(32767) :=
      'UPDATE emp SET ' || col_in || ' = :val
        WHERE hiredate BETWEEN :lodate AND :hidate
        AND :val IS NOT NULL';
BEGIN
   EXECUTE IMMEDIATE dml_str
  USING val_in, start_in, end_in, val_in;
END;




When we are executing a dynamic PL/SQL block (the string ends in a semicolon), we must supply an argument for each unique placeholder.

Example:

CREATE OR REPLACE PROCEDURE updnumval (
   col_in     IN   VARCHAR2,
   start_in   IN   DATE, end_in IN   DATE,
   val_in     IN   NUMBER)
IS
   dml_str VARCHAR2(32767) :=
      'BEGIN
          UPDATE emp SET ' || col_in || ' = :val
           WHERE hiredate BETWEEN :lodate AND :hidate
           AND :val IS NOT NULL;
       END;';
BEGIN
   EXECUTE IMMEDIATE dml_str
  USING val_in, start_in, end_in;
END;



Passing NULL Values:

We can't directly pass NULL via USING.

We will encounter an error, if we try to pass NULL directly. Because NULL doesn't have any datatype.

EXECUTE IMMEDIATE
   'UPDATE employee SET salary = :newsal
     WHERE hire_date IS NULL'
   USING NULL;


So we need to pass an uninitialized variable (default null) or to_number(NULL).


BEGIN
    EXECUTE IMMEDIATE
      'UPDATE employee SET salary = :newsal
        WHERE hire_date IS NULL'
      USING TO_NUMBER (NULL);
END;


NOTE:


It turns out that a dynamically constructed and executed PL/SQL block is not treated as a nested block;
instead, it is handled as if it were a procedure or function called from within the current block.
So any variables local to the current or enclosing blocks are not recognized in the dynamic PL/SQL block;
we can make references only to globally defined programs and data structures. These PL/SQL elements include standalone functions and procedures and any elements defined in the specification of a package.

Video Classes - (ORACLE DBA) - Part 9

Class - 17

Class - 18