pythonstored-procedurescx-oracle

Passing null to an oracle stored proc from python on a user defined type

I am trying to use an Oracle stored proc from python and have trouble with user defined types.

Here is a minimal example to reproduce the issue

create or replace
TYPE  test_type AS TABLE OF VARCHAR2(4000);
/

create or replace procedure python_test (
 a in number,
 b in test_type) as
begin
  null;
end;
/

I actually am not looking to pass anything as test_type from python but simply not set it to anything (which is allowed by real life stored proc).

In SQL, the following works:

execute python_test(1,null);

However, from python with cx_Oracle, the following command

cursor.callproc('python_test', [1, None])

gives me the following error:

PLS-00306: wrong number or types of arguments in call to 'PYTHON_TEST'

Is there a way to make this work?

Solution

  • Yes. If you simply use the default processing, None is treated as a string, which isn't what is expected here. Instead, do something like the following:

    import oracledb

conn = oracledb.connect("user/pw@host/service")
cursor = conn.cursor()

var = cursor.var(conn.gettype("TEST_TYPE"))
cursor.callproc("python_test", [1, var])