14 Replies Latest reply: Feb 27, 2014 3:09 AM by Elco RSS

    PL / SQL dynamic content

    Elco

      Hello,

       

      I have now a working PL / SQL script (see below). I can Add items with this like office and Iphone (hardware, software, communication etc.)

       

      Now I want to sort the items on category. Does anybody know how I can make this in my PL/SQL code? I'm new with pl/sql...

       

      APEX version 4.2

      In the table Items I got Item_ID, Item_name and Item_category

       

      I hope somebody can help my in the good direction

       

       

      Thanks!

       

       

       

       

      declare

        l_employee_number varchar2(30) := :P7_EMPLOYEE_NUMBER;

      begin

       

       

      sys.htp.p('<div class="Userinfo">');

      for x in (select * from APEX_EBS_ACtive_list where employee_number = l_employee_number ) loop

          sys.htp.p('<div class="Userinfo">');

          sys.htp.p('<strong>User:</strong>');

          sys.htp.p('<p>');

          sys.htp.p(apex_escape.html(x.first_name) || ' ' || apex_escape.html(x.last_name) || '<br />');

          sys.htp.p(apex_escape.html(x.employee_number) || '<br />');

          sys.htp.p('</p>');

        end loop;

      sys.htp.p('</div>');

      sys.htp.p('</div>');

       

       

      sys.htp.p('<div class="Items" >');

      sys.htp.p('<table width="100%" cellspacing="0" cellpadding="0" border="0">

      <thead>

      <tr><th class="left"></th><th></th><th></th></tr>

      </thead>

      <tbody>');

      for c1 in (select it.item_id, it.item_name, 'Add to Cart' add_to_order

      from items it

      where 1=1

      and it.item_id not in (select item_id

                      from user_items

                      where 1=1

                      and employee_number = :p7_employee_number)

      order by item_name) loop

         sys.htp.p('<tr><td class="left"><a href="javascript:popUp2('''||apex_util.prepare_url('f?p=&APP_ID.:20:'||:app_session||':ADD:::P20_item_ID:'||

              apex_escape.html(c1.item_ID))||''',''100'',''400'');">' || apex_escape.html(c1.item_name)||'</a></td><td>'||

              '</td><td><a href="'||apex_util.prepare_url('f?p=&APP_ID.:12:'||:app_session||':ADD:::P12_item_ID:'||

              c1.item_ID)||'" class="uButton uAltButton iconButton plus"><span>Add<i class="iR"></i></span></a></td></tr>');

      end loop;

      sys.htp.p('</tbody></table>');

      sys.htp.p('</div>');

      --

      -- display current order

      --

      sys.htp.p('<div class="demoProducts" >');

      sys.htp.p('<table width="100%" cellspacing="0" cellpadding="0" border="0">

      <thead>

      <tr><th class="left"></th></tr>

      </thead>

      </table>

      <table width="100%" cellspacing="0" cellpadding="0" border="0">

      <tbody>');

       

       

      declare

          c number := 0; t number := 0;

      begin

      -- loop over cart values

      for c1 in (select it.item_name , it.item_id ,    'Remove' remove

      from user_items ui,

      items it

      where l_employee_number = ui.employee_number

      and ui.item_id = it.item_id

      order by it.item_name)

      loop

      sys.htp.p('<div class="sideCartItem"><a href="'||

          apex_util.prepare_url('f?p=&APP_ID.:12:&SESSION.:REMOVE:::P12_item_ID:'||apex_escape.html(c1.item_id))||

          '"><img src="#IMAGE_PREFIX#delete.gif" alt="Remove from cart" title="Remove from cart" /></a>  

          <a href="javascript:popUp2('''||apex_util.prepare_url('f?p=&APP_ID.:20:'||:app_session||':ADD:::P20_item_id:'||

              apex_escape.html(c1.item_id))||''',''700'',''400'');">'||apex_escape.html(c1.item_name)||'</a>

       

       

       

       

      </div>');

       

       

      end loop;

      sys.htp.p('</tbody></table>');

       

       

       

       

      end;

      sys.htp.p('</div>');

      end;

        • 1. Re: PL / SQL dynamic content
          Kofi

          Hi Elco, I don't know if you have to be so dynamic for a shopping cart. Can you put up a running version on apex.oracle.com?

          There's probably a simpler way to achieve your goals involving reports, collections, dynamic actions and other goodies.

          Kofi

          • 2. Re: PL / SQL dynamic content
            Elco

            Hi Kofi,

             

            I will try to build it tomorrow in the apex.oracle.com website.

             

            Regards
            Elco

            • 3. Re: PL / SQL dynamic content
              Elco

              Hi Kofi,

               

              How do you think it can simpeler?

               

              Regards

              Elco

              • 4. Re: PL / SQL dynamic content
                Elco

                Here is the information :

                 

                WORKSPACE DP_TEST

                USERNAME           test

                Password               123456

                • 5. Re: PL / SQL dynamic content
                  fac586

                  Elco wrote:

                   

                  Here is the information :

                   

                  WORKSPACE DP_TEST

                  USERNAME           test

                  Password               123456

                  Application, page, and region?

                  • 6. Re: PL / SQL dynamic content
                    Kofi

                    In addition to what fac586 asked, I can't seem to be able to login using those credentials. Am I missing something?

                    • 7. Re: PL / SQL dynamic content
                      Elco

                      Some buttons and pages are not working in this version

                       

                      I can log in with

                       

                      WORKSPACE                         DP_TEST

                      username                                test

                      password                                123456

                       

                      page 12 is all about

                      application februari 2014

                      region body 3


                      THNX

                      • 8. Re: PL / SQL dynamic content
                        Kofi

                        So looking at the region, I don't see why it can't be a report with buttons to add and remove stuff. Is there a particular reason why it all has to be dynamic?

                        So are you always going to navigate from page 7 to 12?

                        For the items you are displaying do you need a single employee? Or you can pick for multiple employees directly on page 12?

                        Either way, a report that filters on the items should work.  Is it because of the look that you went dynamic?

                        Kofi

                        • 9. Re: PL / SQL dynamic content
                          Kofi

                          Elco, Page 50 should give you some ideas about how to get started. Depending on your use case, I would strongly consider collections as well.  I have created an application process as well as a javascript function.

                          It's very rough but it should give you some ideas.

                          Kofi

                          • 10. Re: PL / SQL dynamic content
                            Elco

                            Hi Kofi,

                             

                            Thanks! I looks so easy when you compare it with the dynamic version! This is what I want (on 1 page the select user and select items)

                             

                            what do you mean with collections?

                             

                            Regards

                            Elco

                            • 11. Re: PL / SQL dynamic content
                              Kofi

                              Hi Elco, read these;

                              17.4 Using Collections

                              http://nyoug.org/Presentations/2011/March/Millinger_Collections_in_Oracle_APEX.pdf

                               

                              The one second version is as follows;

                              Basically in a shopping cart type application, typically you put items in a cart and then checkout when you are ready to actually purchase. So for an item ordering system, the end user might not necessarily want all the items they have chosen when they see the potential total bill and all that. So a collection serves almost like a temporary table. You then actually store those orders in your actual ordering table when the user 'Finalizes' the purchase.  They are great for maintaining session-related data which is really what a shopping application is all about.

                              If you're new to Apex, I would suggest getting a good book as well. I can recommend the "Expert Oracle Application Express" book. It has a lot of real-life tips.

                              Get the concepts right and the syntax will follow.

                              Kofi

                              • 12. Re: PL / SQL dynamic content
                                Elco

                                Sorry Kofi but What you have done on page 50 I can't remake that.. I get an java error see page 100...

                                 

                                Can you explain what i did wrong?

                                • 13. Re: PL / SQL dynamic content
                                  Kofi

                                  Elco, I don't see your java error. What is it?

                                  I would actually, seeing as you're new to this, change the button to submit the page instead of dynamically refreshing the region.

                                  Cheers

                                  Kofi

                                  • 14. Re: PL / SQL dynamic content
                                    Elco

                                    Kofi,

                                     

                                    Indeed there is here no Java error... the Java error is at my own APEX.. on this site there is no error ( I have changed the button options but there happens nothing)

                                     

                                    At my own APEX version I have preciesly take the same page number, page items and the java code.. but there I see a Java error and here page 100 is not working for some reason..

                                     

                                    Thanks in advanged