Home » SQL & PL/SQL » SQL & PL/SQL » Confusion about data length in CLOB type variable in PL/SQL (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit)
Confusion about data length in CLOB type variable in PL/SQL [message #631849] Tue, 20 January 2015 21:30 Go to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

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 #631850 is a reply to message #631849] Tue, 20 January 2015 21:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Would you please explain why is this error generated?
The largest PL/SQL variable is 32765; RPAD is what throws the error.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
mokarem wrote on Tue, 20 January 2015 22:30
Would 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.
Re: Confusion about data length in CLOB type variable in PL/SQL [message #631880 is a reply to message #631877] Wed, 21 January 2015 07:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>l_var3 := rpad (to_clob('*'),48000,'*');

shows once again why EXPLICIT datatype conversion is superior to relying on implicit datatype conversion.
Re: Confusion about data length in CLOB type variable in PL/SQL [message #631920 is a reply to message #631877] Wed, 21 January 2015 21:59 Go to previous message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Many Thanks!
Previous Topic: Want to split value based on space if no space want to show null in second column
Next Topic: status based use of SUM function
Goto Forum:
  


Current Time: Tue Mar 19 04:12:50 CDT 2024