Re: Quick compare of PL/SQL records

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Fri, 29 Apr 2005 17:22:38 +0300
Message-ID: <6e49b6d005042907226df92930@mail.gmail.com>

The only sensible built in comparison I could imagine with records probably is equivalence i.e. as it is for object types (and also only in SQL not PL/SQL as you can see below). But I cannot imagine how one can define built in operators like > or < for records, because each record could have very special meaning for his member fields.

17:10:07 gints_at_CMISMGR2> create type objtype as object (a number, b number)= ;
17:10:29 2 /

Type created.

Elapsed: 00:00:00.00
17:10:30 gints_at_CMISMGR2> create table objtab (a objtype);

Table created.

Elapsed: 00:00:00.00
17:10:58 gints_at_CMISMGR2> insert into objtab values (objtype(1, 1));

1 row created.

Elapsed: 00:00:00.00
17:11:19 gints_at_CMISMGR2> select * from objtab where a =3D a;

A(A, B)

---------------------------------------------------------------------------=

---

OBJTYPE(1, 1) 1 row selected.

Elapsed: 00:00:00.00
17:11:38 gints_at_CMISMGR2> select * from objtab where a > a; select * from objtab where a > a

                           *

ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method

Elapsed: 00:00:00.00

17:11:44 gints_at_CMISMGR2> declare
17:11:51   2  var1 objtype;
17:11:57   3  var2 objtype;
17:12:03   4  begin
17:12:06   5    var1 :=3D objtype(1, 1);
17:12:17   6    var2 :=3D objtype(1, 1);
17:12:25   7    if var1 =3D var2 then=20
17:12:37   8      null;
17:12:40   9    else=20
17:12:43  10      null;
17:12:48  11    end if;
17:12:50  12  end;
17:12:52  13  /
  if var1 =3D var2 then
          *

ERROR at line 7:
ORA-06550: line 7, column 11:
PLS-00526: A MAP or ORDER function is required for comparing objects in PL/= SQL. Elapsed: 00:00:00.00
17:12:53 gints_at_CMISMGR2>=20

On 4/29/05, Ranko Mosic <ranko.mosic_at_gmail.com> wrote:
> Yes, I know what manual says - that's why I am posting the question here.
> The thing is, I need to compare about 100 types of records with dozens of
> attributes each. I would like to be able to avoid field level
> comparison and do it
> on record level.
> rm
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 29 2005 - 10:26:57 CDT