Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 25th, 2007, 11:25 PM
Authorized User
 
Join Date: Jan 2007
Location: , , .
Posts: 46
Thanks: 2
Thanked 1 Time in 1 Post
Default Problem with CURSOR FOR LOOP

     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
 
Old March 26th, 2007, 03:34 AM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default

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.
 
Old March 28th, 2007, 02:32 AM
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

"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

 
Old March 28th, 2007, 01:48 PM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default

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.
 
Old March 28th, 2007, 07:45 PM
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by peace95
 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!

 
Old March 28th, 2007, 09:44 PM
Authorized User
 
Join Date: Jan 2007
Location: , , .
Posts: 46
Thanks: 2
Thanked 1 Time in 1 Post
Default

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
 
Old March 28th, 2007, 11:56 PM
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old March 29th, 2007, 06:25 PM
Authorized User
 
Join Date: Jan 2007
Location: , , .
Posts: 46
Thanks: 2
Thanked 1 Time in 1 Post
Default

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
 
Old March 31st, 2007, 03:32 AM
Authorized User
 
Join Date: Jan 2007
Location: , , .
Posts: 46
Thanks: 2
Thanked 1 Time in 1 Post
Default

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
 
Old March 31st, 2007, 04:09 AM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default

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.




Similar Threads
Thread Thread Starter Forum Replies Last Post
Have a problem in For-each loop LeoMathew XSLT 2 July 16th, 2008 05:20 AM
cursor disappearance problem nasirmunir Javascript How-To 7 July 3rd, 2008 03:49 PM
Cursor Blink Problem in FireFox aliirfan84 ASP.NET 1.0 and 1.1 Professional 1 June 11th, 2008 08:46 AM
Magnetic Cursor - Target Area Cursor? gcarcass .NET Framework 2.0 1 May 5th, 2008 07:20 AM
loop problem smilesmita Pro PHP 1 November 2nd, 2007 02:15 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.