Script to create a View for every table 2

[synapsevampire]

I've done this numerous times with SQL Server, and now want to create a script to generate a View for every table because I don't want developers using tables directly due to the volume of changes occuring in the database design.

The script should generate the Views using column names, as in:

Create or Replace View <table.name>
as
select col1, col2 col3 from <table.name

Everything should be read from the object repository, so the SQL will by dynamic and I suspect involve a cursor.

Thanks for your time.

-k

 

[sybaseguru]

Just remember that you cannot have a view name with the same name as table_name in the same schema.
So let's try this as it does the job

SET SERVEROUTPUT ON;
DECLARE
        v_header  VARCHAR2(255) DEFAULT NULL;
        v_column  VARCHAR2(255) DEFAULT NULL;
        v_trailer VARCHAR2(255) DEFAULT NULL;
        v_no_of_columns NUMBER;
        v_col_count NUMBER;
BEGIN
  FOR F IN (SELECT TABLE_NAME FROM USER_TABLES)
  LOOP
    BEGIN
       v_col_count := 0;
       SELECT COUNT(1) INTO v_no_of_columns FROM user_tab_columns WHERE TABLE_NAME = F.TABLE_NAME;
       v_header := 'CREATE OR REPLACE VIEW '||F.TABLE_NAME||'_VIEW'||CHR(13)||'AS SELECT ';
       v_column := NULL;
       FOR C IN (SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = F.TABLE_NAME)
       LOOP
          BEGIN
             v_col_count := v_col_count + 1;
             IF (v_col_count = v_no_of_columns)
             THEN
              v_column := v_column||C.COLUMN_NAME;
             ELSE
               v_column := v_column||C.COLUMN_NAME||','||CHR(13);
             END IF;
          END;
       END LOOP;
       v_trailer := 'FROM '||F.TABLE_NAME||';';
    END;
    DBMS_OUTPUT.NEW_LINE;
    DBMS_OUTPUT.PUT_LINE('PROMPT CREATING VIEW '||F.TABLE_NAME||'_VIEW'||';');
    DBMS_OUTPUT.PUT_LINE(v_header||CHR(13)||v_column||CHR(13)||v_trailer);
  END LOOP;
END;

Now if you run this against a given user's schema such as scott you will get:

PROMPT CREATING VIEW BONUS_VIEW;
CREATE OR REPLACE VIEW BONUS_VIEW
AS SELECT 
ENAME,
JOB,
SAL,
COMM
FROM BONUS;

PROMPT CREATING VIEW DEPT_VIEW;
CREATE OR REPLACE VIEW DEPT_VIEW
AS SELECT 
DEPTNO,
DNAME,
LOC
FROM DEPT;

PROMPT CREATING VIEW EMP_VIEW;
CREATE OR REPLACE VIEW EMP_VIEW
AS SELECT 
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
FROM EMP;

PROMPT CREATING VIEW SALGRADE_VIEW;
CREATE OR REPLACE VIEW SALGRADE_VIEW
AS SELECT 
GRADE,
LOSAL,
HISAL
FROM SALGRADE;

PROMPT CREATING VIEW TARGET_TABLE_VIEW;
CREATE OR REPLACE VIEW TARGET_TABLE_VIEW
AS SELECT 
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
FROM TARGET_TABLE;

PROMPT CREATING VIEW TRACK_VIEW;
CREATE OR REPLACE VIEW TRACK_VIEW
AS SELECT 
TRACKID
FROM TRACK;

PROMPT CREATING VIEW TRACKING_VIEW;
CREATE OR REPLACE VIEW TRACKING_VIEW
AS SELECT 
TRACKINGID,
VALUE
FROM TRACKING;

PL/SQL procedure successfully completed

So all you need is to cut and paste it or spool it to a file and it should produce the required views. In this case all views are called <TABLE_NAME>_VIEW.
Good luck and hope this helps

 

[sem]

You may also execute it at the same time:

begin
for c in (select table_name from user_tables) loop
execute immediate 'create or replace view '||
c.table_name||'_view as select * from '||
c.table_name;
end loop;
end;

Regards, Dima

 

[synapsevampire]

Thanks to both of you, that gave me the basics!

-k