Reputation: 99
I'm using the PLSQL procedure to return the data from the table and the ORDS will convert the CLOB to string using stringify function but the stringify is failing if i'm trying to conver huge data like 5000 rows from the table , how to fix this? for small data no issues i'm facing
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(p_po_number => p_po_number,
po_response => v_clob);
l_response := JSON_OBJECT_T.parse(v_clob);
owa_util.mime_header (''application/json'', true);
htp.p(l_response.stringify);
END;'
);
COMMIT;
END;
and my PLSQL procedure below:
create or replace PROCEDURE GET_PO_DETAILS (p_po_number IN VARCHAR2, po_response OUT CLOB) AS
-- Variables to handle the CLOB response
l_po_header_json CLOB;
l_po_lines_json CLOB;
BEGIN
-- Initialize CLOB variables
l_po_header_json := '';
-- CTE to aggregate PO lines into JSON format
WITH po_data AS (
SELECT
h.PO_HEADER_ID,
h.PO_NUMBER,
h.VENDOR_NAME,
h.ORDER_DATE,
h.TOTAL_AMOUNT,
h.STATUS,
h.CREATED_BY,
h.CREATED_DATE,
h.UPDATED_BY,
h.UPDATED_DATE,
-- Aggregating PO lines into JSON array
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 l.CREATED_DATE,
'UPDATED_BY' VALUE l.UPDATED_BY,
'UPDATED_DATE' VALUE l.UPDATED_DATE
)
) AS LINES
FROM PO_HEADER h
LEFT JOIN PO_LINES l ON h.PO_HEADER_ID = l.PO_HEADER_ID
WHERE h.PO_NUMBER = p_po_number
GROUP BY
h.PO_HEADER_ID, h.PO_NUMBER, h.VENDOR_NAME, h.ORDER_DATE,
h.TOTAL_AMOUNT, h.STATUS, h.CREATED_BY, h.CREATED_DATE,
h.UPDATED_BY, h.UPDATED_DATE
)
-- Selecting the final JSON structure including PO header and lines
SELECT JSON_OBJECT(
'PO_HEADER_ID' VALUE PO_HEADER_ID,
'PO_NUMBER' VALUE PO_NUMBER,
'VENDOR_NAME' VALUE VENDOR_NAME,
'ORDER_DATE' VALUE ORDER_DATE,
'TOTAL_AMOUNT' VALUE TOTAL_AMOUNT,
'STATUS' VALUE STATUS,
'CREATED_BY' VALUE CREATED_BY,
'CREATED_DATE' VALUE CREATED_DATE,
'UPDATED_BY' VALUE UPDATED_BY,
'UPDATED_DATE' VALUE UPDATED_DATE,
'LINES' VALUE LINES
) INTO l_po_header_json
FROM po_data;
-- Assigning the final JSON result to the OUT parameter
po_response := l_po_header_json;
EXCEPTION
WHEN OTHERS THEN
-- Handle errors gracefully by returning an error message in the JSON format
po_response := '{"status": "ERROR", "message": "An error occurred while fetching PO details: ' || SQLERRM || '"}';
END GET_PO_DETAILS;
Upvotes: 0
Views: 24