This discussion is archived
2 Replies Latest reply: Jan 31, 2013 9:05 AM by rp0428 RSS

Call the SP with IN OUT parameter

936666 Newbie
Currently Being Moderated
how to Call the SP with IN OUT parameter

mysp in out varchar

say my sp is as

create procedure mysp (ip in out varchar2)
begin
DBMS_OUTPUT.PUT_LINE('hi this is test from'||ip);
end;

i need to write the java code

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.CallableStatement;

public class level3
{
          public static void main(String[] args) throws SQLException, Throwable{
     System.out.println("You are going to be there.......");
     String url = "jdbc:oracle:thin:@localhost:1521:ORA11G";
     Properties props = new Properties();
     props.setProperty("user", "user");
     props.setProperty("password", "*********");
     Connection Conn = DriverManager.getConnection(url,props);

CallableStatement cstmt = Conn.prepareCall("{call mysp (?)}");
               cstmt.setString(1, "myname");
String param1 = cstmt.getString(1);
          cstmt.execute();
}

}

i would like to return the output as

hi this is test from myname

but i am unable to get the output what is wrong

Error:

Exception in thread "main" java.sql.SQLException: Invalid column index
     at oracle.jdbc.driver.OracleCallableStatement.getString(OracleCallableStatement.java:442)
     at oracle.jdbc.driver.OracleCallableStatementWrapper.getString(OracleCallableStatementWrapper.java:863)
     at CopyOflevel4.main(CopyOflevel4.java:22)

Edited by: 933663 on Jan 30, 2013 10:21 PM
  • 1. Re: Call the SP with IN OUT parameter
    Tolls Journeyer
    Currently Being Moderated
    You need to registerOutParameter as well.
  • 2. Re: Call the SP with IN OUT parameter
    rp0428 Guru
    Currently Being Moderated
    >
    i would like to return the output as

    hi this is test from myname

    but i am unable to get the output what is wrong
    >
    1. Your procedure doesn't produce any output. It only uses the input.

    2. If you want output modify your procedure to produce the output.

    3. Modify your Java code so that it knows that there will be output.

    4. Get the output AFTER you execute the procedure - your code tries to get it before the execute call

    This works for me.
    create or replace procedure mysp (ip in out varchar2) as
    myOutput varchar2(4000);
    begin
     myOutput := 'hi this is test from ' || ip;  -- produce something to return
    DBMS_OUTPUT.PUT_LINE(myOutput);
    ip := myOutput;  -- return the output
    end;
    /
    . . .
    CallableStatement cstmt = Conn.prepareCall("{call mysp (?)}");
    cstmt.registerOutParameter(1, java.sql.Types.VARCHAR); // tell JDBC that there will be output
    cstmt.setString(1, "myname");
    cstmt.execute();
    String param1 = cstmt.getString(1); // get the output - NOTE that this is AFTER you do the execute!
    System.out.println(param1);
    
    You are going to be there.......
    hi this is test from myname
    If you use JDBC you need to be familiar with the JDBC Developer's Guide
    http://docs.oracle.com/cd/B28359_01/java.111/b31224/getsta.htm#sthref81

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points