We've retrieved some sample code from Oracle and AskTom, but we can't seem to read the contents of the CLOB. From the following, we get an error:
Amount BINARY_INTEGER := 32767;
Position INTEGER := 2;
WHERE tk = 16;
DBMS_OUTPUT.PUT_LINE('------------ LOB READ EXAMPLE ------------');
DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer);
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at line 15
Right, last question then. Is there any reason why we should save anything as a CLOB, if we're going to save the file extension and use the appropriate .exe to open the file (even saving .txt files as a BLOB)? There may be files for other pieces of software that are going to come along in the future. My understanding is that the underlying file would be saved unchanged if we use BLOB's, but they are converted in some way to Ascii if saved as a CLOB?
Just trying to keep the code as simple as possible, and saving everything as a BLOB would be just that, the easiest.
You can most certainly store ASCII data in a BLOB field, however BLOBS will be sent to the client without undergoing any required characterset translation. CLOBS on the other hand, will be translated into the client's characterset.