PL/SQL "table" question...

From: Vijay Damodaran <vijayd_at_nortel.com>
Date: 1997/05/21
Message-ID: <3383125F.75FA@nortel.com>#1/1

I have created a package as follows:

---

/* Package Specification */

create or replace package Admin as

   type admintab is table of cmn_gpm_admin.admin_empno%type

        not null index by binary_integer;    function GetAdmin(appln in cmn_gpm_admin.application%type)

        return admintab;
end Admin;

/* Package Body */

create or replace package body Admin as

function GetAdmin(appln in cmn_gpm_admin.application%type) return admintab is

   l_admin admintab;
begin

     :
     :

   return l_admin;
end GetAdmin;
/* end of function GetAdmin. */

end Admin;
/* end of package body for Admin */

---

The problem is not in the package specs or its body, both compile fine. But I am trying to execute this package within sqlplus and it is this script which gives the problem. The script I use is:

---

/* start of SQL*Plus script */

1 declare
2 type admtab is table of cmn_gpm_admin.admin_empno%type 3 not null index by binary_integer; 4 adm admtab;
5 begin
6 adm := admin.GetADmin('AE');
7 dbms_output.put_line('adm count = ' || to_char(adm.count)); 8 end;
/* end of SQL script */

---

When I try to execute this script, I get the following error:

  ORA-06550: line 6, column 10:
  PLS-00382: expression is of wrong type
  ORA-06550: line 7, column 50:
  PLS-00487: Invalid reference to variable 'ADM'

Question1:

---

Does PL/SQL consider the "admtab" declared in the SQL script and "admintab" declared in the package to be different  datatype even though they have the exact same declaration. I tried using varchar2(8) instead of
"cmn_gpm_admin.admin_empno%type", but that didn't work. So, how do I reference a table returned by a packaged function? I want to avoid declaring a global variable in the package specification for the datatype "admintab".

Question2:

---

Are global variable specifications defined in a package stored in the SGA on the server? So, when a client application such as Forms45 appln, makes a reference to this global variable, is there a call to the server to get the information?

Thanks for ur replies.

VJ.

-- 
Vijay Damodaran
vijayd_at_nortel.com
Work: (972) 685-8150

Received on Wed May 21 1997 - 00:00:00 CDT