How to create ALTER READ ONLY using PL/SQL for multiple tables?

Question

I'd like to know how to use PL/SQL to ALTER tables to READ ONLY through creating a PROCEDURE. I tried the below code block so far :

CREATE PROCEDURE UPDATE_PROJ_REQU_READ_ONLY
FOR EACH ROW
BEGIN
  ALTER TABLE projects READ ONLY;
  ALTER TABLE request_activities READ ONLY;
  ALTER TABLE proj_rec_services READ ONLY;           
END UPDATE_PROJ_REQU_READ_ONLY;

Answer 1

If you want to ALTER all the tables in the current schema, then create this procedure as

SQL> CREATE OR REPLACE PROCEDURE UPDATE_PR_READ_ONLY IS
  v_sql VARCHAR2(150);
BEGIN
  FOR c IN
           (
            SELECT *
              FROM user_objects o
             WHERE o.object_type  
           )
  LOOP
    v_sql := 'ALTER TABLE '||c.object_name||' READ ONLY';
    EXECUTE IMMEDIATE v_sql;
  END LOOP;          
END;
/

or If you want to ALTER only three individual tables, then create this procedure as

SQL> CREATE OR REPLACE PROCEDURE UPDATE_PR_READ_ONLY IS
  v_sql VARCHAR2(150);
  v_tab owa.vc_arr;
BEGIN
  v_tab(1):='PROJECTS'; v_tab(2):='REQUEST_ACTIVITIES'; v_tab(3):='PROJ_REC_SERVICES';
  FOR i in 1..3
  LOOP
    v_sql := 'ALTER TABLE '||v_tab(i)||' READ ONLY';
   EXECUTE IMMEDIATE v_sql;
  END LOOP;              
END;
/

by looping through array values set to those table's name.

Whatever procedure create, call as

SQL> EXEC UPDATE_PR_READ_ONLY;

Note that

• adding procedure name after the last END of the PROCEDURE is optional, and if added it should be the same as the PROCEDURE name. Thus, UPDATE_PROJ_REQU_READ_ONLY should be converted to UPDATE_PR_READ_ONLY in this case.
• expression FOR EACH ROW is used for database triggers, but not for stored procedures
• add a keyword IS or AS after procedure name in the first line, more preferable to use OR REPLACE option after CREATE keyword
• a DDL statement cannot be issued without using EXECUTE IMMEDIATE within a stored procedure