Chapter 9

1. Chapter 9 Views, Synonyms, and Sequences

2. Views, Synonyms, and Sequences • Views are used extensively in reporting applications and also to present subsets of data to applications. • Synonyms provide a means to transparently allow users to display and use other users’ objects. • Sequences are often used to generate unique numbers that are used to populate primary-key and foreign-key values.

3. Uses for Views • Create an efficient method of storing a SQL query for reuse. • Provide an interface layer between an application and physical tables. • Hide the complexity of a SQL query from an application. • Report to a user about only a subset of columns and/or rows.

4. Creating a View • Create views on tables, views, or materialized views. • Use the CREATE VIEW statement. create table sales( sales_id number primary key ,amnt number ,state varchar2(2) ); • If the view already exists, consider using CREATE OR REPLACE VIEW statement.

5. Views With Check Option • You can issue UPDATE statements against views which result in updating the underlying tables referenced in the view SQL query. • WITH CHECK OPTION specifies that the UPDATE statement can only affect rows that are selected by the SQL the view is based upon. create or replace view sales_rockies as select sales_id ,amnt ,state from sales where state in ('CO','UT','WY','ID','AZ') with check option;

6. Creating Read-Only Views • If you don’t want a user to be able to perform INSERT, UPDATE, or DELETE operations on a view, then don’t grant those object privileges on the underlying table(s) to users with access to the view. • Also, specify READ ONLY when creating the view. create or replace view sales_rockies as select sales_id ,amnt ,state from sales where state in ('CO','UT','WY','ID','AZ') with read only;

7. Updatable Join Views • If you have multiple tables defined in the FROM clause of the SQL query on which the view is based, it’s still possible to update the underlying tables. • The DML statement must modify only one underlying table. • The view must be created without the READ ONLY clause. • The column being updated belongs to the key-preserved table in the join view (there is only one key-preserved table in a join view).

8. Creating an INSTEAD OF Trigger • An INSTEAD OF trigger on a view instructs Oracle to execute PL/SQL code instead of using the DML statement. create or replace trigger emp_dept_v_updt instead of update on emp_dept_v for each row begin update emp set emp_name=UPPER(:new.emp_name) where emp_id=:old.emp_id; end; /

9. Implementing an Invisible Column in a View • New starting with Oracle Database 12c create or replace view sales_co (sales_id, amnt, state, sales_person_id invisible) as select sales_id, amnt, state, sales_person_id from sales where state = 'CO';

10. Modifying a View Definition • CREATE OR REPLACE allows you to modify the view definition without dropping the object. • Advantage is that you don’t have to reissue any grants on the view. • Most DBAs/Developers almost always use the CREATE OR REPLACE, and not dropping and recreating the view.

11. Displaying the SQL Used to Create a View • Troubleshooting issues with views often begins with viewing the SQL that the view is based upon. select view_name ,text from dba_views where owner = upper('&owner') and view_name like upper('&view_name');

12. Oracle Internal View Definitions • You may occasionally need the definition of an Oracle internal view. • Sometimes you may wonder how Oracle is retrieving information; you might be troubleshooting a complex database issue or bug. SQL> select view_definition from v$fixed_view_definition where view_name='V$BH'; • Gives you insight into Oracle’s internal workings.

13. Renaming a View • You may want to change the name so that it better conforms to standard. • You may want to rename a view before dropping it so that you can better determine whether it’s in use. SQL> rename inv to inv_old;

14. Dropping a View • In general, for objects not being used, it’s better to drop them so as to not clutter up your environment and make it more difficult to maintain and manage. • Drop views that aren’t being used. SQL> drop view inv_v;

15. Uses for Synonyms • Provide transparent access to other users’ database objects • Eliminates the need to hard code schema name into application code

16. Synonyms are Pointers to Database Objects • Tables • Views or object views • Other synonyms • Remote objects via a database link • PL/SQL packages, procedures, and functions • Materialized views • Sequences • Java class schema object • User-defined object types

17. Creating a Synonym • Use the CREATE SYNONYM statement to create a synonym. SQL> select * from inv; • Query from DBA/USER/ALL_SYNONYMS to view details.

