Click here to Skip to main content
15,669,040 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, i have searched online for quite a bit now, but i still havent come across anything that i can use to solve my issue. I am trying to put an EXCEPTION when the parameter is incorrect, it will prompt the user that the parameter is incorrect.

So the thing is, when the parameter is wrong, it will not go into the LOOP, which is why i am having trouble trying to solve it.

create or replace function ListNation(regionName char)
return  varchar2 is
myOutput varchar2(300);
nation_Name varchar2(200);
	myOutput := '';
	for rowResult in (select r_regionkey, r_name, listagg(rtrim(n_name), ', ') within
	group(order by n_name)
	as nation_Name
	from region
	inner join nation
	on r_regionkey =  n_regionkey
	where r_name = upper(regionName)
	group by r_name, r_regionkey)

		myOutput := rowResult.r_regionkey|| ' ' ||rtrim(rowResult.r_name)|| ': '
		|| rowResult.nation_Name;
	end loop;

	return myOutput;

end ListNation;  
select listnation('Americ') from dual;

What I have tried:

SQL> select listnation('Americ') from dual;


1 row selected.
Updated 19-May-21 3:49am
Richard MacCutchan 19-May-21 7:34am    
Maybe because there is no country named "AMERIC".
Member 15206072 19-May-21 9:03am    
Yes i know, thats why i want to throw an exception if there isnt any, instead of returning empty row. I am still having an issue figuring out
Richard MacCutchan 19-May-21 9:27am    
Then you need to check the return value and take action if it does not return any rows.
Member 15206072 19-May-21 9:42am    
if(myOutput='') then
dbms_output.put_line('No record found');
end if;
Tried using an if statement but if doenst output anything too
Richard MacCutchan 19-May-21 10:19am    
You need to establish exactly what text is being set in the myOutput variable. Looking at the statement
myOutput := rowResult.r_regionkey|| ' ' ||rtrim(rowResult.r_name)|| ': '
		|| rowResult.nation_Name;

I would guess it could well be " : ".

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900