Insert with (CTE and) select ... as of scn (flashback) and table alias(es) does not work in PL/SQL - ORA-00984: column not allowed here

Question

In a legacy system we want to copy a lot of productive data/tables in other tables in a stored procedure. That all copied data is consistent with each other, we want to use the <tableName> as of scn <scnNumber>.

The SQL seemed to be correct in the stored procedure, but compiling failed with ORA-00984: column not allowed here. A lot of other queries compile without any problem.

After reducing the SQL to a simple PL/SQL block and building up the SQL I found the "problem cause" - as of scn v_scn does not work with any (following) table alias:

declare
     v_scn number := 29058161423; -- in productive code calculated 
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;
 /

This returns: ORA-00984: column not allowed here

Following the link https://stackoverflow.com/a/51878828/26515477 and using as of scn(v_scn) brought the same error:

declare
     v_scn number := 29058161423; -- in productive code calculated 
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;
 /

Currently I found only these solutions

With Dynamic SQL, String Concatenation and Hard Coded SCN

... with dynamic SQL - here with ... as of scn(<scnValue>)::

declare
     v_scn number := 2905861122894; -- in productive code calculated
begin
    execute immediate ' -- has to be dynamic SQL, else ORA-00984: column not allowed here
        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; 
/

or with ... as of scn <scnValue>:

declare
     v_scn number := 2905861122894; -- in productive code calculated
begin
    execute immediate ' -- has to be dynamic SQL, else ORA-00984: column not allowed here
        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; 
/

These solutions have the disadvantage, if there are ' somewhere in the regular SQL I have to escape these or use (partial) q string literals. I find that awkward.

With Dynamic SQL and Repeating Input Variable

declare
     v_scn number := 2905861122894; -- in productive code calculated 
begin
    execute immediate ' -- has to be dynamic SQL, else ORA-00984: column not allowed here
        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;
/

Above code is the same, no matter if I used :1 or :v_scn and as of scn :... or as of scn(:...) - I have to repeat the value in the using term for every occurrence! And still: if there are ' somewhere in the regular SQL I have to escape these or use (partial) q string literals. I find both awkward.

Is there any other working solution without "akward" disadvantages?

Answer 1

You can get ORA-00984: column not allowed here when using ANSI joins in a flashback query. This is Bug 32568211 on Oracle Support, but it doesn't really give any details.

Try rewriting your query with Oracle syntax:

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,
         rates_now ran,
         t_currencyrate as of scn v_scn cra
    where ran.currencyId = cur.currency_id
      and ran.currencyId = cra.currencyrate_currencyid
      and ran.mostRecentDate = cra.currencyrate_date