Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using global variable in package

821045Mar 13 2011 — edited Mar 14 2011
Hi. I have created a function (Retrieve_SerialNo) which returns a variable, which I use throughout my package. I would like to assign this variable as a global variable to be used by all functions and procedures in the package. Can someone help me in the declaration of this variable as a global variable? Also, is it necessary for me to initialize this variable whenever the package executes. If yes, how would I do this?
CREATE OR REPLACE PACKAGE BODY Supply_Item_Interface AS

 FUNCTION Retrieve_SerialNo RETURN VARCHAR2 IS
  
    v_serial_no VARCHAR2(20);
  
    CURSOR Serial_Code IS
      SELECT S.Serial_Code
        FROM Spare_Parts s, Orders r
       WHERE s.serial_code = r.serial_code;
  
  BEGIN
  
    OPEN Serial_Code;
  
    LOOP
      FETCH Serial_Code
        INTO v_serial_no;
      EXIT WHEN Serial_Code%NOTFOUND;
    
      v_serial_no := v_serial_no;
    
    END LOOP;
  
    CLOSE Serial_Code;
  
    RETURN v_serial_no;
  
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
    
  END;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 11 2011
Added on Mar 13 2011
4 comments
1,607 views