betweenGo

Oracle SQL queries can throw exceptions. For example in this query f there is no data then Oracle will throw a NO_DATA_FOUND exception.

SELECT status_date INTO v_status_date FROM member WHERE member_id = p_member_id_in;

ORA-01403: no data found

If this query is part of a stored procedure and is called from Hibernate you will get this uninformative exception.

org.hibernate.exception.DataException: could not execute query

Oracle SQL, like many languages, has a try catch construct. In this example you could do the following.

BEGIN
  SELECT status_date INTO v_status_date FROM member WHERE member_id = p_member_id_in;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_status_date := NULL;
END;

Related posts:

1. Ruby on Rails and Oracle
2. Oracle Triggers
3. Describing Tables and Constraints in Oracle
4. HibernateException: Found two representations of same collection
5. Calling Oracle Stored Procedures and Functions