I have created an Oracle query that I need to later integrate into a procedure.
In my SQL block, I have two nested loops like this (select statements work well):
TO EXPLAIN m_card_no NUMBER; BEGIN FOR i IN (SELECT DISTINCT VISA_NUMBER FROM LC.WEEKLY_ROP_CARD_A WHERE IS 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_name, 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_APPLA). "ARRIVAL") TRAN FROM WEEKLY_ROP_CARD_A A JOIN LCT_OCCUPATION_CLEAR L ON L.OCCUPATION_CODE = A.OCCUPATION_CODE JOIN LCT_APPL_VISA OF 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 = & # 39; IN & # 39; AND (LCM_VISA_APPL_TYPE_ID <> 6 or LCM_VISA_APPL_TYPE_ID is null) and lpad (v.visa_no, 8,0) = i.VISA_NUMBER ) LOOP - Ask another table here END LOOP; END LOOP; THE END;
The query in the first FOR loop has over 1.6 million records and the second loop has over 600,000 records. If I run both queries separately, the result is displayed so that no errors occur in the query. But when I execute the above block, it does not even get the insert statement. Is there a way to insert records without problems?