Skip navigation.

Oracle SQL Model - An Alternative to UNION for Creating A list of Static Values

Here’s an alternative to the union statement for creating a collection of values using the Oracle SQL Model construct in 10g.

--Using SQL Model clause to return a list of items.

  SELECT   col_1 Product, col_2 Price, col_3 Description
    FROM   (SELECT   -1 col_id,
                     RPAD ('X', 30) col_1,
                     TO_NUMBER(RPAD ('1', 3)) col_2,
                     RPAD ('X', 40) col_3                                          
              --Change RPAD number to reflect length of column value.
              FROM   DUAL)
   WHERE   col_id <> -1
MODEL
   DIMENSION BY (col_id)
   MEASURES (col_1,col_2,col_3)
   RULES
      (col_1[1] = 'Apple' ,col_2[1] = 1.25 ,col_3[1] = 'Red Delicious',
       col_1[2] = 'Orange',col_2[2] = 1.50 ,col_3[2] = 'Naval',
       col_1[3] = 'Banana',col_2[3] = 100  ,col_3[3] = 'Locally grown'       
      )
ORDER BY   col_1;   

Although this might be more verbose than a series of UNION statements some benefits of this method include:

1) Being able to define not only the data type, but also the length of values in a given column.
I.e In the example above if one was to inadvertently enter a Product length greater than the specified
30 chars you would get an ORA-25137: Data value out of range error. Also, entering a non-numeric
value for price will return an ORA-01722: invalid number error. So here we've been able to apply some
datatype validation rules on what we enter into our collection.

2) The ability to view the data under the Rules section in a perhaps more readable "flattened" or "table-like" fashion.