Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-22950 and sub-select

crupperMar 12 2008 — edited Mar 13 2008
I get a ORA-22950 bug, but I put the function anschrift to a sub-select. It has no effect and I do not know why.
CREATE OR REPLACE TYPE Anschrift_TYPE AS OBJECT
( str VARCHAR2(100),
  plz VARCHAR2(10),
  ort VARCHAR2(100),
  zus VARCHAR2(100));


CREATE OR REPLACE FUNCTION Anschrift
 (p_pa_id IN NUMBER,
  p_typ   IN VARCHAR2 DEFAULT NULL)
RETURN Anschrift_TYPE IS
...
begin
...
end;


SQL> SELECT  ksgr,
  2          ksart,
  3          bekv,
  4          plz,
  5          SUM(DECODE (ksaid, '6', 1, 0)) anzahl_bkk,
  6          SUM (DECODE (ksgr, 1, 1, 2, 1, 0)) anzahl_gkv 
  7      FROM(select kn_ska_q042006.kska_kgr ksgr,
  8                  kassen.ks_ksa_id ksart,
  9                  kassen.ks_bekv_nr bekv,
 10                  anschrift(kska_pa) plz,
 11                  ks_ksa_id ksaid
 12           from kn_ska_q042006, kassen
 13     WHERE (    (kassen.ks_ksgr_id = kn_ska_q042006.kska_kgr)
 14            AND (kassen.ks_kzv_id = kn_ska_q042006.kska_kkzv)
 15            AND (kassen.ks_reg_kz = kn_ska_q042006.kska_kreg)
 16            AND (kassen.ks_serie_nr = kn_ska_q042006.kska_kser)
 17            AND (kassen.ks_pruefz = kn_ska_q042006.kska_kprf)
 18            AND (kassen.ks_abr_st = kn_ska_q042006.kska_kabr)
 19            AND ((kassen.ks_bekv_nr = 137372424900))))
 20  GROUP BY ksgr,
 21          ksart,
 22          bekv,
 23          plz;
        plz
        *
FEHLER in Zeile 23:
ORA-22950: ORDER-Befehl für Objekte ohne Methoden MAP bzw. ORDER nicht möglich

SQL>

Comments

Billy Verreynne
The Oracle message file says:
22950, 00000, "cannot ORDER objects without MAP or ORDER method"
// *Cause: an object type must have a MAP or ORDER method defined for
// all comparisons other than equality and inequality comparisons.
// *Action: Define a MAP or ORDER method for the object type

Sounds pretty straight forward - the ADT (Advance Data Type) that you have created needs a MAP or ORDER method to be used in the fashion you are using it.

You need to define comparison method for the ADT as detailed in [url http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14260/adobjbas.htm#sthref211]Oracle® Database Application Developer's Guide - Object-Relational Features.
crupper
THX! I solved my problem: 
CREATE OR REPLACE TYPE ANSCHRIFT_TYPE AS OBJECT
(
  STR VARCHAR2(100),
  PLZ VARCHAR2(10),
  ORT VARCHAR2(100),
  ZUS VARCHAR2(100),
  MAP MEMBER FUNCTION ANSCHRIFTEN RETURN VARCHAR2
);

CREATE OR REPLACE TYPE BODY ANSCHRIFT_TYPE AS 
MAP MEMBER FUNCTION ANSCHRIFTEN RETURN VARCHAR2 IS 
BEGIN
  NULL;
END; 
END;
/
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 10 2008
Added on Mar 12 2008
2 comments
4,431 views