Re: Q: To check a record's existence in a table, FAST!

From: Petter Harnes <Petter.Harnes_at_teleplan.no>
Date: 1997/05/09
Message-ID: <01bc5c48$f8bd7da0$3a1919ac@kd8qg>#1/1

Why not use EXISTS (seems to me like that why it's there) In my application I've used a soultion with a helptable OneValue (which contain one column (int) and one row (1)):

 FUNCTION RecordExist RETURN BOOLEAN IS

    CURSOR Check IS

     SELECT Val
        FROM OneValue
     WHERE EXISTS (SELECT *
                               FROM <table-name>
                               WHERE <where-clause>);
     vVal INTEGER; -- Dummy
 BEGIN
     FETCH Check INTO Val;
     IF Check%found THEN
          RETURN TRUE;
     END IF;
     RETURN False;

 END RecordExist;  

Uses explicit cursor to avoid a second fetch. If you use implicit cursor there is a second fetch performed to verify if Oracle should raise exception TO_MANY_ROWS.

---

---

Petter Harnes,
Consultant, Teleplan AS
Petter.Harnes_at_teleplan.no Received on Fri May 09 1997 - 00:00:00 CDT