Error 8/40 PLS-00364: loop index variable 'I' use is invalid

Question

When I execute this stored procedure:

create or replace procedure "delete-archive"
is 
begin
for i in (select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'))
 where FILENAME like 'JDE%')
 loop
 UTL_FILE.FREMOVE ('DATA_PUMP_DIR', i.filename );
 end loop;
end;

I get this error:

8/40 PLS-00364: loop index variable 'I' use is invalid

The owner has execute permission. It was like that GRANT DBA TO pqp;. Do you think that the problem may be permissions?

Answer 1

Answers originally left as comments:

---

Gerard H. Pille: A stored procedure can't use permissions granted via a role. You need:

GRANT EXECUTE ON RDSADMIN.RDS_FILE_UTIL TO PQP

---

Albert Godfrind: As an aside, granting DBA to a regular user or application is a very bad idea. It should only ever be granted to an actual DBA account.

Answer 2

The points about permissions and roles are all valid, but this particular error appears to be a syntax error, not a permissions error. The error message "8/40 PLS-00364: loop index variable 'I' use is invalid" is referring to the call to "UTL_FILE.FREMOVE", not the select statement.

Also not sure the select statement should be implicit (i.e. "select *"). In general these should be explicit to ensure what you're getting back matches the number of variables to be inserted into (i.e. "select FILENAME" matches up with the cursor, "i.filename").

create or replace procedure "delete-archive"
is 
begin
 for i in (select filename 
 from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'))
 where FILENAME like 'JDE%')
 loop
 UTL_FILE.FREMOVE ('DATA_PUMP_DIR', i.filename );
 end loop;
end;

• pmdba: The only real difference from your original is explicitly calling out "filename" in the select statement instead of the implicit "*". See if that helps.