1 Reply Latest reply on Mar 9, 2018 2:43 PM by thatJeffSmith-Oracle

    Breaking Oracle 12c Release 1 Instance when creating virtual column on JSON_QUERY?

    femangl

      Hello!

       

      I've already asked my question on stackoverflow.com (https://stackoverflow.com/questions/49194527/oracle-12c-error-when-trying-to-create-virtual-column-on-json-query-result ), but I got the advise to also ask it here as well:

       

      I've a problem creating a virtual column on the result of a JSON_QUERY to a JSON field in my table. The funny thing is, I was able to create several such columns, but one make so big troubles, that it makes the Oracle instance unusable.

       

      The JSON is saved in a column called DESCRIPTION in a table called PRODUCT and contains a JSON object that looks like this:

      {
         "id": 1,
         "name": "foo",
         "codes": ["A","B","C"],
         "packages": [
         {
         "number": "1234",
         "size": "small"
         },
         {
         "number": "5678",
         "size": "medium"
         }
         ]
       }
      

       

      It is possible to query the information without any problems. E.g.

      SELECT JSON_QUERY(DESCRIPTION, '$.codes') FROM PRODUCT;
      

       

      returns me ["A", "B", "C"] and the command

       ALTER TABLE PRODUCT ADD (CODES VARCHAR2(500) GENERATED ALWAYS AS (JSON_QUERY(DESCRIPTION, '$.codes' RETURNING VARCHAR2(500))));
      

      creates a column with the correct content.

       

      But now my problem: If I try the same thing with the package numbers, the creation of the virtual column breaks the Oracle instance - do not try this on an instance you care about.

      Querying the data works perfectly fine:

      SELECT JSON_QUERY(DESCRIPTION, '$.packages[*].number' WITH WRAPPER) FROM PRODUCT;
      

      gives me ["1234","5678"]. But trying to create a virtual column:

      ALTER TABLE PRODUCT ADD (PACKAGE_NUMBERS VARCHAR2(500) GENERATED ALWAYS AS (JSON_QUERY(DESCRIPTION, '$.packages[*].number' WITH WRAPPER RETURNING VARCHAR2(500))));
      

       

      returns me no error, everything looks fine, but after this command every SQL-command I send to the database is answered with:

      Error: ORA-00907: missing right parenthesis
      SQLState:  42000
      ErrorCode: 907
      

      and the only thing I can do to resolve this is to throw the instance away and start from scratch.

       

      Anyone has an idea what I'm doing wrong?

       

      Kind regards,

      Felix