in Oracle database, how do I create a table of varchar2 type without length

for using it as a returning type in a function, I need to create a type at database level. I do it by typing the command :

CREATE TYPE empno_tbl 
    IS TABLE OF VARCHAR2(100);

but this obliges me to choose a length. I would like to create a string kind table type without a specific length.

is there a way to do this ?

Answer

If you really want it without a length then use CLOB.

CREATE TYPE empno_tbl IS TABLE OF CLOB;

(There is a limit but in Oracle 19c the CLOB datatype has a limit of 4GB * DB_BLOCK_SIZE initialization parameter, which gives a total size of 8 TB to 128 TB, and if you are reaching that limit then there is probably something wrong with your approach.)

The maximum size for a VARCHAR2 is 4000 bytes, so if you can cope with that limit then just use:

CREATE TYPE empno_tbl IS TABLE OF VARCHAR2(4000);