Forum Stats

  • 3,874,134 Users
  • 2,266,687 Discussions
  • 7,911,732 Comments

Discussions

Query related to MAX limit of VARCHAR2

902880
902880 Member Posts: 5
edited Aug 8, 2012 4:23AM in SQL & PL/SQL
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
Tagged:

Answers

  • Suri
    Suri Member Posts: 637 Silver Badge
    edited Aug 8, 2012 4:23AM
    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
    APC Member Posts: 11,316 Bronze Crown
    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
This discussion has been closed.