title: Use WITH clause inside a stored procedure
body: <p>I want to use the <code>WITH</code> clause to fill a table inside a stored procedure. I was reading that said clause keeps the data in memory while the session is alive.</p>
  
  <p>There is a table that has an information that I need to update that is called sprlink, but that table it has millions of records. Currently I have the update as follows but it is very slow:</p>
  
  <pre><code>open act_rpt_list_cdo;
  loop
    fetch act_rpt_list_cdo bulk collect into v_act_rpt_list_cdo limit 100;
    for i in 1 .. v_act_rpt_list_cdo.count loop
      update RPT_LIST_CDO set name_cdo=(select trim(sln.linkvalue) from sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 164 and logidto=0),
                              date_cdo=(select trim(sln.linkvalue) from sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 165 and logidto=0),
                              cod_cdo=(select trim(sln.linkvalue) from sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 166 and logidto=0),
                              log_cdo=(select trim(sln.linkvalue) from sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 167 and logidto=0),
                              data_cdo=(select trim(sln.linkvalue) from sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 168 and logidto=0)
  
      where rowid=v_act_rpt_list_cdo(i).ri;
    end loop;
    exit when act_rpt_list_cdo%notfound;
  end loop;
  close act_rpt_list_cdo
  </code></pre>
  
  <p>I wanted to use the clause in this context limiting the records but I have no idea how to use it within a procedure:</p>
  
  <pre><code>WITH tt_sprlink AS (select sln.objectid as objectid , sln.linkid as linkid, trim(sln.linkvalue) linkvalue
     from sprlinks sln join RPT_LIST_CDO rpt on (sln.objectid=rpt.id and sln.logidto=0)
    where sln.linkid in (164,165,166,167,168))
  select *
  from tt_sprlink
  </code></pre>
  
  <p>To be able to do something in the following way since the tt_srplink would only have the necessary records for my update.</p>
  
  <pre><code>act_rpt_list_cdo
  loop
    fetch act_rpt_list_cdo bulk collect into v_act_rpt_list_cdo limit 100;
    for i in 1 .. v_act_rpt_list_cdo.count loop
      update RPT_LIST_CDO set name_cdo=(select trim(sln.linkvalue) from tt_sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 164),
                              date_cdo=(select trim(sln.linkvalue) from tt_sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 165),
                              cod_cdo=(select trim(sln.linkvalue) from tt_sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 166),
                              log_cdo=(select trim(sln.linkvalue) from tt_sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 167),
                              data_cdo=(select trim(sln.linkvalue) from tt_sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 168)
  
      where rowid=v_act_rpt_list_cdo(i).ri;
    end loop;
    exit when act_rpt_list_cdo%notfound;
  end loop;
  close act_rpt_list_cdo
  </code></pre>
  
  <p>Any suggestion or idea please?</p>