I have a procedure inside the oracle database, but I'd like to run it from a desktop java application I'm developing. How do I execute an oracle procedure with parameters in java?
I have a procedure inside the oracle database, but I'd like to run it from a desktop java application I'm developing. How do I execute an oracle procedure with parameters in java?
The database commands you want to use in your java application should be done via the "JDBC Connector", which is a library that makes it much easier to connect to the database you need.
In your case, you can download Oracle's here: link
I do not know what the IDE is using, but if it is java recommend reading this article: link
According to Oracle itself, you can use the connector to do procedure: link
Regarding the specific question, take a look at how to start a connection with the connector and after that this code will help you solve the problem. (Link code I cited above).
import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.*;
public class EmpSearch
{
public static void main (String args[]) throws SQLException
{
// check whether there are two command-line arguments before proceeding
if ( args.length < 2)
{
System.out.println("Enter both a first and last name as command-line arguments.");
System.out.println("You can enter a complete name or an initial substring.");
System.out.println("For example: java EmpSearch j doe");
}
else
{
// connect to a local XE database as user HR
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:hr/hr@localhost:1521/XE");
Connection conn = ods.getConnection();
// call the PL/SQL procedures with the three parameters
// the first two string parameters (1 and 2) are passed to the procedure
// as command-line arguments
// the REF CURSOR parameter (3) is returned from the procedure
String jobquery = "begin get_emp_info(?, ?, ?); end;";
CallableStatement callStmt = conn.prepareCall(jobquery);
callStmt.registerOutParameter(3, OracleTypes.CURSOR);
callStmt.setString(1, args[0]);
callStmt.setString(2, args[1]);
callStmt.execute();
// return the result set
ResultSet rset = (ResultSet)callStmt.getObject(3);
// determine the number of columns in each row of the result set
ResultSetMetaData rsetMeta = rset.getMetaData();
int count = rsetMeta.getColumnCount();
// print the results, all the columns in each row
while (rset.next()) {
String rsetRow = "";
for (int i=1; i<=count; i++){
rsetRow = rsetRow + " " + rset.getString(i);
}
System.out.println(rsetRow);
}
}
}
}
Another way to get the same result is by using the SimpleJdbcCall
class:
SimpleJdbcCall call = new SimpleJdbcCall(new JdbcTemplate(getDataSource())).withCatalogName("Owner"."PackageName").withFuctionName("Store Procedure Name");
SqlParameterSource paramMap = new MapSqlParameterSource()
.addValue("attribute1", attribute1.getId())
.addValue("attribute2", Date.valueOf(LocalDate.now()));
Long executionId = call.executeFunction(BigDecimal.class, paramMap).longValue();