Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Reading the content of a CLOB column

Posted on 2004-10-21
6
Medium Priority
?
3,202 Views
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
0
Comment
Question by:jytr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 12376152
This is an excellant link for dealing with Oracle clob:

http://www.psoug.org/reference/dbms_lob.html
0
 
LVL 5

Assisted Solution

by:crimson117
crimson117 earned 900 total points
ID: 12378012
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:

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

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.
0
 
LVL 9

Accepted Solution

by:
konektor earned 600 total points
ID: 12378434
declare
  v_lob_col CLOB;
  v_amt NUMBER(100);
  v_offset NUMBER:=1;
begin
  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
    dbms_lob.read(v_lob_loc, 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;
end;
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

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

thanks and regards
0
 
LVL 3

Expert Comment

by:oratim
ID: 12379205
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..."
0
 

Expert Comment

by:pfeddis
ID: 12379323
create procedure read_clob
is
  clobColumn  clob;
  iClobSize  integer;
  rBuffer  raw(32767);
  vBuffer  varchar2(32767);
  nOffset  number := 1;
  iMaxSize  integer := 32767;
  iChunkSize  integer;
  iLeftOver  integer;
begin
  dbms_lob.createTemporary(clobColumn, true);
  --select lob_col into clobColumn from your_table where....
  iClobSize := dbms_lob.getlength(clobColumn);
  if iClobSize > 0
  then
    loop
      iLeftOver := (iClobSize - nOffset + 1);
      if iLeftOver <= iMaxSize
      then
        iChunkSize := iLeftOver;
      else
        iChunkSize := iMaxSize;
      end if;
      dbms_lob.read(clobColumn, iChunkSize, nOffset, rBuffer);
      vBuffer := utl_raw.cast_to_varchar2(rBuffer);
      --processing....
      nOffset := nOffset + iMaxSize;
      if nOffset > iClobSize
      then
        exit;
      end if;
    end loop;
  end if;
  dbms_lob.freeTemporary(clobColumn);
end read_clob;
/
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

604 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