Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Working with LOB's

    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:

    Code:
    DECLARE
        lob_loc           CLOB;
        Buffer            RAW(32767);
        Amount            BINARY_INTEGER := 32767;
        Position          INTEGER := 2;
    BEGIN
    
    	  SELECT theclob
    	  INTO lob_loc
    	  FROM metadata.demo
    	  WHERE tk = 16; 
    
       DBMS_OUTPUT.PUT_LINE('------------ LOB READ EXAMPLE ------------');
    
       DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer);
    
    END;
    
    
    ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
    ORA-06512: at line 15
    Any pointers?

    10gR1
    -Chuck
    Last edited by chuck_forbes; 10-05-06 at 15:00.

  2. #2
    Join Date
    May 2006
    Posts
    132
    Define your buffer as:

    Buffer VARCHAR2(32767);

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    That was it, I was mixing up BLOBs and CLOBs.

    We're working on saving .doc, .exe, .pdf, & .xls files in the database. Is it safe to say that we can just save these all as BLOBs, not CLOBs?

    -Chuck

  4. #4
    Join Date
    May 2006
    Posts
    132
    Absolutely, those need to be stored as BLOBS.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    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.

    -cf

  6. #6
    Join Date
    May 2006
    Posts
    132
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •