5 Replies Latest reply on Jul 10, 2012 1:52 PM by 926088

    Using type defined in package in object definition


      i have the following package:
      create or replace package my_package is
          type t_objectlist is table of my_ObjectClass index by binary_integer;
      and i now want to use the definded type in an object definition:
      create or replace type my_Object as Object (
          myMember my_package.t_objectlist
      i get an error telling me, the identifier my_package.t_objectlist is not defined.

      Using the type in a pl/sql block works.

      Any hint on that?

      Thank you
        • 1. Re: Using type defined in package in object definition
          Barbara Boehmer
          You can only use types that are created in PL/SQL within PL/SQL, not within SQL. You can use types that are created in SQL within SQL or PL/SQL.
          1 person found this helpful
          • 2. Re: Using type defined in package in object definition
            MiBoe wrote:

            type t_objectlist is table of my_ObjectClass index by binary_integer;
            Why define an associative array when the name of the name-value pair is simply a sequential index number?

            In other words, this is what an associative array is when the name is a number:
            SQL> declare
              2          type t_objectlist is table of varchar2(100) index by binary_integer;
              4          objList t_objectlist;
              5  begin
              6          objList(100) := 'object 1';  --// 1st item in the array - index name is 100 (not 1)
              7          objList(1) := 'object 2';
              8          objList(0) := 'object 3';    --// last item in the array - index name is 0
              9  end;
             10  /
            PL/SQL procedure successfully completed.
            However, this is (almost) never used as above - instead values are sequentially assigned and the name is simply a sequential number. In this case there is absolutely no need for making this array construct an associative array. A plain vanilla array is what should be used.

            Understanding the differences between a normal array and an associative array are important.

            As Barbara commented, you cannot use PL/SQL data types in SQL. Record structures, boolean, associative arrays and so on, are not supported by the SQL engine.

            Associative arrays in SQL needs to be treated as formal name-value object declaration. E.g.
            create or replace type TNameValue as object(
              name varchar2(100),
              value varchar2(4000)
            The collection class for this object can implement methods to access a value via name, check for the existence of a name, provide a list of sorted names, and so on.

            Keep in mind that name-values are an exception in relational data models. EAV (Entity Attribute Value) data models are fatally flawed ito performance and flexibility.
            1 person found this helpful
            • 3. Re: Using type defined in package in object definition
              Ok, i see that oracle separates the object type definition (header) and the object implementation (body) into sql and pl/sql and therefore i can not use the type defined in a package to declare a member variable of an object, right?

              My intention was to implement a classic chain of command pattern and therefore i would have needed a collection of objects as member in the command object. Here the commands (delegates) have to be sequentially numbered to ensure the correct process.

              I guess i'll have to fall back to a more static solution using package methods.

              The only thing i'm wondering about is that a package header seens to be pl/sql while an object header is sql... strange and somehow inconsistent, but might have to do with the fact that one could create a table of that object type. At least i have never seen anybody creating tables of package type ;)

              Thank you

              Maybe Oracle should introduce Objects and TableObjects where the last ones are the current object implementation and the first one is pure pl/sql (sth. like object oriented packages)
              • 4. Re: Using type defined in package in object definition
                SQL is.. well SQL. The Structured Query Language.

                PL/SQL is 2 languages. PL stands for Programming Language. Is based on Ada (part of the Pascal family of languages). It is a formal procedural language. PL is however tightly integrated with the SQL language.

                This allows you to code in 2 different programming languages, inside the same source. And the compiler figures out what is PL, what is SQL, and where and how the different languages need to be glued together.

                I've posted sample code in {message:id=9424102} that demonstrates how a typical language (like Java, C/C++, Pascal, etc) needs to use the SQL language, and how PL/SQL integrates the SQL language.

                As the PL/SQL engine and SQL engine resides and executes in the very same server process, this integration goes fairly deep down into the cores of both. For example, both engines share the exact same code (modules) for functions like trunc() or to_date(). Both use the same basic scalar data types (string, number and date).

                However, the Pascal/3GL roots of PL/SQL provides features like associative arrays, data types like boolean, user defined record structures and so on. These features are not part of the SQL language - nor really needed for the SQL language either.

                The SQL language is however not a procedural language. And when it comes to defining SQL ADTs (Advance Data Types, aka SQL object classes), you need something more than the SQL language to implement methods for the type. PL/SQL, with its tight SQL integration, fits this bill very nicely.

                This implementation makes a lot of logical and technical sense IMO. The SQL language has a specific focus and characteristics. The PL/SQL language has a different focus and characteristics. The total of 2 languages together is greater than the sum of these languages individually.

                What I dislike is a language that is "extended" beyond the core of what it was designed to do. In the RDBMS world, you get procedural extensions to the SQL language (like Microsoft T-SQL). This takes some procedural language features and apply these to a language that by its core design and nature, is not procedural. This does not make sense to me.

                For application development, instead of using Java or .Net as the application layer, the complete application layer can be done in PL/SQL. As it (the language) is a fully blown formal procedural language designed for writing applications. Add SQL integration and the fact that a SQL call and SQL data transfer occurs inside the same physical process, a PL/SQL application is usually a lot faster, with fewer moving parts, and more scalable, than a Java or .Net application.

                And this is how I use PL/SQL. I have written very complex applications using PL/SQL only - using Apex (also a PL/SQL suite of s/w) as the user interface. I'm pretty sure that a team of Java developers would have taken considerably longer. That the s/w would have been less performant. And h/w would have been more expensive as a middle-tier servers would have been required to host the application (instead of using the database layer).
                • 5. Re: Using type defined in package in object definition
                  That is exactly the reason why i am trying to port the object oriented aspects to my pl/sql implementations :)

                  best regards