Forum Stats

  • 3,852,528 Users
  • 2,264,112 Discussions


Computation or Process ?

user134954 Member Posts: 95
edited Apr 9, 2013 4:32AM in APEX Discussions
Hi there,

I have the following values in my variable :P4_Linie


Now I know how to convert the ":" into "," and how to replace the -9. The -9 is the "all selector" of a checkbox pluging meaning if all checkboxes are checked the value -9 is given before the checkboxes data so sometimes it is there and sometimes not.

I know how to do this in a select statement:
SELECT *  FROM mv_auswertung WHERE INSTR (
          REPLACE (:P4_LINIE, '-9', '')
          ':'||Linie||':') > 0
Now I get TEST1,TEST2,TEST3 and now I can use this in a select into query.

But I need apex to do this via PL/SQL process or computation so the item is given to the next page in my items to submit.

I need to submit TEST1,TEST2,TEST3 for my IR. Hoping you can help.

Thx in advance



  • Nicolette
    Nicolette Member Posts: 526 Silver Badge

    Replace is also a pl/sql function.
    If the new value needs to be submitted you would need a page item to hold that value.

    The computation would then belong to the new item and be
    return REPLACE (:P4_LINIE, '-9', '');
    In a procedure it would be
    :P4_new_item := REPLACE (:P4_LINIE, '-9', '');
    If you have apex 4.2 then you can define the new item to be submitted, on the refresh of a region, is a region setting.

    Just remember that the value of a page item is always a string.
    So something like this will not work
    select *
    from   some_table
    where  some_column in(:p4_new_item) 
  • Thorsten,

    Hmm. I have a few ideas, as well.

    Do you want the value of :P4_LINIE actually changed to the new value without the "-9:" on page 4 or do you just want the new/changed value available to the new page?

    (Related question) Do you want the computation to happen before page 4 is displayed or just before transferring to the new page?

    Do you have a Submit button to get to the next page; what's the mechanism for transferring to the new page?

    There are two (or more) ways to do this.
    -- 1) Merely reference/use :P4_LINIE on the new page? But I think we have to ensure :P4_LINIE is saved in the session state for this to work.
    -- 2) Pass :P4_LINIE to :Px_LINIE on the transfer to page x.

    But I need to know how you (want to) transfer to the new page.

  • user134954
    user134954 Member Posts: 95
    Hi Howard,

    I need to transfer the selected items so I can use them in a "select into" query.

    -9 is the "all selector" and should be cut if "all" is selected.

    Just check:

    If I continue to the next page the value selected should be used in an interactive report via URL.

    So my URL should not be like :


    but like:

  • user134954
    user134954 Member Posts: 95
    I am close now. If i try a computation return colon seperated values

    Select distinct TEST
    from TESTSTABLE where TEST
              ',' || TRANSLATE (REPLACE (:P35_TEST, '-9', ''), ':', ',') || ',',
              ','||TEST||',') > 0)
    The URL now cuts the -9 BUT the values in the URL are still shown as

    TEST1:TEST2 not as TEST1,TEST2
  • Howard (... in Training)
    Howard (... in Training) Member Posts: 905
    edited Mar 28, 2013 9:25AM
    I always start with a simple-minded approach. (When one has a simple mind, ....)

    Corrections needed. A couple moments please.


    Edited by: Howard (... in Training) on Mar 28, 2013 9:24 AM
  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    Something like
    select distinct test
    from testtable
    where instr( replace( P4_LINIE, '-9' ) || ':', ':' || test || ':' ) > 0 
  • Let's try this again:

    How about:
    This assumes you set up a button to do the transfer:
    Under Action When Button Clicked, select "Page in this Application", and put Page 35.
    Under "Set These Items" put P35_LINIE (Note: No colons, ampersands, or periods in the item name here.)
    Under "With These Values" put &P4_LINIE. (Note: use & and . here.)

    I shy away from coding parameters -- all but the simplest cases -- in URLs.

  • user134954
    user134954 Member Posts: 95

    this will just give me the first value of the string. If its TEST1:TEST2:TEST3

    the result will only be TEST1
  • user134954
    user134954 Member Posts: 95

    the Plugin uses : as delimiter. So the values will be look like TEST1:TEST2. I want them to be TEST1,TEST2


    I now got the values in my URL....


    But if try
    Select :P35_TEST from dual 
    I only get TESTVALUE1 ....

    Dont know why.
  • In the URL, the first ":" terminates the value passed to :P35_ITEM. So all it parses out is "TESTVALUE1".

This discussion has been closed.