How to pass a array object to an oracle stored procedure?

Question

how to pass the array object?

Here I have connected the Oracle DB through Python by using the Oracledb library

#DB connection

connection = oracledb.connect(user="hr", password=userpwd,dsn="dbhost.example.com/orclpdb") 

#cursor object
cursor = connection.cursor()

#declare a output variable

 out_values = cursor.var(int)

here i need to pass the pass the values to oracle stored procedure

array_values = ['u','U','G']
  cursor.callproc('schema.pkg.get_prc',[array_values ,9713979, '2', '2', 'N'])

above array_values I need to pass the array object.how to pass the array object?

"errorMessage": "ORA-06550: l: wrong number or types of arguments in call to 'get_prc

below are the stored procedure

 **schema.pkg.get_prc**(pt_enrol_data IN  tb_enrol_matching,
  p_enrol_log_id IN  NUMBER,
  p_columns IN  VARCHAR2,
  p_dynamic_columns IN  VARCHAR2)
  
  CREATE OR REPLACE TYPE schema.tb_enrol_matching
  IS OBJECT
  
  Email  VARCHAR2(4000),
  Forename  VARCHAR2(4000),
  Lastname VARCHAR2(4000),);

how to pass the array object and call the stored procedure i dont know the problem is, I believ. Thanks in advance.`

Answer 1

Your data-type of the column in the database is NOT an array; it is an object. Therefore you do NOT want to pass an array from python; you want to pass an object.

See the "7.12. Changing Bind Data Types using an Input Type Handler" documentation. Which gives the example:

# A standard Python object
class Building:

    def __init__(self, build_id, description, num_floors, date_built):
        self.building_id = build_id
        self.description = description
        self.num_floors = num_floors
        self.date_built = date_built

building = Building(1, "Skyscraper 1", 5, datetime.date(2001, 5, 24))

# Get Python representation of the Oracle user defined type UDT_BUILDING obj_type = con.gettype("UDT_BUILDING")

# convert a Python Building object to the Oracle user defined type
# UDT_BUILDING def building_in_converter(value):
    obj = obj_type.newobject()
    obj.BUILDINGID = value.building_id
    obj.DESCRIPTION = value.description
    obj.NUMFLOORS = value.num_floors
    obj.DATEBUILT = value.date_built
    return obj

def input_type_handler(cursor, value, num_elements):
    if isinstance(value, Building):
        return cursor.var(obj_type, arraysize=num_elements,
                          inconverter=building_in_converter)


# With the input type handler, the bound Python object is converted
# to the required Oracle object before being inserted cur.inputtypehandler = input_type_handler cur.execute("insert into
myTable values (:1, :2)", (1, building))

Change Building to match the structure of TB_ENROL_MATCHING:

class EnrolMatching:
    def __init__(self, email, forename, lastname):
        self.email = email
        self.forename = forename
        self.lastname = lastname


ENROL_MATCHING_TYPE = connection.gettype("TB_ENROL_MATCHING")


def enrol_matching_in_converter(value):
    obj = ENROL_MATCHING_TYPE.newobject()
    obj.EMAIL = value.email
    obj.FORENAME = value.forename
    obj.LASTNAME = value.lastname
    return obj

def input_type_handler(cursor, value, num_elements):
    if isinstance(value, EnrolMatching):
        return cursor.var(obj_type, arraysize=num_elements,
                          inconverter=enrol_matching_in_converter)


cursor.inputtypehandler = input_type_handler
cursor.callproc(
    "schema.pkg.get_prc",
    (EnrolMatching("u", "U", "G"), 9713979, "2", "N"),
)

Note: this is untested as I do not have your tables or procedure to test against but you should get the general idea of how to implement it.

If you wanted to simplify it to the bare minimum code then:

ENROL_MATCHING_TYPE = connection.gettype("TB_ENROL_MATCHING")
enrol_matching = ENROL_MATCHING_TYPE.newobject()
enrol_matching.EMAIL = "u"
enrol_matching.FORENAME = "U"
enrol_matching.LASTNAME = "G"
cursor.callproc("schema.pkg.get_prc", (enrol_matching, 9713979, "2", "N"))

Note 2: Again untested for the same reasons.