Query related to MAX limit of VARCHAR2

902880

Hello,
I have run the below PL/SQL block and it gives out the o/p as follows:

DECLARE
  LEN1  NUMBER;
BEGIN
  
  SELECT LENGTH(RPAD('*', 32760, '*')) INTO LEN1 FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('LEN1: '||LEN1);

END; 

Output--> LEN1: 4000

But why is it that when i run the same PL/SQL block by assigning the 32K character string to a variable it throws an error -

DECLARE
  LEN1  NUMBER;
  STR   VARCHAR2(32767);
BEGIN
  
  STR := RPAD('*', 32760, '*');
  
  SELECT LENGTH(STR) INTO LEN1 FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('LEN1: '||LEN1);

END;  

Output-->
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 8

I understand that VARCHAR2 has a limit of 4000 chars in SQL and 32767 chars in PL/SQL. If so, why not the second block atleast return 4000 going with this limitation?

Regards,
Sujana

Suri

Hi,

Maximum size for VARCHAR2 is 4000. So you cant add 32767 characters to a variable which you have defined with VARCHAR2 datatype.

Below will work

DECLARE
  LEN1  NUMBER;
  STR   VARCHAR2(32767);
BEGIN
  
  STR := RPAD('*', 4000, '*');
  
  SELECT LENGTH(STR) INTO LEN1 FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('LEN1: '||LEN1);
 
END;

Thanks,
Suri

Edited by: Suri on Aug 8, 2012 1:50 PM

Edited by: Suri on Aug 8, 2012 1:52 PM

APC

In your first example the database engine overrode your length for the RPAD() call with the SQL limit (4000 characters). In your second example you presented the engine with a 32K string and it hurled. This is because the engine hasn't been programmed to truncate a string which is too long.

If anything I think the first example is inconsistent. Certainly that behaviour is [url http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions147.htm#i78723]not documented.

Cheers, APC