Home » SQL & PL/SQL » SQL & PL/SQL » Viewing Oracle BLOB
Viewing Oracle BLOB [message #32363] Fri, 13 August 2004 12:13 Go to next message
DCVData
Messages: 8
Registered: July 2004
Junior Member
I am receiving a message .. "Data type is not supported".. when I try to do a select on a BLOB column.� This happens in SQLPlus and TOAD.� Anyone run into this?� Hints?

dcvData
Re: Viewing Oracle BLOB [message #32366 is a reply to message #32363] Fri, 13 August 2004 13:12 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Old versions of sql*net don't support blobs. Check which oracle home you are using in Toad. File > New Connection. Check the checkboxes and see what the current Toad home is using "SQL*net help" button.

If you only have one Oracle installation, you can just open a DOS window and type TNSPING to see the version or just open SQL*Plus. I think you need Net8 or higher.
Re: Viewing Oracle BLOB [message #32367 is a reply to message #32366] Fri, 13 August 2004 13:53 Go to previous messageGo to next message
DCVData
Messages: 8
Registered: July 2004
Junior Member
Thanks. I checked and we are on 9i. Any other hits are appreciated.

--dcv

____________________________________________

Old versions of sql*net don't support blobs. Check which oracle home you are using in Toad. File > New Connection. Check the checkboxes and see what the current Toad home is using "SQL*net help" button.

If you only have one Oracle installation, you can just open a DOS window and type TNSPING to see the version or just open SQL*Plus. I think you need Net8 or higher.
Re: Viewing Oracle BLOB [message #32368 is a reply to message #32367] Fri, 13 August 2004 14:49 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
It's maybe worth checking TOAD connection options to make sure that "force sql*net" isn't selected.
Re: Viewing Oracle BLOB [message #32382 is a reply to message #32363] Mon, 16 August 2004 00:42 Go to previous messageGo to next message
Martin
Messages: 83
Registered: February 2000
Member
Hy dcvDATA

if you have only text inside the blob you cane use
utl_raw.cast_to_varchar2 function to view the text
if you have stored videos or other binary stuff it woud not work of cource

Regards Martin
Re: Viewing Oracle BLOB [message #32396 is a reply to message #32363] Mon, 16 August 2004 04:51 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
So does your BLOB contain textual information?

If so, this procedure will let you at least see it. One drawback of the procedure is that the formatting gets broken up due to DBMS_OUTPUT and VARCHAR2 length limitations.
CREATE OR REPLACE PROCEDURE read_blob (p_fnd_lobs_name IN VARCHAR2)
IS
    l_blob              BLOB;
    l_blob_length       NUMBER;
    l_amount            BINARY_INTEGER := 10000; -- must be <= ~32765.
    l_offset            INTEGER := 1;
    l_buffer            RAW(20000);
    l_text_buffer       VARCHAR2(32767);
    l_break_at          PLS_INTEGER := 141; -- must be <= 255.
BEGIN
    -- Get the BLOB from the APPLSYS.FND_LOBS table.
    SELECT fl.file_data
    INTO   l_blob
    FROM   applsys.fnd_lobs        fl
    WHERE  fl.file_name = p_fnd_lobs_name;

    -- Figure out how long the BLOB is.
    l_blob_length := DBMS_LOB.GETLENGTH(l_blob);

    -- We'll loop through the BLOB as many times as necessary to
    -- get all its data.
    FOR i IN 1..CEIL(l_blob_length/l_amount) LOOP

        -- Read in the given chunk of the BLOB.
        DBMS_LOB.READ(l_blob
        ,             l_amount
        ,             l_offset
        ,             l_buffer);

        -- The DBMS_LOB.READ procedure dictates that its output be RAW.
        -- This next procedure converts that RAW data to character data.
        l_text_buffer := UTL_RAW.CAST_TO_VARCHAR2(l_buffer);

        -- Now that we have character data (up to l_amount characters long),
        -- chunk it out so that we can call DBMS_OUTPUT (which only accepts
        -- arguments less than or equal to 255 characters).
        FOR j IN 1..CEIL(LENGTH(l_text_buffer) / l_break_at) LOOP
            DBMS_OUTPUT.PUT_LINE(SUBSTR(l_text_buffer
                                 ,      (((j - 1) * l_break_at) + 1)
                                 ,      LEAST(LENGTH(l_text_buffer)
                                              -
                                              ((j - 1) * l_break_at)
                                        ,     l_break_at)));
        END LOOP;

        -- For the next iteration through the BLOB, bump up your offset
        -- location (i.e., where you start reading from).
        l_offset := l_offset + l_amount;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('!ERROR: ' || SUBSTR(SQLERRM,1,247));
END;
/
How you get to your BLOB will vary by application; the above is an Oracle Apps solution.

Hope this helps,

Art.
Previous Topic: get the decimal value
Next Topic: SQL Query: for row level manipulations
Goto Forum:
  


Current Time: Fri May 16 20:03:05 CDT 2025