CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Leo
Leo

Reputation: 1919

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 ?

Upvotes: 0

Views: 2571

Answers (2)

Roman Ieromenko
Roman Ieromenko

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;
  • compiled

What is the cause? Thank you

Upvotes: 0

MT0
MT0

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

Related Questions