1 2 Previous Next 15 Replies Latest reply: Apr 9, 2013 3:32 AM by user134954 RSS

    Computation or Process ?

      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

        • 1. Re: Computation or Process ?

          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) 
          • 2. Re: Computation or Process ?
            Howard (... in Training)

            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.

            • 3. Re: Computation or Process ?
              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:

              • 4. Re: Computation or Process ?
                I am close now. If i try a computation return colon seperated values

                Select distinct TEST
                from TESTSTABLE where TEST
                in (SELECT TEST
                  FROM TESTTABLE
                 WHERE INSTR (
                          ',' || 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
                • 5. Re: Computation or Process ?
                  Howard (... in Training)
                  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
                  • 6. Re: Computation or Process ?
                    Something like
                    select distinct test
                    from testtable
                    where instr( replace( P4_LINIE, '-9' ) || ':', ':' || test || ':' ) > 0 
                    • 7. Re: Computation or Process ?
                      Howard (... in Training)
                      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.

                      • 8. Re: Computation or Process ?

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

                        the result will only be TEST1
                        • 9. Re: Computation or Process ?

                          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....

                          my URL shows :P35_TEST:TESTVALUE1:TESTVALUE2

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

                          Dont know why.
                          • 10. Re: Computation or Process ?
                            Howard (... in Training)
                            In the URL, the first ":" terminates the value passed to :P35_ITEM. So all it parses out is "TESTVALUE1".

                            • 11. Re: Computation or Process ?
                              Howard (... in Training)
                              If we don't pass the value to page 35 but instead only reference the value from page 4, we won't have a URL problem. But for your processing on page 35, do you need the ":" replaced by "," anyway? I assume the answer is yes.

                              • 12. Re: Computation or Process ?
                                Yes. The final thing should be an interactive report with many variables in the url.

                                Thats the only way to save the IR. So the variables are set as IR-Filters.

                                Because of that, the URL should hold all variables with a ",".

                                I have already tried to reference the variables and this works. But I need them in my URL.
                                • 13. Re: Computation or Process ?
                                  Howard (... in Training)
                                  ??? Okay. This shows how to remove the "9" and replaces other ":" by ",". What else do you need?
                                  select REPLACE( REPLACE('-9:TEST1:TEST2:TEST3', '-9:', ''), ':', ',') from dual; 
                                  • 14. Re: Computation or Process ?

                                    You mean like [url http://apex.oracle.com/pls/apex/f?p=VANBAREN_FORUM_TRY_OUT:CHECKBOX1&c=VANBAREN]this.

                                    This method uses a hidden item to store the string with comma's.
                                    A computation that replaces the *:* with *,* in the checkbox item and sets the result in the hidden item.
                                    And a branch that sets the items on the recivieving page and submits the hidden item.

                                    Try out the [url http://apex.oracle.com/pls/apex/f?p=VANBAREN_FORUM_TRY_OUT:CHECKBOX1&c=VANBAREN]demo and see the consequence of not checking all the options.

                                    1 2 Previous Next