This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Mar 20, 2012 8:10 AM by Tom Petrus RSS

Get Next or Previous Primary Key Value

SeBasTiaan Newbie
Currently Being Moderated
Hi Guys,

I'm using this Interactive Report with a Get Next or Previous Primary Key Value process.
This works fine if you don't filter anything, because it will bring you to the next ID,

BUT, can I make this work so I only get the ID's from the selected rows (using filters)??

It's obvious that you would like to page trough the results of the IR instead of ALL records.

Kind regards,

Bas
  • 1. Re: Get Next or Previous Primary Key Value
    peter_raganitsch Oracle ACE Director
    Currently Being Moderated
    Hi Gus,

    i'm afraid the "Get Next and Previous Key" Process can't work with custom WHERE-Clauses, which is what you would need.

    Instead of that Process create a PL/SQL Process and select the ID values with a statement like this:
    DECLARE
        vPreviousRow NUMBER;
        vNextRow     NUMBER;
        vSeqId       NUMBER        := :P0_SEQ_ID;
        vOrderBy     VARCHAR2(200) := :G_ORDER_BY;
    BEGIN
        EXECUTE IMMEDIATE 'SELECT PREVIOUSROW
                                , NEXTROW
                             FROM (SELECT SEQ_ID
                                        , LAG(SEQ_ID)  over(ORDER BY '||vOrderBy||') PREVIOUSROW
                                        , LEAD(SEQ_ID) over(ORDER BY '||vOrderBy||') NEXTROW
                                     FROM my_whatever_table
                                  )
                           WHERE SEQ_ID = '||vSeqId
                     INTO vPreviousRow, vNextRow;
        --
        :P0_SEQ_ID_PREV := vPreviousRow;
        :P0_SEQ_ID_NEXT := vNextRow;
    END;
    brgds,
    Peter

    -----

    get Syntax Highlighting for the Application Builder: http://apex.oracle.com/pls/apex/f?p=APEX_DEVELOPER_ADDON:ABOUT:0:::::

    Blog: http://www.oracle-and-apex.com
    ApexLib: http://apexlib.oracleapex.info
    BuilderPlugin: http://builderplugin.oracleapex.info
    Work: http://www.click-click.at and http://www.wirsindapex.at
  • 2. Re: Get Next or Previous Primary Key Value
    SeBasTiaan Newbie
    Currently Being Moderated
    Hi Peter,

    Thanks for the reply. Didn't answer earlier because I didn't receive an email.

    This process needs to be on the page where I have my single record view right?
    Instead of the get next or previous pr.....

    Thanks,

    BAs
  • 3. Re: Get Next or Previous Primary Key Value
    SeBasTiaan Newbie
    Currently Being Moderated
    isn't there an other from missing?
  • 4. Re: Get Next or Previous Primary Key Value
    SeBasTiaan Newbie
    Currently Being Moderated
    Hi Peter,

    How to use the :G_ORDER_BY
    Is that a global?

    Gr, Bas
  • 5. Re: Get Next or Previous Primary Key Value
    bjarkekr Newbie
    Currently Being Moderated
    In one application I have actually done this by combining som javascript with a little plsql code.

    What I have done is creating a table with the following columns: tablename, usersession, primarykeyvalue, sequence, time

    Then I have created a button on each page where there is an IR (this is done by a general js file and some help from jquery.)

    When a user clicks the button a js function is called that collects the id from the edit link on the IR. the JS function then calls an application process that stores all the values in the table with the usersession, so I know who saved it. After saving the js sends the user to the detail page with the first item selected.

    The detail page checks if there is anything in the table and then shows some buttons: first, prev, next, last. so the user can browse the results.

    This is all done dynamicly so all pages that has an IR and a detail page has this functionality.
  • 6. Re: Get Next or Previous Primary Key Value
    peter_raganitsch Oracle ACE Director
    Currently Being Moderated
    Sebastian,

    this is just an example, please don't follow it line by line. Try to understand what's happening and adapt it to your environment.

    In my environment i have an Application Item named G_ORDER_BY. You don't need to have that one, you could also have a hardcoded order by or some other source to read that from.

    brgds,
    Peter

    -----

    get Syntax Highlighting for the Application Builder: http://apex.oracle.com/pls/apex/f?p=APEX_DEVELOPER_ADDON:ABOUT:0:::::

    Blog: http://www.oracle-and-apex.com
    ApexLib: http://apexlib.oracleapex.info
    BuilderPlugin: http://builderplugin.oracleapex.info
    Work: http://www.click-click.at and http://www.wirsindapex.at
  • 7. Re: Get Next or Previous Primary Key Value
    Tom Petrus Expert
    Currently Being Moderated
    Hi Sebastiaan,
    I also went through the trouble of the IR+form navigation troubles, and was quite disappointed with the standard Record Navigation. So, in hopes of finding a solution i trawled through google search results :) Thankfully, i found a blog posting by Simon Hunt, where he shares his solution with us ( SHUNT'S Blog: http://simonhunt.blogspot.com/2009/12/next-and-prev-from-interactive-report-2.html ).
    However, it wasn't adequate enough for me, as i have many IRs with columns that have their display values derived from an LOV. His solution is awesome though, lots of pure sql to parse it :) I Then took his code and adapted/adjusted it to my needs. The steps i took are explained in a blog post i made, where the code has been included. http://tpetrus.blogspot.com/2012/03/record-navigation-in-oracle-apex.html
    I also set up a small sample app here: http://apex.oracle.com/pls/apex/f?p=54687:30.
  • 8. Re: Get Next or Previous Primary Key Value
    jariola Guru
    Currently Being Moderated
    Hi Tom,

    Great example! Thanks for sharing.


    Regards,
    Jari

    http://dbswh.webhop.net/dbswh/f?p=BLOG:HOME:0
  • 9. Re: Get Next or Previous Primary Key Value
    SeBasTiaan Newbie
    Currently Being Moderated
    Hi Tom,

    This is great, exactly what I'm looking for.
    Is there a way we can report this feature for future updates in Apex?

    I've installed the package and trying out the page process, but I keep on getting an ORA-00911: invalid character error when executing the package.
    For example, I've tried the following:

    DECLARE
         v_next           VARCHAR2(50);
         v_prev           VARCHAR2(50);
         v_top           VARCHAR2(50);
         v_bot           VARCHAR2(50);
         v_cur_tot      VARCHAR2(50);
         v_debug      VARCHAR2(5000);
    BEGIN
         apex_record_navigation.get_navigation_values
         (
              p_app_id           => 120,
              p_session_id      => 1085157955926700,
              p_column_id          => 'ID',
              p_value               => 134076,
              p_page_id          => 1,
              p_next               => v_next,
              p_prev               => v_prev,
              p_top               => v_top,
              p_bot               => v_bot,
              p_cur_tot          => v_cur_tot,
              p_debug               => v_debug
         );
    END;

    Any ideas on this?

    Thanks so much!

    Regards, Bas
  • 10. Re: Get Next or Previous Primary Key Value
    Tom Petrus Expert
    Currently Being Moderated
    Hi Sebastiaan,

    First of all, does your IR query happen to contain a substitution string, such as #OWNER#? My code won't account for that namely. Any "weird" filtering?

    Further more, what i usually do to debug a problem like this, i go into my package and comment out the "execute immediate" part at the very end of the package. This is to prevent the execution of the built up query and not throw that error.
    Then take that code block and put it in a sql command, while adding
    dbms_output.put_line(v_debug);
    at the end.
    This should give you the complete built up query as a string. Maybe when you look at it, something might catch your eye where it goes wrong. If you do or don't, let us know. Could be something i haven't bumped into yet.

    I know it's not ideal and involves you having to know what you're doing as you are required to actually understand the queries you've written, but oh well :)
  • 11. Re: Get Next or Previous Primary Key Value
    SeBasTiaan Newbie
    Currently Being Moderated
    OMG, it's absolutly working!
    Had some stuff in my query for testing with the rownums etc.

    But it's working like a charm!
    Thank you so much...

    Kind regards, Bas
  • 12. Re: Get Next or Previous Primary Key Value
    SeBasTiaan Newbie
    Currently Being Moderated
    Hi Tom,

    Thanks for the great work.

    I hope you can help me with the following.
    The users are now testing this functionality but they are running into an error / problem.

    When searching an IR, without giving a column name to search in, the IR will search all columns and return all records with for example the word 'house'.

    When redirecting to the edit page with the primary key id of the row, I will get an error: ORA-01403: no data found.

    Only when I click on a row where the word 'house' is found in the NAME column, it brings me to the edit page and navigation will only go thrue records with the word 'house' in the NAME and will skip records where the word 'house' is found in a different column.

    Can you please tell me if I am doing something wrong, or if you experience the same. If I try this in your demo app, it works fine...

    Any ideas?

    Thanks so much!

    Regards, Bas
  • 13. Re: Get Next or Previous Primary Key Value
    SeBasTiaan Newbie
    Currently Being Moderated
    To get a better idea of what I mean, please look at the following screenshots:

    http://ruijt.nl/vara/ir.png

    http://ruijt.nl/vara/query.png

    As you can see, the prev and top and bot id's are incorrect, compared to the IR.

    Thanks!
  • 14. Re: Get Next or Previous Primary Key Value
    Tom Petrus Expert
    Currently Being Moderated
    Hi Bas,

    You're quite lucky as i've spent some more time working on this code. I was just finishing up a new post when i noticed your follow-up here: http://tpetrus.blogspot.com/2012/03/record-navigation-plugged-in.html . I've cleaned my code a bit, changed a couple of things (bind variable parsing), but mainly i added a lot of debug logging for this process. When you now run a page in debug, you should be able to see a lot more happening, plus the final combined select will be there too. I suggest you take a look at it. You don't have to use the plugin, you could re-create the package and then alter your call to the procedure too if that'd suit your better for the moment.
    Go to the page when you get an error, run it in debug, see where it fails. Copy the combined select and take a look at it, see where it falls flat on its face. It could very well be a bug or some error on my part - i'm not faultless unfortunately :-) I've tried this on some pages where i have this process set up, but didn't find anything wrong. Are those columns with 'house' columns based on an lov per chance?
    Let me know what went/goes wrong!

    Edit:
    Wow hey, i did find a bug :) It can go wrong when there are multiple report versions (like a primary and alternative). In my demo app for example, i select the primary report instead of the alternative and do a search for 'one'. If i pick a record with a code <> 'Default', i'll get an ora-01403 since for some reason, the alternative is still being used for the base sql. I'm looking into it, maybe it has to do with your issue aswell?

    Edited by: Tom on Mar 20, 2012 2:36 PM
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points