CODEWITHSUNDEEP
javaoracle-databasestored-procedures
Will Dazey
Will Dazey

Reputation: 273

Calling stored procedures on Oracle throws ORA-06550 exception

I have written a simple test using a jdbc connection and I am trying to understand the failure and possible incorrect Oracle syntax. From my understanding, this should work and searching the internet for a couple hours didn't turn up much to refute this.

Simple stored procedure:

CREATE OR REPLACE PROCEDURE printHelloWorld 
    (in_param_one IN VARCHAR2, out_param_one OUT VARCHAR2) 
    AUTHID CURRENT_USER IS 
BEGIN 
    out_param_one := 'Hello World'; 
END;

Test1:

@Test
public void testOracleStoredProcedureWithIndexes() throws SQLException {
    ...
    Connection con = DriverManager.getConnection(host, props);

    java.sql.CallableStatement cstmt = con.prepareCall("BEGIN printHelloWorld(in_param_one => ?, out_param_one => ?); END;");

    ((oracle.jdbc.internal.OracleCallableStatement)cstmt).setString(1, "Test");
    ((oracle.jdbc.internal.OracleCallableStatement)cstmt).registerOutParameter(2, java.sql.Types.VARCHAR);

    ((java.sql.PreparedStatement)cstmt).execute();
    ...
}

Test2:

@Test
public void testOracleStoredProcedureWithNamedParameters() throws SQLException {
    ...
    Connection con = DriverManager.getConnection(host, props);

    java.sql.CallableStatement cstmt = con.prepareCall("BEGIN printHelloWorld(in_param_one => ?, out_param_one => ?); END;");

    ((oracle.jdbc.internal.OracleCallableStatement)cstmt).setString("in_param_one", "Test");
    ((oracle.jdbc.internal.OracleCallableStatement)cstmt).registerOutParameter("out_param_one", java.sql.Types.VARCHAR);

    ((java.sql.PreparedStatement)cstmt).execute();
}

Running these two tests, "Test1" passes and "Test2" fails. The failure I get from "Test2" is the following:

Caused by: Error : 6550, Position : 55, Sql = BEGIN procPrintHelloWorld2(in_param_one => IN_PARAM_ONE=>:0, out_param_one => OUT_PARAM_ONE=>:1); END;, OriginalSql = BEGIN procPrintHelloWorld2(in_param_one => ?, out_param_one => ?); END;, Error Msg = ORA-06550: line 1, column 56:
PLS-00103: Encountered the symbol ">" when expecting one of the following:

   . ( ) , * @ % & = - + < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || multiset member submultiset
ORA-06550: line 1, column 92:
PLS-00103: Encountered the symbol ">" when expecting one of the following:

   . ( ) , * @ % & = - + < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || multiset member submultiset

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)

I read somewhere that I shouldn't use the ":VAR" syntax, but changing to that does get this working... can anyone more familiar with Oracle point out what I'm doing wrong?

Thanks!

Upvotes: 0

Views: 247

Answers (1)

kfinity
kfinity

Reputation: 9091

Do you see this in your output?

procPrintHelloWorld2(in_param_one => IN_PARAM_ONE=>:0, out_param_one => OUT_PARAM_ONE=>:1)

It's doubling your named parameters, taking the ? in your raw SQL and replacing it with IN_PARAM_ONE=>:0 to get in_param_one => IN_PARAM_ONE=>:0.

If you remove those from your SQL string, I think it should work fine either way.

java.sql.CallableStatement cstmt = con.prepareCall("BEGIN printHelloWorld(?, ?); END;");

Upvotes: 1

Related Questions