I'm trying to run the following block of code, and it's giving me the error.
The below script utilizes a with statement to create a virtual table (YY) that looks at each line of the table. If any of the listed columns in the case statement inside the cursor return null, the sql returns the foreign key. If all columns being checked have entries, it returns 'VALID ENTRY'.
If I run JUST the query inside the cursor, it runs exactly as expected. But there are too many rows returned to use in an "in" statement, hence why I'm trying to utilize a pl/sql block instead.
However when I put this into the cursor,and try to either output it via dbms_output, or just run a select statement, it is throwing the listed error. Says line 48, which I have bolded and enlarged that section.
I am not able to provide the table structure and inserted data. The last time I provided it, I was reprimanded for publishing intellectual property.
So hopefully this makes enough sense that someone can help resolve it, or see what I'm trying to do and suggest a better method.
set serveroutput on size 100000;
set feedback on;
DECLARE
cursor c1_cur is (
WITH YY
AS (SELECT CASE
WHEN OTH_ACTIV IS NULL
OR PICNIC IS NULL
OR RELAX IS NULL
OR GATHERING IS NULL
OR RESORTS IS NULL
OR BACK IS NULL
OR PRIM_CAM IS NULL
THEN
adm_cn_fk
ELSE
'VALID ENTRY'
END ADM_NO_PRTPT
FROM FSUM.ACTIVITY
WHERE adm_cn_fk IN (SELECT adm_cn
FROM fsum.admin
WHERE su_round = 3)
)
SELECT YY.ADM_NO_PRTPT
FROM YY
WHERE YY.ADM_NO_PRTPT <> 'VALID ENTRY'
);
BEGIN
for a_list in c1_cur LOOP
select * from FSUM.ACTIVITY where adm_cn_fk = a_list;
END LOOP;
END;
/
Thanks.