In Oracle, how do i create stored procedure to insert value in 2 tables

Question

I am trying to insert below details in two table. but it shows error. where i am wrong?

create or replace PROCEDURE ADD_customer_order( 
customer_id in varchar, Shipping_id_arg in number,
order_date_arg in date, Total_price_arg in decimal,
inventory_id_arg in number, order_quantity_arg in number) 
AS
BEGIN
INSERT INTO customer_order (customer_id,Shipping_id,Order_date,total_price) VALUES(customer_id_arg,Shipping_id_arg,order_date_arg, total_price_arg); 
insert into order_details (inventory_id,order_quantity) values(scope_identity(),inventory_id_arg,order_quantity_arg); 
END;

Answer 1

It helps if you format it nicely.

create or replace procedure add_customer_order( 
  customer_id      in varchar2, shipping_id_arg in number,
  order_date_arg   in date    , total_price_arg in decimal,
  inventory_id_arg in number  , order_quantity_arg in number) 
as
begin
  insert into customer_order 
  (customer_id    , shipping_id    , order_date    , total_price) 
  values
  (customer_id_arg, shipping_id_arg, order_date_arg, total_price_arg); 

  insert into order_details 
  (                  inventory_id    , order_quantity) 
  values
  (scope_identity(), inventory_id_arg, order_quantity_arg); 
end;

Doing so, you easily note that the second INSERT is invalid, as you're inserting 3 values into 2 columns:

  insert into order_details 
  (                  inventory_id    , order_quantity) 
  values
  (scope_identity(), inventory_id_arg, order_quantity_arg); 

Either remove scope_identity() (what is it?), or include additional column into the column list you're inserting into.

---

After reading your comment, it seems that returning clause might help. See the following example (somewhat simpler than yours; didn't feel like typing that much). Trigger is used to auto-increment ORDER_ID column. in CUSTOMER_ORDER table (I'm on 11g XE; don't have identity columns here).

SQL> create table customer_order (order_id number, customer_id number);

Table created.

SQL> create table order_details (order_id number, inventory_id number);

Table created.

SQL> create sequence seqo;

Sequence created.

SQL> create or replace trigger trg_co
  2    before insert on customer_order
  3    for each row
  4  begin
  5    :new.order_id := seqo.nextval;
  6  end;
  7  /

Trigger created.

Procedure: note local variable declared in line #4 and returning clause in line #7:

SQL> create or replace procedure p_test
  2    (par_customer_id in number, par_inventory_id in number)
  3  is
  4    l_order_id   customer_order.order_id%type;
  5  begin
  6    insert into customer_order (customer_id) values (par_customer_id)
  7    returning order_id into l_order_id;
  8
  9    insert into order_details (order_id, inventory_id)
 10    values (l_order_id, par_inventory_id);
 11  end;
 12  /

Procedure created.

Testing:

SQL> exec p_test(100, 200);

PL/SQL procedure successfully completed.

SQL> exec p_test (235, 2230);

PL/SQL procedure successfully completed.

SQL> select * From customer_order;

  ORDER_ID CUSTOMER_ID
---------- -----------
         1         100
         2         235

SQL> select * From order_details;

  ORDER_ID INVENTORY_ID
---------- ------------
         1          200
         2         2230

SQL>

The same ORDER_ID value is used in both tables.