Execute a procedure from a Java application

0

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?

    
asked by anonymous 17.06.2014 / 15:32

2 answers

3

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);
       }

    }
  }
}
    
17.06.2014 / 15:54
-1

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();
    
20.06.2018 / 21:10