I need to reference a dataset inside a cursor and pick a date value from the dataset and use it as a parameter to another query. I can not create temp tables but was wondering if I can pre-place my dataset in some in-memory or data structure to be searched later in my PL/SQL cursor stub?
My dataset is:
select PMO, Projects, PMName, designatedDate from PMOdesignations; -- Dataset D1
And if you look for the <<<< in the PL/SQL block below that is how I want to use this dataset.
SELECT DISTINCT proj_code
FOR item IN c1
SELECT COUNT(DISTINCT pm)
WHERE pmloc = 'NA' AND p_code = item.proj_code;
IF cntpm > 1
DBMS_OUTPUT.put_line('Several Project managers');
/* Put all projects under cursor proj_code into a some sort of table */
select * from all_PM_activities where project = proj_code; <<<< T1
/* Pick the designationDate from the Dataset1 and pick out the activities in T1 that are between that designatedDate given D1.pm = T1.pm */
DBMS_OUTPUT.put_line('just one project manager');