Hi,
I have a problem inserting a more than 32k size pdf in a table. I can insert in anonymous block or using plsql developer ide test procedure. But in real time scenario it is not. Let me explain clearly. We have a screen where we upload xml where the pdf string is embedded the string length is 58752(>32k). we have two tables, after import the xml file from front end first insert the uploaded pdf string in table t1 which has a clob column (No idea how they are inserting from front end). After that i fetch that data from that table trying to insert in another table(pdf_data) which has blob column so i used plf_base64_clob_to_blob for conversion. I have no issue when pdf file length <32k in real scenario, its inserting in pdf_data table after calling the function insert_pdf_data. We have procedure there i call the function(insert_pdf_data) to insert the pdf file in pdf_data table. Before calling the function i checked the length of the variable dbms_lob.getlength(as_app_sign) ---> nothing shows(>32k). The weird part is, in anonymous block or test the procedure by passing real parameter value step by step its inserting in pdf_data table. here i can see the length dbms_lob.getlength(as_app_sign) --->58752.
xml:
..
<APP_SIGN>JVBERi0xLjUNCiXi48/TDQoyIDAgb2JqDQo8PC9UeXBlIC9DYXRhbG9nDQovUGFnZXMgMyAwIFIN
Cj4+DQplbmRvYmoNCjEgMCBvYmoNCjw8L1Byb2R1Y2VyIChmQ29kZXIgUERGIHByb2R1Y2VyKQ0K
L0NyZWF0aW9uRGF0ZSAoRDoyMDE5MDgyNjE1MDQ1NiswNiczMCcpDQo+Pg0KZW5kb2JqDQozIDAg
....
<APP_SIGN>
...
function:
CREATE OR REPLACE FUNCTION insert_pdf_data
(
as_fno varchar2(10),
as_a_code IN amc.amc_code%TYPE,
as_app_sign clob,
as_sataus IN VARCHAR2
) RETURN VARCHAR2 IS
--declared variables here
BEGIN
ls_file := as_a_code || '_' || as_fno || '.TIF';
BEGIN
BEGIN
SELECT COUNT(*)
INTO ll_count
FROM pdf_data
WHERE filename = ls_file AND
image_type = 'S' AND
fid = as_fno;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20780,
excep);
ll_count := 0;
END;
IF ll_count >= 1 THEN
RETURN 'Z';
ELSE
IF length(as_app_sign) > 0 THEN
BEGIN
INSERT INTO pdf_data
(filename,
image_type,
amc_code,
fid,
image_date,
image,
editor_id,
xy_cor)
VALUES
(ls_file,
'S',
as_a_code,
as_fno,
SYSDATE,
plf_base64_clob_to_blob(as_app_sign),
'',
'');
IF SQL%ROWCOUNT > 0 THEN
RETURN 'Y';
ELSE
RETURN 'C';
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20781,
excep);
RETURN 'D';
END;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20782,
excep);
RETURN 'E';
END;
RETURN 'F';
END insert_pdf_data;
When inserting and when not inserting
In my procedure
`procedure p1(v1,v2,v3)
...
select image_data into as_app_sign from t1 where col1=...;
dbms_output.put_line(dbms_lob.getlength(as_app_sign));----->nothing
ret_var:=insert_pdf_data(as_app_sign);--Not inserting
...
In anonymous block
`declare
begin
select image_data into as_app_sign from t1 where col1=...;
dbms_output.put_line(dbms_lob.getlength(as_app_sign));----->58752
ret_var:=insert_pdf_data(as_app_sign);--inserting in pdf_data
end;`
In test procedure in plsql dev ide
`procedure p1(v1,v2,v3)
..
select image_data into as_app_sign from t1 where col1=...;
dbms_output.put_line(dbms_lob.getlength(as_app_sign));----->58752
ret_var:=insert_pdf_data(as_app_sign);`--inserting in pdf_data
..
dbms_lob.getlength(as_app_sign)