Problem: 'ORA-02376: invalid or redundant resource' occurs on the execute immediate (v_sql_stmt) statement.
Troubleshooting performed: Included dbms_output.out_line (v_sql_stmt), which yields the correctly formatted commands from the array, and when copying the dbms output to manually run the commands individually outside of the array - the 9 alter profile commands execute successfully. Also, reduced array to only one value (failed_login_attempts 3), ORA error still occurs on execute immediate statement (not really an array with one value, was just trying to troubleshoot). Research of ORA-02376 states 'a resource that is not defined or that is specified twice appears in the create or alter profile statement'. The dbms_output.put_line shows that none of the resources are specified twice, and since the commands run successfully outside of the array, my thought is that they are defined.
Question: Any idea why ORA-02376 occurs on the execute immediate (v_sql_stmt) command? Thank you!
DECLARE
TYPE array_n IS VARRAY (9) OF VARCHAR2 (31);
array array_n
:= array_n ('FAILED_LOGIN_ATTEMPTS 3',
'IDLE_TIME 15',
'PASSWORD_GRACE_TIME 5',
'PASSWORD_LIFE_TIME 35',
'PASSWORD_LOCK_TIME unlimited',
'PASSWORD_REUSE_MAX 1024',
'PASSWORD_REUSE_TIME 365',
'PASSWORD_VERIFY_FUNCTION null',
'SESSIONS_PER_USER 1024');
v_sql_stmt VARCHAR (90);
v_env VARCHAR2 (10);
v_date DATE;
BEGIN
SELECT environment INTO v_env FROM core.site;
IF v_env = 'PROD'
THEN
FOR i IN 1 .. array.COUNT
LOOP
v_sql_stmt := 'alter profile default limit ' || array ( i )|| ' ;';
DBMS_OUTPUT.put_line (v_sql_stmt);
EXECUTE IMMEDIATE (v_sql_stmt);
END LOOP;
ELSE
SELECT SYSDATE INTO v_date FROM DUAL;
DBMS_OUTPUT.put_line (v_date);
END IF;
END;
***************************************************************************
Error at line 2
ORA-02376: invalid or redundant resource
ORA-06512: at line 24