Forum Stats

  • 3,874,184 Users
  • 2,266,680 Discussions
  • 7,911,761 Comments

Discussions

Usage of MEMBER OF with record type variables

Miquel Despuig
Miquel Despuig Member Posts: 4 Red Ribbon
edited Nov 23, 2022 1:55PM in SQL & PL/SQL

Hi Community!

I'm trying to use an operator MEMBER OF with record type variables. When I do this, I get a type error. I've managed to write a function that iterates over the records of the nested table variable comparing the items one by one. I wonder if there's a way to make this work either by changing the operation type or changing my data structure.

Here sample code to test:

CREATE TABLE F4108 (

  iolotn NCHAR(30),

  ioitm NUMBER,

  iomcu NCHAR(12),

  ioohdj NUMBER(6,0)

);


INSERT INTO F4108 (IOLOTN,IOITM,IOMCU,IOOHDJ) values ('00003323           ','6880','  10VILPA1','122228');

INSERT INTO F4108 (IOLOTN,IOITM,IOMCU,IOOHDJ) values ('00003323           ','6350','  10VILPA1','122228');

INSERT INTO F4108 (IOLOTN,IOITM,IOMCU,IOOHDJ) values ('00003323           ','6343','  10VILPA1','122228');

INSERT INTO F4108 (IOLOTN,IOITM,IOMCU,IOOHDJ) values ('00003323           ','6377','  10VILPA1','122228');


DECLARE

  TYPE t_lots_ntt IS TABLE OF F4108%ROWTYPE;

   

  queue t_lots_ntt := t_lots_ntt();

  visited t_lots_ntt := t_lots_ntt();

   

  l_lot F4108%ROWTYPE;

  idx PLS_INTEGER;


BEGIN


  SELECT * BULK COLLECT INTO queue

  FROM F4108;

   

  l_lot := queue(queue.FIRST);

   

  dbms_output.put_line('Record we know is member of the nested table:');

  dbms_output.put_line(l_lot.iolotn||' '||l_lot.ioitm||' '||l_lot.iomcu);

   

  dbms_output.put_line('Nested table records: ');

  idx := queue.FIRST;

  LOOP

    EXIT WHEN idx IS NULL;

    IF l_lot MEMBER OF queue THEN

    dbms_output.put_line(queue(idx).iolotn||' '||queue(idx).ioitm||' '||queue(idx).iomcu);

END IF;

    idx := queue.NEXT(idx);

  END LOOP;


END;

Tagged:

