PL/SQL中带Flashback SCN、表别名的INSERT语句报ORA-00984

在遗留系统的存储过程中，需要将大量生产数据复制到其他表，为保证数据一致性，计划使用<tableName> as of scn <scnNumber>语法。但SQL在存储过程中编译时触发ORA-00984: column not allowed here错误，其他查询可正常编译。排查后发现根源是**as of scn v_scn无法与后续表别名配合使用**。

错误示例代码 #

declare
     v_scn number := 29058161423; -- 生产环境中会动态计算
begin
    insert into temp_currency (currency_id, currency_code, currency_rate, currency_version)
    with rates_now as (
         select icr.currencyrate_currencyid currencyId, max(icr.currencyrate_date) mostRecentDate
         from t_currencyrate as of scn v_scn icr
         group by icr.currencyrate_currencyid
    )
    select cur.currency_id, cur.currency_code, 1.23 currencyrate_rate, cur.currency_version       
    from t_currency as of scn v_scn cur
    join rates_now ran
        on ran.currencyId = cur.currency_id
    join t_currencyrate as of scn v_scn  cra
        on ran.currencyId = cra.currencyrate_currencyid
            and ran.mostRecentDate = cra.currencyrate_date;
 end;
 /

执行后返回错误：ORA-00984: column not allowed here

尝试改进写法仍报错 #

尝试使用as of scn(v_scn)的写法，仍触发相同错误：

declare
     v_scn number := 29058161423; -- 生产环境中会动态计算
begin
    insert into temp_currency (currency_id, currency_code, currency_rate, currency_version)
    with rates_now as (
         select icr.currencyrate_currencyid currencyId, max(icr.currencyrate_date) mostRecentDate
         from t_currencyrate as of scn(v_scn) icr
         group by icr.currencyrate_currencyid
    )
    select cur.currency_id, cur.currency_code, 1.23 currencyrate_rate, cur.currency_version       
    from t_currency as of scn(v_scn) cur
    join rates_now ran
        on ran.currencyId = cur.currency_id
    join t_currencyrate as of scn(v_scn) cra
        on ran.currencyId = cra.currencyrate_currencyid
            and ran.mostRecentDate = cra.currencyrate_date;
 end;
 /

现有可行方案 #

方案一：动态SQL+字符串拼接硬编码SCN #

通过动态SQL拼接SCN值，有两种写法：

写法1：as of scn(<scnValue>)

declare
     v_scn number := 2905861122894; -- 生产环境中会动态计算
begin
    execute immediate ' 
        insert into temp_currency (currency_id, currency_code, currency_rate, currency_version)
        with rates_now as (
            select icr.currencyrate_currencyid currencyId, max(icr.currencyrate_date) mostRecentDate 
            from t_currencyrate as of scn(' || v_scn || ') icr
            group by icr.currencyrate_currencyid
        )
        select cur.currency_id, cur.currency_code, 1.23 currencyrate_rate, cur.currency_version
        from t_currency as of scn(' || v_scn || ') cur
        join rates_now ran
             on ran.currencyId = cur.currency_id
        join t_currencyrate as of scn(' || v_scn || ')  cra
             on ran.currencyId = cra.currencyrate_currencyid
                 and ran.mostRecentDate = cra.currencyrate_date
     '; 
end; 
/

写法2：as of scn <scnValue>

declare
     v_scn number := 2905861122894; -- 生产环境中会动态计算
begin
    execute immediate ' 
        insert into temp_currency (currency_id, currency_code, currency_rate, currency_version)
        with rates_now as (
            select icr.currencyrate_currencyid currencyId, max(icr.currencyrate_date) mostRecentDate 
            from t_currencyrate as of scn ' || v_scn || ' icr
            group by icr.currencyrate_currencyid
        )
        select cur.currency_id, cur.currency_code, 1.23 currencyrate_rate, cur.currency_version
        from t_currency as of scn ' || v_scn || ' cur
        join rates_now ran
             on ran.currencyId = cur.currency_id
        join t_currencyrate as of scn ' || v_scn || ' cra
             on ran.currencyId = cra.currencyrate_currencyid
                 and ran.mostRecentDate = cra.currencyrate_date
     '; 
end; 
/

弊端：若SQL中包含单引号'，需要手动转义或使用q字符串字面量，操作繁琐。

方案二：动态SQL+重复传入变量 #

使用绑定变量，但需要在using子句中重复传递SCN参数：

declare
     v_scn number := 2905861122894; -- 生产环境中会动态计算 
begin
    execute immediate ' 
        insert into temp_currency (currency_id, currency_code, currency_rate, currency_version)
        with rates_now as (
             select icr.currencyrate_currencyid currencyId, max(icr.currencyrate_date) mostRecentDate
             from t_currencyrate as of scn :1 icr
             group by icr.currencyrate_currencyid 
        )             
        select cur.currency_id, cur.currency_code, 1.23 currencyrate_rate, cur.currency_version
        from t_currency as of scn :1 cur
        join rates_now ran
            on ran.currencyId = cur.currency_id 
        join t_currencyrate as of scn :1 cra 
            on ran.currencyId = cra.currencyrate_currencyid
                and ran.mostRecentDate = cra.currencyrate_date
     ' using v_scn, v_scn, v_scn;
end;
/

弊端：无论使用位置绑定:1还是命名绑定:v_scn，也不管是as of scn :...还是as of scn(:...)写法，都需要在using子句中重复传参，同时仍需处理SQL中单引号的转义问题，操作繁琐。

提问 #

是否存在无上述弊端的可行解决方案？

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