create or replace procedure p1_comm
(v_comm_name IN prohibited_commodity.CCOMMODITY_NAME%type,
v_comm_port IN prohibited_commodity. CPROHIBITED_PORT%type,
v_record_found out varchar2)
as
record_found NUMBER:=0;
v_port_name varchar2(30):= 'ALL';
TYPE proh_typ IS TABLE of prohibited_commodity%rowtype index by binary_integer;
proh_details proh_typ;
BEGIN
select *
bulk collect into proh_details
from prohibited_commodity;
for i in proh_details.first.. proh_details.last
loop
if proh_details(i).CPROHIBITED_PORT = v_comm_port
or proh_details(i).CPROHIBITED_PORT = 'ALL'
and proh_details(i).CCOMMODITY_NAME = v_comm_name
then
record_found := 1;
dbms_output.put_line (' EQUAL');
else
dbms_output.put_line ('NOT EQUAL');
end if ;
end loop;
Exception
when no_data_found then null ;
end p1_comm ;

 create or replace procedure P_COMM
       (v_comm_name IN prohibited_commodity.CCOMMODITY_NAME%type,
       v_comm_port IN prohibited_commodity. CPROHIBITED_PORT%type,
       v_record_found out varchar2)
       as
       record_found NUMBER:=0;
       v_port_name  varchar2(30):= 'ALL';
   TYPE t_proh_cname is table of  prohibited_commodity.CCOMMODITY_NAME%type;
   TYPE t_proh_port     is table of  prohibited_commodity.CPROHIBITED_PORT%type;
   l_proh_cname  t_proh_cname ;
   l_proh_port   t_proh_port ;
   BEGIN
   SELECT CCOMMODITY_NAME,CPROHIBITED_PORT bulk collect into l_proh_cname,l_proh_port
   FROM  prohibited_commodity;
   for i in l_proh_cname.first ..l_proh_cname.last loop
      if l_proh_port(i) IN ('v_comm_port','ALL')
         and l_proh_cname(i) = v_comm_name
     then 
    record_found := 1; 
    dbms_output.put_line (' EQUAL');

    else

    dbms_output.put_line ('NOT EQUAL');

    end if ;

    end loop;
    v_record_found := record_found ;

    Exception
    when no_data_found then null ;
    end P_COMM ;

DECLARE
  TYPE l_Name IS TABLE OF emp.eName%TYPE INDEX BY BINARY_INTEGER ;
   TYPE l_sal IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER ;
   l_Name_Tab  L_NAME;
   l_sal_Tab   L_SAL;
BEGIN
  SELECT eName,
         sal
  BULK COLLECT INTO l_Name_Tab,
         l_sal_Tab
  FROM   emp
  WHERE  deptNo IN (1,
                    2);
  
  IF (l_Name_Tab.COUNT) > 0 THEN
    dbms_Output.Put_Line('equal');
  ELSE
    dbms_Output.Put_Line('not equal');
  END IF;
END;

create or replace procedure P_COMM1
        (v_comm_name IN prohibited_commodity.CCOMMODITY_NAME%type,
        v_comm_port IN prohibited_commodity. CPROHIBITED_PORT%type,
        v_record_found out varchar2)
        as
        record_found NUMBER:=0;
        v_port_name  varchar2(30):= 'ALL';
    TYPE t_proh_cname is table of  prohibited_commodity.CCOMMODITY_NAME%type;
    TYPE t_proh_port     is table of  prohibited_commodity.CPROHIBITED_PORT%type;
    l_proh_cname  t_proh_cname ;
    l_proh_port   t_proh_port ;
    BEGIN
    SELECT CCOMMODITY_NAME,CPROHIBITED_PORT bulk collect into l_proh_cname,l_proh_port
    FROM  prohibited_commodity
    WHERE CPROHIBITED_PORT IN ('v_comm_port','ALL')
     and  CCOMMODITY_NAME = ('v_comm_name') ;
       if (l_proh_cname.count) > 0
      then
       record_found := 1;
    else
       record_found := 0;
     end if;
  v_record_found := record_found ;
   Exception
   when no_data_found then null ;
   end P_COMM1 ;

WHERE CPROHIBITED_PORT IN ('v_comm_port','ALL')
  and  CCOMMODITY_NAME = ('v_comm_name') ;