Support Questions
Find answers, ask questions, and share your expertise

Groovy: Calling Stored Proc with Type Parameter

Groovy: Calling Stored Proc with Type Parameter


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)
   v_prc_name        VARCHAR2 (50) := 'sp_hp_stu_cert_status_chk';
   v_count           NUMBER;
   v_user_id         VARCHAR2 (500);
   required_fields   EXCEPTION;

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

import org.apache.nifi.controller.ControllerService
import */
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:@//', 'XXXX', 'XXXXX$', 'oracle.jdbc.OracleDriver')

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];, parametersList) {out_details, out_status_code,out_status_desc ->
    status_desc = out_status_desc
    status_code = out_status_code


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