-- 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; /
17668