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!

Query related to MAX limit of VARCHAR2

902880Aug 8 2012 — edited Aug 8 2012
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

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 5 2012
Added on Aug 8 2012
2 comments
269 views