AAA

Kiểm tra đối tượng

SELECT object_name, editionable 
FROM all_objects 
WHERE object_name = 'FBH_HD_TT_TON' AND OWNER='AAA_TCDN';

Nếu editonable là Y tức là procedure đang là editionable (default) N thì là noneditionable

UAT09. Chuyển sang NONEDITIONABLE (DEFAULT)

DECLARE
  v_sql VARCHAR2(1000);
BEGIN
  FOR obj_rec IN (SELECT OBJECT_NAME, OWNER, EDITIONABLE
                    FROM ALL_OBJECTS
                   WHERE OBJECT_TYPE = 'PROCEDURE'
                     AND OWNER = 'AAA_TCDN'
                     AND EDITIONABLE = 'Y') LOOP
    -- Chuyển procedure thành noneditionable 
    v_sql := 'ALTER PROCEDURE ' || obj_rec.OWNER || '.' ||
             obj_rec.OBJECT_NAME || ' NONEDITIONABLE';
    EXECUTE IMMEDIATE v_sql;
  END LOOP;
END;
/ 
DECLARE v_sql VARCHAR2(1000);
BEGIN
  FOR obj_rec IN (SELECT OBJECT_NAME, OWNER, EDITIONABLE
                    FROM ALL_OBJECTS
                   WHERE OBJECT_TYPE = 'FUNCTION'
                     AND OWNER = 'AAA_TCDN'
                     AND EDITIONABLE = 'Y') LOOP
    -- Chuyển procedure thành noneditionable 
    v_sql := 'ALTER FUNCTION ' || obj_rec.OWNER || '.' ||
             obj_rec.OBJECT_NAME || ' NONEDITIONABLE';
    EXECUTE IMMEDIATE v_sql;
  END LOOP;
END;

UAT02. Chuyển sang EDITIONABLE (DEFAULT) 

-- thực hiện chạy script để chuyển procedure sang editionable

DECLARE
  v_sql VARCHAR2(1000);
BEGIN
  FOR obj_rec IN (SELECT OBJECT_NAME, OWNER, EDITIONABLE
                    FROM ALL_OBJECTS
                   WHERE OBJECT_TYPE = 'PROCEDURE'
                     AND OWNER = 'AAA_TCDN'
                     AND EDITIONABLE = 'N') LOOP
    -- Chuyển procedure thành noneditionable 
    v_sql := 'ALTER PROCEDURE ' || obj_rec.OWNER || '.' ||
             obj_rec.OBJECT_NAME || ' EDITIONABLE';
    EXECUTE IMMEDIATE v_sql;
  END LOOP;
END;
/ 
DECLARE v_sql VARCHAR2(1000);
BEGIN
  FOR obj_rec IN (SELECT OBJECT_NAME, OWNER, EDITIONABLE
                    FROM ALL_OBJECTS
                   WHERE OBJECT_TYPE = 'FUNCTION'
                     AND OWNER = 'AAA_TCDN'
                     AND EDITIONABLE = 'N') LOOP
    -- Chuyển procedure thành noneditionable 
    v_sql := 'ALTER FUNCTION ' || obj_rec.OWNER || '.' ||
             obj_rec.OBJECT_NAME || ' EDITIONABLE';
    EXECUTE IMMEDIATE v_sql;
  END LOOP;
END;