AnsweredAssumed Answered

How to run the Store Procedure via DevTest ?

Question asked by PreethiManoj on Jun 14, 2017
Latest reply on Aug 31, 2017 by PreethiManoj

I want to run the below storeProcedure in CA DevTest. When i am running in Oracle DB, i m getting the output.

 

Output of the below code is 5 digit number : 12335

 

Store Procedure :

 

DECLARE
              v_batch_id NUMBER;
          BEGIN
              v_batch_id := UTIL_PRCS_PKG.GET_BATCH('TABLE_NAME');
              dbms_output.put_line (v_batch_id);
          END;

 

Is anyone came across the same issue. Pls provide the steps to run.

 

-------------------------------------------

I tried to run the Store_Procedure via "Java Script Step" using Java Code. I am getting the output as "Script Executed. Result is Class Main". But, I am expecting it should display "Batch_id".

 

Store_Procedure name : UTIL_PRCS_PKG.GET_BATCH

Table name : 'OMT_PROMOTION_STG'

 

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Main {
  static String url = "jdbc:oracle:thin:@ldap://p01imap.na.bestbuy.com:3060/d01frapt01,cn=OracleContext,dc=world";
  static String username = "FDM_H02APTK_BAT01";
  static String password = "h02aptkfr3sa23512";
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(url, username, password);
    CallableStatement stmt = conn.prepareCall("{?= call UTIL_PRCS_PKG.GET_BATCH('OMT_PROMOTION_STG')}");
    stmt.registerOutParameter(1, java.sql.Types.VARCHAR);
 String batchID = callableStatement.getString(2);
    stmt.execute();
System.out.println("BatchID : " + batchID);
    conn.close();
  }
}
Please help me in executing the above java code for SQL Store_Procedure.

Outcomes