Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> PL/SQL "table" question...

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US