Avatar of LuckyLucks
LuckyLucks
 asked on

setting local variables in a cursor block

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
Oracle Database

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
LuckyLucks

ASKER
I revised accordingly to but seem to get an error at the line 1 declaration. This confuses me - why would the DECLARE not accept a variable?The funny thing is only left in the DECLARE line for the cntpm and still got an error at line 1 which disappears when I change it to:

VAR cntpm INTEGER;

---------------------------------------------- previously below ------------------------

DECLARE cntpm   INTEGER;
Sean Stuber

VAR cntpm INTEGER;   --- this declares a sqlplus variable,  this is a stand alone command, it doesn't apply to what I posted

DECLARE cntpm   INTEGER;    --- this is part of a pl/sql block, if you only have this your block is incomplete.

Lines 1-27 are one single command,    The declare is not a separate command, it is part of the pl/sql block.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck