DML Trigger

DML triggers are the triggers which are created over DML statements such as insert into a table, update a view, delete from a table etc. DML triggers execute every time a DML statement is executed.
Mainly DML triggers are 2 types, row level and statement level.

Row level triggers are fired whenever a row changes (Row Level trigger will always fire for each row) .
Statement level triggers are fires whenever any statement executes ( for example a batch update like I am updating the whole table using just one update command, the trigger will fire only once ) . Both row level and statement level can be defined with 3 different DMLs, insert,delete and update.

Row Level and Statement Level Trigger:

• Before Insert  and After Insert 
• Before Update and After Update
• Before Delete and After Delete

CREATE  TABLE schema_audit
 (
  ddl_date      DATE,
  ddl_user      VARCHAR2(15),
  object_created    VARCHAR2(15),
  object_name     VARCHAR2(15),
  ddl_operation    VARCHAR2(15)
);

ALTER TRIGGER:

Alter Trigger command can be used to rename, compile, disable and enable the trigger.

Recompile Trigger:

ALTER TRIGGER trigger_name compile;

Disable Trigger:

ALTER TRIGGER trigger_name disable;

Enable Trigger:

ALTER TRIGGER trigger_name enable;

Rename Trigger:

ALTER TRIGGER trigger_name_old rename to trigger_name_new;

OLD and NEW qualifiers

• The OLD and NEW qualifiers are used to reference the values of a column before and after the data change, respectively.
• The OLD and NEW qualifiers can be used only with row triggers. They cannot be used with statement triggers. The OLD and NEW qualifiers must be prefixed with a colon (:) in every SQL and PL/SQL statement except when they are referenced in a WHEN restricting clause.

Example of using OLD and NEW qualifiers:

CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employees

FOR EACH ROW

BEGIN

     INSERT INTO audit_emp_table (user_name, timestamp,
     id, old_last_name, new_last_name, old_title,
     new_title, old_salary, new_salary)
     VALUES (USER, SYSDATE, :OLD.employee_id,
     :OLD.last_name, :NEW.last_name, :OLD.job_id,
     :NEW.job_id, :OLD.salary, :NEW.salary );

END;

Trigger Details

A trigger is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, or the database. The triggers executes implicitly whenever a particular event takes place.

Type OF Trigger:

1. Application Trigger:

 Application triggers execute implicitly whenever a particular data manipulation language (DML) event occurs within an application (ex. Oracle Forms, Oracle Reports)

2. Database trigger: 

These triggers are fired whenever a data event (such as DML,DDL) or system event (such as logon or shutdown) occurs on a schema or database.

Advantages Of Database Trigger:

Improve Data Security:

• Provide enhanced and complex security checks
• Provide enhanced and complex auditing

Improve Data integrity:

• Enforce dynamic data integrity constraints
• Enforce complex referential integrity constraints
• Ensure that related operations are performed together implicitly

Trigger Data Dictionary:

SELECT * FROM ALL_TRIGGERS;

Nested Table

Nested table we can create in db level, please check the below example

Steps1:

CREATE TYPE TYPE_NAME AS TABLE OF VARCHAR2(30);

Steps2:

CREATE TABLE nested_tbl (ID NUMBER,col1 type_name)
NESTED TABLE col1 STORE AS colmn1_tab;

Steps3:

SELECT * FROM NESTED_TBL;

Steps4:

begin
INSERT INTO nested_tbl VALUES (1, type_name('A'));
INSERT INTO nested_tbl VALUES (2, type_name('B', 'C'));
INSERT INTO nested_tbl VALUES (3, type_name('D', 'E', 'F'));
COMMIT;
end;

Steps5:

SELECT * FROM nested_tbl;

SELECT id, COLUMN_VALUE FROM nested_tbl t1, TABLE(t1.col1) t2;

Bind Variables

Bind Variables also we call as host variables .Check the use of bind variable

Bind Variables in SQL*PLUS:

You declare a bind variable in SQL*Plus, then reference the bind variable in the SELECT statement(declare bind variable, assign value into bind variable and use bind variable in select statement)

SQL> variable deptno number

SQL> exec :deptno := 10
SQL> select * from emp where deptno = :deptno;

Bind Variables in PL/SQL:

PL/SQL itself takes care of most of the issues to do with bind variables, in fact every reference(p_empno) to a PL/SQL variable is a bind variable.

create or replace procedure upd_sal(p_empno in number)

as

  begin

    update emp

    set sal=sal*2

    where empno = p_empno;

    commit;

  end;

/

Bind Variables in Dynamic SQL:

The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows:

create or replace procedure upd_sal(p_empno in number)

as

  begin

    execute immediate

     'update emp set

     sal = sal*2 where empno = :1' using p_empno;

  commit;

  end;

/

LEADING hint

If the query is more complex and it becomes more difficult to figure out the order of all the tables using in ORDER hint. We can figure out the which table access first (driving table), but may not know which table to access after that. The LEADING hint allow us to  specify one table to drive the query while optimizer figure out the rest table.

Syntax:

select /*+ LEADING (table1) */ column1, …

Example1:

select /*+ LEADING(DEPT) */ emp.empno, ename, dept.deptno, itemno

from emp, dept, orders

where emp.deptno = dept.deptno

and emp.empno = orders.empno

and dept.deptno = 1

and emp.empno = 7747

and orders.ordno = 45;

PARALLEL hint

The PARALLEL hint causes the optimizer to break a query into pieces (the degree of parallelism) and process each piece with a different process. The PARALLEL hint will enable the use of parallel operations. If the degree is not specified with the hint, the default degree specifiedduring the table creation will be used.

Syntax:

select /*+ PARALLEL (table, DEGREE) */ ...

The degree is the number of pieces into which the query is broken.

Example1:

select /*+ PARALLEL (order_line_items) */ invoice_number, invoice_date

from order_line_items

order by invoice_date;

Example2:

select /*+ PARALLEL (order_line_items, 4) */ invoice_number, invoice_date

from order_line_items

order by invoice_date;

This statement does not specify a degree of parallelism. The default degree of parallelism is dictated by the table definition when the table was created.

Example3:

select /*+ PARALLEL (oli, 4) */ invoice_number, invoice_date

from order_line_items oli

order by invoice_date;

ORDERED hint

The ORDERED hint is one of the most powerful hints. It process the table of query in the sequential order listed in the FORM clause.
When the ORDERED hint is not used, Oracle may internally switch the driving table when compared to how tables are listed in the FROM clause (EXPLAIN PLAN can show how tables are accessed).

Syntax:

select /*+ ORDERED */ column1, …

Example1:

select /*+ ORDERED */ empno, ename, dept.deptno

from emp, dept

where emp.deptno = dept.deptno

and dept.deptno = 1

and emp.empno = 7747;

Emp and dept have been analyzed (using the cost-based optimizer) and there are no indexes on both the table, the emp table is accessed first and the dept table is accessed second.

Example2:

select /*+ ORDERED */ emp.empno, ename, dept.deptno, itemno

from emp, dept, orders

where emp.deptno = dept.deptno

and emp.empno = orders.empno

and dept.deptno = 1

and emp.empno = 7747

and orders.ordno = 40;;

INDEX Hint

The INDEX hint is frequently used to force one or more indexes to be executed for a given query. Oracle generally chooses the correct index or indexes with the optimizer, but when the optimizer chooses the wrong index or no index at all, this hint is excellent. You may also use multiple indexes with this hint and Oracle will choose one or more of the indexes specified based on the best plan. If you only specify one index, the optimizer considers only one index.

Syntax:

select /*+ INDEX (table index1, index2…) */ column1, …

from Table_Name

where conditions;

Example1:

select /*+ INDEX (emp deptno_idx) */ empno, ename, deptno
from emp
where deptno = 10;

Example2:

