Exception PL/SQL

PL/SQL error management features 
1- Defining exceptions
2- Raising exceptions 
3- Handing exceptions 

We will explain every topic individually

1-Defining exceptions

The EXCEPTION is a limited type of data. 
Has just two attributes: code and message. 
You can RAISE and handle an exception, but it cannot be passed as an argument in a program. 
syntax

pragma exception_init(error_name,-error_number);

example :-

declare

   bulk_erros exception

   pragma exception_init(bulk_erros,-20006);

begin

  ...

end;

2-Raising exceptions  
a-RAISE raises the specified exception by name. 
   RAISE; re-raises current exception. Callable only within the exception section. b-RAISE_APPLICATION_ERROR
  Communicates an application specific error back to a non-PL/SQL host environment.
  Error numbers restricted to the -20,999 - -20,000 range.
  Syntax :-

  RAISE_APPLICATION_ERROR (num binary_integer, msg varchar2, 

                                                                       keeperrorstack boolean default FALSE);

  Example :
  RAISE_APPLICATION_ERROR (-20070, ‘Employee must be more than 18 years old.’); 

3-Handing exceptions  

a-The EXCEPTION section consolidates all error handling logic in a block. 
b-We can use the following functions at exception section: 
   –SQLCODE --> get error number.
   –SQLERRM--> get error message.
   –DBMS_UTILITY.FORMAT_ERROR_STACK 
       Returns exception in more details.
   –DBMS_UTILITY.FORMAT_ERROR_BACKTRACE 
      Returns the full stack of errors with line number information.number that raised the error.
      This stack is available only if you the error is unhandled.
c-The DBMS_ERRLOG package 
   –Quick and easy logging of DML errors
d- We can use The AFTER SERVERERROR trigger to log any error have been raised at database.

Calling Stored Procedures or Functions from Code Java

To invoke a stored procedure or function  from within an entity object or an application module, you need to follow below steps:
1. Create a CallableStatement with the PL/SQL block containing the stored procedure or function invocation
2. Bind any parameters ( IN or OUT )
3. Execute the statement.
4. Optionally retrieve the values of any OUT parameters or return value of function
5. Close the statement. 

I will illustrate previous steps in below examples

1-Stored Procedure with OUT parameter 

 public void testStoredProcedure() {  
   
     OADBTransaction txn = getDBTransaction();  
   
     CallableStatement callableStatement =  
   
       txn.createCallableStatement("begin xxx_procedure(:1, :2); end;",OADBTransaction.DEFAULT);  
   
     try {  
   
       callableStatement.registerOutParameter(2, Types.VARCHAR);  
   
       callableStatement.setString(1, "mahmoud");  
   
       String outParamValue = null;  
   
       callableStatement.execute();  
   
       outParamValue = callableStatement.getString(1);  
   
       callableStatement.close();  
   
     } catch (SQLException sqle) {  
   
       callableStatement.close();  
   
     }  
   }   

2- Stored Function

 public void testFunction() {  
     OADBTransaction txn = getDBTransaction();  
   
     CallableStatement callableStatement =  
   
       txn.createCallableStatement("begin :1 := xx_function(:2); end;",OADBTransaction.DEFAULT);  
   
     try {  
   
       callableStatement.registerOutParameter(1, Types.VARCHAR);  
   
       callableStatement.setString(1, "mahmoud");  
   
       String outParamValue = null;  
   
       callableStatement.execute();  
   
       outParamValue = callableStatement.getString(1);  
   
       callableStatement.close();  
   
     } catch (SQLException sqle) {  
   
       callableStatement.close();  
     }  
   }  

Thinking in ROWID

We hear ‘ROWID’ many times from database developers, So we should think about what purpose it serves. 

Oracle says, ‘They are the fastest way to access a single row’

ROWID gives us the location of the data for a given row in a table. The datafile – then the position of the row in the block, then the data block in the datafile. 

As you can imagine, this data is UNIQUE. So, each row in your table will have a unique ROWID. 

This is only because the data for 2 rows can’t exist in the same space.

It’s not uncommon for a table to lack any unique columns or PKs or unique indexes. But, if you know the ROWID, then you will have at least 1 unique differentiator for that record.

It’s also important to remember that a record’s ROWID is not guaranteed to remain unchanged.

Since it’s a PSEUDO column, it’s not actually stored in the database. That means we can’t INSERT, UPDATE, DELETE  ROWID. But, we can use it in a SELECT or WHERE.

Oracle is saying ROWIDs aren’t stored in a table. They can and WILL be stored in an INDEX. Indexes allow us to quickly jump to the records we are looking for. However, the ROWID is dynamically assembled as you retrieve that data.  

Main majors use of ROWIDs

1. Use it to delete duplicate rows from your table.
2. Use it to edit SELECT results in Toad for Oracle

Deleting Records
Add ‘ROWID’ to your SELECT string. Once you’ve identified the records you want to delete, just record those values. Then issue your ‘DELETE FROM TABLE WHERE ROWID in (…).

