DECLARE 

QUR varchar2( 1000 );

BEGIN

QUR:='select templ_id into gen_arr from tc_templs_user where user_id =ALT';
--HTP.P(TO_CHAR(QUR));

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
p_collection_name => ‘IR_TEST’,
p_query => QUR );


END;

QUR:='select templ_id from tc_templs_user where user_id =ALT';

QUR:='select templ_id from tc_templs_user where user_id =''ALT''';

DECLARE
QUR VARCHAR2( 1000 );
gen_arr apex_application_global.vc_arr2; -- äëÿ ids òåìïëåéòîâ

BEGIN

select templ_id into QUR  from tc_templs_user where user_id ='ALT';

gen_arr( 1 ):= QUR;

FOR z IN  1 ..gen_arr.count LOOP

dbms_output.put_line(gen_arr(z));
          
END LOOP;

/*APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
p_collection_name => 'IR_TEST',
p_query => QUR );*/


END;

declare
    sels apex_application_global.vc_arr2; -- ÷èñëà èç content çàïèñè
    tmp VARCHAR2( 1000 ); -- ïåðåìåííàÿ ïîëÿ content
    gen_arr apex_application_global.vc_arr2;-- íàáîð id çàïèñåé
    sample_arr apex_application_global.vc_arr2; -- âûáîð øàòòëà
    i pls_integer;
    cnt pls_integer default  0 ;
begin
  
  /* IDS */
  
  sample_arr( 1 ):=  67 ;
  sample_arr( 2 ):=  73 ;
  sample_arr( 3 ):=  69 ;
  sample_arr( 4 ):=  81 ;
  sample_arr( 5 ):=  121 ;
  
  
  select templ_id bulk collect into gen_arr from tc_templs_user where user_id='ALT'; -- Âûáèðàåò ids øàáëîíîâ
  
  for i in  1  .. gen_arr.count loop  
  dbms_output.put_line(gen_arr(i));
  
    select content into tmp from tc_templs_user where user_id = 'ALT' and templ_id = gen_arr(i);-- ñ÷èòûâàåò ïîëå content êîíêðåòíîãî id
        sels:=APEX_UTIL.STRING_TO_TABLE(tmp); -- Âûáèðàåì ÷èñëà èç ïîëÿ content â ìàññèâ
        --dbms_output.put_line(gen_arr(i));
              
              for a in  1 ..sels.count loop 
            
              /* Îáðàáîòêà ñîâïàäåíèé */
            
              for s in  1 ..sample_arr.count loop
            
              if sels(a)=sample_arr(s) then
              cnt:=cnt+ 1 ;
              dbms_output.put_line('cnt: '||cnt);
              end if;
            
              end loop;
            
            dbms_output.put_line('sels'||' _ '||sels(a));-- âûâîä ìàññèâà
            --dbms_output.put_line('sels'||' _ '||gen_arr(i));
            end loop;
        --dbms_output.put_line(tmp);
        end loop; 
    --dbms_output.put_line(gen_arr(i));

   

  
end;

declare
    sels apex_application_global.vc_arr2; -- ÷èñëà èç content çàïèñè
    tmp VARCHAR2( 1000 ); -- ïåðåìåííàÿ ïîëÿ content
    gen_arr apex_application_global.vc_arr2;-- íàáîð id çàïèñåé
    sample_arr apex_application_global.vc_arr2; -- âûáîð øàòòëà
    i pls_integer;
    cnt pls_integer default  0 ;
    new_templ varchar2( 1000 ); -- "ïåðåñòðîåííûé" òåìïëåéò
    collect_tmpl apex_application_global.vc_arr2; -- íàáîð ãîòîâûõ ïðîâåðåííûõ øàáëîíîâ
    tmp_A varchar2( 1000 ); -- :)
    digits_arr apex_application_global.vc_arr2;
begin

  /* IDS Resolve */
  sample_arr( 1 ):=  67 ;
  sample_arr( 2 ):=  73 ;
  sample_arr( 3 ):=  69 ;
  sample_arr( 4 ):=  81 ;
  sample_arr( 5 ):=  121 ;


  select templ_id bulk collect into gen_arr from tc_templs_user where user_id='ALT'; -- Âûáèðàåò ids øàáëîíîâ


-- DBG Sector
for i in  1 ..gen_arr.count loop
tmp:= tmp||gen_arr(i)||':';
end loop;

