Retrieving a VARRAY Item Type Using the TYPE Attribute

Question

I want to get the type of varray store elements through the type attribute or ANY work around.

for example, our type is defined as

CREATE TYPE "READINGS" AS VARRAY (200) OF NUMBER(21, 6); 

(readings are varray with elements of type number(21,6) )

READINGS - a column in the INTERVALS table. INTERVALS is the central table, and we have batch processes on INTERVALS that perform sql storage procedures. In the storage procedure, we have a mapping of declarations with hard-coded variables of type READING type type VArray, which is NUMBER(21, 6) , for example, the storage procedure has variable declarations, such as

 CONSUMPTION NUMBER(21, 6); 

when the definition of Varray changes, or varray is discarded and recreated with different sizes and precision, ex instead of number(21,6) changes to number(25,9) , we need to change the variable declarations in all package storage procedures. All I'm looking for is declaring a CONSUMPTION variable, see VArray Element Type. I want something like this

 CONSUMPTION INTERVALS.READINGS.COLUMN_TYPE%TYPE; 

(I want something like this, refer to the type of elements stored in varray)

Answer 1

Why do you create a table with a VARRAY column in the first place? It would be much more reasonable to create a separate table for READINGS with a foreign key, which allows you to associate rows with the INTERVALS table. Then you could easily declare columns of type READINGS.COLUMN_NAME%TYPE .

Collections are extremely useful in PL / SQL. I have never seen a case where they have improved on a standardized standardized approach to data modeling. I have seen many cases where including collections in your data model ends up making it difficult to work with your data model, and your code is more difficult to write and maintain.

If you do not want to correct the data model, you can

• Declare a SUBTYPE or a packed variable of type NUMBER(21, 6) , which you use as a type for variable declarations. You will have to change this definition if and when you change an ad of type VARRAY .
• Create an object type with a single attribute (a NUMBER(21,6) ) and define VARRAY based on this object type. Then you can declare object type instances in your code.

Answer 2

This is not a necessary solution, but you can get a type definition string for further use in dynamic SQL

 SELECT regexp_substr(text, 'VARRAY.*?OF\s+(.+?)(;|\s)*$', 1, 1, 'i', 1) FROM user_source WHERE name = 'READINGS'