Create an anonymous block that calls both the package units. Use DBMS_OUTPUT sta
ID: 3687704 • Letter: C
Question
Create an anonymous block that calls both the package units. Use DBMS_OUTPUT statements to display values returned from the program units to verify the data.
I am having trouble calling up the procedure and function. Here is the package that was created.
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; /
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; /
Explanation / Answer
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 ;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.