Hi,
Hope this helps u from akadia web site
Regards Rachid
Stored Procedures with IN and OUT Paramerters using JDBC
JDBC provides a stored procedure SQL escape that allows stored procedures to
be called in a standard way for all RDBMs. This escape syntax has one with
and one without result parameter. If the result parameter is used, it must
be registered as an OUT parameter. The other parameters can be used for
input, output or both. All parameters are referred to sequentially, by
number, beginning by 1.
begin ? := func [(?,?,?...)]; end;
begin proc [(?,?,?...)]; end;
According to the SQL92 syntax:
{?= call func [(?,?,?...)]}
{call proc [(?,?,?...)]}
The class CallableStatement inherits the class PreparedStatement and this
class is the successor of Statement. As the names CallableStatement says, in
this class extensions for IN and OUT parameter are defined. IN parameter
values are set using the set methods inherited from PreparedStatement. OUT
parameter must be registered prior to executing the stored procedure. Their
values are retrieved after execution via the predefined getXXX() methods.
A CallableStatement can return one value, one ResultSet or multiple
ResultSet objects. Some examples show you the usage of the class
CallableStatement.
Simple Stored Procedure
The PL/SQL package java_demo defines the procedure get_emp(), using the IN
parameter p_id and the OUT parameters p_ename, p_job, p_sal, analogous to
the attributes of the table EMP (schema scott/tiger).
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
class StoredProcedure {
public static void main (String args []) throws Exception {
// You can put a database name after
// the @ sign in the connection URL.
String strUrl = "jdbc:oracle:oci8:@asu1";
Connection dbConn = null;
// Register Oracle driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// Connect to the database
dbConn = DriverManager.getConnection(strUrl,"scott","tiger");
// Prepare a PL/SQL call
// PROCEDURE get_emp(p_id IN NUMBER,
// p_ename OUT VARCHAR2,
// p_job OUT VARCHAR2,
// p_sal OUT NUMBER);
String strQuery = "{call java_demo.get_emp(?, ?, ?, ?)}";
CallableStatement cstmt = dbConn.prepareCall(strQuery);
// The id argument is the first ?
// Declare that the second ? is a return value of type VARCHAR
// Declare that the third ? is a return value of type VARCHAR
// Declare that the forth ? is a return value of type NUMBER
cstmt.setInt(1, 7654);
cstmt.registerOutParameter(2, OracleTypes.VARCHAR);
cstmt.registerOutParameter(3, OracleTypes.VARCHAR);
cstmt.registerOutParameter(4, OracleTypes.NUMBER);
cstmt.execute();
// Get the data
System.out.println(cstmt.getString(2));
System.out.println(cstmt.getString(3));
System.out.println(cstmt.getDouble(4));
// Close statement and connection
cstmt.close();
dbConn.close();
}
}
Simple Stored Function
The PL/SQL package java_demo defines the function get_sal(), using the IN
parameter p_id and the result parameter sal, analogous to the attributes of
the table EMP (schema scott/tiger).
...
// Prepare a PL/SQL call
// FUNCTION get_sal(p_id IN NUMBER) RETURN NUMBER;
String strQuery = "{ ? = call java_demo.get_sal(?)}";
CallableStatement cstmt = dbConn.prepareCall(strQuery);
// Declare that the first ? is a return value of type DOUBLE
// The id argument is the second ?
cstmt.registerOutParameter(1, OracleTypes.NUMBER);
cstmt.setInt(2, 7654);
cstmt.execute();
// Get the data
System.out.println(cstmt.getDouble(1));
...
Stored Function with Record Set
The PL/SQL package java_demo defines the procedure list_emp(), using the IN
parameter p_job and the result parameter as ResultSet, analogous to the
attributes of the table EMP (schema scott/tiger). The ResultSet contains all
the employees matching the defined job.
...
// Prepare a PL/SQL call
// FUNCTION list_emp(p_job VARCHAR2) RETURN myreftype;
String strQuery = "{ ? = call java_demo.list_emp(?)}";
CallableStatement cstmt = dbConn.prepareCall(strQuery);
// Select all the SALESMAN person
// Declare that the first ? is a return value
// of type OBJECT (Cursor Ref.)
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.setString(2, "SALESMAN");
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(1);
// Loop the cursor
while (rset.next()) {
System.out.println(rset.getString("ename"));
}
...
-----Message d'origine-----
De : A mailing list about Java Server Pages specification and reference
[mailto:[EMAIL PROTECTED]]De la part de Mattias Jiderhamn
Envoye : vendredi 20 juillet 2001 06:34
A : [EMAIL PROTECTED]
Objet : Re: Calling ORACLE Stored Procedure
Are you using CallableStatement instead of Statement?
> Sent: den 20 juli 2001 00:04
> Subject: Calling ORACLE Stored Procedure
>
> I'm trying to call a stored proc in oracle from java. I'm called the
same
> proc from another system successfully, but when I do the call from
java I'm
> getting a message from oracle saying the proc needs to be declared.
>
> Any ideas?
>
> Examples of calling an ORACLE SP w/ input & output parms?
>
> Thanks.
>
>
========================================================================
===
> To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
JSP-INTEREST".
> For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST
DIGEST".
> Some relevant FAQs on JSP/Servlets can be found at:
>
> http://java.sun.com/products/jsp/faq.html
> http://www.esperanto.org.nz/jsp/jspfaq.html
> http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
> http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
JSP-INTEREST".
For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST
DIGEST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST DIGEST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
