运行PL/SQL块时遇ORA-00922等错误，请求排查解决

错误原因分析 #

1. 最初的ORA-00947错误 #

该错误提示"not enough values"，核心问题出在第二个查询的WHERE子句语法错误：

WHERE profile in 'Profile_A' OR username IN (SELECT username FROM smith.emp)

IN操作符后必须用括号包裹值列表，即使只有单个值，正确写法应为profile IN ('Profile_A')，语法违规导致SQL解析失败，触发ORA-00947。

2. 后续的ORA-00922错误 #

该错误提示"missing or invalid option"，主要由两方面原因导致：

• 变量长度不足：定义的modified_ddl varchar2(400)长度过小，DBMS_METADATA.GET_DDL返回的DDL片段（从开头到DEFAULT前的部分）可能超过400字符，导致语句被截断，执行时出现语法错误。
• DDL生成逻辑潜在风险：若INSTR(ddl, 'DEFAULT')返回0（极端情况），SUBSTR(ddl, 1, INSTR(ddl, 'DEFAULT') - 1)会取到负数长度，导致字符串截取异常，生成无效DDL。

修正后的代码 #

DECLARE
  v_original_profiles SYS.ODCIVARCHAR2LIST;
  v_original_usernames SYS.ODCIVARCHAR2LIST;           
  modified_ddl varchar2(4000); -- 增大变量长度避免截断
  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') -- 修正IN子句语法
                OR username IN (SELECT username FROM smith.emp))            
            SELECT CASE 
                     WHEN INSTR(ddl, 'DEFAULT') > 0 
                     THEN REPLACE(SUBSTR(ddl, 1, INSTR(ddl, 'DEFAULT') - 1), 'CREATE', 'ALTER') || ';'
                     ELSE REPLACE(ddl, 'CREATE', 'ALTER')
                   END AS modified_ddl
            FROM t) 
  LOOP
    BEGIN
      DBMS_OUTPUT.PUT_LINE('Executing DDL: ' || r.modified_ddl); -- 输出DDL用于验证
      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;
/

关键修正点 #

• 修正WHERE profile in 'Profile_A'为WHERE profile IN ('Profile_A')，符合SQL语法规范。
• 将modified_ddl的长度从400改为4000，避免DDL语句被截断。
• 增加CASE判断处理INSTR(ddl, 'DEFAULT')返回0的极端情况，确保生成的DDL始终有效。
• 添加DBMS_OUTPUT.PUT_LINE输出待执行的DDL，便于验证语句完整性。

内容的提问来源于stack exchange，提问作者laureen85