Reputation: 1919
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 ?
Upvotes: 0
Views: 2571
Reputation: 284
I have the following :
TYPE T_STR_table_TYPE IS TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(4000);
k_columns T_STR_table_TYPE := T_STR_table_TYPE();
However, on Oracle line 2 (where is set k_columns) 19.0.0.0.0 works properly without any compilation errors unlike 12.2.0.1.0 Oracle version where PLS-00222 error obtained
Initialisation substituted on
k_columns T_STR_table_TYPE;
What is the cause? Thank you
Upvotes: 0
Reputation: 168806
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);
Upvotes: 4