This forum is currently locked. You can't register or post questions at this time. (read more)

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

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

shoan
Senior Member
Posts: 123
Joined: Thu Mar 17, 2011 6:27 am

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 4 guests

cron