Oracle type 2016 "INTERVAL YEAR(2) TO MONTH" not supported

[adoutt]

I can do it properly with PL/SQL tool but ues cx_oracle following questions appear
cursor.execute('''select * from (select * from "ABC"."ALL_TYPE_TABLE5" order by dbms_random.random()) where rownum <= 1000''')
cx_Oracle.NotSupportedError: Oracle type 2016 not supported.

create table ALL_TYPE_TABLE5
(
col_1_bd BINARY_DOUBLE,
col_2_bf BINARY_FLOAT,
col_5_char CHAR(255),
col_6_date DATE,
col_7_idts INTERVAL DAY(2) TO SECOND(6),
col_8_iytm INTERVAL YEAR(2) TO MONTH,
col_11_number NUMBER,
col_12_nvarchar NVARCHAR2(255),
col_13_raw RAW(20),
col_14_timestamp TIMESTAMP(6),
col_15_twltz TIMESTAMP(6) WITH LOCAL TIME ZONE,
col_16_twtz TIMESTAMP(6) WITH TIME ZONE,
col_17_varchar VARCHAR2(255),
col_18_nchar NCHAR(20),
col_21_number_p2 NUMBER(5,2),
col_22_number_p4 NUMBER(8,4),
col_23_number_p8 NUMBER(16,8),
col_24_decimal INTEGER,
col_25_decimal_p2 NUMBER(4,2),
col_26_decimal_p4 NUMBER(7,4),
col_27_decimal_p8 NUMBER(10,8),
col_28_float_p4 FLOAT,
col_29_float_p8 FLOAT,
col_30_float_p16 FLOAT
)

[cjbj]

Thanks for taking time to create the report. It is INTERVAL YEAR(2) TO MONTH that fails, as no doubt you know. I'll let @anthony-tuininga comment on the feasibility of support for this.

For reference, you can format code in github issues using https://help.github.com/en/github/writing-on-github/creating-and-highlighting-code-blocks

[anthony-tuininga]

Since Python does not natively support interval year to month data, cx_Oracle would have to implement an object itself. Do you have a need for it? If you do, can you explain what you would like to see included in this new type, other than the number of years and the number of months?

[adoutt]

From my point of view, I am willing to accept this new type to ensure that my code will not fail to read this table due to this type of error

[cjbj]

@adoutt can you write a PL/SQL function that accepts INTERVAL YEAR TO MONTH and returns some kind of string? Then use this in your query and convert that string in Python to whatever you want.

[cjbj]

Python-oracledb supports queries using INTERVAL YEAR TO MONTH, see https://python-oracledb.readthedocs.io/en/latest/api_manual/module.html#oracledb-intervalym-class