Comments

  • Paulzip
    Paulzip Member Posts: 8,801 Blue Diamond
    edited Nov 23, 2022 2:39PM

    MEMBER OF works on known object types registered with the data dictionary, and for complex types needs a MAP function for comparisons, so you can't do things the way you are trying.

    You'll need something like this...

    create or replace type tlot is object (
      iolotn NCHAR(30)
    , ioitm  NUMBER
    , iomcu  NCHAR(12)
    , ioohdj NUMBER(6,0)
    , map member function AsString return nvarchar2
    );
    /
    
    
    create or replace type body tlot is
    
    map member function AsString return nvarchar2 is
      SEP constant nvarchar2(1) := chr(31); -- Unit separator
    begin
      return iolotn || SEP || to_nchar(ioitm) || SEP || iomcu || SEP || to_nchar(ioohdj);
    end;
    
    end;
    /
    
    
    DECLARE
      TYPE t_lots_ntt IS TABLE OF tLot;   
      queue t_lots_ntt := t_lots_ntt();
      visited t_lots_ntt := t_lots_ntt();   
      l_lot tLot;
      idx PLS_INTEGER;
    BEGIN
      SELECT tLot(IOLOTN, IOITM, IOMCU, IOOHDJ) BULK COLLECT INTO queue
      FROM F4108;   
      l_lot := queue(queue.FIRST);   
      dbms_output.put_line('Record we know is member of the nested table:');
      dbms_output.put_line(l_lot.iolotn||' '||l_lot.ioitm||' '||l_lot.iomcu);   
      dbms_output.put_line('Nested table records: ');
      idx := queue.FIRST;
      LOOP
        EXIT WHEN idx IS NULL;
        IF l_lot MEMBER OF queue THEN
          dbms_output.put_line(queue(idx).iolotn||' '||queue(idx).ioitm||' '||queue(idx).iomcu);
        END IF;
        idx := queue.NEXT(idx);
      END LOOP;
    END;
    /
    
    Record we know is member of the nested table:
    00003323                       6880   10VILPA1
    Nested table records:
    00003323                       6880   10VILPA1
    00003323                       6350   10VILPA1
    00003323                       6343   10VILPA1
    00003323                       6377   10VILPA1
    
    
    
    Miquel Despuig
  • Miquel Despuig
    Miquel Despuig Member Posts: 4 Red Ribbon

    Thanks @Paulzip!

    As I understand it, the only way to make it work is with an object defined at the schema level and inside the object a function that will work as an equality comparison, right?

    If I wanted to avoid creating an object, the alternative would be loop through the nested table comparing each row with the record variable and maybe define the operation as a function inside the anonymous block or package?

    In your opinion is there any other method that I should be aware of?

    Again, thanks a lot for the quick response!

  • mathguy
    mathguy Member Posts: 10,895 Black Diamond
    edited Nov 24, 2022 9:56PM

    The ideal solution would be for Oracle to expose its comparison operator for equality of records, and to use it for collection comparison like "member of". They wouldn't need to do too much work, since they already have it in some form. Namely, they already implement UNIQUE constraints and indexes on tables, on multiple columns. So they already have a way to decide two rows are "the same" or not. There are restrictions on UNIQUE constraints, and the same could apply to the comparison of records, but otherwise, they already have this, they only need to expose it in PL/SQL.

    Before you look for solutions, you need to think about one potential issue: how should NULL be handled? You need to add a complete specification of this in your problem statement, before writing the first line of code.

    In its definition of "unique", Oracle says NULL is not equal to NULL (or "the same as" NULL), when you look at a single column. That is, NULL may appear any number of times in a UNIQUE column. But for multi-column conditions, it's more nuanced (and it doesn't really make sense to me). Namely, for three columns let's say, (NULL, NULL, NULL) is not the same as (NULL, NULL, NULL) - this combination may appear any number of times. But a combination with only one or two NULL may only appear once. So (3, 'ab', NULL) is the same as (3, 'ab', NULL). I don't think Oracle, or anyone for that matter, can give a meaningful reason for this choice, but this is what Oracle does.

    In your case, if the collection already contains (NULL, NULL, NULL), is this record a "member of" the collection? If it is, then your definition doesn't match Oracle's definition of "unique". Note that the map function that Paulzip suggested means that (NULL, NULL, NULL) is equal to itself; is that OK? If not, you need more direct ways to address NULL.


    EDIT: We shouldn't even need to reference UNIQUE for this discussion. Oracle has the IN condition, where it checks if a "record" (a tuple) is in a rowset. Something like

    select case when (10, 'SALESMAN') in (select deptno, job from scott.emp)
                then 1 else 0 end as IS_MEMBER_OF
    from   dual
    ;
    

    Here SCOTT.EMP is your table of records, the tuple (10, 'SALESMAN') is the record on which you want to check the "member of" condition, and IS_MEMBER_OF is not Boolean because Oracle SQL does not implement the type, but it's close enough. Oracle can check the IN condition for us; why can't we check MEMBER OF in PL/SQL in exactly the same manner? Of course, there are many restrictions on the IN condition, and I don't expect the corresponding MEMBER OF to be any more tolerant; but why not expose it at all? Now we must define object types instead of record types, and create a new map function for every record type (object type). Why??

    Miquel Despuig
  • Paulzip
    Paulzip Member Posts: 8,801 Blue Diamond
    edited Nov 25, 2022 12:44AM

    SQL can do Member Of and equality and inequality comparisons on complex types, but PL/SQL requires a map function on complex types for some reason.

    Regarding Mathguy's comments, Member Of can be a bit weird regarding null attributed object instances, which is why my solution to your case was based on usability for your scenario. If I had a null attributed object instances and was looking for it in a collection, I'd want it to return True if it were in there, perhaps that's just me. I'm sure there are arguments for the converse.

    This small sample shows some of the weird things...

    create or replace type tlot is object (
      iolotn NCHAR(30)
    , ioitm NUMBER
    , iomcu NCHAR(12)
    , ioohdj NUMBER(6,0)
    --  no map member function
    )
    /
    
    Type created.
    
    create or replace type tlots is table of tlot
    /
    
    Type created.
    
    select case
             when null_attribs member of     tlots(null_attribs) then 1
             when null_attribs not member of tlots(null_attribs) then 0
           end res1
         , case
             when null_tlot member of     tlots(null_tlot) then 1
             when null_tlot not member of tlots(null_tlot) then 0
           end res2
         , case
             when null_tlot member of     tlots(null_attribs, null_tlot) then 1
             when null_tlot not member of tlots(null_attribs, null_tlot) then 0
           end res3
         , case
             when null_attribs member of     tlots(null_attribs, null_tlot) then 1
             when null_attribs not member of tlots(null_attribs, null_tlot) then 0
           end res4
         , case
             when null_attribs member of     tlots(null_attribs, null_attribs) then 1
             when null_attribs not member of tlots(null_attribs, null_attribs) then 0
           end res5
         , case
             when null_tlot member of     tlots(null_tlot, null_tlot) then 1
             when null_tlot not member of tlots(null_tlot, null_tlot) then 0
           end res6
         , case
             when null_tlot = null_tlot then 1
             when null_tlot <> null_tlot then 0
           end res7
    from (
      select tlot(null, null, null, null) null_attribs
           , cast(null as tlot) null_tlot -- For clarity
      from dual
    )
    /
    
    
          RES1|      RES2|      RES3|      RES4|      RES5|      RES6|      RES7
    ----------|----------|----------|----------|----------|----------|----------
             0|{NULL}    |{NULL}    |{NULL}    |         0|{NULL}    |{NULL}
    
    -- RES1 and RES5 ???!!!!!!
    
    
    declare
      null_attribs tlot := tlot(null, null, null, null);
      null_tlot    tlot;
    begin
      dbms_output.put_line('Equal = '|| case
                                          when null_attribs =  null_attribs then '1'
                                          when null_attribs <> null_attribs then '0'
                                          else 'null'
                                        end);
    end;
    /
                                          when null_attribs =  null_attribs then '1'
                                                            *
    ERROR at line 6:
    ORA-06550: line 6, column 57:
    PLS-00526: A MAP or ORDER function is required for comparing objects in PL/SQL.
    ORA-06550: line 7, column 57:
    PLS-00526: A MAP or ORDER function is required for comparing objects in PL/SQL.
    
    declare
      null_attribs tlot := tlot(null, null, null, null);
      null_tlot    tlot;
    begin
      dbms_output.put_line('Member Of = '|| case
                                              when null_attribs member of     tlots(null_attribs) then '1'
                                              when null_attribs not member of tlots(null_attribs) then '0'
                                              else 'null'
                                            end);
    end;
    /
                                              when null_attribs member of     tlots(null_attribs) then '1'
                                                   *
    ERROR at line 6:
    ORA-06550: line 6, column 48:
    PLS-00526: A MAP or ORDER function is required for comparing objects in PL/SQL.
    ORA-06550: line 7, column 48:
    PLS-00526: A MAP or ORDER function is required for comparing objects in PL/SQL.
    
    
    Miquel Despuig
  • Miquel Despuig
    Miquel Despuig Member Posts: 4 Red Ribbon

    @mathguy

    Honestly, I didn't think ahead about how to handle nulls. It seems to me that it has more nuances than I was expecting. I was hoping to take advantage of MEMBER OF function to check if my object indeed was inside a nested table that I'm using as a queue or stack for a sort of graph traversal (by the way, nice threat).

    I think I'll go with making sure I don't get nulls when the objects are created inside a custom constructor and raise an error if that's the case, since the data should never have nulls because the fields are primary keys of the table i'm pulling data from.

    @Paulzip thanks for the examples on odd behaviours of the function! Good to have it documented here!

    OFFTOPIC

    I can't figure out where's the button to make que question answered!😅

  • Miquel Despuig
    Miquel Despuig Member Posts: 4 Red Ribbon

    Have read this doc, but the button mentioned is not appearing for me neither in Edge nor Firefox even after deleting cookies and all that good stuff.

  • mathguy
    mathguy Member Posts: 10,895 Black Diamond

    The reason you don't see the "Accept Answer" button is that you did not set up your original post as a question.

    On this site you can post either a "question" or a "discussion". I thought "question" is the default (but perhaps I am wrong); in any case, you can change the default before you post the question/discussion (before the original post).

    I believe a moderator can change your thread from a "discussion" to a "question", I've seen it done before; alas, you can't do it. (Why the original poster can't but a moderator can, I have no idea.)

  • mathguy
    mathguy Member Posts: 10,895 Black Diamond

    There is (at least) one way to avoid defining our own version of "equality of records/objects" and use Oracle's definition - and implementation - of the IN condition. You can tell that I am not at all happy with defining "equality" for each record/object type I must use, when what I mean is actual equality of records and not something else!

    Note that I make no attempt to handle NULL in any way other than what Oracle does with the IN condition.

    For that, we need to declare both the "record type" and the "nested-table-of-records type" at the schema level (so we will have "object type" and "table of such objects"). The object type only needs properties, it doesn't need methods - so it doesn't need a body definition. This resembles "records" more closely.

    create type my_obj_type as object
    ( id      number
    , message varchar2(100)
    , dt      date
    );
    /
    
    create or replace type mot_list is table of my_obj_type;
    /
    

    (Note that I chose a different type than was shown in the original post - that's on purpose.)

    Then in the block below, I declare a collection of "records" - really objects (hard-coded, no need to use a table for this) and two objects to test the "member of" workaround on them. We end up with something just slightly less disgusting than a jellyfish:

    declare
      lx   mot_list := mot_list ( my_obj_type(101, 'win', date '2022-03-20'),
                                  my_obj_type(103, 'ok' , date '2021-12-02'),
                                  my_obj_type(105, 'yes', date '2022-01-10')
                                );
      o1   my_obj_type := my_obj_type(103, 'ok', date '2021-12-02');
      o2   my_obj_type := my_obj_type(105, null, date '2022-01-10');
      flag number;
    begin
      select case when (o1.id, o1.message, o1.dt) in (select * from table(lx))
                  then 1 else 0 end
        into flag
        from dual;
      dbms_output.put_line('Result for o1: ' || case flag when 1 then 'yes' else 'no' end);
    
      select case when (o2.id, o2.message, o2.dt) in (select * from table(lx))
                  then 1 else 0 end
        into flag
        from dual;
      dbms_output.put_line('Result for o2: ' || case flag when 1 then 'yes' else 'no' end);
    end;
    /
    
    
    Result for o1: yes
    Result for o2: no
    
    
    PL/SQL procedure successfully completed.
    

    Note that Oracle SQL doesn't support the Boolean data type, so I had to fake it with the numeric FLAG; if a true Boolean is needed in the PL/SQL context, that can be arranged easily.

    Miquel Despuig
  • Paulzip
    Paulzip Member Posts: 8,801 Blue Diamond

    @mathguy 23c has finally introduced boolean to SQL

    Miquel Despuig