In SQLDeveloper classic, you don't have to enter the “set define off” statement at all.
In VS you have to run both statements together. If you run them separately, it prompts for a substitution variable.
set define off;
SELECT po.po_document FROM j_purchaseorder po
WHERE json_exists(po.po_document,
'$?(@.LineItems.Part.UPCCode == 85391628927 && @.LineItems.Quantity > 3)');
Set up code from Oracle documentation:
CREATE TABLE j_purchaseorder
(id VARCHAR2 (32) NOT NULL PRIMARY KEY,
date_loaded TIMESTAMP (6) WITH TIME ZONE,
po_document JSON);
INSERT INTO j_purchaseorder
VALUES (SYS_GUID(),
to_date('30-DEC-2014'),
'{"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" : false,
"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 } ] }');