Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Groovy: Calling Stored Proc with Type Parameter

Highlighted

Groovy: Calling Stored Proc with Type Parameter

Explorer

Hi All,

I have a oracle stored proc and that proc using the Arry type input parameter

create or replace TYPE "ARR_HP_STU_CERT_USERNAME" as table of varchar2(32767);

PROC Input type

create or replace PROCEDURE         sp_hp_stu_cert_status_chk (
   in_track_id           IN     NUMBER,
   in_username           IN     arr_hp_stu_cert_username,
   in_certification_id   IN     VARCHAR2,
   out_details              OUT SYS_REFCURSOR,
   out_status_code          OUT NUMBER,
   out_status_desc          OUT VARCHAR2)
AS
   v_prc_name        VARCHAR2 (50) := 'sp_hp_stu_cert_status_chk';
   v_count           NUMBER;
   v_user_id         VARCHAR2 (500);
   required_fields   EXCEPTION;
BEGIN

I have written a groovy code to execute that proc but its giving error about casting :

/*import org.apache.commons.io.IOUtils
import org.apache.nifi.controller.ControllerService
import org.apache.nifi.processor.io.StreamCallback */
import java.nio.charset.*
import groovy.sql.OutParameter
import groovy.sql.InParameter
import groovy.sql.Sql
import oracle.jdbc.OracleTypes
import java.sql.ResultSet
//import java.sql.*




def sql = Sql.newInstance('jdbc:oracle:thin:@//XXXXX.itcs.entsvcs.net:1525/XXXX', 'XXXX', 'XXXXX$', 'oracle.jdbc.OracleDriver')
 in_track_id='1798'
 in_certification_id='crtfy000000000003144'




OutParameter CURSOR_PARAMETER = new OutParameter() {
    public int getType() {
        return OracleTypes.CURSOR;
    }
};


InParameter ArrayUserName = new InParameter() {
    public  int  getType() {
        return OracleTypes.ARRAY;
    }
    public Object getValue(){


    }
};




def data = []
 String sqlString ="""{call sp_hp_stu_cert_status_chk(?, ?, ?, ?, ?, ?)}""";
def  status_desc
def  status_code
def  status_data
def testID
def parametersList = [in_track_id, ArrayUserName, in_certification_id, CURSOR_PARAMETER, Sql.NUMERIC ,Sql.VARCHAR];


sql.call(sqlString, parametersList) {out_details, out_status_code,out_status_desc ->
    status_desc = out_status_desc
    status_code = out_status_code
};


Print(status_desc)
Print(status_code)


Can anyone help how I can pass "ARR_HP_STU_CERT_USERNAME" as ARRY input in our stored procedure

Don't have an account?
Coming from Hortonworks? Activate your account here