How do i run any oracle function from Hibernet.

satyaki · **Joined:** Fri May 11, 2007 4:44 am **Posts:** 7

Hi!
I'm new to hibernet. How do i call any oracle function through hibernet? Please post here any sample code which demonstrate the entire thing. Thanks in advance.

Regards.

Satyaki De.

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

Code:

List l =session.getNamedQuery("callFunction")
    .setString("param1", param1)
    .setString("param2, param2)
    .setMaxResults(50)
    .list(); // named parameters are better

from: http://forum.java.sun.com/thread.jspa?t ... ID=4476380
Also check Hibernate docs 'Chapter 16. Native SQL' here: http://www.hibernate.org/hib_docs/v3/re ... rysql.html
or just try... : http://www.google.co.uk/search?hl=en&q= ... tnG=Search

satyaki · **Joined:** Fri May 11, 2007 4:44 am **Posts:** 7

Thanks for your reply. I'm checking it. I've tried with this procedure. My question is - can you provide the mapped class details - i mean how will i map this function in Hibernet? Can you post any sample script?

Regards.

Satyaki De.

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

Nope, 'cause I've never done it lol. Have a look at the examples I posted! Lot's of people have already done this and there is plenty of information already out there about this.

Let us know how you get on. And if I pointed you in the right direction don't forget to vote. >.>

satyaki · **Joined:** Fri May 11, 2007 4:44 am **Posts:** 7

Ya, thanks once again - i'm looking into the link.

>>if I pointed you in the right direction don't forget to vote. >.>

Sorry, not clear. I'm newbi. Can you pls clarify - what do you mean by this.

Regards.

Satyaki De.

satyaki · **Joined:** Fri May 11, 2007 4:44 am **Posts:** 7

Hi!
I've tried with your solution. But, it is not working. I don't know whats wrong with the procedure.

I can clarify one more thing - which might be a important hint for you. My function is look like this --

Code:

create or replace function sat_emp(sal in number) return number
is
  s_sal   number(10);
begin
  s_sal := sal + 500*2.5;
  return s_sal;
end;

Now, please advice me - how to proceed. How to map this function in hibernet. As i've to call this function in SELECT statement of a hibernet query. I'm looking for your answer. The most confusing thing is that - here one column is used as input and also as output. It will be used as --

Code:

select empno,sat_emp(sal)
from emp
where deptno = 20;

Hope, now you can get my point. Thanks in advance for your support.

Regards.

Satyaki De.

rdrunner · **Joined:** Thu May 03, 2007 2:30 am **Posts:** 18

Hello...

The way you call the function is normal.... You supply a function with an input value, and it will return the calulated result.

The function ill be called for each row in your select and it will then calculate the "higher" salery for each emp.

You can give the function a meaningfull alias so it will return a column name. In your example the DB will default a name for the column.

So you can write your sql like this:

select empno,sat_emp(sal) CalculatedSal
from emp
where deptno = 20;

The syntax used here is rather a "Direct querry" and not the one discussed in the link...

satyaki · **Joined:** Fri May 11, 2007 4:44 am **Posts:** 7

Yes, i'll use alias name but in that case how will i solve the problem.

Regards.

Satyaki De.

satyaki · **Joined:** Fri May 11, 2007 4:44 am **Posts:** 7

Can you tell me - how do i map that function in Hibernet?

Regards.

Satyaki De.

satyaki · **Joined:** Fri May 11, 2007 4:44 am **Posts:** 7

Can anyone pls help me out of this - as you have mentioned CalculatedSal alias name should mapped. Now, my question is - with which i should map? Will i map it with sal field or any other field? In case any other field what willbe the exact procedure? Cos, that field doesn't exist in the table representation of the class file. Thanks in advance.

Regards.

Satyaki De.