“ora-00905:缺少关键字”错误是因为 into 而不是 as ; 改变现状：

... AS diff

现在可以了，但是。。。
我换成了。我得到下面的错误ora-30076:提取源的提取字段无效
这意味着您的列实际上不是时间戳，而是字符串，因此您需要首先将它们转换为实际的时间戳：

with cte (ts1, ts2) as (
      select to_timestamp_tz(additional_column1, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM'),
             to_timestamp_tz(additional_column2, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM')
      from your_table
    )
    select (extract(day from ts2-ts1)*24*60*60)
         + (extract(hour from ts2-ts1)*60*60)
         + (extract(minute from ts2-ts1)*60)
         + extract(second from ts2-ts1) as diff
    from cte;

或者在cte中也进行计算：

with cte (diff) as (
  select to_timestamp_tz(additional_column2, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM')
       - to_timestamp_tz(additional_column1, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM')
       as diff
  from your_table
)
select (extract(day from diff)*24*60*60)
     + (extract(hour from diff)*60*60)
     + (extract(minute from diff)*60)
     + extract(second from diff) as diff
from cte;

db<>小提琴

我执行以下测试用例，它显然使用了我自己的nls设置：

PARAMETER                                VALUE
---------------------------------------- ----------------------------------------
NLS_RDBMS_VERSION                        12.2.0.1.0
NLS_NCHAR_CONV_EXCP                      FALSE
NLS_LENGTH_SEMANTICS                     BYTE
NLS_COMP                                 BINARY
NLS_DUAL_CURRENCY                        $
NLS_TIMESTAMP_TZ_FORMAT                  DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT                       HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT                     DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT                          HH.MI.SSXFF AM
NLS_SORT                                 BINARY
NLS_DATE_LANGUAGE                        AMERICAN

PARAMETER                                VALUE
---------------------------------------- ----------------------------------------
NLS_DATE_FORMAT                          DD-MON-RR
NLS_CALENDAR                             GREGORIAN
NLS_NUMERIC_CHARACTERS                   .,
NLS_NCHAR_CHARACTERSET                   AL16UTF16
NLS_CHARACTERSET                         AL32UTF8
NLS_ISO_CURRENCY                         AMERICA
NLS_CURRENCY                             $
NLS_TERRITORY                            AMERICA
NLS_LANGUAGE                             AMERICAN

SQL> create table my_test ( c1 timestamp , c2 timestamp ) ;

Table created.

SQL> insert into my_test values ( systimestamp , null ) ;

1 row created.

SQL> -- I wait for a couple of seconds 
SQL> update my_test set c2=systimestamp ;

1 row updated.

SQL> commit;

Commit complete.

SQL> col c1 for a40
SQL> col c2 for a40
SQL> select c1,c2 from my_test ;

C1                                       C2
---------------------------------------- ----------------------------------------
30-JUN-20 11.03.01.262132 AM             30-JUN-20 11.03.16.079616 AM

SQL>  SELECT (extract(DAY FROM c2-C1)*24*60*60)+
  2  (extract(HOUR FROM C2-C1)*60*60)+
  3  (extract(MINUTE FROM C2-C1)*60)+
  4  extract(SECOND FROM C2-C1)
  5* FROM my_test
SQL> /

(EXTRACT(DAYFROMC2-C1)*24*60*60)+(EXTRACT(HOURFROMC2-C1)*60*60)+(EXTRACT(MINUTEFROMC2-C1)*60)+EXTRACT(SECONDFROMC2-C1)
----------------------------------------------------------------------------------------------------------------------
                                                                                                             14.817484

SQL>

不能在查询中放入一个into，即pl/sql，而不是sql。这里的问题到底是什么，因为您的查询对我来说是有效的，只要我将。
更新答案
由于您的案例使用varchar2作为timestamp列并存储时区（如果您问我这是没有意义的），所以案例是不同的。您需要对查询应用一个转换，将varchar2转换为正确的时间戳。

SQL> create table my_test ( c1 varchar2(100), c2 varchar2(100) );

Table created.

SQL> insert into my_test values ( to_char(systimestamp) , null );

1 row created.

SQL> update my_test set c2=to_char(systimestamp) ;

1 row updated.

SQL> commit;

Commit complete.

SQL> set lines 200
SQL> col c1 for a50
SQL> col c2 for a50
SQL> select c1,c2 from my_test ;

C1                                                 C2
-------------------------------------------------- --------------------------------------------------
30-JUN-20 11.12.11.094964 AM +02:00                30-JUN-20 11.12.26.082914 AM +02:00

在这里您可以看到，这些值存储为varchar2，但带有时区格式的时间戳。

SQL> select to_timestamp_tz(c1) , to_timestamp_tz(c2) from my_test ;

TO_TIMESTAMP_TZ(C1)                                                         TO_TIMESTAMP_TZ(C2)
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
30-JUN-20 11.12.11.094964000 AM +02:00                                      30-JUN-20 11.12.26.082914000 AM +02:00

现在，与您之前使用的查询相同

SQL> SELECT (extract(DAY FROM to_timestamp_tz(c2)-to_timestamp_tz(C1))*24*60*60)+
  2  (extract(HOUR FROM to_timestamp_tz(c2)-to_timestamp_tz(C1))*60*60)+
  3  (extract(MINUTE FROM to_timestamp_tz(c2)-to_timestamp_tz(C1))*60)+
  4  extract(SECOND FROM to_timestamp_tz(c2)-to_timestamp_tz(C1))
FROM my_test;  5

(EXTRACT(DAYFROMTO_TIMESTAMP_TZ(C2)-TO_TIMESTAMP_TZ(C1))*24*60*60)+(EXTRACT(HOURFROMTO_TIMESTAMP_TZ(C2)-TO_TIMESTAMP_TZ(C1))*60*60)+(EXTRACT(MINUTEFROMTO_TIMESTAMP_TZ(C2)-TO_TIMESTAMP_TZ(C1))*60)+EXT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                                       14.98795

SQL>

希望有帮助。