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)
FOR REC IN (select object_name from all_procedures where object_type ='PROCEDURE'
and owner = 'COMMON_IPE_R2_USER'
and object_name in ( select OBJECT_NAME from user_objects where object_type = 'PROCEDURE')
order by object_name)
LOOP
--DBMS_OUTPUT.PUT_LINE(REC.OBJECT_NAME);
EXECUTE IMMEDIATE 'DROP PROCEDURE 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 ='PROCEDURE' 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;
END;
/
