From: "Sybrand Bakker" <postbus@sybrandb.demon.nl>
Newsgroups: comp.databases.oracle.server
Subject: Re: Passing PL/SQL tables in formal parameter list
Date: Tue, 10 Sep 2002 00:17:33 +0200
Organization: Posted via Supernews, http://www.supernews.com
Message-ID: <unq7vitl3ir2bd@corp.supernews.com>
Reply-To: "Sybrand Bakker" <postbus@sybrandb.-verwijderdit.demon.nl>
References: <P15f9.260402$aA.45165@sccrnsc02> <3D7CFED9.3A2734C@exesolutions.com> <%m7f9.421387$UU1.66027@sccrnsc03>
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Complaints-To: abuse@supernews.com
Lines: 78


You need to define the TYPE in the package *specification*
and you can use it anywhere provided you have execute privilege on the
package.
Just as in any other procedural language.

Hth


--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

"Michael J. Moore" <hicamel_x_the_spam@attbi.com> wrote in message
news:%m7f9.421387$UU1.66027@sccrnsc03...
> I am trying to learn what works and what does not work in PL/SQL. I have
 not
> been able to find
> any specific documentation that says this cannot be done, nor have I been
> able to find an example of how to do it.  I have seen it done within a
> Package where the TYPEs are defined as package variables, but that begs
 the
> question, how would a person call that packaged procedure from outside of
> the package. If I can solve THIS question, I will be able to answer the
> 'packaged procedure' question implicitly.
> Thanks,
> Mike
>
> "Daniel Morgan" <dmorgan@exesolutions.com> wrote in message
> news:3D7CFED9.3A2734C@exesolutions.com...
> > "Michael J. Moore" wrote:
> >
> > > How can I define a STAND ALONE procedure to have a PL/SQL table in
 it's
> > > formal parameter list?
> > > I tried this .... I understand WHY it is not working, but what do I
 need
 to
> > > do to accomplish
> > > the intended result?:
> >
>
> --------------------------------------------------------------------------
> --
> > > ------------------------
> > > SQL> CREATE or REPLACE PROCEDURE Test1(v_Tab1 OUT t_Tabtype) AS
> > >   2
> > >   3  TYPE t_Tabtype IS TABLE OF VARCHAR2(20)
> > >   4  INDEX BY BINARY_INTEGER;
> > >   5
> > >   6  v_Tab1 t_Tabtype;
> > >   7
> > >   8  BEGIN
> > >   9     null;
> > >  10  END Test1;
> > >  11  /
> > >
> > > Warning: Procedure created with compilation errors.
> > >
> > > SQL> show errors
> > > Errors for PROCEDURE TEST1:
> > >
> > > LINE/COL ERROR
> > > -------- ------------------------------------------------------
> > > 0/0      PL/SQL: Compilation unit analysis terminated
> > > 1/28     PLS-00201: identifier 'T_TABTYPE' must be declared
> >
> > Tell us what you are trying to accomplish? Perhaps a PL/SQL table is not
 the
> > best solution.
> >
> > Daniel Morgan
> >
>
>