Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 742
  • Last Modified:

Oracle package global variable seen by procedures, error "PLS-00225: subprogram or cursor reference is out of scope"

I'm trying to use a global variable within a package for all procedures to see that global variable.  I tried procedure A.variable_name  for global variable.  

Here is how I'm trying to use a global variable to pass variables within a package from one procedure A to procedure B but i get an error "PLS-00225: subprogram or cursor reference is out of scope"


CREATE OR REPLACE PACKAGE PKG_1
AS
   PROCEDURE A;

   PROCEDURE B;

END PKG_1;
/

CREATE OR REPLACE PACKAGE BODY PKG_1
AS
 PROCEDURE A
 IS
   
   CURSOR cur_A
   IS
 SELECT DISTINCT data
  FROM table_X;
   

 BEGIN
   
   OPEN cur_A;

   <<fetch_cur_A>>
   LOOP
      FETCH cur_A
       INTO rec_A;

      EXIT fetch_A WHEN cur_A%NOTFOUND;

        SELECT data
        INTO variable_name_A
        FROM table_A
        WHERE column_A = rec_A;

        IF variable_name_A is NOT NULL THEN

          B;  -- call procedure B

        END IF;

    END LOOP;

   CLOSE cur_A;

   END A;
 
  PROCEDURE B
   
   BEGIN
         
             SELECT x, y, z
               INTO v_x, v_y, v_z
             from table_Z
            WHERE column_P = (A.variable_name_A);  --using global variable by using                 --procedure_name.variable_name
                 
  END B;

END PKG_1;
0
talahi
Asked:
talahi
1 Solution
 
slightwv (䄆 Netminder) Commented:
You cannot use procedure A's variables outside procedure A.

Declare a global package variable.  Then use it.


CREATE OR REPLACE PACKAGE PKG_1
AS
   My_Global_variable    varchar2(100);

   PROCEDURE A;

   PROCEDURE B;

END PKG_1;
/

Open in new window

0
 
talahiAuthor Commented:
Thanks, saves me time from trying to make that work.  I'll try to OUT the variable from a and IN to B.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now