In this assignment, you create packaged procedures to retrieve shopper information. Brewbeans is adding an application page where customer service agents can retrieve shopper information by using shopper ID or last name. Create a package named SHOP_QUERY_PKG containing overloaded procedures to perform these lookups. They should return the shoppers name, state, phone number, and email address. Test the package twice. First, call the procedure with shopper ID 23, and then call it with the last name Ratman. Both test values refer to the same shopper, so they should return the same shopper information.

Someone please advise on what i am doinf wrong. Thanks in advance!

CREATE OR REPLACE PACKAGE shop_query_pkg IS

PROCEDURE CUSTOMER_info_pp

(p_shop_txt VARCHAR2,

P_ST_TXT VARCHAR2,

P_phone_num NUMBER,

P_EMAIL_TXT VARCHAR2);

PROCEDURE CUSTOMER_info_pp

(p_shop_txt VARCHAR2,

P_ST_TXT VARCHAR2,

P_phone_num NUMBER,

P_EMAIL_TXT VARCHAR2);

END;

Second part,

CREATE OR REPLACE PACKAGE shop_query_pkg

IS

PROCEDURE CUSTOMER_info_pp

(p_shop_txt VARCHAR2,

P_ST_TXT VARCHAR2,

P_phone_num NUMBER,

P_EMAIL_TXT VARCHAR2);

IS

BEGIN

SELECT SHIPLASTNAME, P_ST

INTO P_SHOP, P_ST

WHERE idbasket = 23;

CREATE OR REPLACE PACKAGE shop_query_pkg

IS

PROCEDURE CUSTOMER_info_pp

(p_shop_txt VARCHAR2,

P_ST_TXT VARCHAR2,

P_phone_num NUMBER,

P_EMAIL_TXT VARCHAR2);

IS

BEGIN

SELECT SHIPLASTNAME, P_ST

INTO P_SHOP, P_ST

WHERE idbasket = 23;

END:

END;

last part,

CREATE OR REPLACE PACKAGE BODY order_info_pkg IS

FUNCTION ship_name_pf

(p_basket IN NUMBER)

RETURN VARCHAR2 IS

lv_name_txt VARCHAR2(25);

BEGIN

SELECT shipfirstname||' '||shiplastname INTO lv_name_txt

FROM bb_basket WHERE idBasket = p_basket;

RETURN lv_name_txt;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Invalid basket id');

END ship_name_pf;

PROCEDURE basket_info_pp

(p_basket IN NUMBER, p_shop OUT NUMBER, p_date OUT DATE)

IS

BEGIN

SELECT idshopper, dtordered

INTO p_shop, p_date

FROM bb_basket

WHERE idbasket = p_basket;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Invalid basket id');

END basket_info_pp;

END; /