dbms_lob.getlength() returns different source and destination lengths
819135Sep 17 2011 — edited Sep 19 2011I am fairly new to PL/SQL so maybe this is an obvious problem but here goes. I am updating a clob field with a text file ~5KB in size. The field updates fine (as far as I can tell). Before I update the field, I open the source file as a bfile and then inquire the length using dbms_lob.getlength(). I then update the clob field using dbms_lob.loadclobfromfile(). This seems to work fine. However, when I use dbms_lob.getlength() on the destination object returned by dbms_lob.loadclobfromfile(), I get a length 3 characters less than then the source object (5072 vs 5075). Both the source and destination offsets are set to 1.
Probing on what documentation I could find, I found this at http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_lob.htm#i998484:
"The length returned for a BFILE includes the EOF, if it exists. Any 0-byte or space filler in the LOB caused by previous ERASE or WRITE operations is also included in the length count. The length of an empty internal LOB is 0."
I did not create the source file and I believe it is a Unix type file (because of the lack of CRs) and I am running on Windows 7. I am also using 11g Express. Could the use of a Unix-type file for a CLOB on a Windows system be causing this character count difference?
Once I found this issue I can work around it. I just want to understand what is going on.
Thanks to all who look at this.