SQL & PL/SQL

Hi ,

When run as a sql it is working fine.

Not sure what i am missing in the below PL/SQL block,
i am trying to collect the spl.cut_text into the TEST_ID,
but it is not happenning,

What i need to alter in pl/sql to achieve the below output ?


DECLARE
TEST_ID varchar2(50);
BEGIN

FOR PCL IN (

WITH split_a_id AS
( SELECT 'ABCDEFGHIJKLMNOPQRSTUV' AS a_text ,
SUM(to_number(SUBSTR('13233424' || '0',-level,1))) over (order by rownum) AS cut_length
FROM DUAL
CONNECT BY level<=LENGTH('13233424')
)
SELECT spl.cut_text INTO TEST_ID
FROM
(SELECT SUBSTR(a_text,1,LENGTH(a_text) - cut_length) cut_text
FROM split_a_id
) spl
)
LOOP
dbms_output.put_line('TEST_ID : ' || TEST_ID);
END LOOP;
END;




oUTPUT
----------------------
ABCDEFGHIJKLMNOPQRST
ABCDEFGHIJKLMNOPQR
ABCDEFGHIJKLMNOP
ABCDEFGHIJKL
ABCDEFGHI
ABCDEF
ABCD
A