How To Call Database Stored Function And Procedure From Java Code

Como llamar una funcion almacenada en base de datos y a un procedimiento de Java.
En este tutorial que sigue, se explica como llamar a tu propio procedimiento en codigo java.
Se mantiene en ingles  la explicación, como en la publicación original.

Explaination

Before I start writing about this post, first I would like to recommend you, to have basic knowledge of SQL,PL/SQL and Java code. So that, you will know, what is happening here.

First Step : Writing Store Function and Procedure

-- This is a Stored Function, which has one IN parameter and returns String value (in Java)
create or replace function get_empName(emp_id IN NUMBER) RETURN VARCHAR2 IS
emp_name emp.ename%type;
BEGIN
select ename into emp_name from emp where empno=emp_id;
return emp_name;
end;
/

-- This is a Stored Procedure, which has one IN parameter and one OUT parameter 

create or replace procedure PR_empName(emp_id IN NUMBER,emp_name OUT VARCHAR2) IS
BEGIN
select ename into emp_name from emp where empno=emp_id;
end;
/

Once, you are done with writing above Function and Procedure, then next step is to write your Database Connectivity Code

Second Step: Writing Database Connectivity Code

public Connection dbstate() {
        connstr = "jdbc:oracle:thin:@localhost:1521/test";
        try {
            String uname = "scott";     // Database UserName
            String pass = "tiger";        // Database User Password
            Class.forName("oracle.jdbc.OracleDriver").newInstance();
            connect = DriverManager.getConnection(connstr, uname, pass);
        } catch (Exception e) {
            System.out.print("Server Busy.. Please Try Later !!" + e);
        }
        return connect;
    }

Change credential and Database details accordingly (here test is a database name and soctt/tiger is a credential). Test your connectivity and make sure it is working fine. Then, in the next step, we will be calling our Stored Function and Procedure.

Step Third : Calling Stored Function and Procedure

 public String callSp(int id) {
        String name = null;
        try {
            String call = "{ call PR_empName(?,?)}";
            CallableStatement cstmt = con.dbstate().prepareCall(call);
            cstmt.setInt(1, id);
            cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
            cstmt.executeQuery();
            name = cstmt.getString(2);
            con.dbstate().close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }

Careful while registering your OUT parameter of Function or Procedure in Java Code, otherwise it will lead into wrong output or exception.

public String callFun(int id) {
        String name = null;
        try {
            String call = "{ ? = call get_empName(?)}";
            CallableStatement cstmt = con.dbstate().prepareCall(call);
            cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.VARCHAR);
            cstmt.setInt(2, id);
            cstmt.executeQuery();
            name = cstmt.getString(1);
            con.dbstate().close();
        } catch (Exception e) {
            e.printStackTrace();
        }        return name;
    }

Now, call these function and check your output.

   spCall call = new spCall();  // instantiating your class and call your function 
    System.out.println(call.callFun(7369)); /
    System.out.println(call.callSp(7499)); 

Fuente: http://www.jweavers.com/posts/How_To_Call_Database_Stored_Function_And_Procedure_From_Java_Code.php?goback=.gmr_2005072.gde_2005072_member_5818370184997519360#!