Results 1 to 9 of 9

Thread: Cursor Problem

  1. #1
    Join Date
    Oct 2012
    Posts
    5

    Unanswered: Cursor Problem

    Hello
    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:

    Code:
    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:
    Code:
    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:

    Code:
    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:
    Code:
     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.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    so few lines & so many errors!
    below might get you closer.
    Code:
      1  create or replace procedure perid
      2  AS
      3  thePersonid int;
      4  theInterest varchar( 20);
      5  cursor c1 is select distinct personid, interest from PERSONINTERESTS order by personid;
      6  begin
      7    open c1
      8       loop
      9        fetch c1 into thePersonid, theInterest;
     10          EXIT WHEN c1%NOTFOUND;
     11           dbms_output.put_line(thePersonid.personid ||  theInterest.interest);
     12           end loop;
     13           close c1;
     14*       end;
    SQL> /
    
    Warning: Procedure created with compilation errors.
    
    SQL> show error
    Errors for PROCEDURE PERID:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    8/6      PLS-00103: Encountered the symbol "LOOP" when expecting one of
             the following:
             . ( % ; for
             The symbol "; was inserted before "LOOP" to continue.
    
    13/10    PLS-00103: Encountered the symbol "CLOSE"
    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2012
    Posts
    5
    Since we haven't use replace in procedure for lecture notes. This why I have to use declare statement with the cursor:

    eclare thePersonid int;
    declare theInterest varchar( 20);
    declare NotFound Condition FOR

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL is different from PL/SQL.
    SQL is different from SQL*Plus
    PL/SQL is different from SQL*Plus.

    what is valid in one context, it not necessarily valid in any other context.

    SQL*Plus Basics
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2012
    Posts
    5
    I'm not getting any output result

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I'm not getting any output result
    While I believe above to be 100% factual, it is also 100% devoid of any actionable detail.

    my car won't go.
    Tell me how to make my car go.

    I don't know what you have.
    I don't know what you do.
    I don't know what you see.
    It is really, Really, REALLY difficult to fix a problem that can not be seen.
    use COPY & PASTE so we can see what you do & how Oracle responds.

    I am not convinced that you successfully created any procedure to actually invoke.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Oct 2012
    Posts
    5
    Apoloize for not providing the error code, but this error that I'm getting.

    Code:
    Describe Error: Failed to execute EXPLAIN plan: ORA-00900: invalid SQL statement
    
    
     DBMS_OUTPUT:
     ------------
     
    
    
     0 record(s) affected 
    
     [Executed: 3/31/2013 8:37:08 PM] [Execution: 94ms]

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    see a picture of my car that won't go.
    Tell me how to make my car go.

    I am NOT standing behind you!
    My crystal ball is broken so I don't know what you do that generates these errors?

    You have table. We don't
    You have data. We don't
    You have code. We don't
    I find it impossible to fix code I can not see.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Based on a query you posted in the first message, here are some options:
    Code:
    create or replace procedure p_interest is
      cursor c1 is
        select distinct personid, interest
        from PERSONINTERESTS
        order by personid;
      c1r c1%rowtype;
    begin
      open c1;
      loop
        fetch c1 into c1r;
        exit when c1%notfound;
    
        dbms_output.put_line(c1r.personid ||' - '|| c1r.interest);
      end loop;
      close c1;
    end;
    /
    Or:
    Code:
    create or replace procedure p_interest is
    begin
      for c1r in (select distinct personid, interest
                  from PERSONINTERESTS
                  order by personid
                 )
      loop
        dbms_output.put_line(c1r.personid ||' - '|| c1r.interest);
      end loop;
    end;
    /
    Is this what you are looking for?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •