Exception Handling - II

RAISE / FORMAT_ERROR_STACK / FORMAT_ERROR_BACKTRACE

RAISE will get you the right error message only if that was the last program executed; assume a scenario where the RAISE is placed in a subprogram which does not handle exception: you will lose the error message, FORMAT_ERROR_STACK and FORMAT_ERROR_BACKTRACE will help there

Sample

CREATE OR REPLACE PROCEDURE p1 IS

BEGIN

  dbms_output.put_line('in p1');

  RAISE no_data_found;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From p1:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

END;

CREATE OR REPLACE PROCEDURE p2 IS

BEGIN

  dbms_output.put_line('in p2 b4 Calling P1');

  p1;

  dbms_output.put_line('in p2 after Calling P1');

END;

CREATE OR REPLACE PROCEDURE p3 IS

BEGIN

  dbms_output.put_line('in p3 b4 Calling P2');

  p2;

  dbms_output.put_line('in p3 b4 Calling P2');

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From P3:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200)); 

END;

Output

in p3 b4 Calling P2

in p2 b4 Calling P1

in p1

From p1:ERROR100 ORA-01403: no data found

in p2 after Calling P1

in p3 b4 Calling P2

When the control comes to P3 we do not know what error happened, Adding a RAISE statement will help.

CREATE OR REPLACE PROCEDURE p1 IS

BEGIN

  dbms_output.put_line('in p1');

  RAISE no_data_found;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From p1:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);

    RAISE;

END;

CREATE OR REPLACE PROCEDURE p2 IS

BEGIN

  dbms_output.put_line('in p2 b4 Calling P1');

  p1;

  dbms_output.put_line('in p2 after Calling P1');

END;

CREATE OR REPLACE PROCEDURE p3 IS

BEGIN

  dbms_output.put_line('in p3 b4 Calling P2');

  p2;

  dbms_output.put_line('in p3 b4 Calling P2');

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From P3:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);   

 

END;

Output

in p3 b4 Calling P2

in p2 b4 Calling P1

in p1

From p1:ERROR100 ORA-01403: no data found

ORA-01403: no data found

From P3:ERROR100 ORA-01403: no data found

ORA-01403: no data found

RAISE throws the error. Now FORMAT_ERROR_STACK does not give much information more than SQLERRM, but FORMAT_ERROR_BACKTRACE will give you more information than ERROR_STACK, the output will be similar to the output of the SQLERRM function.

 

CREATE OR REPLACE PROCEDURE p1 IS

BEGIN

  dbms_output.put_line('in p1');

  RAISE no_data_found;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From p1:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);

    RAISE;

END;

CREATE OR REPLACE PROCEDURE p2 IS

BEGIN

  dbms_output.put_line('in p2 b4 Calling P1');

  p1;

  dbms_output.put_line('in p2 after Calling P1');

END;

CREATE OR REPLACE PROCEDURE p3 IS

BEGIN

  dbms_output.put_line('in p3 b4 Calling P2');

  p2;

  dbms_output.put_line('in p3 b4 Calling P2');

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From P3:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);

    dbms_output.put_line(dbms_utility.format_error_backtrace);

 

END;

Output

in p3 b4 Calling P2

in p2 b4 Calling P1

in p1

From p1:ERROR100 ORA-01403: no data found

ORA-01403: no data found

From P3:ERROR100 ORA-01403: no data found

ORA-01403: no data found

ORA-06512: at "SCOTT.P1", line 10

ORA-06512: at "SCOTT.P2", line 4

ORA-06512: at "SCOTT.P3", line 4

 

Exception Handling

How do you throw the exception after logging in the error?

Do a raise after logging the exception

CREATE OR REPLACE PROCEDURE sp_test IS

  ln NUMBER;

BEGIN

  ln := 5 / 0;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('Error');

    RAISE;

END;

Output would be

Error

And “ORA-01476: divisor is equal to zero” Will be raised

Function inside Procedure

Can we have a function inside procedure?

 

 Sure we can

CREATE OR REPLACE PROCEDURE sp_test (ID IN number)

AS

FUNCTION fn_test (Id IN number)

  RETURN varchar 2

IS

DECLARE eName varchar2(50);

BEGIN

  SELECT Emp_Name INTO eName

    FROM emp WHERE EmpId = Id;

  RETURN oID;

EXCEPTION

  WHEN no_data_found THEN

    eName:= NULL;

    RETURN eName;

END;

BEGIN

  IF(fn_test (ID) IS NULL) THEN

    RAISE_APPLICATION_ERROR(-20001,'EmpId is not valid');

  END IF;

END; 

/

The only disadvantage with this, you will not be able to use this function from any other packages or procedures

File Name from Folder Path

How do you retrive the file name from the folder Name from Folder path:

flname := 'C:\Folder1\SubFolder1\SubFolder3\MyTest.xls';

SELECT substr(flname, instr(flname, '\', -1) + 1) FROM dual;

Output will be
MyTest.xls

SQL Loader

All you need is CTL file, data file and the table to put in the data

Control File (Test.ctl)

LOAD DATA

TRUNCATE

INTO TABLE EMP

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

(

emp_id "trim(:emp_id)",

emp_name "trim(:emp_name)",

emp_dept "trim(:emp_dept)"

)

Data file would be (Test.csv)

emp_id,emp_name,emp_dept

1,John,10

2,Jane,30

Go to the command Prompt and type

C:\ >sqlldr.exe userid=scott/tiger@oradb control=c:\sqldrtest\TEST.ctl log= c:\sqldrtest\20110922.LOG bad= c:\sqldrtest\20110922.BAD data= c:\sqldrtest\Test.csv errors=1000

SQL*Loader: Release 10.2.0.4.0 - Production on Thu Sep 22 11:08:02 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Commit point reached - logical record count 2

It is always a good practice to have Errors=0, so that the file does not get processed even if there is one error

"Failed to access IIS metabase" Error

I was working on a sample web project, where in I came across this error "Failed to access IIS metabase". This happened only when I tried to run the web project by "Using Local IIS Web Server" instead of the default "Use Visual Studio Development Server".  From the Error I was able to find out it is something related to IIS web server only. When I tried to dig into this issue, I understood this is because of my IIS installation. I installed IIS web server after installing my ASP.NET framework.

I got few suggestions to fix this error first one was 
 "To repair framework from the Add/Remove programs" : But for some reasons this did not work

Next suggestion was
  "To Register ASP.NET on IIS" . 
Registering ASP.NET on IIS is not just a matter of associating the various .aspx, .asmx, .axd, .ashx and the other ASP.NET extensions to the aspnet_isapi.dll ISAPI, more has to be done to create the ASP.NET account and to set it for ASP.NET requests, register the ISAPI itself and other stuff.

To do this we will have to use the aspnet_regiis utility
1. Click Start -> run -> cmd - ENTER
2. At the command prompt, type the following, and then press ENTER:
"%windir%\Microsoft.NET\Framework\<version>\aspnet_regiis.exe" -i
   Here version is the version number of the .NET Framework that are installed on our server
   -i is the register the .NET Framework 
   -u is to unregister the .NET Framework

We will have to do this for all the versions installed on the server. I did the following

"%windir%\Microsoft.NET\Framework\v1.1.4322\aspnet_regiis.exe" -i
"%windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe" -i
"%windir%\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe" -i

My IIS web server started working!!!