Link to home
Start Free TrialLog in
Avatar of LuckyLucks
LuckyLucks

asked on

Updating a temp table inside a PL/SQL block

Hi:

 I want to declare a temp table and update it inside a cursor loop in PL/SQL block. Please look at the <<< signs. Could you help me with the syntax as presently I am getting an error?

select colA, colB, colC, colD, colE, null as "ColD"
INTO #Temp
from myTable;          <<<<<<

 
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;

        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');

            UPDATE #TEMP SET COLD=cntpm where project_code = item.proj_code;   <<<<<<
             

        ELSE
            DBMS_OUTPUT.put_line('just one project manager');
        END IF;
    END LOOP;
END;
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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
ASKER CERTIFIED 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