Kayshav.com
About Developer Oracle 11g Technology Information Sitemap

CLOB In Anonymous Block
-- Input to database procedure or function 
-- could be a CLOB if the search string 
-- length will be more than 4000 characters 
-- long.  In anonymous PL/SQL block the Max 
-- size can be 32767 (but works upto 32773 
-- in 11gR2). To get length greater than 
-- 32767, the data has to to stored in a 
-- database table CLOB column and string 
-- has to be parsed.

SET TIMING ON;
SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
 CURSOR c_id IS
  WITH q_data AS
   (SELECT  100000+level tst_idx 
    FROM DUAL
    CONNECT BY level <=4682)
  SELECT tst_idx, COUNT(*) OVER() cnt
  FROM q_data;

  v_idx    SIMPLE_INTEGER := 0;
  v_str    CLOB;
BEGIN
 FOR id_rec IN c_id LOOP
  v_idx := v_idx + 1;

  IF v_idx < id_rec.cnt THEN
    v_str := v_str||id_rec.tst_idx||','; 
  ELSE
    v_str := v_str||id_rec.tst_idx;
  END IF;

 END LOOP;
 DBMS_OUTPUT.PUT_LINE(CHR(10)||
   ' ID_String Length ['||v_idx||'] => '||
   DBMS_LOB.GETLENGTH(v_str));
END;
/

   CLOB In Anonymous Block

  DBMS_LOB Reference

  Insert CLOB Data to Oracle Table

  CLOB Data to VARCHAR2

  Oracle 11gXEr2 - Index


Last Revised On: March 18th, 2014

  3096