for a in  1 ..sample_arr.count loop
tmp_A:=tmp_A||sample_arr(a)||':';
end loop;

dbms_output.put_line('ID Recs user: '||tmp);
dbms_output.put_line('Sample: '||tmp_A);
dbms_output.put_line('----------');
-- DBG Sector

for i in  1 ..gen_arr.count loop -- in the IDS Mains

   select content into tmp from tc_templs_user where user_id = 'ALT' and templ_id = gen_arr(i); -- N's id
   dbms_output.put_line('for id: '||gen_arr(i)||' tmp: '||tmp);
   Digits_arr:= APEX_UTIL.STRING_TO_TABLE(tmp); -- ids from current CONTENT
    
    for s in 1..Digits_arr.count loop
    
        for a in 1..sample_arr.count loop -- iterations by sample
        if digits_arr(s)=sample_arr(a) then
        new_templ:=new_templ||sample_arr(a)||':';
        end if;
        end loop;
    
    end loop; 
    
    dbms_output.put_line('for # '||i||' are new_tmpl: '||new_templ);
    
   

-- Execution erase or update 

if new_templ is null then
dbms_output.put_line('Erasing record');
else
dbms_output.put_line('Updating record');
end if;

 dbms_output.put_line('===========================');
 
 new_templ:=null;
end loop;




end;

declare
    sels apex_application_global.vc_arr2; 
    tmp VARCHAR2( 1000 ); 
    gen_arr apex_application_global.vc_arr2;
    sample_arr apex_application_global.vc_arr2; 
    i pls_integer;
    cnt pls_integer default  0 ;
    new_templ varchar2( 1000 ); 
    collect_tmpl apex_application_global.vc_arr2; 
    tmp_A varchar2( 1000 ); -- :)
    digits_arr apex_application_global.vc_arr2;
begin

  /* IDS Resolve */
  sample_arr( 1 ):=  67 ;
  sample_arr( 2 ):=  73 ;
  sample_arr( 3 ):=  121 ;
  sample_arr( 4 ):=  81 ;
  sample_arr( 5 ):=  121 ;
-- Later, it needs to fill from shuttle

  select templ_id bulk collect into gen_arr from tc_templs_user where user_id='ALT'; -- Âûáèðàåò ids øàáëîíîâ


-- DBG Sector
for i in  1 ..gen_arr.count loop
tmp:= tmp||gen_arr(i)||':';
end loop;

for a in  1 ..sample_arr.count loop
tmp_A:=tmp_A||sample_arr(a)||':';
end loop;

dbms_output.put_line('ID Recs user: '||tmp);
dbms_output.put_line('Sample: '||tmp_A);
dbms_output.put_line('----------');
-- DBG Sector

for i in  1 ..gen_arr.count loop -- in the IDS Mains

   select content into tmp from tc_templs_user where user_id = 'ALT' and templ_id = gen_arr(i); -- Ns id
   dbms_output.put_line('for id: '||gen_arr(i)||' tmp: '||tmp);
   Digits_arr:= APEX_UTIL.STRING_TO_TABLE(tmp); -- ids from current CONTENT
    
    for s in  1 ..Digits_arr.count loop
    
        for a in  1 ..sample_arr.count loop -- iterations by sample
        if digits_arr(s)=sample_arr(a) then
        new_templ:=new_templ||sample_arr(a)||':';
        end if;
        end loop;
    
    end loop; 
    
     new_templ:=RTRIM(new_templ,':');
    dbms_output.put_line('for # '||i||' are new_tmpl: '||new_templ);
   
   

-- Execution erase or update 

if new_templ is null then
dbms_output.put_line('Erasing record');
else
dbms_output.put_line('Updating record');
end if;

 dbms_output.put_line('===========================');
 
 new_templ:=null;
end loop;




end;

DECLARE
TMP_VCH VARCHAR2( 100 ) DEFAULT 'WHERE USER_ID=:P4_USER_ID AND TEMPL_ID=GEN_ARR(I)'; -- general condition of request

BEGIN

IF new_templ is null then
dbms_output.put_line('Erasing record');
DELETE FROM TC_TEMPLS_USER WHERE WHR;

ELSE

dbms_output.put_line('Updating record');
-- иначе говоря 
UPDATE TC_TEMPLS_USER SET CONTENT=NEW_TEMPL WHERE WHR;

END IF;