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

Question

I have a problem. When I pass a Python array:

self.notPermited = [2,3]

This is my procedure

def select_ids_entre_amistades(self,cod_us,ids_not):
    lista = []
    try:
        cursor = self.__cursor.var(cx_Oracle.CURSOR)
        print ids_not
        data = self.__cursor.arrayvar(cx_Oracle.NUMBER, ids_not)
        print data
        l_query = self.__cursor.callproc("SCHEMA.PROC_SELECT_IDS_ENT_AMISTADES", [cursor,cod_us,data])
        lista = l_query[0]
        return lista
    except cx_Oracle.DatabaseError as ex:
        error, = ex.args
        print(error.message)
        return lista

The problem is when I call that procedure using this:

self.select_ids_entre_amistades(int_id,self.notPermited)

I visualize in the console the following message:

PLS-00306: wrong number or types of arguments in call to 'PROC

In the database I create the array object like this:

CREATE TYPE SCHEMA.ARRAY_ID_FRIENDS AS TABLE OF INT;

The Oracle stored procedure starts like this:

CREATE OR REPLACE PROCEDURE FACEBOOK.PROC_SELECT_IDS_ENT_AMISTADES
(CONSULTA OUT SYS_REFCURSOR,COD_US IN INT, IDS_FRIEND IN SCHEMA.ARRAY_ID_FRIENDS)

I don't know what the problem is, I believe cx_Oracle.NUMBER is not integer but there aren't other numeric type. Thanks in advance.

Answer 1

Try to use a plsql array in the parameters of the procedure and after that you pass the content of a sql array. The last one will be used to the sql statement into the procedure. It solve my trouble using oracle database 11g because in 12g you don't need to pass the content to an sql array. This could be the code:

def select_ids_entre_amistades(self,cod_us,ids_not):
    lista = []
    try:
        cursor = self.__cursor.var(cx_Oracle.CURSOR)
        varray = self.__cursor.arrayvar(cx_Oracle.NUMBER,ids_not)
        l_query = self.__cursor.callproc("PACKFACE.P_SELECT_IDBFRIENDS", [cursor, cod_us, varray])
        lista = l_query[0]
        return lista
    except cx_Oracle.DatabaseError as ex:
        error, = ex.args
        self.guardar_errores('dato ' + str(error.message))
        return lista

And the stored procedure like this: First you create a type

CREATE OR REPLACE TYPE LIST_IDS AS TABLE OF INT;

Second you create your package

CREATE OR REPLACE PACKAGE PACKFACE IS
TYPE LISTADO_IDS IS TABLE OF INT INDEX BY PLS_INTEGER;
PROCEDURE P_SELECT_IDBFRIENDS (CONSULTA OUT SYS_REFCURSOR,COD_US IN INT,IDS_NOT IN LISTADO_IDS);
END;

And finally create the body of the package

CREATE OR REPLACE PACKAGE BODY PACKFACE IS
    PROCEDURE P_SELECT_IDBFRIENDS (CONSULTA OUT SYS_REFCURSOR,COD_US IN INT, IDS_NOT IN LISTADO_IDS) 
    IS
        num_array LIST_IDS;
    BEGIN
        num_array:=LIST_IDS();
        for i in 1 .. IDS_NOT.count
        loop
            num_array.extend(1);
            num_array(i) := IDS_NOT(i);
        end loop; 
        OPEN CONSULTA FOR 
        SELECT * FROM T_TABLE WHERE ID IN (SELECT COLUMN_VALUE FROM TABLE(num_array));
    END;
END;

I hope that It helps you.

Answer 2

When you look at the cx_Oracle documentation, it says you can create the arrays like this;

 Cursor.arrayvar(dataType, value[, size])

Create an array variable associated with the cursor of the given type and size and return a variable object (Variable Objects). The value is either an integer specifying the number of elements to allocate or it is a list and the number of elements allocated is drawn from the size of the list. If the value is a list, the variable is also set with the contents of the list. If the size is not specified and the type is a string or binary, 4000 bytes (maximum allowable by Oracle) is allocated. This is needed for passing arrays to PL/SQL (in cases where the list might be empty and the type cannot be determined automatically) or returning arrays from PL/SQL.

You may pass your arrays as long as array types are compatible with your PL/SQL procedure's parameter. Here is a simple example to create an array.

>>> myarray=cursor.arrayvar(cx_Oracle.NUMBER,range(0,10))
>>> myarray
<cx_Oracle.NUMBER with value [0.0, 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0]>

Here is a link (belongs to 2005 seems outdated, not sure) showing how to create arrays in PL/SQL side.

EDIT:

I added a complete example below showing how to pass arrayvar and other variable types. I tested the code with Oracle 10g and Python 2.7. I hope this helps.

from __future__ import print_function
import cx_Oracle as cxo

conn = cxo.connect("<YOUR TNS STRING>")

cursor = conn.cursor()
ref_cursor = cursor.var(cxo.CURSOR)
cod_us = cursor.var(cxo.NUMBER, 10)
ids_friend = cursor.arrayvar(cxo.NUMBER, range(0, 10))
ids_friend_sum = cursor.var(cxo.NUMBER)
cursor.execute('''
DECLARE

TYPE REF_CURSOR IS REF CURSOR;
TYPE ARRAY_ID_FRIENDS IS TABLE OF INT INDEX BY BINARY_INTEGER;

    FUNCTION test(CONSULTA OUT REF_CURSOR,
                   COD_US IN INT,
                   IDS_FRIEND IN ARRAY_ID_FRIENDS) RETURN NUMBER
    IS
        sum_ NUMBER:=0;
    BEGIN
        OPEN CONSULTA FOR SELECT 1 FROM DUAL UNION SELECT 2 FROM DUAL;

        FOR i in IDS_FRIEND.FIRST..IDS_FRIEND.LAST LOOP
            sum_:=sum_+IDS_FRIEND(i);
        END LOOP;
        RETURN sum_;
    END;

BEGIN
    :ids_friend_sum:=test(:ref_cursor,:cod_us,:ids_friend);
END;


''', {"ref_cursor": ref_cursor, "cod_us": cod_us, "ids_friend": ids_friend,
      "ids_friend_sum": ids_friend_sum})

print("ref cursor=", end=" ")
for rec in ref_cursor.getvalue():
    print(rec, end="\t")
print("\nids_friend_sum=", ids_friend_sum.getvalue())