Has anyone converted Bruce Armrstrong's code for reading and writing in chunks to an Oracle database to work with APPEON? I have the need to read files larger than 100meg and store it in a database. The average file size is 350meg. I have it working in PB 12.6 & Oracle 12c but it does not work when deployed to APPEON WEB Server. Below is the link to Bruce's code.
http://www.brucearmstrong.org/2008/12/reading-and-writing-blob-in-chunks.html
Thank you in advance,
Ron
--DB Package
CREATE OR REPLACE PACKAGE pkg_blob IS
PROCEDURE NEWB(p_quote_id NUMBER, p_file_no number);
PROCEDURE OPENB(p_quote_id NUMBER, p_file_no number);
FUNCTION READB(p_data out blob) RETURN NUMBER;
FUNCTION WRITEB(p_data IN blob) RETURN NUMBER;
END pkg_blob;
--DB Body
CREATE OR REPLACE PACKAGE BODY pkg_blob IS
src_lob BLOB;
c_amt CONSTANT BINARY_INTEGER := 32765; -- No larger than 32,512 because that's all PB can send
pos number; --INTEGER;
PROCEDURE NEWB(p_quote_id NUMBER, p_file_no number) IS
BEGIN
DELETE FROM quote_files
WHERE qa_quote_id = p_quote_id
and qa_file_number = p_file_no;
INSERT INTO quote_files
(qa_quote_id, qa_file_number, qa_file)
VALUES
(p_quote_id, p_file_no, rawtohex(' '));
COMMIT;
END;
PROCEDURE OPENB(p_quote_id NUMBER, p_file_no number) IS
BEGIN
SELECT qa_file
INTO src_lob
FROM quote_files
WHERE qa_quote_id = p_quote_id
and qa_file_number = p_file_no
FOR UPDATE;
--reset pos whenever we reselect the blob
pos := 1;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
FUNCTION READB(p_data out blob) RETURN NUMBER IS
v_amt integer; --NUMBER;
BEGIN
v_amt := c_amt;
dbms_lob.READ(src_lob, v_amt, pos, p_data);
pos := pos + v_amt;
RETURN v_amt;
EXCEPTION
WHEN no_data_found THEN
RETURN 0;
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END READB;
FUNCTION WRITEB(p_data in blob) RETURN NUMBER IS
v_amt NUMBER;
BEGIN
v_amt := LENGTH(p_data); -- / 2;
DBMS_LOB.WRITE(src_lob, v_amt, pos, p_data);
pos := pos + v_amt;
RETURN v_amt;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END WRITEB;
END pkg_blob;
//Upload File PB Code working with APPEOPN!
int li_file, li_rc
int ll_size
longlong ll_totalsize
blob lb_data
string ls_pathname, ls_filename
longlong il_buflen = 32765
lb_data = Blob ( Space ( il_buflen) )
li_rc = GetFileOpenName ( 'Open File', ls_pathname, ls_filename , '*.*')
IF li_rc =-1 THEN return
li_file = FileOpen ( ls_pathname, StreamMode!, Read!, LockRead! )
SQLCA.newblob( 1, 1 ) // (QA_Quote_id, QA_File_number) Change to vairable data once tested.
SQLCA.openblob( 1, 1 ) // (QA_Quote_id, QA_File_number) Change to vairable data once tested.
ll_size = FileRead ( li_file, lb_data)
DO WHILE ll_size > 0
ll_totalsize += ll_size
li_rc = SQLCA.writeblob( lb_data )
yield()
em_fl.text = String ( ll_totalsize )
ll_size = FileRead ( li_file, lb_data) //, il_buflen )
LOOP
FileClose ( li_file )
commit;
--DB Package
CREATE OR REPLACE PACKAGE pkg_blob IS
PROCEDURE NEWB(p_quote_id NUMBER, p_file_no number);
PROCEDURE OPENB(p_quote_id NUMBER, p_file_no number);
FUNCTION READB(p_data out blob) RETURN NUMBER;
FUNCTION WRITEB(p_data IN blob) RETURN NUMBER;
END pkg_blob;
--DB Body
CREATE OR REPLACE PACKAGE BODY pkg_blob IS
src_lob BLOB;
c_amt CONSTANT BINARY_INTEGER := 32765; -- No larger than 32,512 because that's all PB can send
pos number; --INTEGER;
PROCEDURE NEWB(p_quote_id NUMBER, p_file_no number) IS
BEGIN
DELETE FROM quote_files
WHERE qa_quote_id = p_quote_id
and qa_file_number = p_file_no;
INSERT INTO quote_files
(qa_quote_id, qa_file_number, qa_file)
VALUES
(p_quote_id, p_file_no, rawtohex(' '));
COMMIT;
END;
PROCEDURE OPENB(p_quote_id NUMBER, p_file_no number) IS
BEGIN
SELECT qa_file
INTO src_lob
FROM quote_files
WHERE qa_quote_id = p_quote_id
and qa_file_number = p_file_no
FOR UPDATE;
--reset pos whenever we reselect the blob
pos := 1;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
FUNCTION READB(p_data out blob) RETURN NUMBER IS
v_amt integer; --NUMBER;
BEGIN
v_amt := c_amt;
dbms_lob.READ(src_lob, v_amt, pos, p_data);
pos := pos + v_amt;
RETURN v_amt;
EXCEPTION
WHEN no_data_found THEN
RETURN 0;
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END READB;
FUNCTION WRITEB(p_data in blob) RETURN NUMBER IS
v_amt NUMBER;
BEGIN
v_amt := LENGTH(p_data); -- / 2;
DBMS_LOB.WRITE(src_lob, v_amt, pos, p_data);
pos := pos + v_amt;
RETURN v_amt;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END WRITEB;
END pkg_blob;
//Upload File PB Code working with APPEOPN!
int li_file, li_rc
int ll_size
longlong ll_totalsize
blob lb_data
string ls_pathname, ls_filename
longlong il_buflen = 32765
lb_data = Blob ( Space ( il_buflen) )
li_rc = GetFileOpenName ( 'Open File', ls_pathname, ls_filename , '*.*')
IF li_rc =-1 THEN return
li_file = FileOpen ( ls_pathname, StreamMode!, Read!, LockRead! )
SQLCA.newblob( 1, 1 ) // (QA_Quote_id, QA_File_number) Change to vairable data once tested.
SQLCA.openblob( 1, 1 ) // (QA_Quote_id, QA_File_number) Change to vairable data once tested.
ll_size = FileRead ( li_file, lb_data)
DO WHILE ll_size > 0
ll_totalsize += ll_size
li_rc = SQLCA.writeblob( lb_data )
yield()
em_fl.text = String ( ll_totalsize )
ll_size = FileRead ( li_file, lb_data) //, il_buflen )
LOOP
FileClose ( li_file )
commit;