Using SQLAlchemy to pass a list of values to an Oracle function

Viewed 1359

This is my custom type:

Create or replace TYPE mytype AS VARRAY(100) OF VARCHAR2(10);

And this is the function:

create or replace function test_array (op_array mytype)
    RETURN VARCHAR2 IS
        str_query CLOB := 'test';
    BEGIN
        for i in 1..op_array.count loop
           str_query := str_query || to_char( op_array(i) ) || ',';
        end loop;

        return str_query;
    END;

If I use SQLAlchemy to call the function like this:

Session.query(func.test_array(['1','2','3'])).all()

I always get this message:

[sqlalchemy.engine.base.Engine] SELECT test_array(:test_array_2) AS test_array_1 FROM DUAL
[sqlalchemy.engine.base.Engine] {'test_array_2': ['1', '2', '3']}
*** DatabaseError: (cx_Oracle.DatabaseError) ORA-01484: arrays can only be bound to PL/SQL statements
 [SQL: 'SELECT test_array(:test_array_2) AS test_array_1 FROM DUAL'] [parameters: {'test_array_2': ['1', '2', '3']}]
1 Answers
Related