How long does varchar2 support?

Varchar2 instructions:
In a table column or in PLSQL, the maximum length of this type is 4000 bytes;
Select rpad ('A', 32767, 'B') from dual; -- PLSQL window execution, only 4000 bytes are intercepted by default
-- As shown in the following figure, the length of the result is only 4000.
Select length (rpad ('A', 32767, 'B') from dual;
Length (rpad ('A', 32767, 'B '))
---------------------------
4000

In a stored procedure or function, the maximum length of a variable of this type is 32767. However, although the maximum length of a variable can reach 32767, the variable cannot be read,
The Return Value of the function cannot exceed 4000 bytes.
Create or replace function funny2 return varchar2 is
V_yct varchar2 (32767 );
V_find varchar2 (1000 );
Begin
V_yct: = rpad ('A', 32767, 'B ');
Select instr (v_yct, ',', 1) into v_find from dual; -- Because v_yct is longer than 4000 bytes
Return v_find;
End;
An error is returned when you execute the function as follows:
Select funny2 from dual;
Tip: ORA-01460: the conversion request cannot be implemented or unreasonable

If you change v_yct to 4000 bytes in the function, the function can be successfully executed:
Create or replace function funny2 return varchar2 is
V_yct varchar2 (32767 );
V_find varchar2 (1000 );
Begin
V_yct: = rpad ('A', 4000, 'B ');
Select instr (v_yct, ',', 1) into v_find from dual; -- change to 4000 bytes
Return v_find;
End;

However, the varchar2 type in the function has a length of 32767. You can perform length statistics on this variable.
Create or replace function funny2 return varchar2 is
V_yct varchar2 (32767 );
Begin
V_yct: = rpad ('A', 32767, 'B ');
Return length (v_yct );
End;
SQL> select funny2 from dual;
Funny2
---------------
32767

-- If the varchar2 type returned by the function exceeds 4000 bytes, an error is returned, as shown below:
Create or replace function funny2 return varchar2 is
V_yct varchar2 (32767 );
Begin
V_yct: = rpad ('A', 32767, 'B ');
/* Select instr (v_yct, ',', 1) into v_find from dual ;*/
Return v_yct;
End;
Execute select funny2 from dual;
Tip: ORA-06502: PL/SQL: Number or value error

-- Therefore, if the return value of a function exceeds 4000, you can use the clob type instead, as shown below:
Create or replace function funny2 return clob is
V_yct clob;
Begin
V_yct: = rpad ('A', 32767, 'B ');
Return v_yct;
End;

Extended:

Restrictions on using long:
1. Each table can have only one long column.
-- Create a table with a long field
Create Table fff (ID number, name long );
-- After adding a long type, the prompt is: ORA-01754: The table can contain only one long type column
Alter table fff Add code long;

2. Long Columns cannot appear in integrity constraints (except for null and not null constraints)
-- For example, if you create a long field as the primary key, the following prompt: ORA-02269: the keyword Column cannot be a long data type
Drop table FFF;
Create Table fff (ID number, name long primary key );

3. Long Columns cannot be indexed
Create Table fff (ID number, name long );
-- If you create an index on a long field, the following error occurs: ORA-00997: The long data type is invalid.
Create index inx_name on fff (name );

4The storage function cannot return the long value.
-- As shown in the following function, the long type is returned, and execution of this function will prompt (ORA-00997: Invalid use of long data type)
Create or replace function funny2 return long is
V_yct long;
Begin
V_yct: = rpad ('A', 32760, 'B ');
Return length (v_yct );
End;

5. The long column cannot appear in some parts of the SQL statement:
In the SELECT statement, where, group by, order by, connect by clause, or distinct operator;
SQL functions (such as substr and instr );
The selection list in a subquery or a query combined by a set operator;
If the select list in the create table as select statement contains long fields, it cannot be created.

Insert into fff select object_id, object_name from user_objects where rownum <4;
-- The following query will prompt: ORA-00997: Invalid use of long data type
Select * From fff where name = '1 ';
Select name from fff group by name;
Select * From fff order by name;
Select distinct name from FFF;
Select * From user_objects where object_name in (Select name from fff );
Select name from fff Union
Select name from FFF;
Create Table FFFF as select * From FFF;
-- The following query prompt: ORA-00932: Data Type inconsistent: Should be number, but get long
Select substr (name, 1, 2) from FFF;
Select instr (name, '1') from FFF;

-- Can the long type only contain 32760 bytes when storing characters? If you add it to 32761, you will be prompted (ORA-06502: PL/SQL: Number or value error)
Create or replace function funny2 return varchar2 is
V_yct long;
Begin
V_yct: = rpad ('A', 32760, 'B ');
Return length (v_yct );
End;

For fields or variables with long characters, the clob type can be used. Do not use the long type.