Archive for the ‘Oracle’ Category

Restrictions on table alias in Hibernate Criteria sqlRestriction

Hibernate documentation on SQLRestriction on Crietria

sqlRestriction public static Criterion sqlRestriction(String sql)
Apply a constraint expressed in SQL.
Any occurrences of {alias} will be replaced by the table alias.
Parameters: sql
Returns: Criterion

The catch here is that the only {alias} sqlRestriction understands is the alias name of the table on which the criteria call is constructed. If there is more criterias added  , sqlRestriction does not understand the alias of any other tables joined.  In order to get around this problem in our project, i had to look at the SQL generated by hibernate to find the table alias and use this in the SQL passed to sqlRestriction.

Given below  is the criteria i had to use to make the sqlRetriction work to meet our requirements.

Criteria c = Session.createCriteria(ParentTable.class, “parent”);
c.createCriteria(“parent.movements”, “movements”)
         .add(Restrictions.sqlRestriction(“this_.movement_initial={alias}.movement_init and this_.movement_number={alias}.movement_nbr “));
Hibernate generated an alias of this_ on the root criteria table ‘ParentTable’ and i had to use this alias on the sqlRestriction.

Given below is our table structure


movement_id int(9) (unique)
movement_initial varchar2(5)
movement_number int(3)


child_id int(9) (unique)
movement_id int(9) (non unique)
movement_init varchar2(5)
movement_nbr int(3)
    <class name=”” table=”ParentTable” schema=”XXX”>
        <id name=”movementid” type=”java.lang.Long”>
            <column name=”movement_id” precision=”9″ scale=”0″ />
                <param name=”sequence”>XX_Q1</param>

 <property name=”movementInitial” type=”java.lang.String”>
             <column name=”movement_initial” length=”5″ />
 <property name=”movementNumber” type=”java.lang.Long”>
             <column name=”movement_number” precision=”6″ scale=”0″ />
 <set name=”movements” inverse=”true” cascade=”save-update”>
                <column name=”movement_id” precision=”9″ scale=”0″ not-null=”true” />
            <one-to-many class=””/>

Oracle PL/SQL Date functions

 All examples given here selects from DUAL table and DUAL table is a special one row table present by default in all Oracle database installations. All examples have been executed based on sysdate and these SQLs were run on 2/7/2011.

sysdate function:
You can use sysdate function to get current date and time .
select sysdate from dual;
return 2/7/2011 7:31:31 AM
to_char function:
You can use to_char function to convert a date to a formatted string.
select to_char(sysdate, ‘yyyy/mm/dd’) from dual;
return 2011/02/07
select to_char(sysdate, ‘DDth MON, YYYY’) from dual;
return 07TH FEB, 2011
select to_char(sysdate, ‘YYYY ,DDth MON’) from dual;
return 2011 ,07TH FEB
select to_char(sysdate, ‘MON DDth, YYYY’) from dual;
return FEB 07TH, 2011
select to_char(sysdate, ‘FMMON DDth, YYYY’) from dual;
FEB 7TH, 2011
select to_char(sysdate, ‘FMMon ddth, YYYY’) from dual;
Feb 7th, 2011
select to_char(sysdate, ‘Month DD, YYYY’) from dual;
February  07, 2011
select to_char(sysdate, ‘FMMonth DD, YYYY’) from dual;
February 7, 2011

to_date function:
You can use to_date function to convert a string to a Date.
select to_date(‘2011/02/07’, ‘yyyy/mm/dd’) from dual;
return 2/7/2011
select to_date(‘070211′,’DDMMYY’) from dual;
return 2/7/2011
trunc function:
You can use trunc function to return a date truncated to a specific unit of measure.
Get beginning of the quarter
select trunc(sysdate, ‘Q’) from dual;
return 1/1/2011
Get first day of the Month
select trunc(sysdate, ‘MONTH’) from dual;
return 2/1/2011
Get start day of the week or get first work day of the week
select trunc(sysdate,’DD’) from dual;
return 2/7/2011
Get time in 24 hours
select trunc(sysdate,’HH24′) from dual;
return 2/7/2011 7:00:00 AM
Get time in 12 hours
select trunc(sysdate,’HH12′) from dual;
return 2/7/2011 7:00:00 AM

add_months function:
You can use add_months function to add or subtract n months from a given date.
select add_months(sysdate,2) from dual;
return 4/7/2011 7:15:49 AM
select add_months(sysdate,-4) from dual;
return 10/7/2010 7:16:47 AM
last_day function:
You can use last_day function to return last day of the current month.
select last_day(sysdate) from dual;
return 2/28/2011 7:23:49 AM

