CODEWITHSUNDEEP
oracle-databaseplsql
Prafulla Shirkhedkar
Prafulla Shirkhedkar

Reputation: 41

Retrieve VARRAY element type using TYPE attribute

i want to retrieve type of elements varray stores through type attribute or ANY work around.

for example our type is defined like this

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

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

READINGS is a column in a table INTERVALS. INTERVALS is a central table and we have batch processes on INTERVALS which execute sql store procedures. In store procedure we have hard coded variable declarations mapping to the READING VArray type element type which is NUMBER(21, 6) for example the store procedure has variable declarations like 

CONSUMPTION NUMBER(21, 6);

whenever Varray definition is changed or varray is dropped and recreated with different size and precision, ex instead on number(21,6) is changed to number(25,9) we need to change our variable declarations in all batch process store procedures. All i am looking for is making CONSUMPTION variable declaration, refer to element type of VArray. I want something like this

CONSUMPTION INTERVALS.READINGS.COLUMN_TYPE%TYPE;

(i want some thing like this, refer to the type of elements stored by varray)

Upvotes: 4

Views: 191

Answers (2)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

This is not a required solution, but you can get string of type definition for further using it in dynamic SQL 

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

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

Why are you creating a table with a VARRAY column in the first place? It would generally make far more sense to create a separate table for READINGS with a foreign key that lets you relate the rows back to the INTERVALS table. You could then easily enough declare columns of type READINGS.COLUMN_NAME%TYPE.

Collections are wildly useful in PL/SQL. I've never seen a case where they improved on a standard normalized approach to data modeling. I have seen multiple cases where incorporating collections into your data model ends up making your data model harder to work with and your code harder to write and maintain.

If you don't want to fix the data model, you can

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

Upvotes: 2

Related Questions