This discussion is archived
6 Replies Latest reply: Mar 7, 2013 1:00 PM by Barbara Boehmer RSS

How can an Object use a user-defined type ?

995274 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 ?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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.

Legend

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