[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 898
  • Last Modified:

How to call a Oracle Stored Procedure from Hibernate Code

Can someone give me a example on how to call a Oracle stored procedure which accepts one integer parameter and outputs an integer using Java-Hibernate code.

I have created the below code but I am getting errors.

CREATE OR REPLACE PROCEDURE SP_CourseID(MyCourseID IN NUMBER) IS
BEGIN
SELECT Course_Name FROM COURSES WHERE Course_ID=MyCourseID;
END SP_CourseID;

The Hibernate Mapping file has the below code:

<sql-query name="SPcourseID" callable="true">
   <return-scalar column="Course_Name" type="String"/>  
    { call SP_CourseID(?:CourseID)}  
   </sql-query>  

The Main Program has the below Code:

query = session.getNamedQuery("SPcourseID");
            query.setInteger("CourseID",1);
            courses = query.list();    
            for (Iterator iterator = courses.iterator(); iterator.hasNext();)
            {
                String courseName = (String) iterator.next();
                System.out.println("The course Name from the Stored Procedure is : " + courseName);
            }

0
vgsrikanth
Asked:
vgsrikanth
  • 6
  • 6
  • 4
1 Solution
 
for_yanCommented:

This is how you pass in and out parameters with Callable Stateemnt
http://download.oracle.com/javase/1.3/docs/guide/jdbc/spec/jdbc-spec.frame7.html
0
 
for_yanCommented:
0
 
vgsrikanthAuthor Commented:
Yan, can you give me the code in Hibenate.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
for_yanCommented:


This has different types - not integers but basically does what you want, I guess
CallableStatement cstmt = con.prepareCall(
		"{call reviseTotal(?)}");
cstmt.setByte(1, (byte)25);
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.executeUpdate();
byte x = cstmt.getByte(1);

Open in new window

0
 
mrjoltcolaCommented:
In your code above, your procedure doesn't actually return the Course_Name, though in your Hibernate config you say it does. So I'd say that is the problem.

CREATE OR REPLACE PROCEDURE SP_CourseID(MyCourseID IN NUMBER) IS
BEGIN
SELECT Course_Name FROM COURSES WHERE Course_ID=MyCourseID; -- <-- Course_name goes nowhere
END SP_CourseID;

Open in new window


I assume you either add a INOUT parameter for Course_Name, or you modify this to be a function instead of a procedure. (Functions return values, by definition, though a procedure can also do INOUT parameters, so they effectively can do so as well).
0
 
vgsrikanthAuthor Commented:
Hi mrjoltcola, can you correct the above code and give me the final code.Thank you.
0
 
for_yanCommented:

I assume that your would return cousre name,
otherwise why to have this procedure at all:

CallableStatement cstmt = con.prepareCall(
            "{call SP_CourseID(?)}");
cstmt.setInt(1, courseID);
cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);
cstmt.executeUpdate();
String course = cstmt.getString(1);
0
 
for_yanCommented:
For this case you probably don't want procedure at all - simple
query would be much better,
but I guess you used this one as an example to learn how to
wortk with stored procedures in JDBC
0
 
mrjoltcolaCommented:
Try a PL/SQL function instead:

CREATE OR REPLACE FUNCTION GETCOURSE(MyCourseID IN NUMBER) RETURN VARCHAR IS
  CourseName VARCHAR(100);
BEGIN
  SELECT Course_Name INTO CourseName FROM COURSES WHERE Course_ID=MyCourseID; 
  return CourseName;
END;
/

Open in new window



You can also test call it from SQL with:  select GETCOURSE(123) from dual
0
 
vgsrikanthAuthor Commented:
Hi Yan, thank you for your comments.I do agree with you I dont need a Stored Procedure for the above problem but I am learning Hibernate and I want to call a Stored Procedure from Hibernate code using a NamedQuery.
0
 
vgsrikanthAuthor Commented:
Hi mrjoltcola, thank you for the code.Can you also give me the code to be included in the hibernate mapping file as I am getting errors.Thank you very much.
0
 
for_yanCommented:
0
 
mrjoltcolaCommented:
Try the PROCEDURE version with your current XML config first.

CREATE OR REPLACE PROCEDURE SP_CourseID(MyCourseID IN NUMBER, CourseName OUT VARCHAR) IS
BEGIN
  SELECT Course_Name INTO CourseName FROM COURSES WHERE Course_ID=MyCourseID; 
END;
/

Open in new window

0
 
vgsrikanthAuthor Commented:
Hi mrjoltcola, thank you very much for your code but I am still getting erros.I think there is something wrong with the mapping file code.
0
 
mrjoltcolaCommented:
Show the errors.
0
 
vgsrikanthAuthor Commented:
Hi Mrjoltcola, the following is the error I am getting:


Initial SessionFactory creation failed.org.hibernate.MappingException: could not determine type null
Exception in thread "main" java.lang.ExceptionInInitializerError
      at com.hibernate.util.HibernateUtil.<clinit>(HibernateUtil.java:14)
      at com.hibernate.Main.listCourse(Main.java:22)
      at com.hibernate.Main.main(Main.java:17)
Caused by: org.hibernate.MappingException: could not determine type null
      at org.hibernate.cfg.ResultSetMappingBinder.buildResultSetMappingDefinition(ResultSetMappingBinder.java:86)
      at org.hibernate.cfg.NamedSQLQuerySecondPass.doSecondPass(NamedSQLQuerySecondPass.java:101)
      at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:1172)
      at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1319)
      at com.hibernate.util.HibernateUtil.<clinit>(HibernateUtil.java:11)
      ... 2 more
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now