DECLARE

  query_str  VARCHAR2(32000);
  CURSOR all_syn IS
    SELECT SYNONYM_NAME, TABLE_NAME
    FROM ALL_SYNONYMS
    WHERE SYNONYM_NAME LIKE 'S!_AG!_%' ESCAPE '!';
  CURSOR our_tables IS
    SELECT TABLE_NAME
    FROM   ALL_TABLES
    WHERE  TABLE_NAME LIKE 'AG!_%1' ESCAPE '!';

BEGIN

  query_str := 'CREATE OR REPLACE VIEW ' || LTRIM(all_syn.SYNONYM_NAME, 'S_') || 'AS
        SELECT TO_CHAR(itemnum) itemnum,
               TO_CHAR(keywordnum) keywordnum,
               TO_CHAR(keysetnum) keysetnum,
               MOD_BY_EMPLOYEE,
               MOD_BY_PROCESS,
               MOD_DATE_EMPLOYEE,
               MOD_DATE_PROCESS
        FROM   all_syn.SYNONYM_NAME,
               our_tables.TABLE_NAME
        WHERE  our_tables.TABLE_NAME = ' || LTRIM(all_syn.SYNONYM_NAME, 'S_');
  FOR v_rec IN all_syn LOOP
IF (v_rec.TABLE_NAME LIKE 'KEYXITEM%') THEN
 EXECUTE IMMEDIATE query_str;

    END IF;
  END LOOP;
END;

DECLARE
  query_str  VARCHAR2(32000);
  CURSOR all_syn IS
    SELECT SYNONYM_NAME, TABLE_NAME
    FROM ALL_SYNONYMS
    WHERE SYNONYM_NAME LIKE 'S!_AG!_%' ESCAPE '!';
  CURSOR our_tables IS
    SELECT TABLE_NAME
    FROM   ALL_TABLES
    WHERE  TABLE_NAME LIKE 'AG!_%1' ESCAPE '!';

BEGIN
  FOR v_rec IN all_syn LOOP
    IF (v_rec.TABLE_NAME LIKE 'KEYXITEM%') THEN
      query_str := 'CREATE OR REPLACE VIEW ' || LTRIM(v_rec.SYNONYM_NAME, 'S_') || 'AS
          SELECT TO_CHAR(itemnum) itemnum,
                 TO_CHAR(keywordnum) keywordnum,
                 TO_CHAR(keysetnum) keysetnum,
                 MOD_BY_EMPLOYEE,
                 MOD_BY_PROCESS,
                 MOD_DATE_EMPLOYEE,
                 MOD_DATE_PROCESS
          FROM   all_syn.SYNONYM_NAME,
                 our_tables.TABLE_NAME
          WHERE  our_tables.TABLE_NAME = ' || LTRIM(v_rec.SYNONYM_NAME, 'S_');
      EXECUTE IMMEDIATE query_str;
    END IF;
  END LOOP;
END;