title: Need help in converting the clob to varchar in oracle, I have to use the varchar in case function of oracle
body: <p>I have a requirement where I have to fetch the data from the <code>clob</code> data type , convert to the varchar2, to make a pivot for oracle 10g.</p>
  
  <p>I am using the following </p>
  
  <pre><code>select max(case 
               when key='abc' 
               then dbms_lob.substr(value) 
             end) as data_abc 
    from table.
  </code></pre>
  
  <p>if the value is less than 4000 the above query works fine but if it is more than 4000 it shows and error of buffer limit. On reading few blogs I came to know that <code>dbms_lob.substr()</code> can handle only 4000 characters in sql but can handle up to 32k in a pl/sql statement.</p>
  
  <p>if I write a procedure and run it, it works fine. but i want to use it in a function. below is my function :</p>
  
  <pre><code>create or replace FUNCTION CLOBTOVARCHAR 
  RETURN varchar2 is out_attribute_var varchar2(32767) ;
  BEGIN
    FOR i IN (select attribute_Value  from car_course_attribute where id=1547156)
    LOOP
      out_attribute_var := dbms_lob.substr(i.attribute_Value, 32000, 1);
    END LOOP;
  
    RETURN out_attribute_var;
  
  EXCEPTION
    WHEN OTHERS THEN
      raise_application_error(-20001, 'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
  END CLOBTOVARCHAR;
  </code></pre>
  
  <p>If the data is small it works fine but if the data is bigger than 4k, it gives same error back. now I have two questions:
  1) Am I doing right by converting the <code>clob</code> to varchar2 as I want to get pivot
  2) Is my function correct?</p>