my Data model : select * from gl_code_combinations
working data model: select * from gl_code_combinations fetch first 100 rows only
I am trying to store this response in table test_response by converting base64 into csv format
DECLARE
l_response XMLTYPE;
l_envelope CLOB;
l_url VARCHAR2(4000) := 'XXXX/xmlpserver/services/v2/ReportService';
l_action VARCHAR2(4000) := 'http://xmlns.oracle.com/oxp/service/v2/runReport';
l_response_text CLOB;
l_report_bytes CLOB;
l_csv CLOB;
BEGIN
l_envelope := '<?xml version="1.0" encoding="UTF-8"?>' ||
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v2="http://xmlns.oracle.com/oxp/service/v2">' ||
'<soapenv:Header/>' ||
'<soapenv:Body>' ||
'<v2:runReport>' ||
'<v2:reportRequest>' ||
'<v2:reportAbsolutePath>/GL_CODE_COMBINATIONS/GL_CODE_COMBINATIONS_Report.xdo</v2:reportAbsolutePath>' ||
'<v2:sizeOfDataChunkDownload>-1</v2:sizeOfDataChunkDownload>' ||
'</v2:reportRequest>' ||
'<v2:userID>wns.erp</v2:userID>' ||
'<v2:password>Oracle@123$</v2:password>' ||
'</v2:runReport>' ||
'</soapenv:Body>' ||
'</soapenv:Envelope>';
l_response := apex_web_service.make_request(
p_url => l_url,
p_action => l_action,
p_envelope => l_envelope,
p_username => 'XXX',
p_password => 'XXXX'
);
l_response_text := l_response.getClobVal;
INSERT INTO TEST_RESPONSE (RESPONSE) VALUES (l_response_text);
SELECT SUBSTR(
l_response_text,
INSTR(l_response_text, '<reportBytes>') + LENGTH('<reportBytes>'),
INSTR(l_response_text, '</reportBytes>') - (INSTR(l_response_text, '<reportBytes>') + LENGTH('<reportBytes>'))
)
INTO l_report_bytes
FROM dual;
INSERT INTO TEST_RESPONSE (RESPONSE) VALUES (l_report_bytes);
SELECT LISTAGG(UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_I18N.STRING_TO_RAW(l_report_bytes, 'AL32UTF8'))), ',')
INTO l_csv
FROM dual;
INSERT INTO TEST_RESPONSE (RESPONSE) VALUES (l_csv);
APEX_DEBUG.MESSAGE('Extracted CSV data stored in TEST_RESPONSE table: ' || l_csv);
APEX_DEBUG.MESSAGE('Full response: ' || l_response_text);
EXCEPTION
WHEN OTHERS THEN
APEX_DEBUG.MESSAGE('Error: ' || SQLERRM);
END;