select emp.ROWID, empno , ename

from scott.emp;

If you query a view and include ROWID, you may get a 

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

Your view could be pulling data from multiple source tables. These tables need a PK apparently. This Oracle’s way of saying that it can’t determine the the ROWID based on what you are querying. Also, your row could be pulling from multiple sources, so there would be theoretically multiple ROWIDs

Example Struts 2 + Hibernate + Spring intergration

Hi friends!

Finally after a long time, we are back. Here we’ve yet another example, as the title says, it is an Integration example in which we are going to see how Struts, Spring and Hibernate are getting integrated together.

   

    You create project and  execute follow step by step:

1. I use database Oracle 11g

create table: 

-- Create table

create table TBL_PHARMACYADMIN

(

  USERNAME VARCHAR2(4000),

  PASSWORD VARCHAR2(4000),

  ID       NUMBER

)

and create sequence

CREATE SEQUENCE PHARMACYADMIN_seq

 START WITH     1000

 INCREMENT BY   1

 NOCACHE

 NOCYCLE;

2. Create Project dynamic web application pharmacyprj

- web.xml:

<filter>

  <filter-name>struts2</filter-name>

  <!-- filter-class>org.apache.struts2.dispatcher.FilterDispatcher</filter-class> -->

  <filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>

 </filter>

 <filter-mapping>

  <filter-name>struts2</filter-name>

  <url-pattern>/*</url-pattern>

 </filter-mapping>

 <session-config>

  <session-timeout>30</session-timeout>

 </session-config>

 <listener> 

<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> 

</listener>   

 <context-param>

  <param-name>contextConfigLocation</param-name>

  <param-value>classpath:applicationContext*.xml</param-value>

  <!-- WEB-INF/applicationContext.xml -->

 </context-param>

 <welcome-file-list>

    <welcome-file>index.jsp</welcome-file>

 </welcome-file-list>

- hibernate.cfg.xml:

<session-factory>

<property name="hbm2ddl.auto">update</property>

<property name="dialect">

           org.hibernate.dialect.OracleDialect

       </property>

       <property name="connection.driver_class">

         oracle.jdbc.driver.OracleDriver

       </property>

       <property name="connection.url">

         jdbc:oracle:thin:@127.0.0.1:1521:ST2

       </property>

       <property name="connection.username">st</property>

       <property name="connection.password">123456</property>      

 

       <property name="hibernate.show_sql">true</property>

<property name="current_session_context_class">thread</property>

<property name="hibernate.connection.autocommit">true</property>

       <mapping resource="pharmacyAdmin.hbm.xml"/>

</session-factory>

- applicationContext.xml:

   <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">

<property name="configLocation" value="classpath:hibernate.cfg.xml"/>

</bean>

<bean id="testSpring" class="com.fiz.model.TestSpring">

<property name="message" value="Hello Trung"></property>

</bean>

- pharmacyAdmin.hbm.xml

     <class name="com.fiz.model.pharmacyAdmin" table="tbl_pharmacyadmin">

    <id name="id" column="id" type="long">

    <generator class="sequence-identity">

    <param name="sequence">PHARMACYADMIN_SEQ</param>

    </generator>

    </id>

    <property name="username" column="username" insert="true" update="true" type="java.lang.String"></property>

    <property name="password" column="password" insert="true" update="true" type="java.lang.String"></property>

    </class>

- struts.xml:

                <action name="testaction" class="com.fiz.model.TestSpring">

<result name="success">index.jsp</result>

</action>

- Create class java HibernateSessionFactory.java

package com.fiz.utils;

import org.hibernate.HibernateException;

import org.hibernate.Session;

import org.hibernate.cfg.Configuration;

public class HibernateSessionFactory {

private static String CONFIG_FILE_LOCATION="hibernate.cfg.xml";

private static final ThreadLocal<Session> threadLocal=new ThreadLocal<Session>();

private static Configuration configuration=new Configuration();

private static org.hibernate.SessionFactory sessionFactory;

private static String configFile=CONFIG_FILE_LOCATION;

static{

try{

configuration.configure(configFile);

sessionFactory=configuration.buildSessionFactory();

}catch (Exception e) {

// TODO: handle exception

System.out.println("Error Creating SessionFactory");

e.printStackTrace();

}

}

private HibernateSessionFactory(){

}

public static Session getSession()throws HibernateException{

Session session=(Session)threadLocal.get();

if(session==null || !session.isOpen()){

if(sessionFactory==null){

rebuildSessionFactory();

}

session=(sessionFactory!=null)?sessionFactory.openSession():null;

threadLocal.set(session);

}

return session;

}

public static void rebuildSessionFactory(){

try{

configuration.configure(configFile);

sessionFactory=configuration.buildSessionFactory();

}catch (Exception e) {

// TODO: handle exception

}

}

public static void closeSession()throws HibernateException{

Session session=(Session)threadLocal.get();

threadLocal.set(null);

if(session!=null){

session.close();

}

}

public static Configuration getConfiguration() {

return configuration;

}

public static org.hibernate.SessionFactory getSessionFactory() {

return sessionFactory;

}

public static void setConfigFile(String configFile) {

HibernateSessionFactory.configFile = configFile;

sessionFactory=null;

}

}

- Create interface userDao:

public interface userDao {

public List getAllUsers()throws Exception;

public List getUsersByPage(int pageNo, int pageSize)throws Exception;

public pharmacyAdmin findByUsernameAndPassword(String username, String password);

public Page findByPageNo(int pageNo, int pageSize);

public int addUser(pharmacyAdmin pmcyAdmin);

public int removeUser(pharmacyAdmin pmcyAdmin);

}

- Create class UserDaoImpl.java inherited Interface userDao:

private Session session=HibernateSessionFactory.getSession();

@Override

public List getAllUsers() throws Exception {

// TODO Auto-generated method stub

String sql="from tbl_pharmacyadmin";

Query query=session.createQuery(sql);

return query.list();

}

@Override

public List getUsersByPage(int pageNo, int pageSize) throws Exception {

// TODO Auto-generated method stub

String sql="from tbl_pharmacyadmin";

return null;

}

- In part web, you create files follow below:

Page index.jsp

<body>

<div id="header">

<div id="navigation">

    <ul>

        <li><a href="index.jsp">Home</a></li>

           <li><a href="#">About Us</a></li>

           <li><a href="#">ADMINISTRATOR </a></li>

           <li><a href="#">DISTRIBUTER </a></li>

           <li><a href="#">CUST0MER</a></li>

           <li><a href="#">Contact Us</a></li>

       </ul>

   </div>

</div>

<table width="960px" border="0" cellpadding="0" cellspacing="10" align="center"></table>

<div id="content">

<table width="960" border="0" cellspacing="0" cellpadding="0">

 <tr align="justify">

   <td width="200" valign="top">

    <img src="images/hioxindia-pharmacy_19.jpg" alt="" border="0" />    </td>

   <td width="760" valign="top">

    <h1>Welcome to Pharmaceutical Company </h1>

       <p>This is free Pharmaceutical Company   for your online website, blog or web page which is under Health and Medicine category. Anyone wants good, professional,   for their pharmacy, drug store, medical store then this   would be the best one. This free pharmaceutical company   is designed by professional designers to make your task much easier to customize it.</p><br />

<p>There are many other   categories like Arts, Agriculture, Computers, Satellite, Cars, Finance, Nature, Music, Personal Website, Religious, Fashion, Furniture, Holiday, Travel, Night club are available in this site. Have a look at the collection of HTML  s, pick the right one, preview and download it easily.</p>

   </td>

 </tr>

</table>

</div>

<div id="footer">

<div id="footer_1">

    <ul>

        <li><a href="#"></a></li>

            <li><a href="#"></a></li>

            <li><a href="#"></a></li>

            <li><a href="#"></a></li>

            <li><a href="#"></a></li>

        </ul> <br />

   <span id="design">Designed by <a id="dum" href="http://www.dacsanthanhhoa.com" target="_blank">TrungLee</a></span>

   <script type="text/javascript">

   document.onload = ctck();

   </script>

  </div>

</div>

</body>

- Layout.css:

@CHARSET "UTF-8";

/* CSS Document */

