Hibernate Community • View topic - Queries on Oracle Functions

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Queries on Oracle Functions

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

mburgess720

Post subject: Queries on Oracle Functions

Posted: Tue Sep 20, 2005 12:56 pm

Newbie

Joined: Mon Sep 19, 2005 4:50 pm
Posts: 2

Hello all,

I am trying to do a query from an Oracle function and am tearing my hair out trying to figure out the answer. I am using Hibernate v2 and want to find the result from the following:

Code:

SELECT pkgName.pkgFunction FROM dual WHERE rownum = 1

Basically there is a Oracle Package that contains a Function I need to call. I would prefer not to have to bother the DBA to write a Procedure to wrap this. I saw you can do a <sql-query> with { ? = call ProcedureName } but I cannot find something equivocal to an Oracle Function in the examples.

The return type from the Function is an Integer and (obviously) it only has one row. I have tried various things, such as <query> and using session.getNamedQuery() and hardcoding the SQL into a session.createQuery("SELECT...") but it usually results in Hibernate complaining when it tries to map the pkgName to an alias. I even tried giving the Function call an alias to no avail:

Code:

SELECT pkgName.pkgFunction AS XXX FROM dual WHERE rownum = 1

and then creating a corresponding XXX.hbm.xml mapping file for it -- but it still complains when it tries to rewrite the SQL that 'pkgName' is an invalid alias. Does anyone have an example Oracle Function in a Package query mapping using the DUAL table they could provide?

P.S. - I would prefer not to have to upgrade to Hibernate v3 (especially since they changed all the package names, ugh!) but if it is required I can.

Top

david

Post subject:

Posted: Tue Sep 20, 2005 6:57 pm

Hibernate Team

Joined: Tue Aug 26, 2003 7:19 pm
Posts: 2364
Location: Brisbane, Australia

Use native SQl support or get the JDBC Connection from the session and call this using the JDBC API.

Top

mburgess720

Post subject: Solution

Posted: Thu Sep 22, 2005 9:48 am

Newbie

Joined: Mon Sep 19, 2005 4:50 pm
Posts: 2

david wrote:

Use native SQl support or get the JDBC Connection from the session and call this using the JDBC API.

I did end up implementing this solution. For anyone interested, the code for getting the JDBC connection from Hibernate is below:

Code:

try {
  Session session = ....; // open the Hibernate session
  String query = "SELECT * FROM Foo";
  Connection con = session.connection();  // gets the java.sql.Connection from the Hibernate session
  PreparedStatement pstmt = con.prepareStatement(query);  // Standard JDBC
  ResultSet rs = pstmt.executeQuery();
  session.disconnect();  // disconnect the JDBC connection
}
catch (HibernateException he) {
  // whatever..
}
catch (SQLException sqle) {
  // yada yada..
}
finally {
  // you know the drill... close the session
}

Top

Page 1 of 1

[ 3 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum