Monday, 31 August 2015

Find out Missing Numbers in a sequence

Example:

We have a table ORDH, which is having a field ORDERNO which starts from 300000. currently there are around 100 thousand records exists in this table.
The ORDERNO value is generated from a sequence.
We want to find out the missing (hole in the sequence) ORDERNO from the ORDH table.
(Ex: 300654 is missing and there are lot of other order numbers were also missing)
Is there a way to find out all the missing order numbers from this table?

And We Said....

WITH     got_next_orderno     AS
(
     SELECT     orderno
     ,     LEAD (orderno) OVER (ORDER BY orderno)     AS next_orderno
     FROM     ordh
)
SELECT     ordrno + 1          AS from_no
,     next_orderno - 1     AS to_no
FROM     got_next_orderno
WHERE     orderno     + 1     != next_orderno
;

OR you can use this code as well:

select min_a - 1 + level
      from ( select min(id) min_a
                  , max(id) max_a
               from d3
           )
    connect by level <= max_a - min_a + 1
     minus
     select id
       from d3

get selected list of a shuttle to a textfield

how to get selected list of a shuttle to a textfield(comma seperated) in apex?

And we Said...

You can do it with the help of dynamic action.

Create a dynamic action on the onchange event of your shuttle item (say P1_SHUTTLE) to get the select values in text item (say P1_TEXT) as follows:

Name : give a proper name

Event : Change

Selection Type : Item(s)

Item(s) : P1_SHUTTLE (select your shuttle item)

Condition : is not null

Action : Execute PL/SQL Code

Fire on Page Load : No

PL/SQL Code

BEGIN  
  :P1_TEXT := REPLACE(:P1_SHUTTLE,':',',');  
END;  

Page Items to Submit: P1_SHUTTLE

Page Items to Return: P1_TEXT

Reference :https://community.oracle.com/thread/3786337

Submit and redirect

I was working on a project where i need to use only one button and make some the complex functionality to work together.
Using Apex Oracle 5.0.001.
When ever you want to Update some values in database at the same time you want to redirect you page in application then be prepare for some hard luck to try.
After trying lots of solution i came across this solution which will update values in database as well as redirect page.
i have create page process for it on submit after validation.

Issue: submit page process and page redirect together

Code:

Use of owa_util.redirect_url('f?p=&APP_ID.:73:&APP_SESSION.::NO:::'); apex_application.g_unrecoverable_error := true;

That's All!! yep that's it.