18. Public Synonyms • Public synonyms allow you to create access to the synonym on a database wide basis. • This simplifies some aspects of the DBA’s job. • However, it has a few drawbacks: • Troubleshooting can be problematic if you’re not aware of globally defined (public) synonyms. • Applications that share one database can have collisions on object names if multiple applications use public synonyms that aren’t unique within the database. • Security should be administered as needed, not on a wholesale basis. • You’ll run into DBAs who often use public synonyms, and other DBAs who never use them.

19. Dynamically Generating Synonyms • Use SQL to generate SQL. CONNECT &&master_user/&&master_pwd.@&&tns_alias -- SET LINESIZE 132 PAGESIZE 0 ECHO OFF FEEDBACK OFF SET VERIFY OFF HEAD OFF TERM OFF TRIMSPOOL ON -- SPO gen_syns_dyn.sql -- select 'create or replace synonym ' || table_name || ' for ' || '&&master_user..' || table_name || ';' from user_tables; -- SPO OFF; -- SET ECHO ON FEEDBACK ON VERIFY ON HEAD ON TERM ON;

20. Displaying Synonym Metadata select synonym_name, table_owner, table_name, db_link from user_synonyms order by 1; select owner ,object_name ,object_type ,status from dba_objects where object_name like upper('&object_name%');

21. Renaming a Synonym • Conform to naming standards. • Determine if the synonym is being used. SQL> rename inv_s to inv_st;

22. Dropping a Synonym • In general, you should drop objects that are not being used. SQL> drop synonym inv;

23. Sequences • Object that guarantees SELECT access to a unique integer for the given sequence. • Used often for PK and FK values.

24. Creating a Sequence SQL> create sequence inv_seq; SQL> create sequence inv2 start with 10000 maxvalue 1000000;

25. Sequence Pseudo-columns • Two pseudo column values used to access a sequence: • NEXTVAL • CURRVAL SQL> select inv_seq.nextval from dual; SQL> select inv_seq.currval from dual;

26. Autoincrementing Columns • Starting with 12c, Oracle does have autoincrementing columns • If using 11g or older, manually simulate this with a trigger: create or replace trigger inv_bu_tr before insert on inv for each row begin select inv_seq.nextval into :new.inv_id from dual; end; /

27. Interesting Uses of Sequences SQL> create sequence inv_seq_odd start with 1 increment by 2; SQL> create sequence inv_seq_even start with 2 increment by 2; SQL> create sequence inv_seq_odd_dwn start with -1 increment by -2; SQL> create sequence inv_seq_even_dwn start with -2 increment by -2;

28. Using One Sequence or Many • Usually there are not performance issues with accessing one sequence. • Usually there are not issues with using large integer values for populating NUMBER columns.

29. Viewing Sequence Metadata select sequence_name ,min_value ,max_value ,increment_by from user_sequences; SQL> select dbms_metadata.get_ddl('SEQUENCE','INV_SEQ') from dual;

30. Renaming a Sequence • Conform to naming standards. • Rename the object before you drop it. SQL> rename inv_seq to inv_seq_old;

31. Dropping a Sequence • Drop objects that aren’t being used. SQL> drop sequence inv;

32. Resetting a Sequence • UNDEFINEseq_name • UNDEFINEreset_to • PROMPT "sequence name" ACCEPT '&&seq_name' • PROMPT "reset to value" ACCEPT &&reset_to • COL seq_idNEW_VALUEhold_seq_id • COL min_idNEW_VALUEhold_min_id • -- • SELECT &&reset_to - &&seq_name..nextval - 1 seq_id • FROM dual; • -- • SELECT &&hold_seq_id - 1 min_id FROM dual; • -- • ALTER SEQUENCE &&seq_name INCREMENT BY &hold_seq_idMINVALUE &hold_min_id; • -- • SELECT &&seq_name..nextval FROM dual; • -- • ALTER SEQUENCE &&seq_name INCREMENT BY 1;

33. Summary • Views, Synonyms, and Sequences are used extensively within database applications. • Views are stored SQL queries that allow you to encapsulate SQL logic and present it as an object to the user. • Synonyms provide a transparent mechanism to enable access to another user’s objects. • Sequences are counters that are often used to guarantee unique values are generated for use when populating primary and foreign key columns.