lojayn
asked on
ORACLE PL/SQL
I WANT TO MODIFY THE PACKAGE:
1-FUNCTION SHIP_NAME_PF SHOULD BE USED ONLY FROM WITHIN THIS PACKAGE.
2-ADD TO BASKET_INFO-PP PROCEDURE SO THAT THAT IT RETURN NAME SHIPPED TO BY USING FUNCTION SHIP_NAME_PF
3-CREATE AN ANONYMOUSE BLOCKS THAT WILL INVOKE THE PROCEDURE WITH THE NEW MODIFICATIONS.
THE ORIGINAL PACKAGE IS :
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('Inva lid 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('Inva lid basket id');
END basket_info_pp;
END;
/
i DELETED THE FUNCTION DECLARATION FROM THE PACKAGE SPEC. AND THEN i USED THE FUNCTION IN THE SELECT STATEMENT OF THE PROCEDURE BUT i GET PL/00231 ERROR. THE MODIFIED PACKAGE CODE IS
CREATE OR REPLACE PACKAGE order_info_pkg IS
PROCEDURE basket_info_pp
(p_basket IN NUMBER,
p_shop OUT NUMBER,
p_date OUT DATE,
p_name OUT VARCHAR2);
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('Inva lid basket id');
END ship_name_pf;
PROCEDURE basket_info_pp
(p_basket IN NUMBER,
p_shop OUT NUMBER,
p_date OUT DATE,
p_name OUT VARCHAR2)
IS
BEGIN
SELECT idshopper, dtordered, ship_name_pf(p_basket)
INTO p_shop, p_date,p_name
FROM bb_basket
WHERE idbasket = p_basket;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Inva lid basket id');
END basket_info_pp;
END;
/
would you tell me how to fix the problem?
1-FUNCTION SHIP_NAME_PF SHOULD BE USED ONLY FROM WITHIN THIS PACKAGE.
2-ADD TO BASKET_INFO-PP PROCEDURE SO THAT THAT IT RETURN NAME SHIPPED TO BY USING FUNCTION SHIP_NAME_PF
3-CREATE AN ANONYMOUSE BLOCKS THAT WILL INVOKE THE PROCEDURE WITH THE NEW MODIFICATIONS.
THE ORIGINAL PACKAGE IS :
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('Inva
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('Inva
END basket_info_pp;
END;
/
i DELETED THE FUNCTION DECLARATION FROM THE PACKAGE SPEC. AND THEN i USED THE FUNCTION IN THE SELECT STATEMENT OF THE PROCEDURE BUT i GET PL/00231 ERROR. THE MODIFIED PACKAGE CODE IS
CREATE OR REPLACE PACKAGE order_info_pkg IS
PROCEDURE basket_info_pp
(p_basket IN NUMBER,
p_shop OUT NUMBER,
p_date OUT DATE,
p_name OUT VARCHAR2);
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('Inva
END ship_name_pf;
PROCEDURE basket_info_pp
(p_basket IN NUMBER,
p_shop OUT NUMBER,
p_date OUT DATE,
p_name OUT VARCHAR2)
IS
BEGIN
SELECT idshopper, dtordered, ship_name_pf(p_basket)
INTO p_shop, p_date,p_name
FROM bb_basket
WHERE idbasket = p_basket;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Inva
END basket_info_pp;
END;
/
would you tell me how to fix the problem?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.