Calling Oracle SP using JDBC

Discussion in 'Java' started by Tim Slattery, Aug 15, 2008.

  1. Tim Slattery

    Tim Slattery Guest

    I've got an Oracle database, I've written a function within it, which
    returns a sys_refcursor. The function works, I can run it from
    SQL*Plus.

    But I can't invoke it from Java. I get a connection, then do this:

    CallableStatement sprocStmt = null;
    String spName = "{ call ? := getReporterData(?) }";
    sprocStmt = dbConnection.prepareCall(spName);
    sprocStmt.registerOutParameter(1, OracleTypes.CURSOR);
    sprocStmt.setString(2, key);
    sprocStmt.execute();

    It blowu up on the "execute" call. The error message says "invalid
    character". That's it, nothing else.

    Since the procedure works, the problem has to be in the Java code. But
    where? It looks exactly like the examples I've been working from.
     
    Tim Slattery, Aug 15, 2008
    #1
    1. Advertisements

  2. Your syntax for call is incorrect. Remove the colon :)) before the
    equal (=). That's probably the "invalid character" the error message
    refers to.
     
    Lee Fesperman, Aug 15, 2008
    #2
    1. Advertisements

  3. Tim Slattery

    Tim Slattery Guest

    That's not it, := is the assignment operator in Oracle. All the
    examples I've seen have that syntax.
     
    Tim Slattery, Aug 15, 2008
    #3
  4. That might be true, but what I was describing is 'JDBC' syntax (no :
    before =). Notice that you are using JDBC escape syntax by enclosing
    the call in braces.

    Did you even try my suggestion? Did you get the exact same exception?
     
    Lee Fesperman, Aug 16, 2008
    #4
  5. Tim Slattery

    steve Guest

    The syntax is wrong, "?" is used for parmeter substitution, so your telling
    oracle to call a cursor!!

    below is CORRECT, notice the "? = call"

    because the cursor is returned from the proc, you need to tell the interface
    this fact.



    String The_qry = "{ ? = call
    external_user.PACKAGE_02.RETURN_progparts2(?,?) }";

    cstmt = (OracleCallableStatement) dbconn.prepareCall(The_qry);

    cstmt.registerOutParameter(1, OracleTypes.CURSOR);
    cstmt.setString(2, progid);

    // the program info we want
    cstmt.setInt(3, deployFlag);
    cstmt.execute();

    rset = ((OracleCallableStatement) cstmt).getCursor(1);
    ........


    Steve
     
    steve, Aug 21, 2008
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.