PL/SQL - Use parameter in for loop

Question

I think the problem is that I cannot call a for loop parameter while defining a nested for loop. Errors appended.

I'm trying to write a stored procedure to reorganize index tablespaces online. I'm having an issue with using a parameter in a FOR loop. How can I make my FOR loop use a parameter:

    create or replace procedure REORGANIZE_MASTER AS

      v_stmt varchar2(600);
      v_stmt2 varchar2(600);
      v_stmt3 varchar2(600);
      v_stmt4 varchar2(600);

    begin
      for i in (SELECT LTRIM(username, 'USR_') CUST_SCHEMA
                  FROM dba_users
                 WHERE username LIKE 'USR_S%')
      loop

        v_stmt:='CREATE BIGFILE TABLESPACE REB_'||i.CUST_SCHEMA;
        dbms_output.put_line(v_stmt);
        --execute immediate v_stmt;

          for i in (SELECT index_name FROM dba_indexes WHERE owner = '||i.CUST_SCHEMA||')
          loop
            v_stmt2:= 'alter i.index_name rebuild online tablespace REB_'||i.CUST_SCHEMA;
            dbms_output.put_line(v_stmt2);
            --execute immediate v_stmt2;
          end loop;

        v_stmt3:='drop tablespace IDX_'||i.CUST_SCHEMA||' including contents and datafiles;';
        dbms_output.put_line(v_stmt3);
        --execute immediate v_stmt3;

        v_stmt4:='alter tablespace REB_'||i.CUST_SCHEMA||' rename to IDX_'||i.CUST_SCHEMA;
        dbms_output.put_line(v_stmt4);
        --execute immediate v_stmt4;

      end loop;
    end;
    /

    show errors
    exit


    Errors for PROCEDURE REORGANIZE_MASTER:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    20/17    PL/SQL: Statement ignored
    20/82    PLS-00302: component 'CUST_SCHEMA' must be declared

Your question begins with the line I have 2 main questions. You're only allowed to ask one question at a time. — Siraj Alam
– Siraj Alam, Commented Dec 19, 2017 at 19:17

Alex Poole · Accepted Answer · 2017-12-19 23:27:19Z

3

You're creating two loops, both with the loop variable called i, with one nested inside the other. In the inner loop the "outer" variable name i is masked by the same name given to the inner loop variable, so i.CUST_SCHEMA does not exist in the inner loop. (It still exists in the outer loop - the error has to do with i.CUST_SCHEMA used in the inner loop.)

Your second loop needs another loop variable name, and it also needs to use/concatenate the values from the two loops differently - you're currently looking for indexes owned by the literal value ||i.CUST_SCHEMA|| rather than the value of that cursor field:

  -- call the second loop variable j instead of i (or something more meaningful...)
  -- refer to the schema name directly, not as a text literal
  for j in (SELECT index_name FROM dba_indexes WHERE owner = i.CUST_SCHEMA)
  loop
    -- concatenate in the index name from the j loop
    -- include the 'index' keyword in the alter statement
    v_stmt2:= 'alter index ' || j.index_name
      || ' rebuild online tablespace REB_'||i.CUST_SCHEMA;
    dbms_output.put_line(v_stmt2);
    --execute immediate v_stmt2;
  end loop;

edited Dec 19, 2017 at 23:27

user5683823

answered Dec 19, 2017 at 19:40

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Poole Over a year ago

Thanks @mathguy. Not sure quite how I got from what I was thinking to what I ended up typing...

Collectives™ on Stack Overflow

PL/SQL - Use parameter in for loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related