Cannot pass python None-Type to procedure with custom DbObjectType

[M4X1K02]

First things first

• Oracle database version: 19c
• Mode: Thin mode
• python-oracledb version: 1.2.1

Problem description

I'd like to store a python list consisting of strings into a column. If the list is empty, NULL should be stored instead. For this purpose I have created a custom object type like so:
CREATE OR REPLACE TYPE FEATURES_TYPE AS TABLE OF VARCHAR(50);

A simple table:

CREATE TABLE "CARS_TEST" ("ID" NUMBER GENERATED ALWAYS AS IDENTITY, 
  "FEATURES" "FEATURES_TYPE"
 NESTED TABLE "FEATURES" STORE AS "FEATURES_TEST_TAB"

Stored Procedure:

create or replace PROCEDURE add_car_test (
        features_p IN features_type
)
IS
BEGIN
    insert into CARS_TEST(FEATURES) VALUES(features_p);
END add_car_test;

Python code:

# just connecting to the DB
from database_utils import databaseConnection
conn = databaseConnection.getConnection()

features = ["Blaulicht"]
typeObj = conn.gettype("FEATURES_TYPE")
if features:
    featureObj = typeObj.newobject(features)
else:
    featureObj = None
cur = conn.cursor()
cur.execute("BEGIN add_car_test(:1); END;", (featureObj,))

SQL output:

SQL> select * from cars_test;

        ID
----------
FEATURES
--------------------------------------------------------------------------------
         1
FEATURES_TYPE('Blaulicht')

However, when I set features = None, I get the following error:

Traceback (most recent call last):
  File "C:\Users\maxik\Documents\Projects\PythonProjects\MobileAPI\mobile-env\lib\runpy.py", line 196, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "C:\Users\maxik\Documents\Projects\PythonProjects\MobileAPI\mobile-env\lib\runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "C:\Users\maxik\Documents\Projects\PythonProjects\MobileAPI\tests\quickTst.py", line 703, in <module>
    main()
  File "C:\Users\maxik\Documents\Projects\PythonProjects\MobileAPI\tests\quickTst.py", line 699, in main
    testNestedTable()
  File "C:\Users\maxik\Documents\Projects\PythonProjects\MobileAPI\tests\quickTst.py", line 672, in testNestedTable
    cur.execute("BEGIN add_car_test(:1); END;", (featureObj,))
  File "C:\Users\maxik\Documents\Projects\PythonProjects\MobileAPI\mobile-env\lib\site-packages\oracledb\cursor.py", line 378, in execute
    impl.execute(self)
  File "src\oracledb\impl/thin/cursor.pyx", line 133, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src\oracledb\impl/thin/protocol.pyx", line 383, in oracledb.thin_impl.Protocol._process_single_message
  File "src\oracledb\impl/thin/protocol.pyx", line 384, in oracledb.thin_impl.Protocol._process_single_message
  File "src\oracledb\impl/thin/protocol.pyx", line 377, in oracledb.thin_impl.Protocol._process_message
oracledb.exceptions.DatabaseError: ORA-06550: Zeile 1, Spalte 7:
PLS-00306: Falsche Anzahl oder Typen von Argumenten in Aufruf von 'ADD_CAR_TEST' (Translates to: Wrong number of arguments or types while calling 'ADD_CAR_TEST')
ORA-06550: Zeile 1, Spalte 7:
PL/SQL: Statement ignored

Question

What is the correct way to insert NULL into said column, but also passing it as an argument to the procedure?

[anthony-tuininga]

You're missing one line of code:

cur.setinputsizes(typeObj)

This needs to be added just before the call to cur.execute(). The reason is because the value None doesn't contain any type information, so python-oracledb simply treats it as VARCHAR2 -- which is the wrong type. You must tell python-oracledb what type you intend to pass -- then the value None is acceptable. Hope that makes sense!

[M4X1K02]

Wow, thank you very much. This has indeed solved my problem 👍