Classic Metalink Retires

I found this message on the Classic MetaLink/My Oracle Support website when I went to create a service request this weekend. I tried calling the 800 number but only got a recorded message. I left a message but was not called back. Luckily, we were able to solve our problem ourselves.

A READ-ONLY version of Classic MetaLink is available for Oracle customers and employees during the upgrade from Classic MetaLink/My Oracle Support to the new My Oracle Support platform. During the upgrade weekend, the My Oracle Support portal will not be available. Knowledge Base, Patches & Updates, and Certify are available via this READ-ONLY version of Classic MetaLink during the upgrade period.

PLEASE NOTE: The Knowledge Search is not functioning. If you know the document ID you can view individual documents by using the following URL format and replacing the document ID at the end with the document you want to view:

http://metalink2.oracle.com/metalink/plsql/showdoc?db=NOT&id=466181.1

For any Service Request needs during the upgrade period, please contact Global Customer Support via telephone. This READ-ONLY version of Classic MetaLink can be used to view Service Requests only. Please do not update your Service Request or make changes. You will encounter errors if any updates or changes are attempted. You may copy the contents of your Service Request as needed, but changes and updates will only be available once the upgrade has been completed on the portal.

Following the upgrade, My Oracle Support will become the single online support platform for all Oracle products, and Classic MetaLink will retire.

Fun with XOR

This story begins with a puzzling question asked in one of the OTN forums: “I need some help to know what’s the auxiliary constructs, and examples in the auxiliary constructs.” Eventually the question was traced to the following SQL problem posted on the University of Freiburg’s website:

“Exchange the members of NATO and EU without introducing auxiliary constructs (tables, views, auxiliary entries, PL/SQL). Hint: compute the new value of the attribute Organization as a function from the original one. Note: do not use Oracle’s DECODE-function.”

In another version of the problem, there were additional prohibitions: “DECODE, CASE, REGEXP_REPLACE etc. are not allowed.”

The table required for the exercise is from the Mondial database:

CREATE TABLE isMember
(Country VARCHAR2(4),
 organization VARCHAR2(12),
 Type VARCHAR2(35) DEFAULT 'member',
 CONSTRAINT MemberKey PRIMARY KEY (Country,organization) );

Here is the data for the problem:

INSERT INTO isMember VALUES ('GR','NATO','member');
INSERT INTO isMember VALUES ('F','NATO','member');
INSERT INTO isMember VALUES ('E','NATO','member');
INSERT INTO isMember VALUES ('D','NATO','member');
INSERT INTO isMember VALUES ('I','NATO','member');
INSERT INTO isMember VALUES ('B','NATO','member');
INSERT INTO isMember VALUES ('L','NATO','member');
INSERT INTO isMember VALUES ('NL','NATO','member');
INSERT INTO isMember VALUES ('TR','NATO','member');
INSERT INTO isMember VALUES ('DK','NATO','member');
INSERT INTO isMember VALUES ('N','NATO','member');
INSERT INTO isMember VALUES ('IS','NATO','member');
INSERT INTO isMember VALUES ('P','NATO','member');
INSERT INTO isMember VALUES ('GB','NATO','member');
INSERT INTO isMember VALUES ('CDN','NATO','member');
INSERT INTO isMember VALUES ('USA','NATO','member');

INSERT INTO isMember VALUES ('GR','EU','member');
INSERT INTO isMember VALUES ('F','EU','member');
INSERT INTO isMember VALUES ('E','EU','member');
INSERT INTO isMember VALUES ('A','EU','member');
INSERT INTO isMember VALUES ('CZ','EU','member');
INSERT INTO isMember VALUES ('D','EU','member');
INSERT INTO isMember VALUES ('H','EU','member');
INSERT INTO isMember VALUES ('I','EU','member');
INSERT INTO isMember VALUES ('SK','EU','member');
INSERT INTO isMember VALUES ('SLO','EU','member');
INSERT INTO isMember VALUES ('LV','EU','member');
INSERT INTO isMember VALUES ('LT','EU','member');
INSERT INTO isMember VALUES ('PL','EU','member');
INSERT INTO isMember VALUES ('B','EU','member');
INSERT INTO isMember VALUES ('L','EU','member');
INSERT INTO isMember VALUES ('NL','EU','member');
INSERT INTO isMember VALUES ('BG','EU','member');
INSERT INTO isMember VALUES ('RO','EU','member');
INSERT INTO isMember VALUES ('DK','EU','member');
INSERT INTO isMember VALUES ('EW','EU','member');
INSERT INTO isMember VALUES ('SF','EU','member');
INSERT INTO isMember VALUES ('S','EU','member');
INSERT INTO isMember VALUES ('IRL','EU','member');
INSERT INTO isMember VALUES ('M','EU','member');
INSERT INTO isMember VALUES ('P','EU','member');
INSERT INTO isMember VALUES ('GB','EU','member');
INSERT INTO isMember VALUES ('CY','EU','member');
INSERT INTO isMember VALUES ('MK','EU','membership applicant');
INSERT INTO isMember VALUES ('HR','EU','membership applicant');
INSERT INTO isMember VALUES ('TR','EU','membership applicant');

Here’s my solution. It makes use of the properties of the XOR operation. Oracle provides the bit_XOR function for the purpose.

p XOR p = 0
p XOR q = q XOR p
(p XOR q) XOR r = p XOR (q XOR r)

('EU  ' XOR 'EU  ') XOR 'NATO' = 'NATO'
('NATO' XOR 'EU  ') XOR 'NATO' = 'EU  '

UPDATE isMember
SET organization =
  RTRIM
  (
    UTL_RAW.cast_to_varchar2
    (
      UTL_RAW.bit_xor
      (
        UTL_RAW.bit_xor
        (
          UTL_RAW.cast_to_raw (RPAD(organization, 4)),
          UTL_RAW.cast_to_raw ('EU  ')
        ),
        UTL_RAW.cast_to_raw ('NATO')
      )
    )
  )
WHERE organization IN ('EU', 'NATO');

As with the First International NoCOUG SQL Challenge, I’m sure there are other solutions.