This discussion is archived
6 Replies Latest reply: Nov 25, 2012 9:17 PM by BillyVerreynne RSS

Creating collections inside package.

975837 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    -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 Expert
    Currently Being Moderated
    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.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points