body{

margin:0;

color:#232323;

padding-top: 0px;

padding-right: 0;

padding-bottom: 0;

padding-left: 0;

background:#f0f3f0;

width:100%;

}

div, h1, h2, h3, h4, p, form, label, input, textarea, img, span{

margin:0; padding:0;

}

ul{

margin:0; 

padding:0; 

list-style-type:none;

font-size:0;

line-height:0;

}

.spacer{

clear:both; 

font-size:0; 

line-height:0;

}

h1

{

font:bold 14px/40px Arial, Helvetica, sans-serif;

}

p

{

font:normal 11px/18px Arial, Helvetica, sans-serif;

padding:0 15px 0 0;

}

.more

{

font:normal 12px/18px Arial, Helvetica, sans-serif;

color:#2e3192;

text-decoration:underline;

}

/* header */

#header

{

margin:0 auto;

background:url(images/hioxindia-pharmacy_02.jpg) 0 0 repeat-x;

}

#navigation

{

margin:0 auto;

background:url(images/hioxindia-pharmacy_04.jpg) 0 0 no-repeat;

height:446px;

width:960px;

}

#navigation ul

{

list-style:none;

}

#navigation ul li

{

font:normal 14px/40px Arial, Helvetica, sans-serif;

display:block;

padding:0 30px;

margin-top:410px;

float:left;

text-align:center;

}

#navigation ul li a

{

text-decoration:none;

display:block;

color:#034ea2;

}

#navigation ul li a:hover

{

text-decoration:none;

display:block;

color:#FFFFFF;

}

#content

{

margin:0 auto;

width:960px;

}

HAVE A NICE DAY!