CODEWITHSUNDEEP
oracle-databasetoadclob
Raxak
Raxak

Reputation: 399

Reading data from LONG data column in oracle gives Replacement Characters ���

I am trying to read data from a LONG column in Oracle which is storing an Image file. When doing so I am getting following : IL 39 321 30580� 3� 10 0 59�� ����\0\u0010JFIF\0\u0001\u0001\0\0\0\0\0\0\0��\0C\0\b\u0006\u0006\

I think the question marks are not allowing me to decode the string. What is the meaning of these �� ?

Thanks,

Upvotes: 0

Views: 1051

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59558

In general you should not use LONG data type at all. It is deprecated for ages.

Apart from that, data type LONG is used for text data, i.e. like VARCAHR2 or CLOB. You cannot use it for binary data like images.

Update based on comment

Try this function to convert LONG to BLOB - Good luck!

CREATE OR REPLACE FUNCTION ConvertLONG(InChar IN LONG) RETURN BLOB IS

    dest_lob BLOB;  
    lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
    dest_offset INTEGER := 1;
    src_offset INTEGER := 1;
    read_offset INTEGER := 1;
    warning INTEGER;

BEGIN

    DBMS_LOB.CREATETEMPORARY(dest_lob, TRUE);
    DBMS_LOB.CONVERTTOBLOB(dest_lob, TO_LOB(InChar), DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset, DBMS_LOB.DEFAULT_CSID, lang_context, warning);
    RETURN dest_lob;

END;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143063

First of all, you should avoid using LONG data type columns and switch to BLOB (or CLOB, depending on what you plan to store in there). In your case, as that column contains images, that would be a BLOB.

You can't expect a SQL SELECT statement to return an image "as is" (i.e. to actually see that image on the screen) - all you get is that heap of junk characters. That's expected, it is a binary file, after all.

As you use TOAD, go to Schema Browser, open that table, view its data, right-click column that stores images and choose "Export Blobs (Longs, Raws ...)" from the menu. Follow instructions and export images into some folder on a disk.

Otherwise, you'd have to create an application (for example, using Apex) which is capable of displaying/downloading images.

If that's not what you are trying to do (as you mentioned that you'd want to "decode" a string), could you explain it?

Upvotes: 0

Related Questions