next_day function:
You can use next_day function to get the next week day greater than     the given date.
Today is 02/07/2011 Monday.
select next_day(sysdate,’MONDAY’) from dual;
return 2/14/2011 7:26:36 AM – Returns next Monday
select next_day(sysdate,’TUESDAY’) from dual;
2/8/2011 7:28:23 AM – Returns next Tuesday
select next_day(sysdate,’WEDNESDAY’) from dual;
2/9/2011 7:28:23 AM – Returns next Wednesday
select next_day(sysdate,’THURSDAY’) from dual;
2/10/2011 7:28:23 AM – Returns next Thursday
select next_day(sysdate,’FRIDAY’) from dual;
2/11/2011 7:28:23 AM – Returns next Friday
current_date function:
You can use current_date function to return current date and time in the current time zone.
select current_date from dual;
return 2/7/2011 7:19:07 AM
current_timestamp function:
You can use current_timestamp function to return current date and and time with the local time zone.
select current_timestamp from dual;
return 07-FEB-11 AM -06:00;

ORA-30926: unable to get a stable set of rows in the source tables

Merge statement can be used if you are trying to use transformed data fetched from a set of tables to update another table.

MERGE INTO table_3 c
USING (SELECT a.book_id, a.author_id
         FROM table_1 a, table_2 b
        WHERE a.author_id = b.author_id) src
ON (c.book_id = src.book_id)
  UPDATE SET c.author_id  = src.author_id;

This mergae statement might return error ‘ORA-30926: unable to get a stable set of rows in the source tables’ , if there is duplicates in the ‘USING’ query. For example, duplicate book_id retuned by the ‘src’ query in the given example. This can be fixed by using a DISTINCT in the query

MERGE INTO table_3 c
USING (SELECT DISTINCT a.book_id, a.author_id
         FROM table_1 a, table_2 b
        WHERE a.author_id = b.author_id) src
ON (c.book_id = src.book_id)
  UPDATE SET c.author_id  = src.author_id;

-Dhanya James

Paging the query result

If you have 5000 products in database, you may like to show them in pages on the screen. It’s a common request to only retrieve the records on a certain page. This query must be quick and efficient if we are talking about millions of records in a table for an AJAX control.

In Oracle, we can do this:

SELECT * from
SELECT *, ROWNUM as rownumber from Product order by name
where rownumber between (PageNbr - 1)*PageSize + 1 and PageNbr*PageSize

As you can see, this query needs to fetch all rows first then narrows down the result. A more efficient query is:

select *
from ( select a.*, rownum rnum
from ( select * from product order by name) a
where rownum <= PageNbr*PageSize)
where rnum >= (PageNbr - 1)*PageSize + 1

Oracle will optimize this query, so it is not as resource intensive as the first one.

In SQL Server 2005, we can do similar query:

SELECT row_number() OVER (ORDER BY [name]) AS rowNum, * from Product
WHERE rowNum between (@PageNbr - 1) * @PageSize + 1 and @PageNbr * @PageSize
ORDER BY [name]

The query had been optimized in SQL Server as well.

Calling Oracle Stored Procedure from Hibernate

While calling Oracle stored procedure from Hibernate  there is couple of rules that we need to follow. There can be only one return value and this must be a reference cursor. This return reference cusor must be the first and only out value for the stored procedure. It is required that stored procedure must return a reference cursor to be used from Hibernate.

Here is my stored procedure which return book name and ISBN number for a given branch and for a given author

CREATE OR REPLACE PROCEDURE SP_LIB_DTL(p_cursor    out sys_refcursor,
                                       in_brnch_cd in number,
                                       in_auth_cd in number)
  bookName varchar2(8);
  ISBN     number;
  bookName := null;
  ISBN     := 0;
  open p_cursor for
    select l.book_name, l.isbn_nbr
      into bookName, ISBN
      from LIB_BRNCH_DTL l
     where l.branch_code = in_brnch_cd
     and l.auth_code = in_auth_cd;


HIbernate Mapping for return class and Named Query:

<?xml version=”1.0″ encoding=”utf-8″?>
<!DOCTYPE hibernate-mapping PUBLIC “-//Hibernate/Hibernate Mapping DTD 3.0//EN”“>
<class name=””>
<id name=”ISBN” type=”long” />
<property name=”bookName” type=”string” />
 <sql-query name=”LIB_SP” callable=”true”>
 <return class=””>
   <return-property name=”ISBN” column=”isbn_nbr” />
   <return-property name=”bookName” column=”book_name” />
  {  call SP_LIB_DTL(? , :branchCD ,:authorCD) }

Make sure you have used the correct database field name value for the column attribute for the return property mapping. You will get the following error if you don’t map the correct databse field name

could not execute query; bad SQL grammar [{  call SP_LIB_DTL(? , ?) }];
nested exception is java.sql.SQLException: Invalid column name

Here is the DAO implementation for executing query and to set the bind parameter values.

public  List selectBooks(final BigDecimal branchCode,final BigDecimal authorCode){
        return (List) getHibernateTemplate().execute(new HibernateCallback() {
              public Object doInHibernate(Session session) throws HibernateException, SQLException
                  Query q = session.getNamedQuery(“LIB_SP”);
                  q.setLong(“branchCD”, branchCode.longValue());
                  q.setLong(“authorCD”, authorCode.longValue());
                  return q.list();