CREATE OR REPLACE procedure REFRESH
cursor c1 is
select table_name from DBA_TABLES WHERE SUBSTR(TABLE_NAME, -2) in('_A');
BEGIN
for r1 in c1 loop
      MERGE INTO TEST P
              ON (P.TABLE_NAME = r1.TABLE_NAME)
      WHEN MATCHED
      THEN
	UPDATE set P.UPDATE = SYSTIMESTAMP;
      WHEN NOT MATCHED
      THEN
         INSERT     (P.UPD_TYPE,
                     P.UPDATE,
                     P.DB_NAME,
                     P.TABLE_NAME)
             VALUES (1,
                     SYSTIMESTAMP,
                     'DB01',
                     r1.TABLE_NAME);
   END loop;
commit;
END;
/

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1      PLS-00103: Encountered the symbol "CURSOR" when expecting one of
         the following:
         ( ; is with authid as cluster compress order using compiled
         wrapped external deterministic parallel_enable pipelined
         result_cache

Errors for PROCEDURE REFRESH:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/7      PL/SQL: SQL Statement ignored
7/15     PL/SQL: ORA-02012: missing USING keyword
11/7     PLS-00103: Encountered the symbol "WHEN" when expecting one of
         the following:
         ( begin case declare end exit for goto if loop mod null
         pragma raise return select update while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         continue close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge pipe purge
         The symbol "case" was substituted for "WHEN" to continue.


LINE/COL ERROR
-------- -----------------------------------------------------------------
13/21    PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         into <a SQL statement>

14/24    PLS-00103: Encountered the symbol "UPDATE" when expecting one of
         the following:
         <an identifier> <a double-quoted delimited-identifier>
         current delete exists prior
         The symbol "<an identifier> was inserted before "UPDATE" to
         continue.


LINE/COL ERROR
-------- -----------------------------------------------------------------
17/14    PLS-00103: Encountered the symbol "VALUES" when expecting one of
         the following:
         , * & - + / at mod remainder rem <an identifier>
         <a double-quoted delimited-identifier> <an exponent (**)> as
         from into || multiset bulk year day
         The symbol ", was inserted before "VALUES" to continue.

20/36    PLS-00103: Encountered the symbol ";" when expecting one of the
         following:
         , * & - + / at mod remainder rem <an identifier>
         <a double-quoted delimited-identifier> <an exponent (**)> as

LINE/COL ERROR
-------- -----------------------------------------------------------------
         from into || multiset bulk year day

MERGE INTO TEST P 
USING
(SELECT table_name FROM DBA_TABLES WHERE SUBSTR(TABLE_NAME, -2) = '_A'
) r1 
ON (P.TABLE_NAME = r1.TABLE_NAME)
WHEN MATCHED THEN
  UPDATE SET P.UPDATE = SYSTIMESTAMP
WHEN NOT MATCHED THEN
  INSERT
    (
      P.UPD_TYPE,
      P.UPDATE,
      P.DB_NAME,
      P.TABLE_NAME
    )
    VALUES
    (
      1,
      SYSTIMESTAMP,
      'DB01',
      r1.TABLE_NAME
    );