3 Replies Latest reply: Jun 5, 2008 7:01 AM by Scott RSS

    ORA-06550: line 20,  PLS-00364: loop index variable 'I' use is invalid

    srikavi
      hi all,
      when i run this query in sql workshop
      DECLARE
      v_text_return varchar2(3000);
      CURSOR dwrefresh IS
      SELECT t.server_name,
      t.status,
      t.refreshed_on,
      t.dw_refresh
      FROM lbcreport.server_status@info t
      WHERE To_Char(t.refreshed_on, 'DD') <> To_Char(SYSDATE, 'DD')
      OR To_Char(t.dw_refresh, 'DD') <> To_Char(SYSDATE, 'DD')
      OR Upper(t.status) <> 'ON';
      BEGIN
      v_text_return := '<html><body> <table><tr><td>Server_name</td>';
      v_text_return := v_text_return || '<td>Status</td>';
      v_text_return := v_text_return || '<td>Refreshed_on</td>';
      v_text_return := v_text_return || '<td>Dw_Refresh</td></tr>'||utl_tcp.crlf;

      FOR i in dwrefresh
      LOOP
      v_text_return := v_text_return ||'<tr><td nowrap="nowrap">' ||i.server_name ||'</td>';
      v_text_return := v_text_return ||'<td nowrap="nowrap">' ||i.status ||'</td>';
      v_text_return := v_text_return ||'<td nowrap="nowrap">' ||i.refreshed_on ||'</td>';
      v_text_return := v_text_return ||'<td nowrap="nowrap">' ||i.dw_refresh ||'</td></tr>'|| utl_tcp.crlf;

      END LOOP;
      v_text_return := v_text_return ||'</table></body></html>'|| utl_tcp.crlf;
      dbms_output.put_line(v_text_return);
      end;


      the error is
      ORA-06550: line 11, column 19:
      PL/SQL: ORA-00904: "T"."STATUS": invalid identifier
      ORA-06550: line 4, column 7:
      PL/SQL: SQL Statement ignored
      ORA-06550: line 20, column 68:
      PLS-00364: loop index variable 'I' use is invalid
      ORA-06550: line 20, column 6:
      PL/SQL: Statement ignored
      ORA-06550: line 21, column 64:
      PLS-00364: loop index variable 'I' use is invalid
      ORA-06550: line 21, column 6:
      PL/SQL: Statement ignored
      ORA-06550: line 22, column 64:
      PLS-00364: loop index variable 'I' use is invalid
      ORA-06550: li

      1. DECLARE

      Please, any one tell me how to fix the bug?

      bye
      Srikavi