mu shaikh
mu shaikh

Reputation: 99

JSON_OBJECT_T stringify CLOB is unable to stringy for huge data

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

Answers (0)

Related Questions