Create function in oracle with return true if rec is found.

[shoan]

Hi Team,

I have to create a plsql function in oracle with return the value "True" if the record is found. If the record is not found then it will return "False".

I am new in plsql .Can any body help me by providing example for the above scenario ?


Thanks in Advance
Shoan

[Tim...]

Hi.

Well, you don;t say anything about your table or the search criteria you have, so I will give a general example.

Code: Select all

CREATE OR REPLACE FUNCITON rec_check (p_id IN my_table.id%TYPE)
  RETURN BOOLEAN
AS
  l_row my_table%ROWTYPE;
BEGIN
  SELECT *
  INTO   l_row
  FROM   my_table
  WHERE  id = p_id;
 
  RETURN TRUE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN FALSE;
END;
/

The function accepts a parameter indicating the ID of the row you are searching for. The query pulls back that row. If it finds it, it returns TRUE. If the row is not found a NO_DATA_FOUND exception is triggered, which is caught and returns FALSE.

If you want anything more specific, send the CREATE TABLE and INSERT statements to build a test case and be more specific about your requirements.

Cheers

Tim...