csv Data to Rows - SQL

 If you have a data with Comma, or any other character delimited use the following query to convert it to individual rows.

-- Created on 03/07/2024 by ANANTHAN 

DECLARE

  -- Local variables here

  CURSOR c1 IS WITH rws AS(

    SELECT 'one,two,three' str

    FROM   dual)

    SELECT regexp_substr(str, '[^,]+', 1, LEVEL) split_val

    FROM   rws

    CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) + 1;

BEGIN

  FOR m1 IN c1 LOOP

    dbms_output.put_line(m1.split_val);

  END LOOP;

END;

Output:

one

two

three

Your session has expired - For Custom Apps after Oracle Apex Upgrade from 5 to 20

I was getting the above error "Your session has expired" immediately after upgrade of Oracle Apex from Version 5 to 20 in my premises. Searched a lot and did not find any resolution.

After trying to login in same browser, tried a private browsing mode in Chrome and Firefox. It logged in. That is when i realized that it might be due to session cache/cookie.

As a resolution I cleared all session cookies by clicking "Inspect Element/Storage" in Firefox. It worked after the storage for the site was cleared.

Hierarchical Query with search parameters (Pruning)

The following is a query from a friend, Reproducing it here:

I have a menu structure using a hierarchical query. i have search field to enter some string and search.
I should get the result in the same menu format with with the node and its parents.
Can you please help me with the query for search ?
eg.
A1
A11
A12
A123
A13
B1
B123
C1
C11
C12
If I search for %123% the result -
A1
A12
A123
B1
B123

My idea initially was, yeah possible BUT HOW. I was sure it needed two result sets and joining to form a query to get the output. But wait, what is internet saying about it. 

I stumbled upon this brilliant article in orafaq and I was sure my initial thought was wrong. I was in a different mindset. Thanks to the article and Oracle SQL the query was much simpler. But with a catch, the hierarchy was in reverse.

SELECT lpad(' ', LEVEL, '-') || h.title descr

FROM   test_hier h

CONNECT BY id = PRIOR parent_id

START  WITH parent_id IS NOT NULL

     AND    h.title LIKE '%123%'

And the output was:

DESCR

 A123

- A12

-- A1

 B123

- B1