Script to automaticaly create oracle synonyms

create_synonyms_oracle.sql

-- executar como usuario aplicacao

set serveroutput on

DECLARE
  p_owner VARCHAR(10) := 'GPOP';
  v_comando VARCHAR2(1000);
  
  CURSOR cur_synonym IS
    SELECT 'CREATE OR REPLACE SYNONYM '||p_owner||'_APLICACAO.' || object_name || ' FOR ' || p_owner || '.' || object_name
    FROM all_objects
    where owner = p_owner
    and object_type in ('SEQUENCE', 'TABLE');
    
BEGIN
  dbms_output.put_line('');
  dbms_output.put_line('Inicio do script...');

  OPEN cur_synonym;
  LOOP
    FETCH cur_synonym INTO v_comando;
    EXIT WHEN cur_synonym%NOTFOUND;
    EXECUTE IMMEDIATE v_comando;
    dbms_output.put_line('Comando = ''' || v_comando || ''' executado com sucesso.');
    dbms_output.put_line('');
  END LOOP;
  CLOSE cur_synonym;
  dbms_output.put_line('Fim do script');

END;