Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Object Serialization does not handle Type Hierarchies correctly #1224

Open
markddrake opened this issue Mar 5, 2020 · 4 comments
Open

Object Serialization does not handle Type Hierarchies correctly #1224

markddrake opened this issue Mar 5, 2020 · 4 comments

Comments

@markddrake
Copy link

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

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

  2. 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.

  1. 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'
>
  1. What is your Oracle Database version?
    18,19.20
@anthony-tuininga
Copy link
Member

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
Copy link
Member

This is related to ODPI-C issue 25.

@fuson
Copy link

fuson commented Apr 15, 2021

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
Copy link
Member

cjbj commented Apr 16, 2021

Noted.

@sharadraju sharadraju changed the title Object Serialization does not handle Type Heirarchies correcly Object Serialization does not handle Type Hierarchies correctly Oct 11, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants