1 2 Previous Next 17 Replies Latest reply: Oct 16, 2012 10:21 AM by 967000 Go to original post RSS
      • 15. Re: Is there a datatype that allows me to store more than one item at a time
        967000
        i will be searching for a particular item from inventory an then adding it to purchases table(filling columns of itemname, itemprice, itemquantity) but then how do i know which custid to get into it? Do i have to manually enter custid before in a textbox?
        • 16. Re: Is there a datatype that allows me to store more than one item at a time
          Purvesh K
          Yukta Lolap wrote:
          i will be searching for a particular item from inventory an then adding it to purchases table(filling columns of itemname, itemprice, itemquantity) but then how do i know which custid to get into it? Do i have to manually enter custid before in a textbox?
          Logically, you will be having a Page which will consist of the Items that can be purchased.

          User shall be logging in to make a purchase; When user selects an Item and enters a Quantity, it shall be passed from the Front End (Java/.Net/JS etc.) alongwith the Customer ID and this will make the insert procedure look like below:
          create or replace function insert_purchase
          (
            p_customer_login      in      varchar2,
            p_purchase_lot        in      varchar2,
            p_item_code           in      varchar2,
            p_item_quantity       in      varchar2,
            p_payment_mode        in      varchar2
          )
          return varchar2
          is
            l_items       inventory%rowtype;
          begin
            select *
              into l_items
              from inventory
             where item_code = p_item_code;
             
            insert into purchases
            (
              purchase_id,
              purchase_lot,
              customer_id,
              item_code,
              item_price,
              item_quantity,
              discount,
              purchase_date,
              payment_mode
            )
            values
            (
              (select purchase_sequence.nextval from dual),
              p_purchase_lot,
              p_customer_id,
              p_item_code,
              l_items.item_price,
              p_item_quantity,
              l_items.discount,
              sysdate,
              p_payment_mode
            );
          exception
          when too_many_rows then
            log_proc(sqlerrm);      --> Procedure that executes in Autonomous Transaction mode to log errors
            raise;
          end insert_purchase;
          Assumptions:-
          1. You will have to check the pre-requisites before-hand viz. the Input of Quantity greater than that is present in Inventory.
          2. Error Logging
          3. Exception Handling and Transaction Completion (i.e. Commit/Rollback) to be included in parent procedure/function or if required to be handled in the same procedure.
          • 17. Re: Is there a datatype that allows me to store more than one item at a time
            967000
            Thank you very much for helping me out with my doubts! Oracle forums are indeed very useful!
            1 2 Previous Next