Object Serialization does not handle Type Hierarchies correctly

[markddrake]

See https://www.oracle.com/corporate/security-practices/assurance/vulnerability/reporting.html for how to report security issues.

1. With the async/await programming style, make sure you are using 'await' in the right places.

2. Is it an error or a hang or a crash?

error

3. What error(s) you are seeing?
   Incorrect conversion of PL/SQL types to JSON

4. Include a runnable Node.js script that shows the problem.
   Include all SQL needed to create the database schema. Use Markdown syntax, see https://help.github.com/github/writing-on-github/basic-writing-and-formatting-syntax

Run the following sql script to creates required types, table and rows..

DROP USER T1 cascade
/
GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO T1 identified by oracle
/
ALTER SESSION set CURRENT_SCHEMA = T1
/
create or replace type ADDR_TYPE as OBJECT(
   STREET_LINE_1  VARCHAR2(128),
   STREET_LINE_2  VARCHAR2(128),
   CITY           VARCHAR2(128)
)
NOT FINAL NOT INSTANTIABLE
/
show errors
--
create or replace type US_ADDR_TYPE UNDER ADDR_TYPE (
   STATE VARCHAR2(2),
   ZIPCODE NUMBER(5)
)
NOT FINAL 
/
show errors
--
create or replace type US_ZIP_PLUS4_TYPE UNDER US_ADDR_TYPE (
   PLUS4 NUMBER(4)
)
/
show errors
--
create or replace type UK_ADDR_TYPE UNDER ADDR_TYPE(
   COUNTY VARCHAR2(128),
   POSTCODE VARCHAR2(10)
)
/
show errors
--
create type PERSON_TYPE as OBJECT(
   NAME VARCHAR2(128),
   ADDR ADDR_TYPE
)
/
show errors
--
create table PERSON_TABLE(
   ID NUMBER(4),
   DETAILS PERSON_TYPE
)
/
desc ADDR_TYPE
--
desc US_ADDR_TYPE
--
desc US_ZIP_PLUS4_TYPE
--
desc UK_ADDR_TYPE
--
insert into PERSON_TABLE values (1, PERSON_TYPE('James Bond',UK_ADDR_TYPE('1 ST JAMES PLACE','','LONDON','','SW1 23EF')))
/
insert into PERSON_TABLE values (2, PERSON_TYPE('Fred Flintsone',US_ZIP_PLUS4_TYPE('2 ROCK ST','BOULDERTOWN','ROCK VEGAS','CA',94521,1234)))
/
select * 
  from PERSON_TABLE
/
select ID, JSON_OBJECT(DETAILS)
  from PERSON_TABLE
``

This should create a table with the following rows

SQL> select * from T1.PERSON_TABLE;

        ID DETAILS(NAME, ADDR(STREET_LINE_1, STREET_LINE_2, CITY))
---------- ------------------------------------------------------------------------------------------------------------------------------------
         1 PERSON_TYPE('James Bond', UK_ADDR_TYPE('1 ST JAMES PLACE', NULL, 'LONDON', NULL, 'SW1 23EF'))
         2 PERSON_TYPE('Fred Flintsone', US_ZIP_PLUS4_TYPE('2 ROCK ST', 'BOULDERTOWN', 'ROCK VEGAS', 'CA', 94521, 1234))

SQL>

Now run the following node program

"use strict"
const oracledb = require('oracledb');

async function main() {

  const connectionDetails = {
    user          : "system",
    password      : "oracle",
    connectString : "YDB21903"
  };
  
  const sql3 = 
`select DETAILS from t1.person_table`;
    
  try {
      
    const conn = await oracledb.getConnection(connectionDetails);
	const results = await conn.execute(sql3);
	console.log(results);
	
 } catch(e) {
   console.log('Failed',e);    
 } 
}
main();

The output is

C:\Development\YADAMU>node scratch\oracle\objectTest2.js
{ metaData: [ { name: 'DETAILS' } ],
  rows:
   [ [ [T1.PERSON_TYPE] { NAME: 'James Bond',
         ADDR:
          { STREET_LINE_1: '1 ST JAMES PLACE',
            STREET_LINE_2: null,
            CITY: 'LONDON' } } ],
     [ [T1.PERSON_TYPE] { NAME: 'Fred Flintsone',
         ADDR:
          { STREET_LINE_1: '2 ROCK ST',
            STREET_LINE_2: 'BOULDERTOWN',
            CITY: 'ROCK VEGAS' } } ] ] }

C:\Development\YADAMU>

As you can see it has only output the attributes defined by the base type, it has not output the attributes defined by the sub-types. I would have expected it to output all the information. Another issue is should it also output the 'type' itself, so the program processing the data does not have to try to guess which type or subtype the data is an instance of.

5. Run node and show the output of:

C:\Development\YADAMU\app> node
> process.platform
'win32'
> process.version
'v11.9.0'
> process.arch
'x64'
> require('oracledb').versionString
'4.0.1'
> require('oracledb').oracleClientVersionString
'12.2.0.1.0'
>

6. What is your Oracle Database version?
   18,19.20

[anthony-tuininga]

This is a known limitation. Its on the list of things we intend to correct at some point but I'll let @cjbj comment on priorities!

[anthony-tuininga]

This is related to ODPI-C issue 25.

[fuson]

Guys, its a really huge issue for us. We had to implements non normal type hierarchy but ... like:

BASETYPE
|----> SUBTYPE1
|----> SUBTYPE2

CONTAINER_TYPE (
SUBTYPE1FIELD SUBTYPE1,
SUBTYPE2FIELD SUBTYPE2
) *** and set only one of SUBTYPE1FIELD or SUBTYPE2FIELD.

instead of
CONTAINER_TYPE (
BASETYPEFIELD BASETYPE
)

Its like a hack :(

[cjbj]

Noted.