MODEL Regular Expressions (IT Toolbox Blogs)



As I noted recently, Oracle has two features vex me: One is the MODEL clause; the other is “regular expressions”: Both are compact and cryptic. 

 

So naturally, I wanted to try to combine them. I’m not going to tell you how much head-banging I needed to get a successful result. There was a lot of my usual googling-related questions; leafing through various tutorials, and so on.

 

Yet the resulting query was pretty simple:

 

WITH sample_data AS (
SELECT owner, view_name, text_vc
     , regexp_count( text_vc, '"[^"]{1,30}"' ) as CNT
  FROM dba_views_copy
 WHERE owner = 'SYS'
) -- END OF sample_data
---------------------------------
, model_parser AS (
SELECT owner, view_name
     , i#+1 as column_id, nm as column_name, cnt
  FROM sample_data
 MODEL
 PARTITION BY ( owner, view_name)
 DIMENSION BY ( 0 as i#) -- the field number
   MEASURES
  ( cast(null as varchar2(32)) as NM
  , text_vc
  , cnt
  )
 RULES ITERATE(999)
    UNTIL iteration_number+1 >= cnt[0]
 ( nm[iteration_number]
      = regexp_substr( text_vc[0], '"[^"]{1,30}"'
               , 1, iteration_number+1)
 )
) -- END OF model_parser
---------------------------------
SELECT *
  FROM model_parser
 WHERE owner = 'SYS'
   AND view_name = any('ALL_REGISTERED_MVIEWS', 'ALL_APPLY_ENQUEUE')
 ORDER BY 1, 2, 3;

OWNER VIEW_NAME              COLUMN_ID COLUMN_NAME               CNT
----- ---------------------- --------- ----------------------- -----
SYS   ALL_APPLY_ENQUEUE              1 "RULE_OWNER"                5
SYS   ALL_APPLY_ENQUEUE              2 "RULE_NAME"
SYS   ALL_APPLY_ENQUEUE              3 "DESTINATION_QUEUE_NAME"
SYS   ALL_APPLY_ENQUEUE              4 "'||aq.owner||'"
SYS   ALL_APPLY_ENQUEUE              5 "'||aq.name||'"
SYS   ALL_REGISTERED_MVIEWS          1 "OWNER"                     9
SYS   ALL_REGISTERED_MVIEWS          2 "NAME"
SYS   ALL_REGISTERED_MVIEWS          3 "MVIEW_SITE"
SYS   ALL_REGISTERED_MVIEWS          4 "CAN_USE_LOG"
SYS   ALL_REGISTERED_MVIEWS          5 "UPDATABLE"
SYS   ALL_REGISTERED_MVIEWS          6 "REFRESH_METHOD"
SYS   ALL_REGISTERED_MVIEWS          7 "MVIEW_ID"
SYS   ALL_REGISTERED_MVIEWS          8 "VERSION"
SYS   ALL_REGISTERED_MVIEWS          9 "QUERY_TXT"

14 rows selected.

 

Yes, it’s MODEL equivalent of my earlier queries for parsing out double-quoted column names; which were in turn based on an AskTom question. Instead of recursive SQL, I used the MODEL clause.

 

Not that it’s much different in execution time than my other two queries. Connor McDonald’s proposed pipelined-table function still wins handily.

 

 


 

 

I was able to put together a basic MODEL query easily from my earlier postings on the Fibonacci series. It was making the jump from “parse this one view” to “parse all the views” that was vexing. I initially just moved the owner and view_name into the DIMENSION BY clause. That never ended well. There were the usual raft of obscure errors that seem to degenerate into a neverending game of “Bring Me A Rock.

 

Only when I was writing up the question for review by Connor, Chris and Maria that I realized… Put owner and view_name into a PARTITION BY clause instead of DIMENSION BY… and *poof* the query was done.

 

Whew!

 

<END> 

 
MODEL
 
REGEXP_COUNT
 
REGEXP_SUBSTR

Source: SANS ISC SecNewsFeed @ February 21, 2017 at 11:09AM

0
Share