CONNECT BY LEVEL hangs

Question

I'm puzzled why my query hangs.

I have a simple regexp statement. The count works. The substr works. But when I add connect by syntax, the query hangs.

Query to find first occurrence, returns virtualDomains.GZATPAC_Get_Test_IDs"

select regexp_substr(model_view, 'virtualDomains\..*\"', 1,1)
FROM page
        WHERE 1=1
        AND id = 10815;

Query to find count of occurrences returns 3

select regexp_count(model_view, 'virtualDomains\..*\"')
FROM page
        WHERE 1=1
        AND id = 10815;

Using the following connect by level query hangs.

select regexp_substr(model_view, 'virtualDomains\..*\"', 1,level)
FROM page
        WHERE 1=1
        AND id = 10815
CONNECT BY level <= regexp_count(model_view, 'virtualDomains\..*\"');

Here is a sample of the text:

"{
   "components": [
      {
         "name": "title",
         "showInitially": true,
         "label": "GZATPAC-Test User Assignment",
         "type": "block"
      },
      {
         "resource": "virtualDomains.GZATPAC_Get_Test_IDs",
         "name": "GZATPAC_get_STUTEST_IDs",
         "type": "resource",
         "staticData": []
      },
      {
         "resource": "virtualDomains.GZATPAC_assign_proxy_users",
         "name": "GZATPAC_fill_assign_proxy_users",
         "type": "resource",
         "staticData": []
      },
      {
         "resource": "virtualDomains.GZATPAC_externalUserSearch",
         "name": "GZATPAC_search_for_proxy_users",
         "type": "resource",
         "staticData": []
      },
      {

P.S. I would like to strip out the final quote " in the string; although it works in several regex tools, unfortunately the following returns an empty row. Maybe an oracle limitation?

select regexp_substr(model_view, 'virtualDomains\..*(?:\")')
FROM page
        WHERE 1=1
        AND id = 10815;

Oracle does not support non-capturing groups in regular expressions so your final query will not work. — MT0, Jun 27 '22 at 18:16
Your query does not hang [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=493d61935e9f5b7d277103b34005a033) (but don't try to parse JSON using regular expressions). — MT0, Jun 27 '22 at 18:18
@MTO. I learned three things: 1) JSON parser, 2) CROSS apply, 3) no support for non-capturing groups (although I suspected that). Thank you. — Aaron, Jun 27 '22 at 19:20

score 2 · Accepted Answer · answered Jun 27 '22 at 18:13

Don't use regular expressions to parse ~~HTML~~ JSON; use a proper parser:

SELECT value
FROM   page p
       CROSS APPLY JSON_TABLE(
         p.model_view,
         '$.components[*].resource'
         COLUMNS (
           value VARCHAR2(200) PATH '$'
         )
       )
WHERE  p.id = 10815;

Which, for the sample data:

CREATE TABLE page (
  id NUMBER,
  model_view CLOB CHECK (model_view IS JSON)
);

INSERT INTO page (id, model_view) VALUES (
  10815,
  '{
    "components": [
      {
         "name": "title",
         "showInitially": true,
         "label": "GZATPAC-Test User Assignment",
         "type": "block"
      },
      {
         "resource": "virtualDomains.GZATPAC_Get_Test_IDs",
         "name": "GZATPAC_get_STUTEST_IDs",
         "type": "resource",
         "staticData": []
      },
      {
         "resource": "virtualDomains.GZATPAC_assign_proxy_users",
         "name": "GZATPAC_fill_assign_proxy_users",
         "type": "resource",
         "staticData": []
      },
      {
         "resource": "virtualDomains.GZATPAC_externalUserSearch",
         "name": "GZATPAC_search_for_proxy_users",
         "type": "resource",
         "staticData": []
      }
    ]
  }');

Outputs:

VALUE

virtualDomains.GZATPAC_Get_Test_IDs

virtualDomains.GZATPAC_assign_proxy_users

virtualDomains.GZATPAC_externalUserSearch

db<>fiddle here

CONNECT BY LEVEL hangs

1 Answers1