CREATE OR REPLACE PACKAGE dbfunc AS
  FUNCTION place_order (
    p_product_id INTEGER,
    p_customer_id INTEGER,
    p_quantity INTEGER
  )
  RETURN VARCHAR2;
END dbfunc;
/

CREATE OR REPLACE PACKAGE BODY dbfunc AS
  FUNCTION place_order (
    p_product_id INTEGER,
    p_customer_id INTEGER,
    p_quantity INTEGER
  )
  RETURN VARCHAR2 IS
    v_customer_count INTEGER;
    v_product_count INTEGER;
    v_order_id INTEGER;
  BEGIN
    -- count the number of products with the
    -- supplied p_product_id (should be 1 if the product exists)
    SELECT COUNT(*)
    INTO v_product_count
    FROM products
    WHERE product_id = p_product_id;

    IF v_product_count = 0 THEN
      RETURN 'No such product';
    END IF;

    -- count the number of customers with the
    -- supplied p_customer_id (should be 1)
    SELECT COUNT(*)
    INTO v_customer_count
    FROM customers
    WHERE customer_id = p_customer_id;

    IF v_customer_count = 0 THEN
      RETURN 'No such customer';
    END IF;

    -- get the next value from orders_sq
    SELECT order_sq.nextval
    INTO v_order_id
    FROM dual;

    -- place the order
    INSERT INTO orders (
      order_id, product_id, customer_id, quantity
    ) VALUES (
      v_order_id, p_product_id, p_customer_id, p_quantity
    );
    COMMIT;
    RETURN 'Order placed with id of ' || v_order_id;

  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      RETURN 'Order not placed';
  END place_order;
END dbfunc;
/