Skip to Main Content

SQL Developer for VS Code

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Bug: Syntax Error When Using JSON_TRANSFORM With SET/PREPEND Operation

The following statement works in Oracle Database 23ai but produces a syntax error in the VS Code extension 24.3.1.

with
   j_purchaseorder as (
   select json(
             '{
  "PONumber": 1600,
  "Reference": "ABULL-20140421",
  "Requestor": "Alexis Bull",
  "User": "ABULL",
  "CostCenter": "A50",
  "ShippingInstructions": {
    "name": "Alexis Bull",
    "Address": {
      "street": "200 Sporting Green",
      "city": "South San Francisco",
      "state": "CA",
      "zipCode": 99236,
      "country": "United States of America"
    },
    "Phone": [
      { "type": "Office", "number": "909-555-7307" },
      { "type": "Mobile", "number": "415-555-1234" }
    ]
  },
  "Special Instructions": null,
  "AllowPartialShipment": true,
  "LineItems": [
    {
      "ItemNumber": 1,
      "Part": { "Description": "One Magic Christmas", "UnitPrice": 19.95, "UPCCode": 13131092899 },
      "Quantity": 9.0
    },
    {
      "ItemNumber": 2,
      "Part": { "Description": "Lethal Weapon", "UnitPrice": 19.95, "UPCCode": 85391628927 },
      "Quantity": 5.0
    }
  ]
}') as po_document
   )
select json_transform(
          po_document,
          set '$new' = json('[ "+41527772211", "+41791234578" ]'),
          prepend '$.ShippingInstructions.Phone' = path '$new[*]'
          ignore on missing
          replace on mismatch
          ignore on null
          error on empty
       ) as transformed
  from j_purchaseorder;

Here's the screenshot showing the complete error message:

This post has been answered by Philipp Salvisberg on Apr 15 2025
Jump to Answer
Comments
Post Details
Added on Dec 15 2024
1 comment
110 views