How to get page number from item name in Oracle?-CodePudding

I am working with Oracle Apex. I'm trying to get page number from an item name like P11_USER_NAME but, if I use substr function, then it's returning an error invalid number.

I hope to find any easy solution.

CodePudding user response：

We can use REGEXP_SUBSTR here with a capture group:

SELECT name, REGEXP_SUBSTR(name, '^P([0-9] )', 1, 1, NULL, 1) AS page_no
FROM yourTable;

CodePudding user response：

As you probably know (or don't; doesn't matter), item name doesn't have to contain page number. That's what most of us do because it is simpler for us, developers and because documentation suggests so. It means that - presuming this is page "1" - item name can be

P1_ID (you expect "1" as result here, which would be OK), or
ID (no page number here at all), or
MY_ID (no page number here either), or
P123_ID (you'd get "123" as result, but - that's page 1, not page 123 so you'd get wrong result), or ...

Therefore, I believe that you should actually query apex_application_page_items, pass certain information to it (e.g. workspace name, application ID, item name or whatever you want) and get the correct result, regardless of whether item name contains page number or not.

For example:

SQL> select i.page_id
  2  from apex_application_page_items i
  3  where i.application_id = 120
  4    and i.item_name = 'P121_EVBR';

   PAGE_ID
----------
       121

SQL>

(Query apex_application_page_items to see all information it offers.)