We help IT Professionals succeed at work.

Reference datasets inside PL/SQL cursor and dumping queried datasets

LuckyLucks
LuckyLucks asked
on
183 Views
Last Modified: 2018-02-02
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;
Comment
Watch Question

Senior Oracle DBA
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.