Passing list to PL/SQL procedure

Question

There was a similar question some time ago. I tried to follow the advice's which results in:

Traceback (most recent call last):
  File "<input>", line 1, in <module>
cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TEST_ARRAY'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

This is what I have done so far.

1. Create a new type

create type list_ids as table of int;

2. Create a test procedure

create or replace procedure test_array(ids in list_ids) is
begin
    for i in 1..ids.count
    loop
        dbms_output.PUT_LINE(ids(i));
    end loop;
end;

3. Running the python script

import cx_Oracle


db = cx_Oracle.connect("my_user", "my_password", "<host>:<port>/<sid>")
cursor = db.cursor()

idl = cursor.arrayvar(cx_Oracle.NUMBER, [1,2,3,4])
cursor.callproc("test_array", parameters=[idl])

Traceback (most recent call last):
  File "<input>", line 1, in <module>
cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TEST_ARRAY'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

So what am I missing?

Answer 1

As noted in the comments above, cursor.arrayvar() is only usable for PL/SQL associative arrays, not for nested tables. For nested tables you will need to use the "objects" API. This example shows how to do so, even though it happens to be used for a PL/SQL associative array as well!

I have also adjusted the documentation of cursor.arrayvar() to make clear it is only usable for PL/SQL associative arrays with contiguous keys.