1 2 Previous Next 16 Replies Latest reply: Mar 26, 2014 9:09 AM by Mike Kutz RSS

    How to get item values form shuttle

    jerry44

      Hi, how to get values returned by shuttle, in list of values i'm returning id's of items? I want to obtain returned values from a trailing list, in Application Express 4.0.2. Is there easier way than this Shuttle Leading-Trailing Problem? I don't understand what is EO, VO and in general how to use this code.

        • 1. Re: How to get item values form shuttle
          Stewed

          Check out apex_utils.string_to_table. This will convert the shuttle bod data into an array.

          • 2. Re: How to get item values form shuttle
            Stewed

            Sorry, missed a typo.  The package name is apex_util, not as shown above.

            • 3. Re: How to get item values form shuttle
              Nicolette

              user8851646

               

              The reason you don't understand EO and VO is that the tread you reference belongs to the place/forum E-Business Suite > Technology - OA Framework which is a completely different tool/ framework.

               

              The values of a shuttle are stored in a string of return values where the different returns values are separated by a : .

              So if the options with the return values 10, 30 and 70 are selected the page item value will be 10:30:70.

              As Stewed mentioned you can turn the string into an array with apex_util.string_to_table for easier processing.

               

              If you mean getting the value in javascript use apex.item(pNd).getValue(). This will return an array of the selected options.

               

              Nicolette

              • 4. Re: How to get item values form shuttle
                jerry44

                I need a concrete solution. I'm creating reservation page and the functionality I want to have is counting final price. On page I've item with ID of selected client and car. Car has price per day and options too. But options that can be choosen are in a shuttle. I want to calculate price for selected items from trailing list (right box in shuttle - the choosen options) and use it to general calculation. (Price of car + price of options)  * number days of rent. I want to put calculated value into label item after clicling button "check the price". If client agrees for price I can put data of reservation into database, if not change options and re-calculate. If client agrees save the reservation, if not cancelation.

                • 5. Re: How to get item values form shuttle
                  Nicolette

                  User8851646

                   

                  Showing the total option price using a page item. Use a computation or even the source of the page item with type PL/SQl function body.

                  And the following code. Keep in mind that the names of the page item and table are made up. You have to adapt the code to your specifications.

                  declare
                      cursor c_option(b_option_id in number)
                      is
                        select opt.price
                        from   app_options opt
                        where  opt.id = b_option_id
                      ;
                    
                      l_vc_arr2     apex_application_global.vc_arr2;
                      l_total_price number := 0;
                      col_opt_price   app_options.price%type;
                    
                  begin
                      l_vc_arr2 := apex_util.string_to_table(:P1_SHUTTLE);
                      for z in 1..l_vc_arr2.count loop
                        open  c_option(to_number(l_vc_arr2(z)));
                        fetch c_option
                        into  c_opt_price;
                        close c_option;
                        l_total_price := l_total_price + col_opt_price;
                      end loop;
                      return l_total_price;
                  end;
                  
                  

                  Nicolette

                  • 6. Re: How to get item values form shuttle
                    jerry44

                    Thank You Nicolette, Your answer is helpful and I need to ask You few questions to make sure that I understood You correctly:

                     

                     

                     

                    app_options is the table with all avaiable options?

                     

                    What does block located in line 10? Is it declaration of in-built apex type?

                     

                    Shouldn't be cursor oppened end closed outside the for loop block?

                     

                    Which lines guarantees loading data from Trainling list?

                     

                     

                     

                    There last one problem, when I'm saving the data of reservation there is needed to get ID's of item selected to trailing list [not only pirces] and put them in the table that contains details of reservation option.

                     

                     

                    And the second is that when opening rental using reservation I'll need to load them [details of reservation options]. Is there possible to load page with shuttle where leading list will contain the same avaiable options and there will be preselected data from reservation in trailing list? If clients decides to change options there have to be possibility to change. If nothoing changes ID's loaded to trailing list should be saved in details of rent.

                     

                     

                    How to solve last 2 problems?

                    • 7. Re: How to get item values form shuttle
                      Mike Kutz

                      Ummm... no.

                      open cursor... search one row... get result...  close cursor... repeat ---> bad design.  You're not thinking in SETS of data

                       

                      Again, this is a "Varying IN List" problem.

                      (I just answered this here:  Re: Paste from spreadsheet searchbox  )

                      The key to the answer is on Tom Kyte's Blog here:  The Tom Kyte Blog: Varying in lists...

                       

                      Just replace the commas ',' with colons ':'

                      And... don't forget, SUM() is part of the SQL language.

                       

                       

                      declare
                        l_total_price number;
                      begin
                        with data 
                        as 
                        ( 
                          select    trim( substr (txt, 
                          instr (txt, ':', 1, level  ) + 1, 
                          instr (txt, ':', 1, level+1) 
                          - instr (txt, ':', 1, level) -1 ) ) 
                          as token 
                          from (select ':'||:P1_SHUTTLE||':' txt 
                          from dual) 
                          connect by level <= 
                          length(:P1_SHUTTLE)-length(replace(:P1_SHUTTLE,':',''))+1 
                        ) 
                        select sum( opt.price )
                          into l_total_price
                        from app_options opt
                          join data d on (OPT.id=to_number(D.token));
                      
                        return l_total_price
                      exception
                        when no_data_found then
                          return 0;
                      end;
                      
                      • 8. Re: How to get item values form shuttle
                        Nicolette

                        user8851646

                         

                        As stated the previous provided code example calulates the total price of the selected options.

                        It is a show case of how to use apex_util.string_to _table to convert a string of id's to a table so the id's can be processed.

                        It is not the answers to the situation you described in your post of Mar 14, 2014 but it should be enough for you to code that yourself.

                         

                        Which lines guarantees loading data from Trainling list?

                        The example doesn't load data other then getting the price of an option. Secondly what do you mean by Trainling list as this is the first you use that term.

                         

                        There last one problem, when I'm saving the data of reservation there is needed to get ID's of item selected to trailing list [not only pirces] and put them in the table that contains details of reservation option.

                        Replace the part that gets the price and the calculation of the total in the example with an insert statement and you have your basis dml code.

                         

                        And the second is that when opening rental using reservation I'll need to load them [details of reservation options]. Is there possible to load page with shuttle where leading list will contain the same avaiable options and there will be preselected data from reservation in trailing list? If clients decides to change options there have to be possibility to change. If nothoing changes ID's loaded to trailing list should be saved in details of rent.

                        To pre select a number of options the source of the shuttle item has the be string of the id's delimited by : .

                        An example of how the calculate the string of id's

                        SELECT LISTAGG(empno, ':') WITHIN GROUP (ORDER BY empno) AS employees

                        FROM   emp

                        where  deptno = 10;

                         

                        app_options is the table with all avaiable options?

                        Yes

                         

                        What does block located in line 10? Is it declaration of in-built apex type?

                        From the documentation of sting_to_table

                        PL/SQL array of type APEX_APPLICATION_GLOBAL.VC_ARR2. This array is a VARCHAR2(32767) table.

                         

                        Shouldn't be cursor oppened end closed outside the for loop block?

                        No because you loop trough the selected options that are in the varray. And for every id you fetch the price of that id.

                         

                        Nicolette

                        • 9. Re: How to get item values form shuttle
                          jerry44

                          I've edited your code and below is the result:

                           

                          declare

                              cursor c_option(b_option_id in number) 

                              is 

                                select opcje.cena      //price column

                                from   opcje

                                where  opcje.id_opcji = b_option_id;       //  option_id column

                               

                              l_vc_arr2     apex_application_global.vc_arr2;

                              l_total_price number := 0;

                              col_opt_price   opcje.cena%type;

                              

                          begin 

                              l_vc_arr2 := apex_util.string_to_table(:P110202_SHUTTLE); 

                              for z in 1..l_vc_arr2.count loop 

                                open  c_option(to_number(l_vc_arr2(z));

                                fetch c_option

                                into  col_opt_price;     //here you missed 2 letters

                                close c_option;

                                l_total_price := l_total_price + col_opt_price;

                              end loop;

                              return l_total_price;

                          end;

                           

                          After I put code into settings block in editing page "PL/SQL Code which emits HTML" section, after i apllied changes there appeared such error

                           

                          "1 error has occurred

                          • ORA-06550: line 17, column 45: PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( ) , * % & = - + < / > at in is mod remainder not rem => <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec as between from using || multiset member submultiset The symbol ")" was substituted for ";" to continue."

                           

                          Trailing list relates to right box of shuttle list  as I posted before:

                          " I want to calculate price for selected items from trailing list (right box in shuttle - the choosen options) and use it to general calculation. (Price of car + price of options)  * number days of rent."

                           

                          Mike Kutz, I will check your solution tomorrow. Today i have not enough time. Thanks

                          • 10. Re: How to get item values form shuttle
                            Nicolette

                            user8851646

                             

                            user8851646 wrote:

                             

                            After I put code into settings block in editing page "PL/SQL Code which emits HTML" section, after i apllied changes there appeared such error

                             

                            "1 error has occurred

                            • ORA-06550: line 17, column 45: PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( ) , * % & = - + < / > at in is mod remainder not rem => <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec as between from using || multiset member submultiset The symbol ")" was substituted for ";" to continue."

                            You are probably getting the error because you are using a function while a procedure is expected. The given code presumes the total price of the options is stored in a page item.

                             

                            Trailing list relates to right box of shuttle list  as I posted before:

                            " I want to calculate price for selected items from trailing list (right box in shuttle - the choosen options) and use it to general calculation. (Price of car + price of options)  * number days of rent."

                            If the calculation of the price of the selected options is really your only question than please go with the select statement Mike Krutz has given.

                            Mine code example was given as an example of how the use apex_util.string_to_table. As that is what I took from your second post you wanted an example of.

                             

                            Nicolette

                            • 11. Re: How to get item values form shuttle
                              jerry44

                              I don't understand the code you've put in 7. I guess that lines from 08. trim to 11. are about cutting the data selected from shuttle.

                              You've written "Just replace the commas ',' with colons ':' " every coma in code You posted, replace with colon ? I doubt. Maybe I don't understand you properly.

                               

                              What meas line 15? How to connect it with the context it is put?

                               

                              Does the lines 07-17 define select as identifier "data" ?

                               

                              In line 21 data "d" is the same d as in D.token? I mean "D"

                               

                              Could You describe shortly what this code does?

                              • 12. Re: How to get item values form shuttle
                                Mike Kutz

                                You really truly need to understand SQL (and PL/SQL) in order to get the most out of APEX...

                                You should read the concepts guide up to, through and past this chapter:  SQL

                                If you have questions regarding SQL or PL/SQL, feel free to post up in the "SQL and PL/SQL" forum.

                                 

                                Text Replacement

                                My sample code had already replaced the ',' parts with ':' parts (the parts to be replaced include the single quotes... this value defines what your delimiter is).

                                If you see my other thread, you'll notice that the delimiter being used is: chr(10) -- this is the ASCII code for "new line"

                                I've seen strings where the pipe symbol '|'  was used.

                                 

                                Line 07-17

                                yes, line 07-17 define the SELECT as the identifier data.  This is called "sub-query refactoring".

                                I prefer to use this as it gives me the opportunity to include some meaning behind the SELECT statement.

                                 

                                 

                                Line 15 -- CONNECT BY

                                The basis of this process (07-17) comes from a "cheat" for generating numbers 1 through n:

                                select level column_name -- LEVEL is an Oracle keyword, therefore, we need to rename the column
                                from dual
                                connect by level <= n
                                

                                 

                                Line 16 calculates n

                                 

                                The CONNECT BY clause is an advanced SQL clause which is very tough to explain without diagrams.

                                key words for google:  hierarchical query

                                 

                                short description of code

                                The entire code:

                                • takes your input string and converts it into a table called "data" (lines 05-17)
                                • joins that table to the original table of question (lines 20-21).
                                • sum() up all the PRICE values (line 18)
                                • and stores the result INTO the pl/sql variable l_total_price  (line 19)
                                • since this is a function, you need to RETURN that value. (line 22)
                                • oh.. and if the SELECT statement finds nothing that matches (which is considered an error within Oracle), return 0 (lines 23 - 25)

                                 

                                data D

                                D is an alias for "data" (the "table" we define in lines 05-17)

                                For the most part, Oracle's SQL is case insensitive. d is the same as D

                                Table/column names becomes case sensitive when you surround them with double quotes

                                d is the same as D is the same as "D" .. but all are different than "d".

                                 

                                MK

                                • 13. Re: How to get item values form shuttle
                                  DTSIguy

                                  You're getting the error on this line :

                                   

                                  open  c_option(to_number(l_vc_arr2(z));

                                   

                                  You have three open parens "(" and only two closing...

                                  • 14. Re: How to get item values form shuttle
                                    jerry44

                                    Thanks, You're right. Sometimes is hard to see obvious mistakes.

                                    1 2 Previous Next