MyBatis Calling Oracle stored procedure

[Xiaobin Xue]

Hi everyone,

I am new to MyBatis(I am using version 3.2.5). 

This problem may be asked many times before, but still I don't know the answer.

Right now, I am able to call sql, such as select, insert, update, delete in xml.

But unfortunately I couldn't call Oracle stored procedure, and don't know where I am wrong. :-(((

Please help me and point out where I am wrong if possible, thanks guys!

Here is my code, in Java side,

I have a bean class User.java

public class User {

private String name;

private int age;

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getAge() {

return age;

}

public void setAge(int age) {

this.age = age;

}

}

In my DAO class, I am calling the SP by follow sentence. 

I want to select all the users which age = 22, there is only one such user in database.

User user = new User();

user.setName("tom");

user.setAge(22);

User user2 = (User) session.selectOne("UserMapper.queryUser",user);

In UserMapper.xml, I have a block.

<select id="queryUser" parameterType="User"  statementType = "CALLABLE" >

{CALL QUERYUSER( #{age, mode=IN, jdbcType=INTEGER},

                                                        #{rs_name, mode=OUT, jdbcType=VARCHAR},

#{rs_age, mode=OUT, jdbcType=INTEGER})}

</select>

and in my Oracle database, I have a table USERINFO,

USERINFO(

    NAME VARCHAR(30),

    AGE NUMBER(5)

)

and I am writing my stored procedure like this:

In fact, I don't know how to write SP, I just wrote mine according to what I can get on the Internet.

So please forgive me if I wrote a terrible stored procedure.

CREATE OR REPLACE

PROCEDURE queryUser (OUT rs_name VARCHAR,

                     OUT rs_age INTEGER,

                     IN age INTEGER

                       ) AS

BEGIN

   SELECT name,age INTO rs_name,rs_age

   FROM USERINFO

   where age = #age;

END;

When I ran it, it will throw exception:

Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: 

### Error querying database.  Cause: java.sql.SQLException: ORA-06550: line 1, column 7:

PLS-00905: object MYBATIS.QUERYUSER is invalid

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

### The error may involve UserMapper.queryUser-Inline

### The error occurred while setting parameters

### Cause: java.sql.SQLException: ORA-06550: line 1, column 7:

PLS-00905: object MYBATIS.QUERYUSER is invalid

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

I guess I am writing the incorrect stored procedure, or incorrect xml, or both.

I also tried using cursor, it didn't work neither, or maybe I wrote in a wrong way.

Anyway, I don't know how to write a stored procedure for MyBatis.

I previously used stored functions, and use jdbc to call them, it can work; but for MyBatis, it doesn't consider it as procedure. Is MyBatis only able to call procedure not function??

Can someone please help me?

Really appreciate that.

Thanks and Regards,

Xiaobin

[Jose María Zaragoza]

2014-03-27 1:27 GMT+01:00 Xiaobin Xue <xxb...@gmail.com>:

> When I ran it, it will throw exception:
> Exception in thread "main"
> org.apache.ibatis.exceptions.PersistenceException:
> ### Error querying database. Cause: java.sql.SQLException: ORA-06550: line
> 1, column 7:
> PLS-00905: object MYBATIS.QUERYUSER is invalid
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> ### The error may involve UserMapper.queryUser-Inline
> ### The error occurred while setting parameters
> ### Cause: java.sql.SQLException: ORA-06550: line 1, column 7:
> PLS-00905: object MYBATIS.QUERYUSER is invalid
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>

Can you execute this store procedure from another ORACLE client (Toad
, for example ) ?

Can you check the state of this store procedure ?
Maybe this procedure is not compiled correctly (PACKAGE + PACKAGE BODY )

Regards

[Xiaobin Xue]

Hi Jose,

In fact, I don't how to write the correct Oracle stored procedure for MyBatis.

The goal of the stored procedure should be the same of this sql:

select name, age from userInfo where age = #input number

do you have an complete and simple example for how to call oracle stored procedure? or if possible, can you help me write the runnable stored procedure for this case?

Thanks

[Guy Rouillier]

What Jose was saying is to first get your stored procedure working,
without thinking about MyBatis. Your stored procedure definition is
incorrect, Oracle probably has it marked as invalid. I just tried to
run it, and immediately got a syntax error.

Parameters are defined like this: rs_name OUT VARCHAR2

Name first, then optionally IN or OUT, and finally the datatype.

So, start by consulting the Oracle documentation on writing stored
procedures. Once you have the stored proc working, then work on getting
it to work with MyBatis.

--
Guy Rouillier

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[Xiaobin Xue]

Thank you Jose&Guy,

I made it work last week, forgot to post here.

I corrected my stored procedure.

create or replace procedure queryUser

( in_name IN VARCHAR2, 

user_cursor OUT SYS_REFCURSOR) 

is 

begin 

      open user_cursor for 

      select name,age from userInfo 

      where name = in_name;

end;

and corrected my mapper.xml

<resultMap id="userResult" type="User">  ----> type can also be HashMap here

<result property="name" column="name" jdbcType="VARCHAR" javaType="java.lang.String"/>

<result property="age" column="age" jdbcType="INTEGER" javaType="java.lang.Integer"/>

</resultMap>

<select id="queryUser"   statementType = "CALLABLE" parameterType="java.util.HashMap">

{CALL QUERYUSER(

               #{in_name, mode=IN, jdbcType=VARCHAR, javaType=java.lang.String},

#{user_cursor, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet,resultMap=userResult}

)}

</select>

and In java side 

HashMap<String,Object> rs_map = new HashMap<String,Object>();

rs_map.put("user_cursor", null);

rs_map.put("in_name", "mike");

session.selectOne("com.hcl.mybatis.UserMapper.queryUser",rs_map);

ArrayList<Object> array =(ArrayList<Object>)rs_map.get("user_cursor");

User user = (User)array.get(0);

System.out.println(user.getName()+" "+user.getAge());

Thank you guys! 

Hope the post would be helpful to those who are also new to MyBatis.

Cheers!