Im writing a PLSQL stored procedure. Note that the below is only a part of it and the actual SP is complex than this. All I want to know is that in my nested query below, how i can check a certain value is not in the ROWTYPE that i named as 'l_05_data' ? Is there a way to do it without running thourgh a loop. Currently i have no idea on how to make this check in my nested query because im getting the below error when compiling. Someone please provide a solution for this.
**
Error(52,7): PL/SQL: SQL Statement ignored
Error(66,41): PL/SQL: ORA-00904: "l_05_data"."A2_ID_FK": invalid identifier
Error(66,51): PLS-00302: component 'A2_ID_FK' must be declared
**
stored procedure
create or replace PROCEDURE TEST_SP()
AS
TYPE r05_array IS TABLE OF A2_TBL%ROWTYPE;
l_05_data r05_array;
CURSOR r5_cur
IS
SELECT a2.*
FROM A2_TBL a2;
BEGIN
OPEN r5_cur;
LOOP
BEGIN
FETCH r5_cur BULK COLLECT INTO l_05_data LIMIT batch_limit;
EXIT WHEN l_05_data.count() = 0;
FOR indx IN l_05_data.FIRST ..l_05_data.LAST
LOOP
--some logic
END LOOP;
SELECT A1 BULK COLLECT
INTO l_01_id_data
FROM
( SELECT distinct column_value AS A1 FROM TABLE(l_01_id_data)
MINUS
(
SELECT distinct a1.A1_ID
FROM A1_TBL a1
INNER JOIN A2_TBL a2
ON a1.A1_ID = a2.A2_A1_ID_FK
WHERE a2.A2_STATUS !='X'
OR (a2.A2_A1_ID_FK NOT IN (l_05_data.A2_A1_ID_FK) AND a2.A2_STATUS = 'X')
)
);
COMMIT;
EXCEPTION
--exception is handled here
END;
END LOOP;
CLOSE r5_cur;
END TEST_SP;
below is the two table schema used in the SP
