Link to home
Start Free TrialLog in
Avatar of LuckyLucks
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('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 */

     

         
        ELSE
            DBMS_OUTPUT.put_line('just one project manager');
        END IF;
    END LOOP;
END;
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial