title: Optimize nested loop queries in Oracle
body: <p>I have created an Oracle query, later I need to integrate is in a procedure.</p>
  
  <p>In my SQL block I have two nested loops like this (Select statements are working fine):</p>
  
  <pre><code>DECLARE
    m_card_no NUMBER;
  BEGIN
    FOR i IN (SELECT DISTINCT VISA_NUMBER FROM LC.WEEKLY_ROP_CARD_A WHERE NOT EXISTS (SELECT 1 FROM LC.LCT_MOMP_ROP WHERE VISA_NO = VISA_NUMBER))
    LOOP
      FOR n IN (
        SELECT DISTINCT l.new_clearance_no, l.occupation_code, l.sex_code, rtrim(ltrim(a.civil_number)) civil_number, a.name_e, a.name_a, a.date_of_birth, a.passport_number, a.passport_issue_country_code, a.passort_issue_date, a.passort_expiry_date, a.nationality_code, lpad(rtrim(ltrim(a.visa_number)),8,0) visa_number, a.visa_issue_date, a.visa_expiry_date, v.lct_occupation_clear_id, DECODE(V.LCM_VISA_APPL_TYPE_ID,7,'LOCAL ENDORSEMENT','ARRIVAL') TRAN
        FROM WEEKLY_ROP_CARD_A A
        JOIN LCT_OCCUPATION_CLEAR L ON L.OCCUPATION_CODE = A.OCCUPATION_CODE
        JOIN LCT_APPL_VISA V ON V.LCT_OCCUPATION_CLEAR_ID = L.ID
        where lpad(v.visa_no,8,0)=lpad(rtrim(ltrim(a.visa_number)),8,0) and v.lct_occupation_clear_id=l.id and a.last_mov_type='IN' AND ( LCM_VISA_APPL_TYPE_ID &lt;&gt; 6 or LCM_VISA_APPL_TYPE_ID is null) and lpad(v.visa_no,8,0)=i.VISA_NUMBER
      )
      LOOP
          --INSERT QUERY TO ANOTHER TABLE HERE
      END LOOP;
    END LOOP;
  END;
  </code></pre>
  
  <p>The query in the first FOR loop has over 1.6 Million records and Second loop has over 600,000 records. When I run both queries separately, it provide result so there is no any error in the query. But when I run the above block, it is not even reaching insert statement. Is there a way to insert records without any issue?</p>