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


Truth behind show user

SQLPLUS command line provides a show user help which can print which user you are logged in as. Just learned new today that it is not hitting the DB to get the results.

Tom, who else can bring it to light. I am just 12 years down the line to have read his message.

The content points to this article

The SQLPLUS caches the username as soon as a "connection" is established and saves it for reuse. Whenever somebody issues a show user, it just returns from the cache.

Internally for the first time it will be doing a select user from dual; to set the value. Its like a global variable

Next time onwards it will simply return from its variable instead of running a query. Even if the Connection is killed, SQLPLUS returns the variable value. (This was new to me)

----Copy pasting shamelessly the example from Tom Kyte's article for my reference:
[tkyte@desktop tkyte]$ sqlplus /
 
SQL*Plus: Release 10.1.0.4.0 - Production on Wed Aug 3 10:01:01 2005
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
 
ops$tkyte@ORA10G> @getspid
 
DEDICATED_SE CLIENTPID
------------ ------------
8391         8389
 
ops$tkyte@ORA10G> !kill -9 8391
 
ops$tkyte@ORA10G> show user
USER is "OPS$TKYTE"
ops$tkyte@ORA10G> select user from dual;
select user from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
 
 
ERROR:
ORA-03114: not connected to ORACLE

 
 
ops$tkyte@ORA10G> show user
USER is "OPS$TKYTE"
ops$tkyte@ORA10G>