4 Replies Latest reply: Mar 9, 2013 10:34 AM by fac586 RSS

    Web PL-sql in APEX

    Mini
      Hi friends,

      How to use web pl-sql with APEX. Generally whose does the web Pl-Sql does.

      In order to use Web pl-sql with APEX what all are the steps i need to follow..

      Thanks

      Regards,
      Mini
        • 1. Re: Web PL-sql in APEX
          jariola
          Hi,

          I can not understand what you mean. APEX is for creating web applications using SQL and PL/SQL.

          Regards,
          Jari
          -----
          My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
          Twitter: http://www.twitter.com/jariolai
          • 2. Re: Web PL-sql in APEX
            Mini
            Hi jari,

            I meaned from the below code like
            /* Formatted on 3/9/2013 6:11:38 PM (QP5 v5.114.809.3010) */
            DECLARE
               l_customer_id   varchar2 (30) := :P11_CUSTOMER_ID;
            BEGIN
               --
               -- display customer information
               --
               sys.HTP.p ('<div class="demoCustomerInfo">');
            
               IF :P11_CUSTOMER_OPTIONS = 'EXISTING'
               THEN
                  FOR x IN (SELECT   *
                              FROM   demo_customers
                             WHERE   customer_id = l_customer_id)
                  LOOP
                     sys.HTP.p ('<div class="demoCustomerInfo">');
                     sys.HTP.p ('<strong>Customer:</strong>');
                     sys.HTP.p ('<p>');
                     sys.HTP.p(   sys.HTF.escape_sc (x.cust_first_name)
                               || ' '
                               || sys.HTF.escape_sc (x.cust_last_name)
                               || '<br />');
                     sys.HTP.p (sys.HTF.escape_sc (x.cust_street_address1) || '<br />');
            
                     IF x.cust_street_address2 IS NOT NULL
                     THEN
                        sys.HTP.p (
                           sys.HTF.escape_sc (x.cust_street_address2) || '<br />'
                        );
                     END IF;
            
                     sys.HTP.p(   sys.HTF.escape_sc (x.cust_city)
                               || ', '
                               || sys.HTF.escape_sc (x.cust_state)
                               || '  '
                               || sys.HTF.escape_sc (x.cust_postal_code));
                     sys.HTP.p ('</p>');
                  END LOOP;
               ELSE
                  sys.HTP.p ('<strong>Customer:</strong>');
                  sys.HTP.p ('<p>');
                  sys.HTP.p(   sys.HTF.escape_sc (:P11_CUST_FIRST_NAME)
                            || ' '
                            || sys.HTF.escape_sc (:P11_CUST_LAST_NAME)
                            || '<br />');
                  sys.HTP.p (sys.HTF.escape_sc (:P11_CUST_STREET_ADDRESS1) || '<br />');
            
                  IF :P11_CUST_STREET_ADDRESS2 IS NOT NULL
                  THEN
                     sys.HTP.p (
                        sys.HTF.escape_sc (:P11_CUST_STREET_ADDRESS2) || '<br />'
                     );
                  END IF;
            
                  sys.HTP.p(   sys.HTF.escape_sc (:P11_CUST_CITY)
                            || ', '
                            || sys.HTF.escape_sc (:P11_CUST_STATE)
                            || '  '
                            || sys.HTF.escape_sc (:P11_CUST_POSTAL_CODE));
                  sys.HTP.p ('</p>');
               END IF;
            
               sys.HTP.p ('</div>');
               --
               -- display products
               --
               sys.HTP.p ('<div class="demoProducts" >');
               sys.HTP.p('<table width="100%" cellspacing="0" cellpadding="0" border="0">
            <thead>
            <tr><th class="left">Product</th><th>Price</th><th></th></tr>
            </thead>
            <tbody>');
            
               FOR c1 IN (  SELECT   product_id,
                                     product_name,
                                     list_price,
                                     'Add to Cart' add_to_order
                              FROM   demo_product_info
                             WHERE   product_avail = 'Y'
                          ORDER BY   product_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_PRODUCT_ID:'
                                                     || sys.HTF.escape_sc (c1.product_id))
                            || ''',''700'',''400'');">'
                            || sys.HTF.escape_sc (c1.product_name)
                            || '</a></td><td>'
                            || TRIM (TO_CHAR (c1.list_price, '999G999G990D00'))
                            || '</td><td><a href="'
                            || apex_util.prepare_url(   'f?p=&APP_ID.:12:'
                                                     || :app_session
                                                     || ':ADD:::P12_PRODUCT_ID:'
                                                     || c1.product_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">Current Order</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   c001 pid,
                                        c002 i,
                                        TO_NUMBER (c003) p,
                                        COUNT (c002) q,
                                        SUM (c003) ep,
                                        'Remove' remove
                                 FROM   apex_collections
                                WHERE   collection_name = 'ORDER'
                             GROUP BY   c001, c002, c003
                             ORDER BY   c002)
                  LOOP
                     sys.HTP.p('<div class="sideCartItem"><a href="'
                               || apex_util.prepare_url('f?p=&APP_ID.:12:&SESSION.:REMOVE:::P12_PRODUCT_ID:'
                                                        || sys.HTF.escape_sc (c1.pid))
                               || '"><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_PRODUCT_ID:'
                                                        || sys.HTF.escape_sc (c1.pid))
                               || ''',''700'',''400'');">'
                               || sys.HTF.escape_sc (c1.i)
                               || '</a>
                <span>'
                               || TRIM (TO_CHAR (c1.p, '$999G999G999D00'))
                               || '</span>
                <span>Quantity: '
                               || c1.q
                               || '</span>
                <span class="subtotal">Subtotal: '
                               || TRIM (TO_CHAR (c1.ep, '$999G999G999D00'))
                               || '</span>
            </div>');
                     c := c + 1;
                     t := t + c1.ep;
                  END LOOP;
            
                  sys.HTP.p ('</tbody></table>');
            
                  IF c > 0
                  THEN
                     sys.HTP.p('<div class="sideCartTotal">
                <p>Items: <span>' || c
                               || '</span></p>
                <p class="sideCartTotal">Total: <span>'
                               || TRIM (TO_CHAR (t, '$999G999G999D00'))
                               || '</span></p>
            </div>');
                  ELSE
                     sys.HTP.p (
                        '<div class="alertMessage info" style="margin-top: 8px;">'
                     );
                     sys.HTP.p ('<img src="#IMAGE_PREFIX#f_spacer.gif">');
                     sys.HTP.p ('<div class="innerMessage">');
                     sys.HTP.p ('<h3>Note</h3>');
                     sys.HTP.p ('<p>You have no items in your current order.</p>');
                     sys.HTP.p ('</div>');
                     sys.HTP.p ('</div>');
                  END IF;
               END;
               sys.HTP.p ('</div>');
            END;
            As the above code, uses htp.p pkg and also some html codes in pl-sql, i called that like web-plsql...

            Regards,
            Mini
            • 3. Re: Web PL-sql in APEX
              AndyPol
              Hi

              Of course you can use htp.p in APEX for example into "PL/SQL Dynamic Content" region.

              Regards
              • 4. Re: Web PL-sql in APEX
                fac586
                Mini wrote:

                I meaned from the below code like
                ...
                As the above code, uses htp.p pkg and also some html codes in pl-sql, i called that like web-plsql...
                Why would you want to do that? The same result can be achieved using standard APEX components&mdash;reports and custom templates&mdash;using far less code, and with far better separation of concerns and potential reusability.