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