I'm trying to create one ORDS REST API in my 19c oracle ATP database, i.e. to accept the JSON input payload in my RESTG request (POST HTTP method) and try to insert, update, delete the header information and lines but the PL/SQL block what I have written is not working in 19c, can someone please help me to fix this case
Getting error in PL.SQL like below:
ERROR at line 2: ORA-06550: line 2, column 13: PLS-00201: identifier 'JSON' must be declared ORA-06550: line 0, column 1:
These are my tables:
-- Creating the PO_HEADER table
CREATE TABLE PO_HEADER (
PO_HEADER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
PO_NUMBER VARCHAR2(50) UNIQUE,
VENDOR_NAME VARCHAR2(100),
ORDER_DATE DATE DEFAULT SYSDATE,
TOTAL_AMOUNT NUMBER(15,2) DEFAULT 0,
STATUS VARCHAR2(20) DEFAULT 'DRAFT',
CREATED_BY VARCHAR2(50),
CREATED_DATE DATE DEFAULT SYSDATE,
UPDATED_BY VARCHAR2(50),
UPDATED_DATE DATE
);
-- Creating the PO_LINES table without NOT NULL constraints
CREATE TABLE PO_LINES (
PO_LINE_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
PO_HEADER_ID NUMBER, -- No FK constraint
LINE_NUMBER NUMBER, -- Sequential line number
ITEM_CODE VARCHAR2(50),
ITEM_DESCRIPTION VARCHAR2(200),
QUANTITY NUMBER(10,2) DEFAULT 1 CHECK (QUANTITY > 0),
UNIT_PRICE NUMBER(10,2) DEFAULT 0 CHECK (UNIT_PRICE >= 0),
LINE_TOTAL NUMBER(15,2) GENERATED ALWAYS AS (QUANTITY * UNIT_PRICE) VIRTUAL,
CREATED_BY VARCHAR2(50),
CREATED_DATE DATE DEFAULT SYSDATE,
UPDATED_BY VARCHAR2(50),
UPDATED_DATE DATE
);
-- Creating indexes for performance
CREATE INDEX IDX_PO_HEADER_DATE ON PO_HEADER (ORDER_DATE);
CREATE INDEX IDX_PO_LINES_HEADER_ID ON PO_LINES (PO_HEADER_ID);
ORDS PL/SQL block is below:
DECLARE
v_json JSON := JSON(:body); -- ORDS passes JSON as CLOB, converted to JSON type
v_poNumber VARCHAR2(50);
v_operation VARCHAR2(10);
v_header_id NUMBER;
BEGIN
-- Extract header-level values
v_poNumber := v_json.poNumber;
v_operation := v_json.operation;
-- Handle Header Operations
IF v_operation = 'add' THEN
INSERT INTO PO_HEADER (PO_NUMBER, VENDOR_NAME, ORDER_DATE, TOTAL_AMOUNT, STATUS, CREATED_BY)
VALUES (
v_json.poNumber,
v_json.vendorName,
TO_DATE(v_json.orderDate, 'YYYY-MM-DD'),
v_json.totalAmount,
v_json.status,
v_json.createdBy
)
RETURNING PO_HEADER_ID INTO v_header_id;
ELSIF v_operation = 'update' THEN
UPDATE PO_HEADER
SET VENDOR_NAME = v_json.vendorName,
ORDER_DATE = TO_DATE(v_json.orderDate, 'YYYY-MM-DD'),
TOTAL_AMOUNT = v_json.totalAmount,
STATUS = v_json.status,
UPDATED_BY = v_json.createdBy,
UPDATED_DATE = SYSDATE
WHERE PO_NUMBER = v_poNumber
RETURNING PO_HEADER_ID INTO v_header_id;
ELSIF v_operation = 'remove' THEN
DELETE FROM PO_HEADER WHERE PO_NUMBER = v_poNumber;
DELETE FROM PO_LINES WHERE PO_HEADER_ID = (SELECT PO_HEADER_ID FROM PO_HEADER WHERE PO_NUMBER = v_poNumber);
RETURN;
END IF;
-- Process PO Lines Using JSON_TABLE with JSON Type
FOR line IN (
SELECT *
FROM JSON_TABLE(v_json.poLines, '$[*]'
COLUMNS (
operation VARCHAR2(10) PATH '$.operation',
lineNumber NUMBER PATH '$.lineNumber',
itemCode VARCHAR2(50) PATH '$.itemCode',
itemDescription VARCHAR2(200) PATH '$.itemDescription',
quantity NUMBER PATH '$.quantity',
unitPrice NUMBER PATH '$.unitPrice',
createdBy VARCHAR2(50) PATH '$.createdBy'
)
)
) LOOP
IF line.operation = 'Add' THEN
INSERT INTO PO_LINES (PO_HEADER_ID, LINE_NUMBER, ITEM_CODE, ITEM_DESCRIPTION, QUANTITY, UNIT_PRICE, CREATED_BY)
VALUES (v_header_id, line.lineNumber, line.itemCode, line.itemDescription, line.quantity, line.unitPrice, line.createdBy);
ELSIF line.operation = 'Update' THEN
UPDATE PO_LINES
SET ITEM_CODE = line.itemCode,
ITEM_DESCRIPTION = line.itemDescription,
QUANTITY = line.quantity,
UNIT_PRICE = line.unitPrice,
UPDATED_BY = line.createdBy,
UPDATED_DATE = SYSDATE
WHERE PO_HEADER_ID = v_header_id AND LINE_NUMBER = line.lineNumber;
ELSIF line.operation = 'Remove' THEN
DELETE FROM PO_LINES WHERE PO_HEADER_ID = v_header_id AND LINE_NUMBER = line.lineNumber;
END IF;
END LOOP;
COMMIT;
END;
My JSON input payload:
{
"poNumber": "PO1004",
"vendorName": "Tech Solutions",
"orderDate": "2025-03-01",
"totalAmount": 800.75,
"status": "APPROVED",
"createdBy": "User3",
"operation": "add",
"poLines": [
{
"operation": "Add",
"lineNumber": 1,
"itemCode": "ITEM005",
"itemDescription": "Keyboard",
"quantity": 4,
"unitPrice": 50.00,
"createdBy": "User3"
},
{
"operation": "Remove",
"lineNumber": 2,
"itemCode": "ITEM006",
"itemDescription": "Mouse Pad",
"quantity": 10,
"unitPrice": 8.50,
"createdBy": "User3"
},
,
{
"operation": "update",
"lineNumber": 2,
"itemCode": "ITEM006",
"itemDescription": "Mouse Pad",
"quantity": 10,
"unitPrice": 8.50,
"createdBy": "User3"
}
]
}
v_json JSON := JSON(:body);
Fails because you are using Oracle 19c and the JSON data type was not introduced until Oracle 21c.
any workaround is there for this ?
Use:
v_json CLOB := :body;
Then use JSON_TABLE, as you do later in the PL/SQL block to parse the JSON string into variables.
DECLARE
v_json CLOB := :body;
v_poNumber PO_HEADER.PO_NUMBER%TYPE;
v_operation VARCHAR2(10);
v_header_id PO_HEADER.PO_HEADER%TYPE;
v_total_amount PO_HEADER.TOTAL_AMOUNT%TYPE;
v_vendor_name PO_HEADER.VENDOR_NAME%TYPE;
v_status PO_HEADER.STATUS%TYPE;
v_created_by PO_HEADER.CREATED_BY%TYPE;
BEGIN
SELECT po_number
-- etc.
INTO v_poNumber
-- etc.
FROM JSON_TABLE(
v_json,
'$'
COLUMNS (
po_number VARCHAR2(50) PATH '$.poNumber'
-- etc.
)
);
Then use the variables in your later INSERT/UPDATE statements.