Well, not just anywhere, only in PL/SQL. To be able to use a type in both
SQL and PL/SQL, you need to declare a new SQL type using create or
replace type command. You can only create nested table types this way
though (those without index-by clause). PL/SQL (index-by) tables cannot
be used in SQL.
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:unq7vitl3ir2bd_at_corp.supernews.com...
> 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_at_attbi.com> wrote in message
> news:%m7f9.421387$UU1.66027_at_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_at_exesolutions.com> wrote in message
> > news:3D7CFED9.3A2734C_at_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
> > >
> >
> >
>
>
Received on Tue Sep 10 2002 - 00:12:47 CDT
