Re: PL/SQL "table" question...

From: Art Clarke <fool_at_ofb.net>
Date: 1997/05/21
Message-ID: <5lvkao$747@gap.cco.caltech.edu>#1/1

Vijay Damodaran (vijayd_at_nortel.com) wrote:
> 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".

You can reference the type as follows:
DECLARE

• Declare a variable fo the same type that was created in your
• Admin Package spec. adm Admin.AdminTab; BEGIN adm := admin.GetADmin('AE'); dbms_output.put_line('adm count = ' || to_char(adm.count)); END;

> 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?
Don't know. Received on Wed May 21 1997 - 00:00:00 CDT