bat 运行 procedure
bat:
sqlplus username/password@servicename @"list.sql" >log.out

list:
--VIEWS
@"20 view\XXX_VW.sql"

--FUNCTIONS
@"91 function\RXN_FUN_XXX.sql"

--STORE PROCEDURES
@"90 procedures\RXN_SP_XXX.sql"

BEGIN
-- drop view(s)
    FOR REC IN (select view_name from all_views
      where owner = 'COMMON_IPE_R2_USER'
      and view_name in ( select OBJECT_NAME from user_objects where object_type = 'VIEW')
      order by view_name)
    LOOP
        --DBMS_OUTPUT.PUT_LINE(REC.OBJECT_NAME);
        EXECUTE IMMEDIATE 'DROP VIEW COMMON_IPE_R2_USER.' || REC.view_name;
    END LOOP;
   
    -- grant execute and create synonym for view(s)
    FOR REC IN (select OBJECT_NAME from user_objects where object_type ='VIEW' order by object_name)
    LOOP
        EXECUTE IMMEDIATE 'GRANT SELECT on ' || REC.OBJECT_NAME ||' to COMMON_IPE_R2_USER';
        EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM COMMON_IPE_R2_USER.' ||REC.OBJECT_NAME || ' FOR COMMON_IPE_R2.' || REC.OBJECT_NAME || '';
    END LOOP;
        EXECUTE IMMEDIATE 'GRANT SELECT on RXN_SUPERVISOR_VW to HK_IPE_R2';
        EXECUTE IMMEDIATE 'GRANT SELECT on RXN_SUPERVISOR_VW to SG_IPE_R2';
        EXECUTE IMMEDIATE 'GRANT SELECT on RXN_SUPERVISOR_VW to IPE_HK_R2';
        EXECUTE IMMEDIATE 'GRANT SELECT on RXN_SUPERVISOR_VW to IPE_SG_R2';
        EXECUTE IMMEDIATE 'GRANT SELECT on RXN_SUPERVISOR_FC_VW to HK_IPE_R2';
        EXECUTE IMMEDIATE 'GRANT SELECT on RXN_SUPERVISOR_FC_VW to SG_IPE_R2';
        EXECUTE IMMEDIATE 'GRANT SELECT on RXN_SUPERVISOR_FC_VW to IPE_HK_R2';
        EXECUTE IMMEDIATE 'GRANT SELECT on RXN_SUPERVISOR_FC_VW to IPE_SG_R2';




  -- drop function(s)
    FOR REC IN (select object_name from all_procedures where object_type ='FUNCTION'
      and owner = 'COMMON_IPE_R2_USER'
      and object_name in ( select OBJECT_NAME from user_objects where object_type = 'FUNCTION')
      order by object_name)
    LOOP
        --DBMS_OUTPUT.PUT_LINE(REC.OBJECT_NAME);
        EXECUTE IMMEDIATE 'DROP FUNCTION COMMON_IPE_R2_USER.' || REC.OBJECT_NAME;
    END LOOP;
   
    -- grant execute and create synonym for procedure(s)
    FOR REC IN (select OBJECT_NAME from user_objects where object_type ='FUNCTION' order by object_name)
    --FOR REC IN (select OBJECT_NAME from user_objects where object_type = 'PROCEDURE' order by object_name)
    LOOP
        EXECUTE IMMEDIATE 'GRANT EXECUTE on ' || REC.OBJECT_NAME ||' to COMMON_IPE_R2_USER';
        EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM COMMON_IPE_R2_USER.' ||
      REC.OBJECT_NAME || ' FOR COMMON_IPE_R2.' || REC.OBJECT_NAME || '';
    END LOOP;
   
   
-- drop procedure(s)