aspose file tools*
The moose likes JDBC and the fly likes PL/SQL and JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PL/SQL and JDBC" Watch "PL/SQL and JDBC" New topic
Author

PL/SQL and JDBC

Ambrose Tati
Ranch Hand

Joined: Oct 03, 2000
Posts: 55
Dear all,
My ADD_REC stored procedure takes 2 varchar2 in parameters.
The following call raises exception.
...
CallableStatement storedProc =
con.prepareCall("{call ADD_REC(?,?)}");
storedProc.setString(1,"AAAA");
storedProc.setString(2,"BBBB");
storedProc.executeUpdate();
...
But if I make ADD_REC stored procedure parameterless
the following call works fine.
...
CallableStatement storedProc =
con.prepareCall("{call ADD_REC}");
storedProc.executeUpdate();
...
Can't we call PL/SQL stored procedures with parameters from within Java? Help please.
Thanks
Ambrose Tati
Monty Ireland
Ranch Hand

Joined: Oct 03, 2000
Posts: 161
I have not tested this feature. But, it should work..
Enclosed you will find an application that exec a db2 stored procedure. For what it is worth... I hope it helps.
<code>
import java.sql.*;// java sql classes
public class JdbcTest {
public static void main(String args[]) {
try {
String userid, password;
String account, subgroup, segment, category, sector, level4;
String fromdate, todate, annind, incretind, rtntyp;
System.out.println("Loading JDBC Driver ..." );
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
System.out.println("Connecting To jdbc:db2SNT ..." );
userid = "USER_ID";
password = "PASSWORD";
Connection con =
DriverManager.getConnection("jdbc:db2SNT", userid, password );
System.out.println("Creating SQL Statement ..." );
String sqlsp =
"call SPPEF001 ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";

System.out.println("Preparing Callable Statement JAVA SQL Stored Procedure ..." );
CallableStatement call_stmt = con.prepareCall( sqlsp );
System.out.println("Adding Criteria To JAVA SQL Stored Procedure ..." );
account = "USWF00370002";
subgroup = "000";
segment = "001";
category = "000";
sector = "000";
level4 = "000";
fromdate = "1993-07-31";
todate = "1998-07-31";
annind = "Y";
incretind = "N";
rtntyp = "TB";
call_stmt.setString( 1, account );
call_stmt.setString( 2, subgroup );
call_stmt.setString( 3, segment );
call_stmt.setString( 4, category );
call_stmt.setString( 5, sector );
call_stmt.setString( 6, level4 );
call_stmt.setString( 7, fromdate );
call_stmt.setString( 8, todate );
call_stmt.setString( 9, annind );
call_stmt.setString( 10, incretind );
call_stmt.setString( 11, rtntyp );
call_stmt.setDouble( 12, 0.0 );
System.out.println("Register Output With JAVA SQL Stored Procedure ..." );
call_stmt.registerOutParameter( 12, 8, 6);
System.out.println("Executing JAVA SQL Stored Procedure ..." );
ResultSet call_rs = call_stmt.executeQuery();
System.out.println("Getting Output From JAVA SQL Stored Procedure ..." );
double col1 = call_stmt.getDouble( 12 );
System.out.println("Rate Of Return Is " + col1);

}// end try block

catch(ClassNotFoundException e) {
System.out.println("ClassNotFoundException!!!: " + e);
}
catch(SQLWarning e) {
System.out.println("SQL_CODE !!!: " + e);
}
catch(SQLException e) {
System.out.println("SQL_CLASS !!: " + e);
}// end of catch block(s)

}// end of main
}// end of class JdbcTest
</code>
------------------
We learn more from our mistake's than from our success's.
a.k.a. monty6


Multi Platform Database Developer & DBA on E.S.T.
menon
Greenhorn

Joined: Nov 29, 2000
Posts: 6
I feel that you should use
storedProc.execute();
instead of storedProc.executeUpdate();
Regards
Ram
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PL/SQL and JDBC
 
Similar Threads
PL/SQL stored procedures
Oracle9i wrong number or types of arguments in call to...
Not able to insert image more than 4K in Database
Servlets and PL\SQL
Oracle ODBC Driver Problems. Please help