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

Question

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']}]

Anthony Tuininga · Accepted Answer · 2016-03-10 01:52:17Z

1

Arrays in cx_Oracle are bound to PL/SQL types as in the following:

type mytype is table of varchar2(10) index by binary_integer;

Binding SQL types such as the one you created was not supported until recently. This will be available in 5.3 when it is released but you can build it yourself now if you'd like. In that case you want code like this:

typeObj = connection.gettype("MYTYPE")
value = typeObj(["1", "2", "3"])

This value you should be able to pass to func.test_array without any difficulty.

answered Mar 10, 2016 at 1:52

Anthony Tuininga

7,2062 gold badges17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Luca Verardi Over a year ago

I didn't find the gettype method. I tried searching in cx_Oracle 5.2.1's source with no success.

Anthony Tuininga Over a year ago

It isn't available in 5.2.1. This is new code that is in the source repository but has not yet been released. The source can be found here: bitbucket.org/anthony_tuininga/cx_oracle/src

Luca Verardi Over a year ago

Thanks. i'll wait 5.3 release of cx_oracle.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related