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: 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](http://osdir.com/ml/python.db.cx-
oracle/2005-06/msg00014.html) (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())
    

