ORA-00923 : plsql Arithmetic operator and INTO-CodePudding

This sql code is running fine

SELECT
 primax-stdmax    ,
 stdmax-stdmaxapp  
FROM
(SELECT MAX(sequence#) stdmax, thread#, resetlogs_change#
 FROM v$archived_log
 WHERE standby_dest='YES'
 GROUP BY thread#, resetlogs_change#) a,
(SELECT MAX(sequence#) stdmaxapp, thread#, resetlogs_change#
 FROM v$archived_log
 WHERE standby_dest='YES'
 AND   applied='YES'
 GROUP BY thread#, resetlogs_change#) b,
(SELECT MAX(sequence#) primax, thread#, resetlogs_change#
 FROM v$archived_log
 WHERE standby_dest='NO'
 GROUP BY thread#, resetlogs_change#) c
WHERE a.thread# = b.thread#
AND   b.thread# = c.thread#;

But when I convert into pl/sql code as below , its failing.

declare
 l_shipgap NUMBER := 0;
 l_applygap NUMBER := 0;
 begin
 SELECT
  primax-stdmax     into l_shipgap,
  stdmax-stdmaxapp  into l_applygap
 FROM
 (SELECT MAX(sequence#) stdmax, thread#, resetlogs_change#
  FROM v$archived_log
  WHERE standby_dest='YES'
  GROUP BY thread#, resetlogs_change#) a,
 (SELECT MAX(sequence#) stdmaxapp, thread#, resetlogs_change#
  FROM v$archived_log
  WHERE standby_dest='YES'
  AND   applied='YES'
  GROUP BY thread#, resetlogs_change#) b,
 (SELECT MAX(sequence#) primax, thread#, resetlogs_change#
  FROM v$archived_log
  WHERE standby_dest='NO'
  GROUP BY thread#, resetlogs_change#) c
 WHERE a.thread# = b.thread#
 AND   b.thread# = c.thread#;
 end;
 /

Execution :

Error: SQL> @aa.sql stdmax-stdmaxapp into l_applygap * ERROR at line 7: ORA-06550: line 7, column 20: PL/SQL: ORA-00923: FROM keyword not found where expected ORA-06550: line 5, column 1: PL/SQL: SQL Statement ignored

CodePudding user response：

Wrong syntax.

As a statement contains one SELECT, one FROM, one WHERE clause, the same goes for INTO - only one:

  SELECT
    primax - stdmax,   
    stdmax - stdmaxapp 
  INTO l_shipgap, l_applygap
  FROM ...

(with "one", I'm not talking about subqueries - just like you have, but the general SELECT statement syntax).