PL/SQL执行EXECUTE IMMEDIATE动态建表时报ORA-00923错误

开发PL/SQL脚本时，通过EXECUTE IMMEDIATE执行动态CREATE TABLE AS SELECT语句创建目标表tbl_board_new_method，脚本运行抛出如下错误：

ORA-00923: FROM keyword not found where expected（未在预期位置找到FROM关键字）

问题对应的原始PL/SQL代码如下：

declare 
    yearmonth varchar2(20) := &yearmonth ; 
    begin 
    execute IMMEDIATE 'CREATE TABLE tbl_board_new_method AS  
    with a as (  
    select u.*,case when ooo.terminal_number is not null then "BOX" else "NOBOX" end ISBOX  from (
    select q.*,
    CASE WHEN   substr(i.min_trn_date, 0, 8) IS NOT NULL AND  substr(i.min_trn_date, 0, 8)  <  coalesce(   substr(i.install_date, 0, 8) , q.install_date )  THEN 
    coalesce(   substr(i.min_trn_date, 0, 8) , q.install_date,  substr(i.install_date, 0, 8))  ELSE 
    coalesce(q.install_date,
    substr(i.install_date, 0, 8),
    substr(i.min_trn_date, 0, 8))  END f_install_date, 
    nvl(q.disable_date, substr(i.disable_date, 0, 8)) f_disable_date,
    q.pos_model pos_model1,
    q.pos_brand pos_brand1,
    q.pos_brand_model pos_brand_model1 ,
    CASE
      WHEN UPPER(q.pos_model) IN
      (:COMBO,
      "DIALUP",
      "LAN",
    "BRANCH") THEN
    "POS"
    ELSE
    CASE
    WHEN UPPER(q.pos_model) IN
    ("PCPOS", "TYPICAL") THEN
    "PCPOS"
    ELSE
    CASE
    WHEN UPPER(q.pos_model) IN
    ("MPOS(BT/INTERNET)",
    "MPOS") THEN
    "MPOS"
    ELSE
    CASE
    WHEN UPPER(q.pos_model) =
    "GPRS" THEN
    "GPRS"
    ELSE
    CASE
    WHEN UPPER(q.pos_model) =
    "IPG" THEN
    "IPG"
    ELSE
    "POS"
    END
    END
    END
    END
    
    from trg.tbl_merchant_info q
    left join trg.mvw_terminal_indicators i  on (q.terminal_number = i.terminal_number)
    where coalesce(q.install_date,
    substr(i.install_date, 0, 8),
    substr(i.min_trn_date, 0, 8)) is not null
    and 
    CASE WHEN   substr(i.min_trn_date, 0, 8) IS NOT NULL AND  substr(i.min_trn_date, 0, 8) <    substr(i.install_date, 0, 8)  THEN 
    coalesce(   substr(i.min_trn_date, 0, 8) , q.install_date,  substr(i.install_date, 0, 8))  ELSE 
    coalesce(q.install_date,
    substr(i.install_date, 0, 8),
    substr(i.min_trn_date, 0, 8)) END   <=  '||yearmonth||'||30
    and (nvl(q.disable_date, substr(i.disable_date, 0, 8)) is null OR  nvl(q.disable_date, substr(i.disable_date, 0, 8)) >='||yearmonth||'||01
    ) 
    and (trim(q.pos_model) is null or
    not
    (upper(q.pos_model) like "%MPOS%"  ))
    --- union 
    UNION 
    select q.*,
    CASE WHEN   substr(i.min_trn_date, 0, 8) IS NOT NULL AND  substr(i.min_trn_date, 0, 8)  <    coalesce(   substr(i.install_date, 0, 8) , q.install_date )  THEN 
    coalesce(   substr(i.min_trn_date, 0, 8) , q.install_date,  substr(i.install_date, 0, 8))  ELSE 
    coalesce(q.install_date,
    substr(i.install_date, 0, 8),
    substr(i.min_trn_date, 0, 8))  END f_install_date,
    nvl(q.disable_date, substr(i.disable_date, 0, 8)) f_disable_date,
    q.pos_model pos_model1,
    q.pos_brand pos_brand1,
    q.pos_brand_model pos_brand_model1 ,
    CASE
    WHEN UPPER(q.pos_model) IN
    ("COMBO",
    "POS",
    "DIALUP",
    "LAN",
    "BRANCH") THEN
    "POS"
    ELSE
    CASE
    WHEN UPPER(q.pos_model) IN
    ("PCPOS", "TYPICAL") THEN
    "PCPOS"
    ELSE
    CASE
    WHEN UPPER(q.pos_model) IN
    ("MPOS(BT/INTERNET)",
    "MPOS") THEN
    "MPOS"
    ELSE
    CASE
    WHEN UPPER(q.pos_model) =
    "GPRS" THEN
    "GPRS"
    ELSE
    CASE
    WHEN UPPER(q.pos_model) =
    "IPG" THEN
    "IPG"
    ELSE
    "POS"
    END
    END
    END
    END
    END FINAL_POS_MODEL
    from   trg.tbl_merchant_info q  
    left join trg.mvw_terminal_indicators i
    on (q.terminal_number = i.terminal_number)
    WHERE q.terminal_number IN (SELECT terminalno FROM trg.fct_total_aggrigate_daily d  WHERE substr(trn_date,0,6) = substr('||yearmonth||',0,6) )  
    ) u left join (select * from  trg.mvw_terminal_indicators  ooo 
    where   ooo.box_install is not null and (box_uninstall is null or  substr(ooo.box_uninstall,0,8)>= '||yearmonth||'||01) )  ooo on (ooo.terminal_number = u.terminal_number )
    a.terminal_number = t111.terminalno (+) and a.terminal_number = tt211.terminalno (+) and 
    a.terminal_number = ttt311.terminalno (+) and a.terminal_number = tttt411.terminalno (+) and a.terminal_number = ttttt511.terminalno (+)
    )
    --, pre AS (
    select terminalid, 
    case when m.scale_install is not null then 1 else 0 end   scale_install  ,
    yearmonth
    from p  left join  trg.mvw_terminal_indicators m on (p.terminal_number = m.terminal_number)';
    end ;

该报错是多语法问题共同导致的，核心触发点如下：

• 字符串字面量错误使用双引号：Oracle语法中双引号仅用于包裹需要区分大小写的标识符（表名、字段名），字符串常量必须使用单引号包裹。代码中"BOX"、"NOBOX"、"DIALUP"、"%MPOS%"等所有业务字符串值都用了双引号，Oracle会将其解析为字段名，在对应位置匹配不到后续的FROM关键字，直接触发ORA-00923。
• WITH子句（CTE）结构残缺：定义完a as (...)的CTE后，没有写主查询的SELECT、FROM部分，直接裸写了a.terminal_number = t111.terminalno (+)这类关联条件，且t111、tt211、p等用到的表别名没有在FROM/JOIN子句中定义，SQL结构断裂，解析器找不到合法的FROM位置。
• 动态SQL字符串拼接错误：日期条件部分的单引号不配对，比如<= '||yearmonth||'||30的写法，会导致拼接后的SQL单引号闭合混乱，日期值没有被合法的单引号包裹，额外拼接的||30会被当成SQL语法的一部分解析失败。
• 其他附带语法问题：UNION上下两个结果集的列数不匹配，第一个分支的终端类型判断字段缺少FINAL_POS_MODEL别名；第一个CASE语句中误写的:COMBO属于未定义的绑定变量，没有对应的USING子句传值。

按以下步骤逐点修正即可解决问题：

1. 替换所有字符串值的引号：将动态SQL内部所有包裹业务字符串的双引号替换为两个单引号（动态SQL在PL/SQL中用单引号包裹，内部单引号需要写两个做转义），例如"BOX"改为''BOX''，"%MPOS%"改为''%MPOS%''，所有设备类型枚举值都按该规则修改。
2. 补全/清理残缺的SQL结构：如果t111、tt211等t开头的关联表是遗留的测试代码直接删除；如果是业务需要的关联逻辑，补全主查询的SELECT ... FROM a LEFT JOIN 相关表 ON 关联条件结构，确保所有用到的表别名都在FROM/JOIN部分提前定义，同时补全被注释的pre CTE逻辑，确认最后主查询引用的p别名有合法定义。
3. 修正日期拼接逻辑：调整单引号位置确保拼接后的日期值被单引号包裹，例如<= '||yearmonth||'||30改为<= '''||yearmonth||'30''，>='||yearmonth||'||01改为>='''||yearmonth||'01''。
4. 对齐UNION结果集：给UNION第一个分支的终端类型判断字段加上FINAL_POS_MODEL别名，保证上下两个分支的列数、列顺序完全一致。
5. 修正绑定变量错误：将第一个CASE中误写的:COMBO去掉冒号，按其他字符串的规则改为''COMBO''，和第二个UNION分支的写法保持一致，不需要额外加USING传参。

修正后的核心代码片段参考：

declare 
    yearmonth varchar2(20) := &yearmonth ; 
begin 
    execute IMMEDIATE 'CREATE TABLE tbl_board_new_method AS  
    with a as (  
    select u.*,case when ooo.terminal_number is not null then ''BOX'' else ''NOBOX'' end ISBOX  from (
    select q.*,
    -- 省略中间重复的CASE判断逻辑，所有字符串双引号改两个单引号，第一个分支末尾加FINAL_POS_MODEL别名
    CASE WHEN UPPER(q.pos_model) IN (''COMBO'',''DIALUP'',''LAN'',''BRANCH'') THEN ''POS''
    -- 剩余CASE分支按规则修改
    END FINAL_POS_MODEL
    from trg.tbl_merchant_info q
    left join trg.mvw_terminal_indicators i  on (q.terminal_number = i.terminal_number)
    where coalesce(q.install_date,substr(i.install_date, 0, 8),substr(i.min_trn_date, 0, 8)) is not null
    and -- 日期条件修正单引号
    CASE WHEN ... END <=  '''||yearmonth||'30''
    and (nvl(q.disable_date, substr(i.disable_date, 0, 8)) is null OR  nvl(q.disable_date, substr(i.disable_date, 0, 8)) >='''||yearmonth||'01'') 
    and (trim(q.pos_model) is null or not (upper(q.pos_model) like ''%MPOS%''  ))
    UNION 
    -- 第二个UNION分支同样修正所有双引号为两个单引号
    ) u left join (select * from  trg.mvw_terminal_indicators  ooo 
    where   ooo.box_install is not null and (box_uninstall is null or  substr(ooo.box_uninstall,0,8)>= '''||yearmonth||'01'') )  ooo on (ooo.terminal_number = u.terminal_number )
    )
    -- 补全后续CTE和主查询的完整SELECT、FROM、JOIN逻辑
    select * from a';
end ;

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