Re: Index-by table - compilation errors

On Jun 6, 7:14 pm, "Vojta" <r..._at_centrum.cz> wrote:
> Hello,
>
> my problem is compilation warning "Warning: TYPE created with compilation
> errors" in script:
>
> CREATE OR REPLACE TYPE StrListTyp IS TABLE OF VARCHAR(255) INDEX BY
> BINARY_INTEGER;
>
> After that my procedure, which is using the type, becomes invalid. What is
> wrong on the script?
>
> When I execute script:
>
> CREATE OR REPLACE TYPE StrListTyp IS TABLE OF VARCHAR(255);
>
> type is created without warnings but then I get error "Lower index exceeds
> counter" in procedure when I try to assign value to StrListTyp(index) .
>
> "DA Morgan" <damor..._at_psoug.org> píse v diskusním príspevkunews:1181142003.818855_at_bubbleator.drizzle.com...
>
> > Vojta wrote:
> >> Please, can you advise? I created package in which I defined type:
>
> >> TYPE StrListTyp IS TABLE OF VARCHAR(255) INDEX BY BINARY_INTEGER;
>
> >> Package was created, it works, procedures can work with this type. Then I
> >> tried to create the same type outside package by executing script:
>
> >> CREATE OR REPLACE TYPE StrListTyp IS TABLE OF VARCHAR(255) INDEX BY
> >> BINARY_INTEGER;
>
> >> Type is created with warning: "Warning: TYPE created with compilation
> >> errors". When I try to run procedure using this type I get error "Object
> >> [procedure name] is invalid". If I run the script without "INDEX BY",
> >> type is created without wornings but indexing does not work:
>
> >> CREATE OR REPLACE TYPE StrListTyp IS TABLE OF VARCHAR(255);
>
> >> CREATE PROCEDURE MyProc IS
> >> strList StrListTyp := StrListTyp();
> >> BEGIN
> >> strList(1) := 'ABC'; -- Here I get error "Lower index exceeds counter"
> >> END;
>
> >> Please, what do I do wrong? How can I create index-by table outside
> >> package?
>
> >> Thank you! Vojta
>
> > Why? The error message you have been looking at is well written and
> > quite clear on the matter. What is the business case that makes you
> > want to circumvent it?
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> >www.psoug.org

Index-by tables (aka associative arrays) are PL/SQL only feature, you can't create user-defined data types (which is what CREATE TYPE does) as associative arrays. In other words, you can declare them only in a PL/SQL block (package, declaration section of a procedure or function, etc.).

TABLE OF types without INDEX BY clause are referred to as nested table types, and their elements need to be allocated with <yourtype>. EXTEND() method before you can assign values to them. Your call to the type constructor StrListTyp() created an empty nested table, you need to extend it before you can access its elements, because there are none yet.

More information is available in PL/SQL Developer's Guide and Reference for your Oracle release, chapter on collections. Please take some time to read it and familiarize yourself with various collection types Oracle supports and fully understand critical differences between them.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com

P.S. And please, do NOT top-post. Received on Wed Jun 06 2007 - 14:18:31 CDT