6 Replies Latest reply: Nov 25, 2012 11:17 PM by Billy~Verreynne RSS

    Creating collections inside package.

    975837
      I was trying to create types inside package like:

      -create or replace package pack1 is
      type udtable is table of integer index by binary_integer;
      end;

      -create or replace package BODY pack1 is
      type udtable is table of integer index by binary_integer;
      end;

      Although this is not exact query what i have doubt in my mind.
      Please anybody tell me ,
      is it possible to create object types/collections inside/with packages?
        • 1. Re: Creating collections inside package.
          ranit B
          -create or replace package BODY pack1 is
          type udtable is table of integer index by binary_integer;
          end;
          is it possible to create object types/collections inside/with packages?
          Hi,
          Welcome to the Forum...

          Yes, It is absolutely possible to create object types/collections inside/with packages.
          /* Formatted on 11-23-2012 1:00:39 PM (QP5 v5.163.1008.3004) */
          CREATE OR REPLACE PACKAGE pack1
          IS
             TYPE udtable IS TABLE OF INTEGER
                                INDEX BY BINARY_INTEGER;
          END;
          /
          
          Package created.
          Refer -- http://docs.oracle.com/cd/B10500_01/appdev.920/a96624/05_colls.htm#35385

          Ranit B.
          • 2. Re: Creating collections inside package.
            Manik
            YES valid right from 9i.

            http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/09_packs.htm

            Cheers,
            Manik.
            • 3. Re: Creating collections inside package.
              Billy~Verreynne
              972834 wrote:
              I was trying to create types inside package like:

              -create or replace package pack1 is
              type udtable is table of integer index by binary_integer;
              end;

              -create or replace package BODY pack1 is
              type udtable is table of integer index by binary_integer;
              end;

              Although this is not exact query what i have doubt in my mind.
              Please anybody tell me ,
              is it possible to create object types/collections inside/with packages?
              You have defined as associative array - not a collection and not an object type.

              Collections in PL/SQL are standard arrays - as you would define in C, Pascal and Basic.

              It is important that you understand the difference between an array and an associative array - and how rarely one needs associative arrays in PL/SQL, never mind an associative array that one references by a sequential number.

              Object types cannot be defined in PL/SQL. This requires the create or replace type command. However, object methods and constructors are implemented using PL/SQL. Likewise, objects are frequently used in PL/SQL.
              • 4. Re: Creating collections inside package.
                Billy~Verreynne
                ranit B wrote:

                Yes, It is absolutely possible to create object types/collections inside/with packages.
                Definitions are important here.

                Yes, you can use and instantiate objects in PL/SQL packages.

                Yes, you define object methods and contructors using PL/SQL.

                But no, you cannot create objects (i.e. define classes) using PL/SQL.
                • 5. Re: Creating collections inside package.
                  rp0428
                  >
                  You have defined as associative array - not a collection and not an object type.

                  Collections in PL/SQL are standard arrays - as you would define in C, Pascal and Basic.
                  >
                  As a wise man recently said ;)
                  >
                  Definitions are important here.
                  >
                  An associative array IS a collection type. The definitions in the PL/SQL Language doc are pretty well accepted.
                  http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm
                  >
                  Understanding PL/SQL Collection Types
                  PL/SQL has three collection types, whose characteristics are summarized in Table 5-1.
                  . . .
                  Associative array (or index-by table) - Only in PL/SQL block
                  Nested table - Either in PL/SQL block or at schema level
                  Variable-size array (varray) - Either in PL/SQL block or at schema level
                  >
                  You are correct that Object types must be defined at the schema level but two of the collection types can be defined in PL/SQL as well as RECORD types.
                  • 6. Re: Creating collections inside package.
                    Billy~Verreynne
                    rp0428 wrote:
                    >
                    You have defined as associative array - not a collection and not an object type.

                    Collections in PL/SQL are standard arrays - as you would define in C, Pascal and Basic.
                    >
                    As a wise man recently said ;)
                    >
                    Definitions are important here.
                    >
                    An associative array IS a collection type. The definitions in the PL/SQL Language doc are pretty well accepted.
                    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm
                    The manual simply confuses the issue. I do not agree with it - or with many of the shoddy examples in it.

                    A collection, in programming terms, refers to a class that act as an object container. This is different from an array.
                    You are correct that Object types must be defined at the schema level but two of the collection types can be defined in PL/SQL as well as RECORD types.
                    Record types are also nothing at all like a an object type.

                    IMO, the PL/SQL related manuals, are not up to scratch in adhering to standard programming terminology and establishing sound PL/SQL language standards (bit of an understatement).

                    It is a major contributor to developers writing poor code, using the wrong types (like associative arrays for a cursor fetch), the wrong parameters (like sending the smtp server's own address to it via the UTL_SMTP helo call), and doing this in utterly idiotic programming standards (like writing all reserved words in upper case and being totally ignorant of camel and pascal case).

                    And I'm trying hard not to foam at the mouth here... but I have a major problem with the cr@p that is being punted by some people and some manuals/documentation/books when it comes to PL/SQL.

                    PL/SQL is just another programming language. And a very capable one. Yet, there are those who want to treat it differently and pretend that it is somehow so different than other languages, and then apply their own standards and terminology that not only do not make any sense - but is the opposite of the very mature and robust standards that have evolved over the last 40 years for programming languages.