Logo 
Search:

Java Forum

Ask Question   UnAnswered
Home » Forum » Java       RSS Feeds

Run Oracle stored procedures with parameters from java

  Asked By: Darla    Date: May 28    Category: Java    Views: 1135
  

What is the syntax to run a stored procedure with parameters from
java jdk 131 against Oracle database using jdbc driver and get the result
code.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Akins Massri     Answered On: May 28

Please have a look at the following code. I have made
use of the oracle  thin Driver. This ships with oracle
itself and this the driver that you should be using.
If you need to go through JDBC-ODBC bridge you need to
create a DSN etc etc...

This hsould provide you with ideas of how to call a
stored proc with in and out parameters.

HTH
Vijay
public static String lvStrOracleUser =
"MRT_LEARN_PH2B"; // Oracle User Name
public static String lvStrOraclePwd =
"MRT_LEARN_PH2B"; // Oracle Password
public static String lvStrOracleMachine = "EVEREST";
// oracle m/c name
public static String lvStrOracleService = "EVERORA";
// Oracle service name

// This method returns a connection
// In parameters  : none
// Out parameters : connection (connection object)
private Connection getConnection(String
lvStrDataSource) throws Exception{
Class.forName("jdbc driver class").newInstance();
return
DriverManager.getConnection("jdbc:oracle:thin:@" +
lvStrOracleMachine.toLowerCase() + ":1521:" +

lvStrOracleService.toLowerCase(),
lvStrOracleUser.toLowerCase(),

lvStrOracleUser.toLowerCase());
}
// This method returns a 2D array of existing country
codes
// In parameters : data source name (String)
// : user id (String)
// Out parameters : vector (Vector)
// position 1: error number
(Integer)
// position 2: error
description (String)
// position 3: 2D string array
(String[][])
// [0]: country id
// [1]: country name
// [2]: country code
public Vector getCountries(String
lvStrDataSourceIdentifier, String lvStrUserId) throws
AppServerException{
CallableStatement lvObjCs = null;
Connection lvObjConn = null;
int lvIntErrorNumber = 1;
String lvStrErrorDescription = "Unexpected Error";
try{
lvObjConn = getConnection();
lvObjCs = lvObjConn.prepareCall("{call
PROC_GET_COUNTRIES_01(?, ?, ?, ?, ? )}");
lvObjCs.registerOutParameter(1, Types.ARRAY,
"ARRAY_VARCHAR2_4000");
lvObjCs.registerOutParameter(2, Types.ARRAY,
"ARRAY_VARCHAR2_4000");
lvObjCs.registerOutParameter(3, Types.ARRAY,
"ARRAY_VARCHAR2_4000");
lvObjCs.registerOutParameter(4, Types.INTEGER);
lvObjCs.registerOutParameter(5, Types.VARCHAR);
lvObjCs.execute();
lvIntErrorNumber = lvObjCs.getInt(4);
lvStrErrorDescription = lvObjCs.getString(5);
if((lvIntErrorNumber % 10) == 1){
throw new AppServerException("Error Number: " +
lvIntErrorNumber + ". Error Description: " +
lvStrErrorDescription);
}
String[][] lvStrArrData = new String[3][];
lvStrArrData[0] = (String
[])lvObjCs.getArray(3).getArray();
lvStrArrData[1] = (String
[])lvObjCs.getArray(1).getArray();
lvStrArrData[2] = (String
[])lvObjCs.getArray(2).getArray();
Vector lvVecData = new Vector();
lvVecData.addElement(new
Integer(lvIntErrorNumber));

lvVecData.addElement(lvStrErrorDescription);
lvVecData.addElement(lvStrArrData);
return lvVecData;
}catch(Exception lvObjEx){
gvObjSc.setRollbackOnly();
throw new AppServerException(lvIntErrorNumber,
lvStrErrorDescription, lvObjEx);
}finally{
try{
if(lvObjCs != null){
lvObjCs.close();
}
if(lvObjConn != null){
lvObjConn.close();
}
}catch(Exception lvObjEx){
}
}
}

 
Didn't find what you were looking for? Find more on Run Oracle stored procedures with parameters from java Or get search suggestion and latest updates.




Tagged: