Oracle pl/sql application db objects
當DB物件搬遷的時候，希望把特定的Application裡的物件資料Print出的Script
DECLARE
CURSOR c IS
SELECT *
FROM all_objects
WHERE 1 = 1
AND object_type IN('TABLE', 'SEQUENCE')
AND owner = 'FSMAN';
CURSOR c_a IS
SELECT *
FROM all_objects
WHERE 1 = 1
AND object_type IN('VIEW', 'PAKCAGE')
AND owner = 'APPS'
AND (
object_name LIKE 'ERP_FS%'
OR object_name LIKE 'ERPFS%'
OR object_name LIKE 'HP%'
OR object_name LIKE 'FS%'
OR object_name = 'MSI_VW_FS_INFO_V'
OR object_name = 'VW_TRUS_RP_INCOME_INFO_FS'
OR object_name = 'WP_VW_POS_SALES_FS'
OR object_name = 'MSI_VW_BUS_PERIOD_V'
OR object_name = 'ERP_ERPFS03008_V'
);
BEGIN
FOR v IN c LOOP
IF v.object_type = 'SEQUENCE' THEN
DBMS_OUTPUT.put_line('GRANT SELECT ON FSMAN.' || v.object_name || ' TO APPS WITH GRANT OPTION;');
DBMS_OUTPUT.put_line('GRANT SELECT ON FSMAN.' || v.object_name || ' TO APFS;');
DBMS_OUTPUT.put_line('GRANT SELECT ON FSMAN.' || v.object_name || ' TO RL_FSMAN_ALL;');
ELSE
DBMS_OUTPUT.put_line('GRANT SELECT ON FSMAN.' || v.object_name || ' TO APPS WITH GRANT OPTION;');
DBMS_OUTPUT.put_line('GRANT SELECT,INSERT,DELETE,UPDATE ON FSMAN.' || v.object_name || ' TO APFS;');
DBMS_OUTPUT.put_line('GRANT SELECT,INSERT,DELETE,UPDATE ON FSMAN.' || v.object_name || ' TO RL_FSMAN_ALL;');
END IF;
END LOOP;
FOR v_a IN c_a LOOP
IF v_a.object_type = 'VIEW' THEN
DBMS_OUTPUT.put_line('GRANT SELECT ON APPS.' || v_a.object_name || ' TO APFS;');
DBMS_OUTPUT.put_line('GRANT SELECT ON APPS.' || v_a.object_name || ' TO RL_FSMAN_ALL;');
ELSIF v_a.object_type = 'PACKAGE' THEN
DBMS_OUTPUT.put_line('GRANT EXECUTE ON APPS.' || v_a.object_name || ' TO APPS;');
DBMS_OUTPUT.put_line('GRANT EXECUTE ON APPS.' || v_a.object_name || ' TO RL_FSMAN_ALL;');
END IF;
END LOOP;
END;