#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2010
    Posts
    7
    Rep Power
    0

    TOAD - PL/SQL; LOOP with a select statement


    I can work with 'straight' query's etc., but now I want to make a query with a loop. I have made a simple one to demonstrate what I want but the real one is working is working by it self but I want to get it work with a loop.

    This is the simpel version:
    Code:
    DECLARE
    
        P_UID NUMBER;
        Max_UID NUMBER := 42220;
        
    BEGIN
            
        P_UID := 42210;
        LOOP  
            select *
            from contract lc
            where lc.UIDCONTRACT = P_UID;
        
            P_UID := P_UID + 1;
            EXIT WHEN P_UID > Max_UID;
       END LOOP;
    END;
    The error I get is:
    Line 10, column 9:
    PLS-00428: an INTO clause is expected in this SELECT Statement.

    So I know you can declare a variable and then
    CONTRACTID INTO v_contractID.

    But if I have to put every field in a variable what is then the advantage of a loop. So I expect that I'm on the wrong road and not understand how this works.

    I hope that somebody will explain how to get thsi solved.

    Thanks in advance.

    Nico
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    Originally Posted by nkamp
    But if I have to put every field in a variable what is then the advantage of a loop.
    Good question.
    You should tell us, which problem you are trying to solve. Most probably you don't need a loop at all.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2010
    Posts
    7
    Rep Power
    0
    Ooo, it's not clear? if I run that part of code then I get the error:
    The error I get is:
    Line 10, column 9:
    PLS-00428: an INTO clause is expected in this SELECT Statement.

    What I want in this example the records with UID 42210 till 42220. And I know I can do this with between or with And. In this example oke it is not necessary to do it with a loop.

    But in my other case I want to have the contracts from different years which is complexer. Then I get the same error.

    So, I want to have the 10 contract records in list with all his fields by a loop.

    And I can't belief that I have to put all the fields in different variables and is not possible because then I get the error:
    You have more then one returning value....

    So I hope that somebody can explain or help me with the part of code. Another solution is also good but I need a loop/iteration.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    But in my other case I want to have the contracts from different years which is complexer
    Why don't you do that with just a single query? Doing something in a loop is most of the time a bad idea. Why don't you show us your real problem instead of some stripped down example that does not show your problem and does not even make sense to someone who doesn't know the whole story

    And I can't belief that I have to put all the fields in different variables and is not possible because then I get the error
    You have two choices which are well documented in the PL/SQL manual:

    - use a cursor variable: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/static.htm#CIHCGJAD
    - use bulk collect: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/static.htm#CIHJIBGF

    Another solution is also good but I need a loop/iteration.
    I still don't believe that you need a loop in PL/SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2010
    Posts
    7
    Rep Power
    0
    Every time I post a reply with the 'real' query which I want to use, I get the message:

    [highlight="Could not find phrase 'akismetspam'"]

    So I'm gonna try to make a new thread. Only this text seems oke.

IMN logo majestic logo threadwatch logo seochat tools logo