Multiple indexes for a single table can be specified, but it is usually better to specify only the most restrictive index on a given query (avoiding the merging of the result of each index). If multiple indexes are specified, Oracle chooses which (one or more) to use, so be careful or your hint could potentially be overridden.

select /*+ INDEX (emp deptno_idx, empno_idx) */ empno, ename, deptno
from emp
where deptno = 10
and empno = 7750;

Example3:

select /*+ INDEX */ empno, ename, deptno
from emp
where deptno = 1
and empno = 7750;

In this example, no index is specified. Oracle now weighs all of the possible indexes that are available and chooses one or more to be used. Since we have not specified a particular index, but we have specified the INDEX hint, the optimizer will not do a full table scan.

Top Hints Used

• A hint is an instruction to the optimizer which can influence the optimizer to choose a difference plan for a sql statement. The optimizer uses these hints to choose an execution plan for the statement. 
• Hints should be used only as LAST RESORT if statistics were gathered properly and query is still following a sub-optimal execution plan.

DBAs and developers use below hints in their day-to-day tuning
1. INDEX
2. ORDERED
3. PARALLEL
4. FIRST_ROWS
5. FULL
6. LEADING
7. USE_NL
8. APPEND
9. USE_HASH

What is Tableau ?

Tableau is a visualization tool created by tableau software .Visualization is nothing but the visually showing information .

• Data visualization tool.
• Connect to several data sources.
• Create dashboards on minutes.
• Easy to Use.

Tableau is a software tool that basically connects easily to several data sources, it allows for rapid insight by transforming data into dashboards that look amazing and are also interactive that recreation takes minutes and can be done with simple drag and drop of data field. It does have some advanced capabilities that required experience and training to blow the software that helps people see and understand their data quickly and easily.
Tableau software products they basically have five main products and those are 

• Tableau Desktop.
• Tableau Server.
• Tableau Online.
• Tableau Reader.
• Tableau Public.

Tableau  Reader and Tableau  Public are free to use.
Tableau Desktop and Tableau Server come with a 14 day fully functional free trail period after which the user needs to start paying for the software, Tableau Desktop comes in both a professional and  a lower cost personal Edition so you can look into that on top of those website.
Tableau Online is available with annual subscription for a single user and it scales to support thousand of users.

Find the Object Dependencies

When we are going to change any table design then You might want to find out –

• which packages refer to this table? 
• Are there views created on it? 
• If I change the table design, how many and which objects will be affected?

Above information we will get it from ALL_DEPENDENCIES view. The view ALL_DEPENDENCIES stores information about dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user.

There are two related views:

DBA_DEPENDENCIES: describes all dependencies between objects in the database. This view may only be accessible to users with DBA rights.

USER_DEPENDENCIES: describes dependencies between objects only in the current user’s schema. This view does not have the column OWNER, as it is contained in the view definition.

 Check the below example:

SELECT name, referenced_owner, type, referenced_name, referenced_type
  FROM all_dependencies
 WHERE referenced_name = 'EMP';  

Collection element_type as CURSOR

Element_type : The data type of the collection element is any PL/SQL data type except REF CURSOR. Check the below example where we are using collecting element type as cursor.

element_type:

{ cursor_name%ROWTYPE
| db_table_name{%ROWTYPE | .column_name%TYPE}
| object_name%TYPE
| [REF] object_type_name
| scalar_datatype_name
| record_name[.field_name]%TYPE
| record_type_name
| variable_name%TYPE
}

Check The Below Example:

DECLARE

/*Declare Cursor*/

CURSOR all_emp_dat_cur

IS

SELECT * FROM EMP;

/*Declare table type, element as CURSOR*/

TYPE all_emp_dat_tbl 
IS TABLE OF all_emp_dat_cur%ROWTYPE INDEX BY PLS_INTEGER;

v_all_emp_dat_tbl  all_emp_dat_tbl ;

BEGIN

OPEN all_emp_dat_tbl;

FETCH all_emp_dat_tbl  BULK COLLECT INTO v_all_emp_dat_tbl;

CLOSE all_emp_dat_tbl;

/*write your logic */

END;