Cursor Problem

Page 1 of 1

2 Replies - 223 Views - Last Post: 31 March 2013 - 06:48 AM Rate Topic: -----

#1 deven1974  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 53
  • Joined: 03-October 08

Cursor Problem

Posted 30 March 2013 - 06:22 PM

ello
I have a problem with a cursor that i need some guidance or assistant, pretty much I was able get code to work without cursor functionality with procedure. The assignment is to give this sql query in procedure with cursor. Here is my regular sql query which works:

select distinct personid, interest
from PERSONINTERESTS
order by personid


Apologize for not providing the full information: I need to remove the duplicate from PersonID Column

output:
PERSONID     INTEREST     
 -----------  ------------ 
 1001         Swimming     
 1002         Dancing      
 1002         Music        
 1003         Movie        
 1004         Politics     
 1004         Skiing       
 1005         Music        
 1005         Running      
 1005         Swimming     
 1006         Politics     
 1007         Dancing      
 1008         Running      
 1008         Swimming     
 1009         Kites        
 1009         Toy_Soldiers 
 1009         Weapons      
 1010         Stamps       
 1011         Coins        
 1012         Politics     
 1012         Skiing       
 1013         Swimming     
 1014         Dancing      
 1015         Politics     
 1015         Skiing       
 1015         Weapons      
 1016         Stamps       
 1017         Coins        
 1018         Coins        
 1018         Music        
 1018         Stamps       
 1019         Running      
 1020         Cycling      



But I get errors on procedure with cursor setup:

DROP PROCEDURE
/
create procedure perid
declare thePersonid int;
declare theInterest varchar( 20);
declare NotFound Condition FOR
SQLSTATE '0200';
declare c1 cursor for
(select distinct personid, interest
from PERSONINTERESTS
order by personid);

begin
  open c1
     loop
      fetch c1 into thePersonid, theInterest;
        EXIT WHEN c1%NOTFOUND;
         dbms_output.put_line(thePersonid.personid ||  theInterest.interest);
         end loop;
         close c1;
      end;
/   




But the error message is:
 DBMS_OUTPUT:
 ------------
 

>[Error] Script lines: 1-2 --------------------------
 ORA-04050: invalid or missing procedure, function, or package name
 Script line 1, statement line 1, column 14 

 [Executed: 3/30/2013 8:37:49 PM] [Execution: 0ms] 





If someone can able help me on this.

Is This A Good Question/Topic? 0
  • +

Replies To: Cursor Problem

#2 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5465
  • View blog
  • Posts: 11,740
  • Joined: 02-June 10

Re: Cursor Problem

Posted 31 March 2013 - 06:39 AM

Please don't duplicate threads.
This one is duplicate of:
http://www.dreaminco...cursor-problem/
Was This Post Helpful? 0
  • +
  • -

#3 GunnerInc  Icon User is offline

  • "Hurry up and wait"
  • member icon




Reputation: 858
  • View blog
  • Posts: 2,279
  • Joined: 28-March 11

Re: Cursor Problem

Posted 31 March 2013 - 06:48 AM

*** CLOSED ***
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1