Subscribe

RSS Feed (xml)

Powered By

Skin Design:
Free Blogger Skins

Powered by Blogger

oracle search

Wednesday, August 6, 2008

Simplest row generator meets maximum inefficiency ....oracle

Generating a set of values that are not stored in a table is a reasonably common problem. I recently came across this solution for a set of irregular interval values.

 OPEN cur_rows FOR
'SELECT 3 FROM dual UNION '||
'SELECT 6 FROM dual UNION '||
'SELECT 9 FROM dual UNION '||
'SELECT 12 FROM dual UNION '||
'SELECT 24 FROM dual UNION '||
'SELECT 36 FROM dual';
LOOP
FETCH cur_rows INTO v_num_value;
EXIT WHEN cur_rows%NOTFOUND;
I didn't know which to admire the most. The clever use of dynamic SQL or the sort distinct to get rid of any duplicate literals that may occur from accidentally typing the same line twice. I think the latter wins for being also relationally pure apparently.

No comments: