Reading the content of a CLOB column

Posted on 2004-10-21
Last Modified: 2012-08-13
hi all, I uploaded a file to a CLOB column , i would need:

1. to know how I can know if the file has been actually inserted into the column: where / what I have to go / do in order to see if there is some content?

2. I need procedure code to read the content of the CLOB column

Please send just code that actually works and please indicate steps by steps what I have to do to launch the procedure as, even tought I use it, I am not an Oracle procedure and programmig expert  ...

Thank you and regards
Question by:jytr
    LVL 23

    Expert Comment

    This is an excellant link for dealing with Oracle clob:
    LVL 5

    Assisted Solution

    I assume you mean a text (ascii) file; if you meant a binary file, you need a BLOB column, not a CLOB.

    Procedure to check if the clob you just inserted is really there:

      vPk number := 123;
      vLen number := 0;
      select dbms_lob.getlength(your_clob)
      into vLen
      from your_table
      where = vPk;
      dbms_output.put_line('vLen: ' || vLen);

    if vLen>0, you know there's stuff in the clob.

    Now to read it back out, use vLen in a FOR loop to get small chunks (32,000 chars) of the clob at a time, processing each chunk before you grab the next one.
    LVL 9

    Accepted Solution

      v_lob_col CLOB;
      v_amt NUMBER(100);
      v_offset NUMBER:=1;
      select lob_col into v_lob_col from your_table where condition...
      if v_lob_col is not null and dbms_lob.length(v_lob_col) >0 then
      loop, v_amt, v_offset, v_buff)
        exit when v_amt = 0;
        dbms_output.put_line(v_buff); -- or something else processing ....
        v_offset := v_offset + v_amt;
      end loop;

    Author Comment

    hi konektor, please try your procedure as I get an the line of the if tells me 'ORA-06550: line 7, column 3:
    PL/SQL: ORA-00933: SQL command not properly ended'

    thanks and regards
    LVL 3

    Expert Comment

    because you need to put in your own where clause, and not just cut and paste the procedure.

     select lob_col into v_lob_col from your_table where condition...

    is not a valid SQL statement, because there is no condition for the where clause. you need something like

    primary_key_col = your_primary_key instead of "condition..."

    Expert Comment

    create procedure read_clob
      clobColumn  clob;
      iClobSize  integer;
      rBuffer  raw(32767);
      vBuffer  varchar2(32767);
      nOffset  number := 1;
      iMaxSize  integer := 32767;
      iChunkSize  integer;
      iLeftOver  integer;
      dbms_lob.createTemporary(clobColumn, true);
      --select lob_col into clobColumn from your_table where....
      iClobSize := dbms_lob.getlength(clobColumn);
      if iClobSize > 0
          iLeftOver := (iClobSize - nOffset + 1);
          if iLeftOver <= iMaxSize
            iChunkSize := iLeftOver;
            iChunkSize := iMaxSize;
          end if;
, iChunkSize, nOffset, rBuffer);
          vBuffer := utl_raw.cast_to_varchar2(rBuffer);
          nOffset := nOffset + iMaxSize;
          if nOffset > iClobSize
          end if;
        end loop;
      end if;
    end read_clob;

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Anonabox PRO Tor & VPN Router

    PRO is the most advanced way to fortify your privacy and online anonymity by layering the Tor network with VPN services. Use both together or separately, and without needing to download software onto your devices.

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now