Confusion about data length in CLOB type variable in PL/SQL [message #631849] |
Tue, 20 January 2015 21:30 |
|
Following PL/SQL code is executed successfuly:
DECLARE
l_var1 CLOB;
l_var2 CLOB;
l_var3 CLOB;
BEGIN
l_var1 := rpad ('*',24000,'*');
l_var2 := rpad ('*',24000,'*');
l_var3 := l_var1||l_var2;
END;
But following code shows errors:
DECLARE
l_var1 CLOB;
l_var2 CLOB;
l_var3 CLOB;
BEGIN
l_var3 := rpad ('*',48000,'*');
END;
Error report:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 6
06502. 00000 - "PL/SQL: numeric or value error%s"
Would you please explain why is this error generated, but no error in first block of code?
[Updated on: Tue, 20 January 2015 21:38] Report message to a moderator
|
|
|
|
Re: Confusion about data length in CLOB type variable in PL/SQL [message #631855 is a reply to message #631850] |
Tue, 20 January 2015 23:24 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
BlackSwan wrote on Wed, 21 January 2015 09:09
The largest PL/SQL variable is 32765
Isn't it 32767?
SQL> DECLARE
2 l_var varchar2(32767);
3 BEGIN
4 l_var := rpad ('*',32767,'*');
5 dbms_output.put_line(length(l_var));
6 END;
7 /
32767
PL/SQL procedure successfully completed.
SQL> DECLARE
2 l_var varchar2(32767);
3 BEGIN
4 l_var := rpad ('*',32768,'*');
5 dbms_output.put_line(length(l_var));
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
SQL> DECLARE
2 l_var varchar2(32768);
3 BEGIN
4 l_var := rpad ('*',32767,'*');
5 dbms_output.put_line(length(l_var));
6 END;
7 /
l_var varchar2(32768);
*
ERROR at line 2:
ORA-06550: line 2, column 18:
PLS-00215: String length constraints must be in range (1 .. 32767)
SQL>
|
|
|
Re: Confusion about data length in CLOB type variable in PL/SQL [message #631877 is a reply to message #631849] |
Wed, 21 January 2015 06:47 |
Solomon Yakobson
Messages: 3267 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
mokarem wrote on Tue, 20 January 2015 22:30Would you please explain why is this error generated, but no error in first block of code?
Food for thought:
SQL> DECLARE
2 l_var1 CLOB;
3 l_var2 CLOB;
4 l_var3 CLOB;
5 BEGIN
6 l_var3 := rpad ('*',48000,'*');
7 END;
8 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 6
SQL> DECLARE
2 l_var1 CLOB;
3 l_var2 CLOB;
4 l_var3 CLOB;
5 BEGIN
6 l_var3 := rpad (to_clob('*'),48000,'*');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
|