I'm making use of below PLSQL procedure to return the CLOB data to my ORDS handler (ATP 21c database) but when the huge data (like more than 70 rows of header and lines) is returned then I'm getting error as below. ( NOTE : This is working fine for less data )
ERROR as below:
The request could not be processed because an error occurred whilst attempting to evaluate a SQL statement associated with this resource. Verify that the URI and payload are correctly specified for the requested operation. If the issue persists then please contact the author of the resource. SQL Error Code: 6502, Error Message: ORA-06502: PL/SQL: value or conversion error ORA-06512: at line 9 https://docs.oracle.com/error-help/db/ora-06502/
My PLSQL procedure:
CREATE OR REPLACE PROCEDURE GET_PO_DETAILS(po_response OUT CLOB) AS
l_po_header_json CLOB;
BEGIN
-- Create temporary LOBs
DBMS_LOB.CREATETEMPORARY(po_response, TRUE);
DBMS_LOB.CREATETEMPORARY(l_po_header_json, TRUE);
-- Main query: Aggregate headers and lines into a JSON array
SELECT JSON_ARRAYAGG( -- Outer JSON_ARRAYAGG returns entire CLOB
JSON_OBJECT(
'PO_HEADER_ID' VALUE h.PO_HEADER_ID,
'PO_NUMBER' VALUE h.PO_NUMBER,
'VENDOR_NAME' VALUE h.VENDOR_NAME,
'ORDER_DATE' VALUE TO_CHAR(h.ORDER_DATE, 'YYYY-MM-DD'),
'TOTAL_AMOUNT' VALUE h.TOTAL_AMOUNT,
'STATUS' VALUE h.STATUS,
'CREATED_BY' VALUE h.CREATED_BY,
'CREATED_DATE' VALUE TO_CHAR(h.CREATED_DATE, 'YYYY-MM-DD'),
'UPDATED_BY' VALUE h.UPDATED_BY,
'UPDATED_DATE' VALUE TO_CHAR(h.UPDATED_DATE, 'YYYY-MM-DD'),
'LINES' VALUE (
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'PO_LINE_ID' VALUE l.PO_LINE_ID,
'LINE_NUMBER' VALUE l.LINE_NUMBER,
'ITEM_CODE' VALUE l.ITEM_CODE,
'ITEM_DESCRIPTION' VALUE l.ITEM_DESCRIPTION,
'QUANTITY' VALUE l.QUANTITY,
'UNIT_PRICE' VALUE l.UNIT_PRICE,
'LINE_TOTAL' VALUE l.LINE_TOTAL,
'CREATED_BY' VALUE l.CREATED_BY,
'CREATED_DATE' VALUE TO_CHAR(l.CREATED_DATE, 'YYYY-MM-DD'),
'UPDATED_BY' VALUE l.UPDATED_BY,
'UPDATED_DATE' VALUE TO_CHAR(l.UPDATED_DATE, 'YYYY-MM-DD')
)
)
FROM PO_LINES l
WHERE l.PO_HEADER_ID = h.PO_HEADER_ID
)
)
RETURNING CLOB
)
INTO l_po_header_json
FROM PO_HEADER h;
-- Assign final result
DBMS_LOB.APPEND(po_response, l_po_header_json);
EXCEPTION
WHEN OTHERS THEN
DBMS_LOB.CREATETEMPORARY(po_response, TRUE);
DBMS_LOB.WRITE(po_response, LENGTH(SQLERRM) + 100, 1,
'{"status": "ERROR", "message": "Error while fetching PO details: ' || SQLERRM || '"}'
);
END GET_PO_DETAILS;
My ORDS Get template handler block as below:
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'ADMIN',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'admin',
p_auto_rest_auth => FALSE);
ORDS.DEFINE_MODULE(
p_module_name => 'bl',
p_base_path => '/bl/',
p_items_per_page => 25,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'bl',
p_pattern => 'getData',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'bl',
p_pattern => 'getData',
p_method => 'GET',
p_source_type => 'plsql/block',
p_items_per_page => 25,
p_mimes_allowed => '',
p_comments => NULL,
p_source =>
'declare
p_po_number VARCHAR2(100) := ''PO1005'';
-- A CLOB variable that holds the JSON content
v_clob CLOB;
-- JSON object to hold the parsed JSON
l_response JSON_OBJECT_T;
begin
GET_PO_DETAILS(po_response => v_clob);
--l_response := JSON_OBJECT_T.parse(v_clob);
owa_util.mime_header (''application/json'', true);
htp.p(v_clob);
END;'
);
COMMIT;
END;
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
);
You have several issues:
QUANTITY has the data-type NUMBER(10,2) (which leads to the question, is 0.01 a valid quantity? Or should the column have the data-type NUMBER(8,0)?) and UNIT_PRICE has the data-type NUMBER(10,2) but LINE_TOTAL is a generated column defined as QUANTITY*UNIT_PRICE but it only has the scale and precision NUMBER(15,2). If you have the maximum allowed quantity and price (99999999.99) then that does not fit in the LINE_TOTAL data-type. You need to change the table to LINE_TOTAL NUMBER(20,4).RETURNING CLOB everywhere where the JSON could be larger than 4000 bytes.Like this:
SELECT JSON_ARRAYAGG( -- Outer JSON_ARRAYAGG returns entire CLOB
JSON_OBJECT(
'PO_HEADER_ID' VALUE h.PO_HEADER_ID,
'PO_NUMBER' VALUE h.PO_NUMBER,
'VENDOR_NAME' VALUE h.VENDOR_NAME,
'ORDER_DATE' VALUE TO_CHAR(h.ORDER_DATE, 'YYYY-MM-DD'),
'TOTAL_AMOUNT' VALUE h.TOTAL_AMOUNT,
'STATUS' VALUE h.STATUS,
'CREATED_BY' VALUE h.CREATED_BY,
'CREATED_DATE' VALUE TO_CHAR(h.CREATED_DATE, 'YYYY-MM-DD'),
'UPDATED_BY' VALUE h.UPDATED_BY,
'UPDATED_DATE' VALUE TO_CHAR(h.UPDATED_DATE, 'YYYY-MM-DD'),
'LINES' VALUE (
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'PO_LINE_ID' VALUE l.PO_LINE_ID,
'LINE_NUMBER' VALUE l.LINE_NUMBER,
'ITEM_CODE' VALUE l.ITEM_CODE,
'ITEM_DESCRIPTION' VALUE l.ITEM_DESCRIPTION,
'QUANTITY' VALUE l.QUANTITY,
'UNIT_PRICE' VALUE l.UNIT_PRICE,
'LINE_TOTAL' VALUE l.LINE_TOTAL,
'CREATED_BY' VALUE l.CREATED_BY,
'CREATED_DATE' VALUE TO_CHAR(l.CREATED_DATE, 'YYYY-MM-DD'),
'UPDATED_BY' VALUE l.UPDATED_BY,
'UPDATED_DATE' VALUE TO_CHAR(l.UPDATED_DATE, 'YYYY-MM-DD')
)
RETURNING CLOB
)
FROM PO_LINES l
WHERE l.PO_HEADER_ID = h.PO_HEADER_ID
)
RETURNING CLOB
)
RETURNING CLOB
)
INTO l_po_header_json
FROM PO_HEADER h;
Note: I do not think the JSON_OBJECT for PO_LINES can reach 4000 bytes so it does not need RETURNING CLOB but its aggregation and PO_HEADER objects can.