Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Concatenate long string in PL/SQL

540215Dec 11 2006 — edited Dec 12 2006
I am trying to concatenate multiple clob together, and the system hangs there.

the code is like

DECLARE
x clob := ' ';
BEGIN
for i = 1.. 30
loop
x := x || '[string about 800 bytes]';
END;

I tried using varray but it give me ORA-06533: Subscript beyond count error.

DECLARE
TYPE mytype IS VARRAY(31) OF VARCHAR2(2000);
m mytype:=mytype();

RPT_BEGIN_DATE DATE := TRUNC(SYSDATE, 'MONTH');
RPT_END_DATE DATE := LAST_DAY(SYSDATE);

v_STRING CLOB := '';

X NUMBER :=1;

BEGIN


WHILE RPT_BEGIN_DATE <= RPT_END_DATE
LOOP
DBMS_OUTPUT.PUT_LINE(X);
m(X):='[string about 800 bytes]'
RPT_BEGIN_DATE := RPT_BEGIN_DATE+1;
X := X+1;
END LOOP;

v_STRING := m(1)||m(2)||m(3)||m(4)||m(5)
||m(6)||m(7)||m(8)||m(9)||m(10)||m(11)||m(12)||m(13)
||m(14)||m(15)||m(16)||m(17)||m(18)||m(19)||m(20)
||m(21)||m(22)||m(23)||m(24)||m(25)||m(26)||m(27)
||m(28)||m(29)||m(30)||m(31);

--m.DELETE;

END;

Thanks in advance for any reply !
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 9 2007
Added on Dec 11 2006
8 comments
1,658 views