Hi All,
I'm trying to convert a large pdf file (incoming value as Clob, convert it to blob, then append the blob and update a table where the target column is a blob.
All seems to be working as expected and the final PDF file when downloaded shows correct number of pages, but there is no content(all pages are blank). I presume somehow during the append / update of the blob it got damaged, but I'm not sure how to deal with this..
For smaller files it works fine (as it is simply inserting one blob at a time, rather than updating the blobs...).
The program, which is an Oracle Integration Cloud interface passes chunks of that file to my PL/SQL procedure(code below) as decoded to 64 base value.
Any ideas what I'm doing wrong, please?
Procedure invoked is update_mpp_file_content from below code. The target table is xxsol_mpp_output (file_content column which is blob type).
Oracle DB version 19c.
Thanks in advance!
Aleks
create or replace type blobs as table of blob;
CREATE OR REPLACE EDITIONABLE PACKAGE BODY "HCM"."EXT004_PAYROLL_GATEWAY_PKG"
as
FUNCTION clob_to_blob (p_data IN CLOB)
RETURN BLOB
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/miscellaneous/clob_to_blob.sql
-- Author : Tim Hall
-- Description : Converts a CLOB to a BLOB.
-- Last Modified: 26/12/2016
-- -----------------------------------------------------------------------------------
AS
l_blob BLOB;
l_dest_offset PLS_INTEGER := 1;
l_src_offset PLS_INTEGER := 1;
l_lang_context PLS_INTEGER := DBMS_LOB.default_lang_ctx;
l_warning PLS_INTEGER := DBMS_LOB.warn_inconvertible_char;
BEGIN
DBMS_LOB.createtemporary(
lob_loc => l_blob,
cache => TRUE);
DBMS_LOB.converttoblob(
dest_lob => l_blob,
src_clob => p_data,
amount => DBMS_LOB.lobmaxsize,
dest_offset => l_dest_offset,
src_offset => l_src_offset,
blob_csid => DBMS_LOB.default_csid,
lang_context => l_lang_context,
warning => l_warning);
RETURN l_blob;
END clob_to_blob;
procedure update_mpp_file_content ( p_tracking_var_1 in varchar2, p_ess_job_id in number, p_file_chunk in clob ) is
v_blob blob;
v_current_blob blob;
-- type blobs as table of blob;
function concat_blobs(parts in blobs) return blob
is
temp blob;
begin
if parts is null or parts.count = 0 then
return null;
end if;
dbms_lob.createtemporary(temp, false, dbms_lob.CALL);
for i in parts.first .. parts.last
loop
dbms_lob.open(temp, dbms_lob.LOB_READWRITE); --- aleks
dbms_lob.append(temp, parts(i));
dbms_lob.close(temp); --- aleks
end loop;
return temp;
end concat_blobs;
begin
v_blob := clob_to_blob(p_file_chunk);
begin
select file_content
into v_current_blob
from xxsol_mpp_output
where TRACKING_VAR_1 = p_tracking_var_1
and orig_request = p_ess_job_id
and file_content is not null
;
v_current_blob := concat_blobs(blobs(v_current_blob,v_blob ));
exception when others then
v_current_blob := v_blob;
end;
update xxsol_mpp_output set file_content = v_current_blob
where TRACKING_VAR_1 = p_tracking_var_1
and orig_request = p_ess_job_id;
commit;
end update_mpp_file_content;
end ext004_payroll_gateway_pkg;
/