troubleshooting Question

Reference datasets inside PL/SQL cursor and dumping queried datasets

Avatar of LuckyLucks
LuckyLucks asked on
Oracle Database* PL/SQL
3 Comments3 Solutions190 ViewsLast Modified:
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
johnsone
Senior Oracle DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros