How To Pass A Python Array To An Oracle Stored Procedure?

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: cu

Solution 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:

defselect_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

CREATEOR REPLACE TYPE LIST_IDS ASTABLEOFINT;

Second you create your package

CREATEOR REPLACE PACKAGE PACKFACE IS
TYPE LISTADO_IDS ISTABLEOFINT INDEX BY PLS_INTEGER;
PROCEDURE P_SELECT_IDBFRIENDS (CONSULTA OUT SYS_REFCURSOR,COD_US ININT,IDS_NOT IN LISTADO_IDS);
END;

And finally create the body of the package

CREATEOR REPLACE PACKAGE BODY PACKFACE ISPROCEDURE P_SELECT_IDBFRIENDS (CONSULTA OUT SYS_REFCURSOR,COD_US ININT, IDS_NOT IN LISTADO_IDS) 
    IS
        num_array LIST_IDS;
    BEGIN
        num_array:=LIST_IDS();
        for i in1 .. IDS_NOT.count
        loop
            num_array.extend(1);
            num_array(i) := IDS_NOT(i);
        end loop; 
        OPEN CONSULTA FORSELECT*FROM T_TABLE WHERE ID IN (SELECT COLUMN_VALUE FROMTABLE(num_array));
    END;
END;

I hope that It helps you.

Solution 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())