What is the problem with this PL/SQL Block?

    DECLARE
    TOT_SAL NUMBER:=0;
    CURSOR CR1 IS SELECT ENAME,SAL FROM EMP;
    BEGIN
    FOR R1 IN CR1 LOOP
    TOT_SAL := TOT_SAL + R1.SAL;
    DBMS_OUTPUT.PUT_LINE(R1.E... '||LPAD(R1.SAL,9));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(' =============');
    DBMS_OUTPUT.PUT_LINE('Tot... salary:'||TOT_SAL);
    END;

    Why i am not getting the total salary value?


MAXOOD!

Life is an endless journey towards perfection

__________________
MAXOOD!

Life is an endless journey towards perfection

code_lover:
It is not clear as to what version of Oracle you are using,
my responses are based on Oracle 10g.


DECLARE
    TOT_SAL NUMBER:=0;
Verify Tot_Sal is same datatype as Sal by
the following or verify datatype by doing a DESCRIBE EMP from SAL Prompt:
    Tot_Sal Emp.Sal%TYPE;
    Tot_Sal := 0.0;
CURSOR CR1 IS SELECT ENAME,SAL FROM EMP;
[red]The above cursor statement declares cr1 as cursor(a relational table EMP) does not agree with the FOR LOOP below:
   BEGIN
  OPEN cursor statement is missing;
  OPEN CR1;
    FOR R1 IN CR1
R1 IS NOT Declared in the Declaration Block.
   LOOP
    TOT_SAL := TOT_SAL + R1.SAL;
R1.SAL is used as if the EMP Table is Hierarchy in structure. If this is the case then you must declare or indicate the LEVEL.

    DBMS_OUTPUT.PUT_LINE(R1.E... '||LPAD(R1.SAL,9));
Not sure what E... indicates.
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(' =============');
    DBMS_OUTPUT.PUT_LINE('Tot... salary:'||TOT_SAL);
    END;

If EMP table is not Hierarchy in structure you will need to know the count of Employees for the FOR-LOOP processing in order to LOOP thru individual records to get total salary.
   DECLARE
      v_row_empcount PLS_INTEGER := 1;
      v_ename emp.ename%TYPE;
      v_sal emp.sal%TYPE; add to declare section above
   CURSOR CR1 IS
      SELECT count(EMPID), ENAME, SAL;
   BEGIN
     DBMS_OUTPUT.ENABLE(10000);
   OPEN CR1;
   Process Records 1 at a time
   LOOP
     FETCH CR1 INTO v_row_empcount, v_ename, v_sal;
     EXIT WHEN CR1%NOTFOUND;

     Tot_Sal := Tot_Sal + v_sal;
     v_row_empcount := v_row_empcount + 1;
   END LOOP;
====== Place output statement here=====================
   CLOSE CR1;[/red]

Hope this helps.

========================
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.

"DBMS_OUTPUT.PUT_LINE(R1.E... '||LPAD(R1.SAL,9));" you miss " ' "
the line should write
"DBMS_OUTPUT.PUT_LINE('R1.E... '||LPAD(R1.SAL,9));"
the result:
scott@EXP(11,54)>DECLARE
  2 TOT_SAL NUMBER:=0;
  3 CURSOR CR1 IS SELECT ENAME,SAL FROM EMP;
  4 BEGIN
  5 FOR R1 IN CR1 LOOP
  6 TOT_SAL := TOT_SAL + R1.SAL;
  7 DBMS_OUTPUT.PUT_LINE('R1.E... '||LPAD(R1.SAL,9));
  8 END LOOP;
  9 DBMS_OUTPUT.PUT_LINE(' =============');
 10 DBMS_OUTPUT.PUT_LINE('Tot... salary:'||TOT_SAL);
 11 END;
 12 /
R1.E... 800
R1.E... 1600
R1.E... 1250
R1.E... 2975
R1.E... 1250
R1.E... 2850
R1.E... 2450
R1.E... 3000
R1.E... 5000
R1.E... 1500
R1.E... 1100
R1.E... 950
R1.E... 3000
R1.E... 1300
 =============
Tot... salary:29025

Thanks fenglei, good eye. I was responding in particular to code_lover's question as to why he/she was not getting TOT-SAL value.

========================
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.

You are welcome£¡I have no good eye,but at first i compiled code_lover's program.Error ORA-01756 displayed in screen.
Error ora-01756 explain:
¡°ORA-01756 quoted string not properly terminated
Cause: A quoted string must be terminated with a single quote mark (¡¯).
Action: Insert the closing quote and retry the statement.¡±
So you should pay attention to a single quote mark.last i find it and
it's easy work!

okay i am using Oracle 9i and am not still getting the value of TOT_SAL.
Why is that?

MAXOOD!

Life is an endless journey towards perfection

it's impossible! My db is oracle 9i.You can try to run my script.
let's see error message.

There is no error message.All I am getting is this output:

R1.E... 800
R1.E... 1600
R1.E... 1250
R1.E... 2975
R1.E... 1250
R1.E... 2850
R1.E... 7000
R1.E... 1500
R1.E... 1100
R1.E... 950
R1.E... 3000
R1.E...
R1.E...
R1.E...
=============
Tot... salary:

MAXOOD!

Life is an endless journey towards perfection

I am able to sum up the salary with a simple loop. Here it is:
DECLARE
  CURSOR CR3 IS SELECT ENAME,SAL FROM EMP;
  VENAME EMP.ENAME%TYPE;
  VSAL EMP.SAL%TYPE;
  TOT_SAL NUMBER:= 0;
  COUNTER NUMBER:= 1;
 BEGIN
  OPEN CR3;
  DBMS_OUTPUT.PUT_LINE('ENAME'||' '||'SAL');
  LOOP
  FETCH CR3 INTO VENAME,VSAL;
  TOT_SAL := TOT_SAL + VSAL;
  DBMS_OUTPUT.PUT_LINE(VENAME||' '||VSAL);
  COUNTER:= COUNTER + 1;
  EXIT WHEN COUNTER > &RECORDS;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('================');
   DBMS_OUTPUT.PUT_LINE(TOT_SAL);
  CLOSE CR3;
 END;

But i am not getting output with cursor for loop.Somehow i am not getting the value of TOT_SAL.Hope if someone can figure that out for me.

MAXOOD!

Life is an endless journey towards perfection

code_lover:
 Your Exit Loop is in the wrong place. As I stated before my comments are according to Oracle 10g and according to my resource there was an expansion to Records. Look at the example I sent earlier, try: Immediately after the FETCH--
   EXIT WHEN CR3 %NOTFOUND;



========================
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.