member and collection type [message #684852] |
Wed, 08 September 2021 12:26  |
pointers
Messages: 446 Registered: May 2008
|
Senior Member |
|
|
Hi,
I have an object and a collection based on the object.
I would like to check if the object error_message exists in the collection error_message.
I could do it using loop as shown in the sr_func, but the issue is that my collection is huge in size (below is just a sample, not real data) so I would like
to avoid LOOP to improve performance using MEMBER OF functionality as shown in sr_func_upd but getting error as shown below.
Please suggest.
Thank you in advance.
create or replace type sr_obj as object (error_code number, error_message varchar2(200), error_details varchar2(200));
create or replace type sr_ntt as table of sr_obj;
create or replace function sr_func (p_sr_obj sr_obj, p_sr_ntt sr_ntt) return boolean
as
l_message varchar2(200);
begin
l_message := p_sr_obj.error_message;
for i in 1..p_sr_ntt.count
loop
if (instr(p_sr_obj.error_message,p_sr_ntt(i).error_message )<>0)
then
return true;
end if;
end loop;
return false;
end sr_func;
/
-- calling function --
declare
l_sr_obj sr_obj;
l_sr_ntt sr_ntt;
l_val boolean;
begin
l_sr_ntt:= sr_ntt();
l_sr_ntt.extend(2);
l_sr_obj:= sr_obj(1,'no_data_found sometimes','raised no data found');
l_sr_ntt(1) := sr_obj(1,'test','raised testing');
l_sr_ntt.extend(2);
l_sr_ntt(2) := sr_obj(1,'no_data_found','raised when testing');
l_val:= sr_func(l_sr_obj,l_sr_ntt);
if (l_val)
then
dbms_output.put_line('val is found');
else
dbms_output.put_line('val is not found');
end if;
end;
/
----------------------------------------------------------------------------------------------
-- trying to make this work
create or replace function sr_func_upd (p_sr_obj sr_obj, p_sr_ntt sr_ntt) return boolean
as
l_message varchar2(200);
begin
l_message := p_sr_obj.error_message;
if (l_message member of p_sr_ntt )
then
return true ;
else
return false;
end if;
end sr_func_upd;
/
Error: Error(6,5): PLS-00306: wrong number or types of arguments in call to 'MEMBER OF'
|
|
|
|
|
Re: member and collection type [message #684855 is a reply to message #684854] |
Wed, 08 September 2021 13:38   |
 |
Michel Cadot
Messages: 68418 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
For instance:
SQL> create or replace function sr_func_upd (p_sr_obj sr_obj, p_sr_ntt sr_ntt) return boolean
2 as
3 l_message varchar2(200);
4 l_dummy pls_integer;
5 begin
6 l_message := p_sr_obj.error_message;
7 select 1 into l_dummy from table(p_sr_ntt) where error_message = l_message;
8 return true;
9 exception when no_data_found then return false;
10 end sr_func_upd;
11 /
Function created.
SQL> declare
2 l_sr_obj sr_obj;
3 l_sr_ntt sr_ntt;
4 l_val boolean;
5 begin
6 l_sr_ntt:= sr_ntt();
7 l_sr_ntt.extend(2);
8 l_sr_obj:= sr_obj(1,'no_data_found sometimes','raised no data found');
9 l_sr_ntt(1) := sr_obj(1,'test','raised testing');
10 l_sr_ntt.extend(2);
11 l_sr_ntt(2) := sr_obj(1,'no_data_found','raised when testing');
12 l_val:= sr_func(l_sr_obj,l_sr_ntt);
13 if (l_val) then
14 dbms_output.put_line('val is found');
15 else
16 dbms_output.put_line('val is not found');
17 end if;
18 end;
19 /
val is found
PL/SQL procedure successfully completed.
|
|
|
|