Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
  #2 (permalink)  
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.
  #3 (permalink)  
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

  #4 (permalink)  
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.
  #5 (permalink)  
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!

  #6 (permalink)  
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
  #7 (permalink)  
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.

  #8 (permalink)  
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
  #9 (permalink)  
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
  #10 (permalink)  
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.
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:10 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.