Home » SQL & PL/SQL » SQL & PL/SQL » member and collection type (oracle 12.1)
member and collection type [message #684852] Wed, 08 September 2021 12:26 Go to next message
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 #684853 is a reply to message #684852] Wed, 08 September 2021 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"p_sr_ntt" is of type "sr_ntt" which a table of "sr_obj" which is
"object (error_code number, error_message varchar2(200), error_details varchar2(200))"

"l_message" is of type "varchar2(200)" and so NOT "sr_obj" and so it can't be a member of a table of "sr_obj".

https://docs.oracle.com/database/121/SQLRF/conditions006.htm#SQLRF52135

Quote:
expr must be of the same type as the element type of the nested table
Re: member and collection type [message #684854 is a reply to message #684853] Wed, 08 September 2021 13:08 Go to previous messageGo to next message
pointers
Messages: 446
Registered: May 2008
Senior Member
Thank you Michael...

I am not sure how to fix this or any other alternative approach to avoid loops.

Could you please help.

Regards,
Pointers
Re: member and collection type [message #684855 is a reply to message #684854] Wed, 08 September 2021 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
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.
Re: member and collection type [message #684862 is a reply to message #684855] Sat, 11 September 2021 01:57 Go to previous message
pointers
Messages: 446
Registered: May 2008
Senior Member
Thank you Micheal....
Previous Topic: Slab Level query (merged)
Next Topic: Oracle R12 query to retrieve a list of AP invoices and the corresponding file name/path
Goto Forum:
  


Current Time: Thu Oct 21 09:43:27 CDT 2021