Missing or Invalid option error when running a plsql block

Question

I get an ORA-00922 'Missing or invalid option' when running this SQL block and can't figure out why. I've tried looking for specific syntax error (an errant bracket) but nothing works:

DECLARE
  v_original_profiles SYS.ODCIVARCHAR2LIST;
  v_original_usernames SYS.ODCIVARCHAR2LIST;           
  modified_ddl varchar2(400);
  username varchar2(100);
BEGIN

  SELECT profile, username
  BULK COLLECT INTO v_original_profiles, v_original_usernames      
  FROM dba_users
  WHERE profile in ('profile_x');

  FOR i IN 1 .. v_original_usernames.COUNT LOOP
    EXECUTE IMMEDIATE 'ALTER USER ' || v_original_usernames(i) || ' PROFILE Profile_A';
  END LOOP;

  FOR r IN (WITH t AS (
              SELECT dbms_metadata.get_ddl('USER', username) ddl
              FROM dba_users
              WHERE profile in 'Profile_A' OR username IN (SELECT username FROM smith.emp))            
            SELECT REPLACE(SUBSTR(ddl, 1, INSTR(ddl, 'DEFAULT') - 1), 'CREATE', 'ALTER') || ';' AS modified_ddl
            FROM t) 
LOOP
    BEGIN
      EXECUTE IMMEDIATE r.modified_ddl;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error executing: ' || r.modified_ddl || '. ' || SQLERRM);
    END;
  END LOOP;

  FOR i IN 1 .. v_original_usernames.COUNT LOOP
    EXECUTE IMMEDIATE 'ALTER USER ' || v_original_usernames(i) || ' PROFILE ' || v_original_profiles(i);
  END LOOP;
END;
/

Error at line 1 ORA-06550: line 9, column 5: PL/SQL: ORA-00947: not enough values ORA-06550: line7, column 5: PL/SQL: SQL Statement ignored

Script stopped on line 1.

Thanks! I added the additional variable and replaced the execute immediates with dbms_output_line and it displays the code as intended. However when converting back to the execute immediates i get an ora-00922: missing or invalid option at line 5 and ORA-06512 at line 16

Error at line 5 ORA-00922: missing or invalid option ORA-06512: at line 15

Answer 1

First note: take the advice in the comments on your question regarding dynamic SQL seriously.

Secondly, your error is in the use of v_original_profiles SYS.ODCIVARCHAR2LIST;. This is an array of singular VARCHAR2 values. In the first loop you try to BULK COLLECT two values in one array value.

Solution: use 2 arrays to BULK COLLECT your query results.

DECLARE
  v_original_profiles  SYS.ODCIVARCHAR2LIST;
  v_original_usernames SYS.ODCIVARCHAR2LIST;
BEGIN
  SELECT profile, username
  BULK COLLECT INTO v_original_profiles, v_original_usernames
  FROM dba_users
  WHERE profile in ('profile_x');
END;