title: Data not added accurately through stored procedure (Oracle 11g R2)
body: <p>I have a temp table, data in which is loaded through sqlldr. The data in the temp table is added as follows:</p>
  
  <pre><code>PHONE       ADDRESS     ORDER_ID
  9971410000  D-166 GF    1
  9910020000  H-12D       2
  9873120000  K-19C       3
  9811120000  J-200       4
  9873120000  K-19C       5
  9810320000  N-29B       6
  9810390000  J-235 GF    7
  9873500000  M-39        8
  8447910000  J-62 GF     9
  9873120000  K-19C       10
  </code></pre>
  
  <p>After the data is inserted into the 'temp' table, a trigger calls a procedure to insert into other tables:</p>
  
  <pre><code> create or replace TRIGGER insert_customer
   AFTER INSERT ON temp
   FOR EACH ROW
   BEGIN
     insert_address(:new.phno, :new.addr, :new.ord_id);
   END;
  </code></pre>
  
  <p>The procedure is as follows:</p>
  
  <pre><code>   create or replace procedure INSERT_ADDRESS(
     p_cust_phone customer.cust_phone%type, 
     p_address address.address%type, 
     p_ord_id customer_order.order_id%type
     )
     is
     l_cust_id customer.cust_id%type;
     l_address_id address.address_id%type;
     begin
       begin
        select cust_id 
        into l_cust_id 
        from customer c
        where c.cust_phone=p_cust_phone;
      exception
        when no_data_found then
        insert into customer 
        values (cust_id_seq.nextval, 'No Name', p_cust_phone)
        returning cust_id into l_cust_id;
      end;
  
        select address_id
        into l_address_id
        from address a
        where a.address=p_address;
    exception
        when no_data_found then
        insert into address
        values (address_id_seq.nextval, UPPER(p_address), UPPER(p_area))
        returning address_id into l_address_id;
  
    insert into customer_address
    values (l_cust_id, l_address_id);
  
    insert into customer_order (order_id, cust_id)
    values (p_ord_id,l_cust_id);
  
    end;
  </code></pre>
  
  <p>The data inserted in the CUSTOMER_ORDER table is not correct. The result I get on the following query is listed below:</p>
  
  <pre><code>select * from customer_order;
  
  ord_id   cust_id
  1         1
  2         2
  3         3
  4         4
  6         5
  7         6
  8         7
  9         8
  </code></pre>
  
  <p>The order_id (5,10) are lost since they come from the same customer as ord_id=3. Multiple orders by the same customer are not added in the customer_order table. I am not able to understand where did I make a mistake?</p>