How to set variables in a loop with oracle?

Question

I have a loop and a variable v_rownum and I want to set value for it:

        FOR donvi_rows IN v_donvi
        LOOP
            DECLARE
               v_rownum number;
            SELECT r
            INTO v_rownum
            FROM
            (SELECT ROWNUM AS r, k.Id
            FROM don_vi k
            WHERE k.ParentId = 1 )
            WHERE Id = donvi_rows.Id;

        END LOOP;

But it throw a exception:

PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
The symbol "begin" was substituted for "SELECT" to continue.

I'm currently studying Oracle. I don't know much about it. Please help me to resolve my problem.

score 9 · Accepted Answer · answered Aug 09 '13 at 02:59

With Oracle you need to declare all your variables at the top of the block, before the BEGIN:

DECLARE
    ... variable declarations
BEGIN
    ... program logic
END;

Your code should go something like this:

DECLARE 
    v_rownum number;
    CURSOR v_donvi IS
      SELECT * FROM whatever;
    ... any other declarations
BEGIN
    FOR donvi_rows IN v_donvi
    LOOP
        SELECT r
          INTO v_rownum
          FROM (
            SELECT ROWNUM AS r, k.Id
            FROM don_vi k
            WHERE k.ParentId = 1
          )
          WHERE Id = donvi_rows.Id;
    END LOOP;
END;

score 0 · Answer 2 · answered Feb 26 '16 at 14:54

0

How is your studying?;) I use this:

DECLARE
    q NUMBER;
        BEGIN
            q := &x;
            for x IN 1 .. q LOOP

answered Feb 26 '16 at 14:54

Julian Kolodzey

359
3
9

score 0 · Answer 3 · answered Feb 20 '20 at 10:09

Try this

    FOR donvi_rows IN v_donvi
    LOOP
        DECLARE
           v_rownum number;
        BEGIN
           SELECT r
           INTO v_rownum
           FROM
           (SELECT ROWNUM AS r, k.Id
           FROM don_vi k
           WHERE k.ParentId = 1 )
           WHERE Id = donvi_rows.Id;
        END;
    END LOOP;

How to set variables in a loop with oracle?

3 Answers3