Create and publish JSON via PLSQL
BUILD JSON
PROCEDURE build_Item_Out_Json(p_batch_id IN VARCHAR2,
x_json_resp OUT CLOB)
AS
--cur_item_out SYS_REFCURSOR;
v_row_count NUMBER := 0;
BEGIN
fnd_file.put_line(fnd_file.output, 'In build_Item_Out_Json proc');
BEGIN
-- check to see if any error records exist
SELECT COUNT(*)
INTO v_row_count
FROM xx_item
WHERE batch_id = p_batch_id
AND process_status = 'E'
AND ROWNUM = 1;
EXCEPTION
WHEN no_data_found THEN
v_row_count:=0;
WHEN OTHERS THEN
v_row_count:=0;
END;
-- Open and write the data array if errors exist
IF v_row_count > 0 THEN
SELECT JSON_OBJECT(
'ItemOutJson' VALUE
JSON_ARRAYAGG(
JSON_OBJECT(
'BATCH_ID' VALUE batch_id,
'LINE_NUMBER' VALUE line_number,
'ITEM_NUMBER' VALUE item_number,
'STATUS_MSG' VALUE status_msg
NULL ON NULL -- Forces missing values to output as literal null
)
)
)
INTO x_json_resp
FROM xx_item
WHERE batch_id = p_batch_id
AND process_status = 'E';
ELSE
-- Create an object for the custom status message
-- Initialize and build the JSON structure
APEX_JSON.initialize_clob_output;
APEX_JSON.open_object;
apex_json.open_object('ItemOutJson');
apex_json.write('status', 'No errors found');
apex_json.close_object;
-- Retrieve the payload and free memory
x_json_resp := APEX_JSON.get_clob_output;
APEX_JSON.free_output;
fnd_file.put_line(fnd_file.output, 'No rows found with status E');
END IF;
fnd_file.put_line(fnd_file.output, 'x_json_resp-' || x_json_resp);
-- Explicitly close the system cursor
-- IF cur_item_out%isopen THEN
-- CLOSE cur_item_out;
fnd_file.put_line(fnd_file.log, 'Exiting from build JSON PROC');
EXCEPTION
WHEN others THEN
-- Ensure resources are freed if an unexpected error occurs
apex_json.free_output;
-- Explicitly close the system cursor
-- IF cur_item_out%isopen THEN
-- CLOSE cur_item_out;
-- END IF;
fnd_file.put_line(fnd_file.output, 'Exception in build Json proc-' || SQLERRM);
--raise; --recheck
END build_item_out_json;
------==========---------
PUBLISH JSON
PROCEDURE Publish__Item_Out_Json(
x_json_resp IN CLOB,
p_batch_id IN VARCHAR2,
x_response_body OUT CLOB
) AS
utl_req UTL_HTTP.req;
utl_res UTL_HTTP.resp;
req_length BINARY_INTEGER;
response_body CLOB;
buffer VARCHAR2(32767);
l_instance VARCHAR2(10) := NULL;
l_user_name VARCHAR2(50) := NULL;
l_password VARCHAR2(500):= NULL;
l_user_pwd VARCHAR2(500):= NULL;
url VARCHAR2(4000) := NULL;
l_wallet_path VARCHAR2(240) := NULL;
l_err_code VARCHAR2(100);
l_err_msg VARCHAR2(2000);
-- Variables for CLOB streaming
v_offset PLS_INTEGER := 1;
v_amount PLS_INTEGER := 16000;
v_clob_len PLS_INTEGER;
v_buffer VARCHAR2(16000);
-- Retry Variables
c_max_retries CONSTANT PLS_INTEGER := 2;
c_retry_delay CONSTANT PLS_INTEGER := 3; -- seconds
v_retry_count PLS_INTEGER := 0;
v_success BOOLEAN := FALSE;
v_res_opened BOOLEAN := FALSE;
BEGIN
fnd_file.put_line(fnd_file.log, 'Inside Publish__Item_Out_Json proc');
SELECT NAME INTO l_instance FROM v$containers;
BEGIN
url:= 'URLlink';
l_user_name:='username';
l_password:='pwd';
fnd_file.put_line(fnd_file.log,'url: ' || url);
EXCEPTION
WHEN OTHERS THEN
url := 'urllink';
l_user_name := NULL;
END;
IF url IS NULL THEN
x_response_body := 'Error: Target URL configuration is missing or inactive.';
fnd_file.put_line(fnd_file.log, x_response_body);
RETURN;
END IF;
-- Fetch wallet path safely
l_wallet_path := fnd_profile.value('FND_DB_WALLET_DIR');
IF l_wallet_path IS NOT NULL THEN
UTL_HTTP.set_wallet('file:' || l_wallet_path, NULL);
END IF;
UTL_HTTP.set_transfer_timeout(180);
-- Loop for execution retry
LOOP
v_retry_count := v_retry_count + 1;
BEGIN
-- Clean/Initialize response buffer per attempt
IF DBMS_LOB.istemporary(response_body) = 1 THEN
DBMS_LOB.freetemporary(response_body);
END IF;
DBMS_LOB.createtemporary(response_body, TRUE);
-- Begin Request
utl_req := UTL_HTTP.begin_request(url, 'POST');
-- Set authentication and headers
UTL_HTTP.set_authentication(utl_req, l_user_name, l_password);
UTL_HTTP.set_header(utl_req, 'content-type', 'application/json');
fnd_file.put_line(fnd_file.log,'authentication success: ' || l_user_name);
req_length := DBMS_LOB.getlength(x_json_resp);
UTL_HTTP.set_header(utl_req, 'Content-Length', req_length);
-- Stream input CLOB to request body
--UTL_HTTP.write_text(utl_req, x_json_resp);
-- SAFE CLOB STREAMING: Assign length to prevent skipping the loop
v_clob_len := req_length;
-- SAFE CLOB STREAMING: Prevents implicit conversions/buffer failures
v_offset := 1;
WHILE v_offset <= v_clob_len LOOP
DBMS_LOB.read(x_json_resp, v_amount, v_offset, v_buffer);
UTL_HTTP.write_text(utl_req, v_buffer);
v_offset := v_offset + v_amount;
END LOOP;
fnd_file.put_line(fnd_file.log,'v_offset final value: ' || v_offset);
-- Get response
utl_res := UTL_HTTP.get_response(utl_req);
v_res_opened := TRUE; -- Mark response as active and safe to close
-- Read response body
BEGIN
LOOP
UTL_HTTP.read_text(utl_res, buffer, 32766);
DBMS_LOB.writeappend(response_body, LENGTH(buffer), buffer);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
NULL; -- Expected end of stream
--UTL_HTTP.end_response(utl_res); dont end keep handle active
END;
-- Evaluate output cleanly even if the server body is empty
IF DBMS_LOB.getlength(response_body) > 0 THEN
x_response_body := SUBSTR(response_body, 1, 1000) || ',' || utl_res.status_code || ',' || utl_res.reason_phrase;
ELSE
x_response_body := 'EMPTY_BODY,' || utl_res.status_code || ',' || utl_res.reason_phrase;
END IF;
--x_response_body := SUBSTR(response_body, 1, 1000) || ',' || utl_res.status_code || ',' || utl_res.reason_phrase;
fnd_file.put_line(fnd_file.log,'Response: ' || x_response_body);
-- Check if call was valid
IF utl_res.status_code = 200 OR UPPER(x_response_body) LIKE '%SUCCESS%' THEN
v_success := TRUE;
UTL_HTTP.end_response(utl_res);
v_res_opened := FALSE;
EXIT; -- Exit retry loop on complete success
ELSIF utl_res.status_code IN (429, 500, 502, 503, 504) THEN
-- Raise error to trigger retry logic block for transient errors
RAISE_APPLICATION_ERROR(-20001, 'Transient HTTP status: ' || utl_res.status_code);
ELSE
-- Permanent client failure (400, 401, 403, 404), do not retry
UTL_HTTP.end_response(utl_res);
v_res_opened := FALSE;
EXIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
l_err_msg := UTL_HTTP.get_detailed_sqlerrm;
fnd_file.put_line(fnd_file.log, 'Attempt ' || v_retry_count || ' failed: ' || l_err_msg);
-- Only call end_response if a response handles stream was actually opened
IF v_res_opened THEN
BEGIN
UTL_HTTP.end_response(utl_res);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;
-- Break loop if threshold is hit
IF v_retry_count >= c_max_retries THEN
x_response_body := NVL(x_response_body, l_err_msg);
RAISE;
END IF;
-- Pause connection thread before running next iteration
DBMS_SESSION.sleep(c_retry_delay);
END;
END LOOP;
-- Update status to table based on final outcome
IF v_success THEN
BEGIN
UPDATE xx_ITEM_TL--
SET BATCH_STATUS = ' _Item_Out_Json: Success'
WHERE batch_id = p_batch_id;
--AND process_status = 'E';
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.output, 'Error in update json status for batch: '||p_batch_id||' to batch table-' || SQLERRM);
END;
ELSE
BEGIN
UPDATE xx_ITEM_TL--
SET BATCH_STATUS = ' _Item_Out_Json: Failed'
WHERE batch_id = p_batch_id;
--AND process_status = 'E';
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.output, 'Error in update json status for batch: '||p_batch_id||' to batch table-' || SQLERRM);
END;
END IF;
-- Free temporary CLOB resources
IF DBMS_LOB.istemporary(response_body) = 1 THEN
DBMS_LOB.freetemporary(response_body);
END IF;
EXCEPTION
WHEN OTHERS THEN
-- Final clean up on total system failure
IF DBMS_LOB.istemporary(response_body) = 1 THEN
DBMS_LOB.freetemporary(response_body);
END IF;
l_err_code := SQLCODE;
l_err_msg := UTL_HTTP.get_detailed_sqlerrm;
x_response_body := NVL(x_response_body, l_err_msg);
fnd_file.put_line(fnd_file.output, 'Exception in publish Json proc-' || SQLERRM);
END Publish__Item_Out_Json;
Comments
Post a Comment