troubleshooting Question

setting local variables in a cursor block

Avatar of LuckyLucks
LuckyLucks asked on
Oracle Database
3 Comments1 Solution183 ViewsLast Modified:
Hi

I want to set the cntpm local var in my PL/SQL block. This value is originally returned from a SQL that uses the present cursor variable. But I get an error in my code.

DBMS_OUTPUT.ENABLE;
DECLARE CNTPM INTEGER;

  CURSOR c1 IS
    SELECT distinct proj_code from all_projects_plan ;
   
  BEGIN
  FOR item IN c1
  LOOP
 
    SET CNTPM =: select count(distinct pm) from MYTABLE where pmloc= 'NA' and p_code = item.proj_code

    DBMS_OUTPUT.PUT_LINE('Project = ' || item.proj_code || ' with number of project managers = ' || CNTPM );
   
    IF CNTPM > 1 THEN DBMS_OUTPUT.PUT_LINE ('Several Project managers')
    ELSE  DBMS_OUTPUT.PUT_LINE ('just one project manager');
     
  END LOOP;
END;

Error starting at line : 1 in command -
DBMS_OUTPUT.ENABLE
Error report -
Unknown Command
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer 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 1 Answer 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