Home » SQL & PL/SQL » SQL & PL/SQL » SQL Model Clause to Create Resultset without DDL or Collection
SQL Model Clause to Create Resultset without DDL or Collection [message #391067] Tue, 10 March 2009 19:05 Go to next message
rnanton
Messages: 2
Registered: March 2009
Location: Victoria, BC, Canada
Junior Member
There has been many times where I have needed have a table-like structure that I could easily iterate through some static values, but didn't necessarily want the additional overhead of issuing any DDL to create either a table or collection. (I.e. Oracle Type and/or PL SQL table); Much less creating the insert statements for a table.

In 10g Oracle introduced the SQL model clause. Using this is a great way to produce a data result set without actually creating a table or collection. Best yet...it's really fast and simple.


--Using SQL Model clause to return a list of table names.
--Author: Ross Nanton
SELECT table_name
FROM (SELECT -1 table_id,
RPAD ('X', 30) table_name
--Change RPAD number to reflect length of column value.
FROM DUAL)
WHERE table_id <> -1
MODEL
DIMENSION BY (table_id)
MEASURES (table_name)
RULES
(table_name [1] = 'DEPT',
table_name [2] = 'EMP',
table_name [3] = 'PRODUCT',
table_name [4] = 'SCHEDULE')
ORDER BY table_id;


--Using SQL Model Clause to return a multi-column data set.
--Author: Ross Nanton
SELECT column1 Ministry, column2 Description, column3 Phone
FROM (SELECT -1 column_id,
RPAD ('X', 30) column1,
RPAD ('X', 1) column2,
RPAD ('X', 40) column3
--Change RPAD number to reflect length of column value.
FROM DUAL)
WHERE column_id <> -1
MODEL
DIMENSION BY (column_id)
MEASURES (column1,column2,column3)
RULES
(column1 [1] = 'Apple',column2 [1] = 'A',column3 [1] = 'A juicy red fruit',
column1 [2] = 'Orange',column2 [2] = 'O',column3 [2] = 'An reddish-yellow fruit',
column1 [3] = 'Banana',column2 [3] = 'B',column3 [3] = 'Something you could easily slip on'
)
ORDER BY column_id;
Re: SQL Model Clause to Create Resultset without DDL or Collection [message #391142 is a reply to message #391067] Wed, 11 March 2009 03:56 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Seems an excessively convoluted way to generate such a basic set of reults.
I would use something simple like:

SELECT  'DEPT' from dual union all
SELECT  'EMP' from dual union all
SELECT   'PRODUCT' from dual union all
SELECT  'SCHEDULE' from dual

and
SELECT 'Apple' col1
      , 'A' col2
      , 'A juicy red Apple'  col3
from dual union all
SELECT 'Orange'
     , 'O'
     ,'An reddish-yellow fruit' 
from dual union all
SELECT 'Banana'
     , 'B'
     , 'Something you could easily slip on'  
from dual 
Re: SQL Model Clause to Create Resultset without DDL or Collection [message #391143 is a reply to message #391142] Wed, 11 March 2009 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That's what I thought but I didn't want to dishearten research and new ways.

Regards
Michel
Re: SQL Model Clause to Create Resultset without DDL or Collection [message #391146 is a reply to message #391143] Wed, 11 March 2009 04:03 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Fair play Michel. I just felt it best to point out that it can be done more simply, which may lead to 'Then what can I use it for... hmmm" kind of thought processes. I have to confess, I haven't spent much time on the Model clause (not actually working with Oracle much at the moment unfortunately), but when I did look at it, I quite liked what I saw.
Re: SQL Model Clause to Create Resultset without DDL or Collection [message #391276 is a reply to message #391142] Wed, 11 March 2009 10:52 Go to previous messageGo to next message
rnanton
Messages: 2
Registered: March 2009
Location: Victoria, BC, Canada
Junior Member
Pablolee You're correct in that using a union statement is perhaps more intuitive..and I'm guessing probably a little faster for that matter. However, what I liked about using the SQL Model clause in this fashion was two things:

1) Being able to define not only the datatype, but also the length of values in a given column. I.e In the example below if one was to inadvertently enter a table name length greater than Oracle's permissible length of 30 chars I would get an ORA-25137: Data value out of range error. Also, if I was to enter a non-numeric value for table_size, I would get 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.

SELECT table_name, table_size
FROM (SELECT -1 table_id,
RPAD ('X', 30) table_name,
TO_NUMBER(RPAD(1,10)) table_size
--Change RPAD number to reflect length of column value.
FROM DUAL)
WHERE table_id <> -1
MODEL
DIMENSION BY (table_id)
MEASURES (table_name,table_size)
RULES
(table_name [1] = 'DEPT',table_size[1] = 100,
table_name [2] = 'EMP',table_size[2] = 300,
table_name [3] = 'PRODUCT',table_size[3] = 1100,
table_name [4] = 'SCHEDULE',table_size[4] = 'A')
ORDER BY table_id;


2) The ability to view the data under the Rules section in a more "flattened" or "table-like" fashion. I.e. Just a little more readable IMO.

If I had 10 rows I wished to UNION, I would have to do this using 10 fairly redundant constructs. I.e. 10 SELECT, 10 FROM, 10 DUALs. etc. As Michel indicated, just another way of doing things. Pick your flavor of choice.

[Updated on: Wed, 11 March 2009 11:27]

Report message to a moderator

Re: SQL Model Clause to Create Resultset without DDL or Collection [message #391282 is a reply to message #391067] Wed, 11 March 2009 11:20 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
a solution in search of a problem in my opinion
Previous Topic: Time - Datatypes question
Next Topic: How to get NLS_LANG from other session (merged)
Goto Forum:
  


Current Time: Thu Dec 08 08:48:08 CST 2016

Total time taken to generate the page: 0.05263 seconds