6 Replies Latest reply: Mar 7, 2013 3:00 PM by Barbara Boehmer RSS

    How can an Object use a user-defined type ?

    995274
      Hello,

      I whish to create an object that will use a user-defined type knowing that this type CANNOT be defined using the CREATE TYPE instruction (for example an Associative Array or similar).

      According to the explanations in this thread
      Using type defined in package in object definition
      In Oracle, there seems to be a "collision" between SQL(OBJECTs) and PL/SQL (TYPEs in a PACKAGE) incompatible instructions :-(

      So my question is the following:
      How can I define an object that will use a user-defined type (for some attributes and method parameters), knowing that Oracle does not allow the usage of the CREATE TYPE instruction for my user-defined type (thus it has to be defined within a PACKAGE). And knowing also that in the current version of Oracle it seems to still be impossible to define an object type within a package.

      Then why use an object? If I understood well the documentation, Data Cartridge aggregation mechanism (ODCI) does not allow creating an aggregation function without using an object. One of my uses for this is creating an aggregation function (ODCI object) that uses an Associative Array (a table of integers indexed by a varchar2). So far, it seems that the only solution is to abandon the Associative Array for another structure (that could be declared, this time, with the SQL instruction CREATE TYPE) – If this is the case what structure / collection would you recommend me (the manipulated data is a key (VARCHAR) and a value (INTEGER) and a many accesses on the key – typically a HashTable(String,int) structure).

      Thanks in advance,
      Ronan.
      Note: I use Oracle 11g2 (both Database and XE versions).
        • 1. Re: How can an Object use a user-defined type ?
          Barbara Boehmer
          How about something like the following?
          SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE ud_typ AS OBJECT
            2    (val  INTEGER,
            3       key  VARCHAR2(30));
            4  /
          
          Type created.
          
          SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE ud_tab AS TABLE OF ud_typ;
            2  /
          
          Type created.
          
          SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE obj_for_agg_func AS OBJECT
            2    (param1     ud_tab)
            3    -- whatever;
            4  /
          
          Type created.
          • 2. Re: How can an Object use a user-defined type ?
            Billy~Verreynne
            Ronan Tournier wrote:

            I whish to create an object that will use a user-defined type knowing that this type CANNOT be defined using the CREATE TYPE instruction (for example an Associative Array or similar).
            Dispute the statement. I'm pretty sure that any data type you can use/define in PL/SQL, I can do using UDTs in SQL. Including boolean and associative arrays.

            As for associative arrays - simplistic data structure especially in the SQL context. Offhand I do not see any reasons why one would want to constrain and limit a data model in SQL by throwing it into an associative array.
            • 3. Re: How can an Object use a user-defined type ?
              995274
              Thanks for that (an object within a nested table). I will try that.
              I suppose that the conclusion for the moment (Oracle 11g2 compiler) is:
              it is impossible to use PL/SQL types such as Associative Arrays within Objects.

              Concerning my idea on why using associative arrays :
              I was looking for an in-memory hash structure (key, value "kind of table" with a fast access on a text-based key, and minimum coding for an update procedure--very few lines, 3, with an Associative Array). The main objective is a temporary in-memory structure for intermediate calculus for an aggregation function.
              • 4. Re: How can an Object use a user-defined type ?
                995274
                I liked Associative Array for the very simple update procedure (along with fast access and in-memory capacity)
                DECLARE
                TYPE Associative_Array IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
                  collection Associative_Array;
                
                --update procedure very simple
                  PROCEDURE updateAA(aa IN OUT Associative_Array, term VARCHAR2, i NUMBER) IS 
                  BEGIN
                    IF aa.EXISTS(term) THEN aa(term) := aa(term) + i;
                    ELSE aa(term) := i;
                    END IF;
                  END updateAA;
                
                  PROCEDURE printAA(aa Associative_Array) IS
                    element VARCHAR2(64);
                    i INTEGER;
                  BEGIN 
                    element := aa.FIRST;
                    WHILE element IS NOT NULL LOOP
                      DBMS_OUTPUT.PUT_LINE(element||' '||aa(element));
                      element := aa.NEXT(element);
                    END LOOP;    
                  END printAA;
                
                BEGIN
                  updateAA(collection, 'testA', 1);
                  updateAA(collection, 'testB',1);
                  updateAA(collection, 'testA', 1);
                  printAA(collection);
                END;
                Output
                testA 2
                testB 1
                • 5. Re: How can an Object use a user-defined type ?
                  995274
                  Thanks to Barbara, I was able to create a structure that I should be able to use in my aggregation function.

                  However, the update procedure is far more complex than the Associative Array one...
                  PROCEDURE updateNT(term VARCHAR2, tf INTEGER) IS
                    i NUMBER;
                    found BOOLEAN := FALSE;
                  BEGIN 
                    IF (tf_list.COUNT() <> 0) THEN 
                      i:=tf_list.FIRST();
                      WHILE (NOT found) AND (i<=tf_list.LAST()) LOOP
                        IF tf_list(i).key = term THEN 
                          tf_list(i).value := tf_list(i).value + tf;
                          found := TRUE;
                        END IF;
                        i:=tf_list.NEXT(i);
                      END LOOP;
                      IF (NOT found) THEN
                        tf_list.EXTEND;
                        tf_list(tf_list.LAST()) := TY_tf(key => term, value => tf);
                      END IF;
                    ELSE 
                      tf_list.EXTEND;
                      tf_list(tf_list.LAST()) := TY_tf(key => term, value => tf);
                    END IF;
                  END;
                  Now, how can one speed up this thing (the lookup within a Nested Table)?
                  (The chapter 5 of the PL/SQL manual does not provide any sort of "index-like" structure, that I would like to add to the key part of the Nested Table)
                  • 6. Re: How can an Object use a user-defined type ?
                    Barbara Boehmer
                    Please try the following and see if it is faster for you. It doesn't exactly use an index, but uses rownum, relying on Oracle collections being ordered by definition.

                    -- code:
                    CREATE OR REPLACE TYPE ty_tf AS OBJECT
                      (key      VARCHAR2(30),
                       value    INTEGER);
                    /
                    CREATE OR REPLACE TYPE tab_tf AS TABLE OF ty_tf;
                    /
                    DECLARE
                      tf_list tab_tf := tab_tf();
                    
                      PROCEDURE updateNT
                        (term    IN     VARCHAR2, 
                         tf      IN     INTEGER) 
                      IS
                        i               INTEGER;
                      BEGIN 
                        SELECT rn
                        INTO   i
                        FROM   (SELECT t.*, ROWNUM rn
                                FROM   TABLE (tf_list) t)
                        WHERE  key = term;
                        tf_list(i).value := tf_list(i).value + tf;
                      EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                          tf_list.EXTEND;
                          tf_list(tf_list.LAST()) := TY_tf(key => term, value => tf);
                      END;
                    
                      PROCEDURE printNT IS
                      BEGIN 
                        FOR element IN tf_list.FIRST .. tf_list.LAST LOOP
                          DBMS_OUTPUT.PUT_LINE 
                            (tf_list(element).key || ' ' || tf_list(element).value);
                        END LOOP;    
                      END printNT;
                    BEGIN
                      updateNT ('testA', 1);
                      updateNT ('testB', 1);
                      updateNT ('testA', 1);
                      printNT;
                    END;
                    /
                    -- execution of above code:
                    SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE ty_tf AS OBJECT
                      2    (key      VARCHAR2(30),
                      3       value      INTEGER);
                      4  /
                    
                    Type created.
                    
                    SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE tab_tf AS TABLE OF ty_tf;
                      2  /
                    
                    Type created.
                    
                    SCOTT@orcl_11gR2> DECLARE
                      2    tf_list tab_tf := tab_tf();
                      3  
                      4    PROCEDURE updateNT
                      5        (term       IN      VARCHAR2,
                      6         tf       IN      INTEGER)
                      7    IS
                      8        i           INTEGER;
                      9    BEGIN
                     10        SELECT rn
                     11        INTO     i
                     12        FROM     (SELECT t.*, ROWNUM rn
                     13             FROM     TABLE (tf_list) t)
                     14        WHERE     key = term;
                     15        tf_list(i).value := tf_list(i).value + tf;
                     16    EXCEPTION
                     17        WHEN NO_DATA_FOUND THEN
                     18          tf_list.EXTEND;
                     19          tf_list(tf_list.LAST()) := TY_tf(key => term, value => tf);
                     20    END;
                     21  
                     22    PROCEDURE printNT IS
                     23    BEGIN
                     24        FOR element IN tf_list.FIRST .. tf_list.LAST LOOP
                     25          DBMS_OUTPUT.PUT_LINE
                     26            (tf_list(element).key || ' ' || tf_list(element).value);
                     27        END LOOP;
                     28    END printNT;
                     29  BEGIN
                     30    updateNT ('testA', 1);
                     31    updateNT ('testB', 1);
                     32    updateNT ('testA', 1);
                     33    printNT;
                     34  END;
                     35  /
                    testA 2
                    testB 1
                    
                    PL/SQL procedure successfully completed.