What is wrong with this question. Any assistance will be appreciated.

In this assignment, you modify a package to make program units private. The Brewbeans programming group decided that the SHIP_NAME_PF function in the ORDER_INFO_PKG package should be used only from inside the package. Follow these steps to make this modification:

In notepad, open Assignment 07-03.txt file in the Chapter07 folder, and review the package code.

SQL

Part 1:

1. CREATE OR REPLACE PACKAGE order_info_pkg IS

2. FUNCTION ship_name_pf

3. (p_basket IN NUMBER)

4. RETURN VARCHAR2;

5. PROCEDURE basket_info_pp

6. (p_basket IN NUMBER,

7. p_shop OUT NUMBER,

8. p_date OUT DATE);

9. END;

10.

11. CREATE OR REPLACE PACKAGE BODY order_info_pkg IS

12. FUNCTION ship_name_pf

13. (p_basket IN NUMBER)

14. RETURN VARCHAR2

15. IS

16. lv_name_txt VARCHAR2(25);

17. BEGIN

18. SELECT shipfirstname||' '||shiplastname

19. INTO lv_name_txt

20. FROM bb_basket

21. WHERE idBasket = p_basket;

22. RETURN lv_name_txt;

23. EXCEPTION

24. WHEN NO_DATA_FOUND THEN

25. DBMS_OUTPUT.PUT_LINE('Invalid basket id');

26. END ship_name_pf;

27. PROCEDURE basket_info_pp

28. (p_basket IN NUMBER,

29. p_shop OUT NUMBER,

30. p_date OUT DATE)

31. IS

32. BEGIN

33. SELECT idshopper, dtordered

34. INTO p_shop, p_date

35. FROM bb_basket

36. WHERE idbasket = p_basket;

37. EXCEPTION

38. WHEN NO_DATA_FOUND THEN

39. DBMS_OUTPUT.PUT_LINE('Invalid basket id');

40. END basket_info_pp;

41. END;

Modify the package code to add to the BASKET_INFO_PP procedure so that it also returns the name an order is shipped by using SHIP_NAME_PF function. Make the necessary changes to make the SHIP_NAME_PF function private.

SQL

CREATE OR REPLACE PACKAGE order_info_pkg IS

FUNCTION ship_name_pf

(p_basket IN NUMBER)

RETURN VARCHAR2;

PROCEDURE basket_info_pp

(p_basket IN NUMBER,

p_shop OUT NUMBER,

p_date OUT DATE);

END;

RESULTS:

Create the package by using the modified code.

SQL

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;

RESULTS:

4 Create and run an anonymous block that calls the BASKET_INFO_PP procedure and displays the shopper ID, order date, and ship-to name to check the values returned. Use DBMS_OUTPUT statements to display the values.

Sql:

DECLARE

LV_TXT VARCHAR2(60) ; LV_ID_SHOPPER NUMBER ; LV_DATE DATE ;

BEGIN

ORDER_INFO_PKG.BASKET_INFO_PP ( 23 ,LV_ID_SHOPPER ,LV_DATE ) ; LV_TXT:= order_info_pkg.SHIP_NAME_PF (23) ; DBMS_OUTPUT.PUT_LINE ( LV_TXT ) ;

End ;