How to call a function defined in a PL/SQL package with SQLAlchemy?

If you create a plain old function like this:

CREATE OR REPLACE FUNCTION foo(p_bar_i IN VARCHAR2) RETURNS VARCHAR2
IS
BEGIN
    RETURN p_bar_i || 'baz';
END;
/

You can call it in sqlalchemy easily:

q = session.query(func.foo(MyTable.hello))
# or
sel = select([func.foo(my_table.c.hello)])

How can you call a function defined inside a package from SQLAlchemy, like the following:

CREATE PACKAGE master
IS
    FUNCTION foo(p_bar_i IN VARCHAR2) RETURNS VARCHAR2;
END;
/
CREATE PACKAGE BODY master
IS
    FUNCTION foo(p_bar_i IN VARCHAR2) RETURNS VARCHAR
    IS
    BEGIN
        RETURN p_bar_i || 'baz';
    END;
END;
/

1 answer

  • answered 2017-06-17 18:35 Ravi

    If function is defined inside a package, then you need to specify package name when calling your function.

    package_name.function_name (parameter1, parameter2, ... parameter_n)