This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Oct 16, 2012 8: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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you very much for helping me out with my doubts! Oracle forums are indeed very useful!
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points