Anonymous TABLE or VARRAY type in Oracle

by

Providing you’re not scared of explicitly referencing the SYS schema there are a few. Here are some I use quite often (well odcivarchar2list not so much, as it chews up a lot of memory: for strings I prefer dbms_debug_vc2coll).

SQL> desc sys.odcinumberlist
 sys.odcinumberlist VARRAY(32767) OF NUMBER

SQL> desc sys.odcivarchar2list
 sys.odcivarchar2list VARRAY(32767) OF VARCHAR2(4000)

SQL> desc sys.ODCIDATELIST
 sys.ODCIDATELIST VARRAY(32767) OF DATE

SQL> desc sys.dbms_debug_vc2coll
 sys.dbms_debug_vc2coll TABLE OF VARCHAR2(1000)

SQL>

However, if those aren’t sufficient for your needs run this query to find some more:

select type_name
       , owner
from all_types
where typecode="COLLECTION"
and owner != user
/

Of course, this result will vary from database to database. For instance a lot of the colllections on my database are owned by XDB and not every system will have that installed. The four I listed at the the top of this answer should be available on every database since 9iR2 (and perhaps early) although they are not always documented in earlier versions.

“Note that ALL_COLL_TYPES seems to be an even better dictionary view
to find appropriate types”

That’s a good point. We can also filter on COLL_TYPE to winnow out the VARRAYs. That view was introduced into 10g whereas ALL_TYPES was available on 9i. As with most things Oracle, the later the version the more functionality it has.

More Related Contents:

  1. Creating or simulating two dimensional arrays in PL/SQL
  2. How do I limit the number of rows returned by an Oracle query after ordering?
  3. How to create id with AUTO_INCREMENT on Oracle?
  4. When do I need to use a semicolon vs a slash in Oracle SQL?
  5. Is there an Oracle SQL query that aggregates multiple rows into one row? [duplicate]
  6. How to handle a single quote in Oracle SQL
  7. Select count(*) from multiple tables
  8. Fetch the rows which have the Max value for a column for each distinct value of another column
  9. REGEX to select nth value from a list, allowing for nulls
  10. Concatenate and group multiple rows in Oracle [duplicate]
  11. Difference between BYTE and CHAR in column datatypes
  12. Call a stored procedure with another in Oracle
  13. create table with sequence.nextval in oracle [duplicate]
  14. Oracle: loading a large xml file?
  15. Group by alias (Oracle)
  16. Pivoting of data using two columns
  17. Benefits Of Using SQL Ordinal Position Notation?
  18. ORA-00907: missing right parenthesis
  19. How to store array or multiple values in one column
  20. How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?
  21. Foreign Key Creation issue in Oracle
  22. How do I list all tables in a schema in Oracle SQL?
  23. Is there a version control system for database structure changes?
  24. oracle sql: update if exists else insert [duplicate]
  25. Oracle: Updating a table column using ROWNUM in conjunction with ORDER BY clause
  26. Compare Strings ignoring accents in SQL (ORACLE)
  27. What does a (+) sign mean in an Oracle SQL WHERE clause? [duplicate]
  28. Connecting to Oracle Database using Sql Server Integration Services
  29. ORA-00933: SQL command not properly ended
  30. Update a table with data from another table

Leave a Comment