LuckyLucks
asked on
Reference datasets inside PL/SQL cursor and dumping queried datasets
Hi:
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.
DECLARE
cntpm INTEGER;
CURSOR c1
IS
SELECT DISTINCT proj_code
FROM all_projects_plan;
BEGIN
DBMS_OUTPUT.enable;
FOR item IN c1
LOOP
SELECT COUNT(DISTINCT pm)
INTO cntpm
FROM mytable
WHERE pmloc = 'NA' AND p_code = item.proj_code;
IF cntpm > 1
THEN
DBMS_OUTPUT.put_line('Seve ral 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 */
ELSE
DBMS_OUTPUT.put_line('just one project manager');
END IF;
END LOOP;
END;
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.
DECLARE
cntpm INTEGER;
CURSOR c1
IS
SELECT DISTINCT proj_code
FROM all_projects_plan;
BEGIN
DBMS_OUTPUT.enable;
FOR item IN c1
LOOP
SELECT COUNT(DISTINCT pm)
INTO cntpm
FROM mytable
WHERE pmloc = 'NA' AND p_code = item.proj_code;
IF cntpm > 1
THEN
DBMS_OUTPUT.put_line('Seve
/* 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 */
ELSE
DBMS_OUTPUT.put_line('just
END IF;
END LOOP;
END;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.