July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.
CREATE OR REPLACE PACKAGE Process_Orders AS PROCEDURE Add_Order(p_cno IN orders.cno%TYPE, p_eno IN orders.eno%TYPE, p_received IN OUT orders.received%TYPE); p_creation_date DATE; p_last_update_date DATE; p_created_by VARCHAR2(10); p_last_update_by VARCHAR2(10); END Process_Orders; / show errors set echo on DROP SEQUENCE Order_number_seq; CREATE SEQUENCE Order_number_seq MINVALUE 1 MAXVALUE 1000000 START WITH 1 INCREMENT BY 1; CREATE OR REPLACE PACKAGE BODY Process_Orders AS -- Add a new order for the specified class. -- Order_number_seq should be used to populate the order number (ONO) column. PROCEDURE Add_Order(p_cno IN orders.cno%TYPE, p_eno IN orders.eno%TYPE, p_received IN OUT orders.received%TYPE) AS BEGIN IF p_received = NULL THEN p_received := CURRENT_DATE; END IF; SELECT user INTO p_created_by FROM dual; SELECT user INTO p_last_update_by FROM dual; INSERT INTO orders (ono,cno,eno,received,creation_date,created_by,last_update_date,last_update_by) VALUES (Order_number_seq.NextVal,p_cno,p_eno,p_received, p_creation_date, p_created_by,p_last_update_date,p_last_update_by ); --Exception Handler: EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('ERROR - Unique Constraint Violation: ' || SQLERRM); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Undentified error occured: ' || SQLERRM); END Add_Order; END Process_Orders; / show errors
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.