?
Solved

ORACLE PL/SQL

Posted on 2011-10-22
2
Medium Priority
?
1,022 Views
Last Modified: 2012-05-12
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('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;
/

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('Invalid 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('Invalid basket id');
 END basket_info_pp;
END;
/

would you tell me how to fix the problem?
0
Comment
Question by:lojayn
  • 2
2 Comments
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 1000 total points
ID: 37011215
You cannot use SELECT statement when FUNCTION is local to PACKAGE BODY.

SQL Engine requires FUNCTION to be publi
c

Please refer to this link

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1895113969657
0
 
LVL 14

Accepted Solution

by:
ajexpert earned 1000 total points
ID: 37011261
Ok,

I guess your function only concats the first name and the last name, so you can handle it in following manner.  No need to call FUNCTION in sql.

I have modified your PROCEDURE and FUNCTION , please see my comments starting with /*   */
/* Formatted on 10/22/2011 11:13:34 AM (QP5 v5.115.810.9015) */
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');
         /* YOU NEED TO RETURN EMPTY VALUE HERE */
         RETURN NULL;
   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
      /*   INVOKE FUNCTION HERE AND EXCLUDE FROM SELECT STATEMENT */
      p_name := ship_name_pf (p_basket);

      IF p_name IS NULL
      THEN
         DBMS_OUTPUT.PUT_LINE ('Invalid basket id from function call');
         RAISE;
      END IF;



      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;
/

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month17 days, 2 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question