Run a precedent from a Java application

Question

I have a file inside the oracle database, but I would like to run it from a java desktop application that I am developing. How do I run an oracle protocol with java parameters?

Answer 1

The database commands you want to use in your Java application should be done via "Connector JDBC", which a library makes it much easier to connect to the database you need.

In your case, you can download the Oracle here: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

I don’t know which IDE you are using, but if it is java I recommend reading this article: https://netbeans.org/kb/docs/ide/oracle-db_pt_BR.html

According to Oracle itself, you can use the Nector to make the process: http://docs.oracle.com/cd/B25329_01/doc/appdev.102/b25108/xedev_jdbc.htm

Regarding the specific question, take a look at how to start a connection with the Nector and after that this code will help you solve the problem. (Link code I mentioned 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);
       }

    }
  }
}

Answer 2

Another way to have the same result is by using the class SimpleJdbcCall:

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