0

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?

0
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.

0

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;
1
  • The only real difference from your original is explicitly calling out "filename" in the select statement instead of the implicit "*". See if that helps.
    – pmdba
    Mar 13 '20 at 3:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.