Monday 31 August 2015

Find out Missing Numbers in a sequence

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
  1. BEGIN  
  2.   :P1_TEXT := REPLACE(:P1_SHUTTLE,':',',');  
  3. 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.

Sunday 30 August 2015

How to change logo in apex oracle?

Change logo(image) in apex oracle 5.0(to display in every pages of application)


Application(Working)->Shared components -> Static files(application/workspace)->Upload Image.




After Successfully uploading  image (in application or workspace any you can select to store image )  


Select path of that image that you have uploaded
After copying particular path just paste it in below location(Application Logic- > Application Definition Attribute - > User Interface).
(same page)
Apply Changes and run.
Youtube : https://youtu.be/BkQVrjXPj74

Show values in right side of shuttle

While working with select list and shuttle, when we want to display values into right side of shuttle depending upon selection from select ...