Home » SQL & PL/SQL » SQL & PL/SQL » issue in inserting pdf file in a table (oracle 12c, windows)
issue in inserting pdf file in a table [message #681383] Tue, 14 July 2020 22:51 Go to next message
form_user1
Messages: 18
Registered: July 2011
Location: chennai
Junior Member
HI guys,
I have procedure P1 which has table T1. Where i will store a pdf file from front end screen by embedded the pdf string in a xml file. After importing the pdf string will store in the table T1. The stored table T1 column app_signature datatype is clob. Then procedure P1 will be called here i trying to fetch that pdf file and store in another table T2 in column image_data which has column datatype blob.

select app_signature into ls_app_signature from T1;
insert into T2(image_data) values(clob_to_blob(ls_app_signature));
The issue is when ls_app_signature length is <32k means its inserting. My real time file length is 22650. If ls_app_signature length >32k means its not inserting, real time file length is 58750. Another issue is i can able to find the length of ls_app_signature variable if length <32k inside the procedure using
dbms_lob.getlength(ls_app_signature)
. Where i can't find the length when length greater than >32k inside the procedure. But ... but.. i can able to store in anonymous block.

declare
ls_app_signature clob;
begin
   select app_signature into ls_app_signature from T1;
   insert into T2(image_data) values(clob_to_blob(ls_app_signature)); -------ls_app_signature >32k---inserting successfully!!!
   dbms_output.put_line(dbms_lob.getlength(ls_app_signature));------------58750
end; 
Why it is not inserting inside the procedure and why can't find length greater 32K.

[Updated on: Tue, 14 July 2020 23:22]

Report message to a moderator

Re: issue in inserting pdf file in a table [message #681384 is a reply to message #681383] Tue, 14 July 2020 23:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post the code of your function, the problem is there.

Note that storing a PDF in a CLOB is an error, it just screws the original data.

Re: issue in inserting pdf file in a table [message #681385 is a reply to message #681384] Wed, 15 July 2020 00:06 Go to previous messageGo to next message
form_user1
Messages: 18
Registered: July 2011
Location: chennai
Junior Member
CREATE OR REPLACE FUNCTION clob_to_blob(ls_clob CLOB) RETURN BLOB IS
	pos          NUMBER := 1;
	ls_read      NUMBER;
	ls_line      NUMBER;
	ls_clob_size NUMBER;
	buff_size    VARCHAR2(32767);
	ls_blob      BLOB;
	db_buffer    RAW(32767);
BEGIN
	BEGIN
		dbms_lob.createtemporary(ls_blob, TRUE);
		ls_line      := greatest(65,
					 instr(ls_clob, chr(10)),
					 instr(ls_clob, chr(13)));
		ls_read      := floor(32767 / ls_line) * ls_line;
		ls_clob_size := dbms_lob.getlength(ls_clob);
		WHILE (pos < ls_clob_size) LOOP
			dbms_lob.read(ls_clob, ls_read, pos, buff_size);
			db_buffer := utl_encode.base64_decode(utl_raw.cast_to_raw(buff_size));
			dbms_lob.writeappend(ls_blob,
					     utl_raw.length(db_buffer),
					     db_buffer);
			pos := pos + ls_read;
		END LOOP;
	EXCEPTION
		WHEN OTHERS THEN
			NULL;
	END;
	RETURN ls_blob;
END;
are you saying in my first table T1 app_signature datatype should not be clob?

[Updated on: Wed, 15 July 2020 00:10]

Report message to a moderator

Re: issue in inserting pdf file in a table [message #681387 is a reply to message #681385] Wed, 15 July 2020 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
EXCEPTION
		WHEN OTHERS THEN
			NULL;

This is the main bug; you MUST absolutely remove this and re-execute.
Note that "utl_encode.base64_decode(utl_raw.cast_to_raw(buff_size))" may have a length greater than "buff_size" itself and so may exceed 32767, something you will see if your remove the WHEN OTHERS.
Example:
SQL> select length('michel'), length(utl_encode.base64_decode(utl_raw.cast_to_raw('michel'))) from dual;
LENGTH('MICHEL') LENGTH(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW('MICHEL')))
---------------- ---------------------------------------------------------------
               6                                                               8
In addition, the logic of the algorithm is really obscure (to not say absurd):

Quote:
		ls_line      := greatest(65,
					 instr(ls_clob, chr(10)),
					 instr(ls_clob, chr(13)));
		ls_read      := floor(32767 / ls_line) * ls_line;
		ls_clob_size := dbms_lob.getlength(ls_clob);
		WHILE (pos < ls_clob_size) LOOP
...
			pos := pos + ls_read;
		END LOOP;
Why this "ls_read"? Why searching the end of the first line? And maybe even ignore it if it is before the 65th character?

Re: issue in inserting pdf file in a table [message #681388 is a reply to message #681385] Wed, 15 July 2020 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
are you saying in my first table T1 app_signature datatype should not be clob?
Correct! It should be a BLOB as a PDF file a binary file (but for the very simple first version of PDF format). Just open such a file with notepad (or use TYPE or cat OS command).

Re: issue in inserting pdf file in a table [message #681390 is a reply to message #681388] Wed, 15 July 2020 01:31 Go to previous messageGo to next message
form_user1
Messages: 18
Registered: July 2011
Location: chennai
Junior Member
Actually we are inserting not only pdf. we insert .tif and .jpeg also....
Re: issue in inserting pdf file in a table [message #681391 is a reply to message #681390] Wed, 15 July 2020 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Which are all binary files.
Did you remove WHEN OTHERS and retried?

Re: issue in inserting pdf file in a table [message #681393 is a reply to message #681391] Wed, 15 July 2020 02:34 Go to previous messageGo to next message
form_user1
Messages: 18
Registered: July 2011
Location: chennai
Junior Member
I removed when other then exception from function, still can't insert.

[Updated on: Wed, 15 July 2020 02:35]

Report message to a moderator

Re: issue in inserting pdf file in a table [message #681395 is a reply to message #681393] Wed, 15 July 2020 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You should then have an exception.

Re: issue in inserting pdf file in a table [message #681400 is a reply to message #681395] Wed, 15 July 2020 06:09 Go to previous messageGo to next message
form_user1
Messages: 18
Registered: July 2011
Location: chennai
Junior Member
Is function has issue then in anonymous block it won't insert? but its inserting >32k in the table.
Re: issue in inserting pdf file in a table [message #681401 is a reply to message #681400] Wed, 15 July 2020 06:14 Go to previous message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Prove your claim.
2/ Post a test case that reproduces your problem and we can execute to get the same thing.

Previous Topic: insert rows
Next Topic: REGEXP_LIKE to match string in a list
Goto Forum:
  


Current Time: Mon Sep 28 14:37:47 CDT 2020