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

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

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

Postby shoan » Wed Jan 02, 2013 12:29 pm

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
shoan
Senior Member
 
Posts: 120
Joined: Thu Mar 17, 2011 6:27 am

Re: Create function in oracle with return true if rec is fou

Postby Tim... » Wed Jan 02, 2013 1:19 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 6 guests

cron