CODEWITHSUNDEEP
sqloracle-databaseormmybatis
Christian O.
Christian O.

Reputation: 498

Calling a stored procedure in mybatis

In my Oracle database I have a stored procedure TEST_PROC():

create or replace PROCEDURE TEST_PROC
(
 PARAM_1 IN NUMBER 
, PARAM_2 IN NUMBER 
) AS 
BEGIN
    INSERT INTO sometable(VALUE1, VALUE2) VALUES (PARAM_1, PARAM_2);
END TEST_PROC;

In Oracle SQL Developer, I can call this procedure just fine using Call TEST_PROC(1,2)

Now I want to call this procedure from my java application using mybatis. The relevant section of my mapper XML file looks like this :

<insert id="insertData" parameterType="model" statementType="CALLABLE">
        {call TEST_PROC(
            #{param_1, jdbcType=INTEGER,mode=IN},
            #{param_2, jdbcType=INTEGER,mode=IN}})}
    </insert>

Which should be equivalent to this mybatis function call I found in the documentation (with the difference that my procedure does not return any values):

<select id="selectBlog" resultSets="blogs,authors" resultMap="blogResult" statementType="CALLABLE">
  {call getBlogsAndAuthors(#{id,jdbcType=INTEGER,mode=IN})}
</select>

However, mybatis does not seem to able to find the procedure, as I get the error code:

; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-06550: Zeile 1, Spalte 7: PLS-00201: identifier 'TEST_PROC' must be declared

What am I doing wrong here?

Upvotes: 0

Views: 1029

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

I don't know Java nor YourBatis (just kidding; MyBatis), but - as of Oracle, it looks as if user you're connected to doesn't contain that procedure.

Here's what I mean: there's no procedure accessible to me whose name contains TEST as the first part of its name:

SQL> select object_name from all_objects where object_type = 'PROCEDURE'
  2                                        and upper(object_name) like 'TEST%';

no rows selected

So, when I call it, I get the same error as you:

SQL> exec test_proc;
BEGIN test_proc; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TEST_PROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>

Therefore, check whether you're connected to the same user you used while testing the procedure in SQL Developer.

Upvotes: 1

Related Questions