mercredi 11 novembre 2015

Oracle PL SQL Error in IF THEN ELSE for Procedure

I'm trying to create a if then else in my pl sql code but the below receives an error. I currently use the code to create an automated sequence but when I try to insert the below it results in error because O_IDis an integer in the able of Orders. How can I declare a sequence

 set serveroutput ON;
 DECLARE
O_ID orders.o_id%type;
Order_Date orders.order_date%type;
Shipment_Date orders.shipment_date%type;
Delivery_Date orders.delivery_date%type;
Shipping_Method orders.Shipping_Method%type;
Shipping_days shipping.shipping_days%type;
Delivery_days shipping.delivery_days%type;

Regular number := 3;
Express_shipping number := 2;

   Begin
   O_ID := 'orders_seq.NEXTVAL';
   Order_Date := SYSDATE;
   Shipment_Date := SYSDATE;
Delivery_Date := SYSDATE;
   Shipping_Method := 'Regular';

Shipping_days := 3;
Delivery_days := 5;

    IF Shipping_Method = Regular THEN 
        Shipping_Days := 3;
        Delivery_days := 5;

    ELSIF Shipping_Method = Express_shipping THEN 
        Shipping_Days := 1;
        Delivery_days := 2;
    END IF;

   Insert into Orders(O_ID,Order_Date,Shipping_method,Shipment_date,Delivery_date,Shipping_method)
   Values(O_ID,Order_Date,O_ID,Order_Date,Shipping_method,Shipment_date,Delivery_date,Shipping_method);
  COMMIT;   
  dbms_output.put_line('Order ID: ' || O_ID);
END;

Error:

ERROR at line 1: 
 ORA-06502: PL/SQL: numeric or value error: character to number conversion error 
 ORA-06512: at line 26 

Aucun commentaire:

Enregistrer un commentaire