REF operator

[Lurgrid]

Hello,

When I run a query that has the operator REF() I get an error Error: unknown Oracle type number 0.

Exemple

SELECT REF(X) 
FROM INTERVENANT X 
WHERE X.MAT = 1000;

Here is the doc for REF()

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/REF.html

[Lurgrid]

I've just noticed that if a table contains an element of type REF TYPE then I can't display it.

CREATE TYPE MISSION_TYPE AS OBJECT (
    CODE VARCHAR2(64),
    INTITULE VARCHAR2(64),
    NB_JOURS INTEGER,
    INTERVENANT REF INTERVENANT_TYPE
);

CREATE TABLE MISSION
OF MISSION_TYPE (
    CONSTRAINT PK_CODE PRIMARY KEY (CODE)
);

SELECT * FROM MISSION

I get the same error Error: unknown Oracle type number 0.

[cjbj]

What is INTERVENANT_TYPE defined as?

Likely an ODPI-C limitation.

[kubo]

REF data type isn't supported by rust-oracle. It is an ODPI-C limitation as @cjbj mentioned.

Oracle Call Interface (OCI) supports it. Its data type code is SQLT_REF. ODPI-C converts the OCI data type code to Oracle type number in dpiOracleType__convertFromOracle. However SQLT_REF isn't handled in the function and it returns (dpiOracleTypeNum)0. So the error is Error: unknown Oracle type number 0.

[cjbj]

@Lurgrid were you just testing REF, or do you have a business need for support?

[Lurgrid]

Yes it happened during a normal use of my program. But if that's not possible, I can at worst use the sqlplus binary directly and convert its responses.

[cjbj]

I can't predict when our ODPI-C or @kubo's rust-oracle will support the feature, but I have noted your vote for it.