Home » SQL & PL/SQL » SQL & PL/SQL » executing a function which returns boolean
executing a function which returns boolean [message #339113] Wed, 06 August 2008 15:53 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
I have a table

create table ex(id number,name varchar2(3))


insert into ex values(1,'o');
/
insert into ex values(1,'o');
/
insert into ex values(1,'o')




i wrote a function which checks duplicates



create or replace FUNCTION  is_dup_ex(i_org_id number)
                          
      RETURN BOOLEAN
     IS
     
      l_item  number:= NULL;
     BEGIN
       
  SELECT count(distinct  id) 
    into l_item
 FROM 
   ex
WHERE
   id = i_org_id; 

 if l_item = 1 then
       
  	 RETURN TRUE;

end if;
       
  	 EXCEPTION
  	 WHEN NO_DATA_FOUND THEN
  	   RETURN FALSE;
  
  	 WHEN TOO_MANY_ROWS THEN
  	   RETURN TRUE;
  	  
     END is_dup_ex;


now how to execute this function? and how to call it?

in sqlplus, i am trying to execute it

but how to define host variable which is boolean?

Re: executing a function which returns boolean [message #339115 is a reply to message #339113] Wed, 06 August 2008 16:02 Go to previous messageGo to next message
Littlefoot
Messages: 20899
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You can not (in SQL, but yes in PL/SQL). Return something else (number or character, such as 0/1, Y/N etc.).
Re: executing a function which returns boolean [message #339463 is a reply to message #339113] Thu, 07 August 2008 09:50 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
OK, so i did test it with this procedure



create or replace procedure mproc is

cursor mycur is select id from ex;
l_dup boolean;
begin

for myrec in mycur loop
 
 	l_dup := is_dup_ex(myrec.id);

        dbms_output.put_line('l_dup is '||l_dup);
	
	if (l_dup) then
	
	dbms_output.put_line('record'|| myrec.id ||' exits');
	
	else
		
    dbms_output.put_line('no problem');
	
	end if;
	
end loop;

end mproc;



i am storing the result in a boolean variable..(l_dup)...

if(l_dup) should give me true if duplicate exists..

this works fine, but the problem is, when i am trying to

see the value of l_dup...by printing it, i am getting a

PLS-00306: wrong number or types of arguments in call to ||


cant i see the value of l_dup ?
Re: executing a function which returns boolean [message #339464 is a reply to message #339463] Thu, 07 August 2008 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not describe what you have, use SQL*Plus and copy and paste your session.

Regards
Michel
Re: executing a function which returns boolean [message #339471 is a reply to message #339463] Thu, 07 August 2008 10:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ora1980 wrote on Thu, 07 August 2008 16:50

this works fine, but the problem is, when i am trying to see the value of l_dup...by printing it, i am getting a
PLS-00306: wrong number or types of arguments in call to ||

cant i see the value of l_dup ?

Not by using it directly. You cannot use implicit conversion from boolean to string in PL/SQL.
Since dbms_output (or || in this case) takes string(s) as parameters, you will have to convert that boolean yourself.
The easiest way is to use CASE.

dbms_output.put_line('The value is '||CASE WHEN .... THEN '...' ELSE '...' END);

Now you fill in the dots.
Re: executing a function which returns boolean [message #339481 is a reply to message #339113] Thu, 07 August 2008 10:29 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
now from this understanding, i implemented the same logic

in my application..and wrote the following function

FUNCTION is_dup_cat(i_org_id number,
                          i_cat_set_id    NUMBER,
                           i_cat_id number,
                            i_str_id number,
                            i_segment VARCHAR2 )
      RETURN BOOLEAN
     IS
     
      l_item  number := NULL;
     BEGIN
       
  SELECT count(distinct	 MSI.SEGMENT1) 
    into l_item
 FROM 
   MTL_SYSTEM_ITEMS MSI,
   MTL_ITEM_CATEGORIES MIC,
   MTL_CATEGORIES MC,
   MTL_CATEGORY_SETS MCS
WHERE
  MSI.INVENTORY_ITEM_ID=MIC.INVENTORY_ITEM_ID AND
  MSI.ORGANIZATION_ID=MIC.ORGANIZATION_ID AND
  MIC.CATEGORY_ID=MC.CATEGORY_ID AND
  MIC.CATEGORY_SET_ID=MCS.CATEGORY_SET_ID AND
  MC.STRUCTURE_ID=MCS.STRUCTURE_ID 
  AND MSI.ORGANIZATION_ID = i_org_id AND
  MCS.CATEGORY_SET_ID = i_cat_set_id AND
  MC.CATEGORY_ID = i_cat_id AND
   MC.STRUCTURE_ID = i_str_id 
   AND MSI.SEGMENT1 = i_segment; 
   
   if l_item = 1 then
       
  	 RETURN (TRUE);
   
   end if;
   
  -- RETURN TRUE;
  	 EXCEPTION
  	 WHEN NO_DATA_FOUND THEN
  	   RETURN FALSE;
  
  	 WHEN TOO_MANY_ROWS THEN
  	   RETURN TRUE;
  	  
  END is_dup_cat;



and i am using this function the following way

l_dup_cat := is_dup_cat(l_item_rec.organization_id,l_cat_set_id,l_cat_id,
                 l_str_id,l_item_rec.segment1);
    		  
    		  IF (l_dup_cat) THEN
    
    		     l_status := 'E';
    		  
      	         l_error_rec.error_stage   := 'VALIDATE_DUP_CAT_ITEM';
           	     l_error_rec.error_message := 'CATEGORY ALREADY ASSIGNED TO ITEM';
    	         l_error_rec.error_value   := l_item_rec.organization_id;
    
    		     log_error(i_error_rec => l_error_rec);
    		  
		  END IF;



sorry for poor indentation....but i am getting an error
in my log table...saying

ORA-06503: PL/SQL: Function returned without value

now whats wrong with my function? i am returning TRUE if
the count if l_item is 1...so should i include
another return TRUE which I have commented ?


Re: executing a function which returns boolean [message #339484 is a reply to message #339113] Thu, 07 August 2008 10:36 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
i have handled too_many_rows by returning true...

so why does my function give this error?
Re: executing a function which returns boolean [message #339488 is a reply to message #339113] Thu, 07 August 2008 10:47 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
OH, I have given select count()...in my select stmt,
and i have tried to handle no_data_found, too_many_rows

i dont think a count of 0 will give me no_data_found,
or a count of 4 will give me too_many_rows
Re: executing a function which returns boolean [message #339489 is a reply to message #339113] Thu, 07 August 2008 10:59 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
ok i got it,

i have modified my function..i just removed the count...

Re: executing a function which returns boolean [message #339490 is a reply to message #339489] Thu, 07 August 2008 11:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Great habit, talking to yourself Wink

Good to see you solved your problem.
Re: executing a function which returns boolean [message #339542 is a reply to message #339481] Thu, 07 August 2008 16:24 Go to previous messageGo to next message
Littlefoot
Messages: 20899
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ora1980
sorry for poor indentation

Don't be sorry. Fix it!
Re: executing a function which returns boolean [message #339731 is a reply to message #339542] Fri, 08 August 2008 04:45 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:


if l_item = 1 then

RETURN (TRUE);

end if;





If l_item = 0 then return Flase.Thats fine.I am ok with that.
But what will happend if l_item is 2 or 3 or 4..? False?? Please take care! Smile

Regards,
Oli

[Updated on: Fri, 08 August 2008 04:47]

Report message to a moderator

Previous Topic: How to update the rows?
Next Topic: using case and group by [merged]
Goto Forum:
  


Current Time: Wed Dec 07 13:00:35 CST 2016

Total time taken to generate the page: 0.08754 seconds