Happier – there is a better way to write to work with Oracle

Happier because I found a better way to call oracle stored procs through Hibernate – at least one that works with the code rather than having to work round it.

From searching around I found this link by Len (Marilen Aretius Corciovei)

http://www.len.ro/work/call-oracle-procedure-from-hibernate/

Len’s solution is to use  the dummy DUAL table in Oracle to create a single return value in the form of a reference cursor.

(As an aside because I hadn’t seen it used before so other people might not know about it either – DUAL is a handy table that exists in all Oracle DBs especially for when you need some type of pseudo column to use in a FROM statement. It gets its name because it was originally created to allow an easy way to join a table to itself to get two rows for every row on the original table as part of one of the views in the Oracle Data Dictionary.)

At the top you declare a reference cursor

CREATE OR REPLACE FUNCTION checkLastNPass
   ( params ...)
   RETURN SYS_REFCURSOR

Then further down when you are ready to set a return value – you then create the ‘cursor’  for a single value using DUAL as your table source to return the value.

    
OPEN st_cursor FOR SELECT 0 AS retVal FROM DUAL;
    RETURN st_cursor;
END;
/

And then in the hibernate file you need to tell it to expect a scalar value and tie up the column name (in this case retVal)

<sql-query name="CheckLastNPass" callable="true">
    	<return-scalar column="retVal" type="java.lang.Integer"/>
    	    { ? = call checklastnpass(:userId, ...) }
</sql-query>

Which means you can write much more Hibernate like Java  than sing the connection straight

Query query = hsession.getNamedQuery("CheckLastNPass");
query.setParameter("userId", user.getId());
...
result = (Integer)query.uniqueResult();
Advertisements

East Lothian dad. Likes bad jokes and comic songs. Mostly tired, tries to stay cheerful. Thinks space is awesome. Would like to write more code and better docs.

Posted in Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s