PL/SQL - Triggers

Triggers are stored program , which are automatically executed or fired when some events occur.

Triggers are infact executed in response of the following events:

• DML operation - delete, insert and update
• DDL operation - create, alter or drop
• Other db operations - servererror, logon, logoff, shutdown

Triggers could be defined on table, view, schema or db with which the event is associated.

Benefits of Triggers:

• Generating some derived column values automatically
• Enforcing referencial integrity
• Event logging and storing info on table access
• Auditing
• Imposing security authorization
• Preventing invalid transactions

Trigger types:

• Row level Triggers
• Statement level triggers
• Before triggers
• After triggers

Row level Triggers:

Row level triggers execute once for each row in a transaction. For example, if an update statement updates multiple rows of a table, a row level trigger is fired once for each row affected by the update statement.

Example, an update statement updates 3 rows in a table, then 3 triggers will be fired.

Statement level Triggers:

Statement level triggers are triggered only once for each transaction. For example, when an update commands update 2 rows, the commands contained in the trigger are executed only once.

Before Triggers:

Before trigger execute the trigger action before the triggering event(insert, update or delete). 

• Before triggers are used to drive specific column values before completing a triggering insert, update operation.
• Before triggers are used when the trigger action should determine whether or not the triggering statement should be allowed to complete.

After Triggers:

After trigger executes the trigger action after the triggering event(insert, update or delete).

Example, 

To perform cascade delete operation, it means that user delete the record from one table but the corresponding records in other tables are deleted automatically by a trigger which fired after the execution of delete statement issued by the user.

Example 1 - statment level trigger:

When we will update the customet table, before the update, the following trigger will be fired and put an audit entry into the audit table.

CREATE TRIGGER customer_before_update

BEFORE UPDATE

ON CUSTOMER

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'BEFORE UPDATE OPERATION');

END;

Example 2 - staement level with more events

CREATE TRIGGER customer_after_action

AFTER INSERT OR DELETE OR UPDATE

ON CUSTOMER

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

IF INSERTING THEN

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'INSERT OPERATION');

ELSIF DELETING THEN

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'DELETE OPERATION');

ELSIF UPDATING THEN

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'UPDATE OPERATION');

END IF;

END;

Example row level trigger:

CREATE TRIGGER customer_after_update

AFTER UPDATE

ON CUSTOMER

FOR EACH ROW

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'INSERT OPERATION ROW LEVEL');

END;

OLD and NEW pseudo records with an example:

When a row level trigger fires, the PL/SQL runtime system creates and populates the two pseudo records OLD and NEW.

• For an Insert trigger, OLD : none, NEW: nee value
• For an Update trigger, OLD: old value, NEW: New value
• For a Delete trigger, OLD: old value, NEW: none

CREATE TRIGGER customer_after_update

AFTER UPDATE

ON CUSTOMER

FOR EACH ROW

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)

VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);

END;

Row level Triggers Example with WHEN clause:

CREATE TRIGGER customer_after_update

AFTER UPDATE

ON CUSTOMER

FOR EACH ROW

WHEN (OLD.REGION = 'SOUTH')

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)

VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);

END;

Row level triggers with OF clause

CREATE TRIGGER customer_after_update

AFTER UPDATE

OF CUSTOMER_ID

ON CUSTOMER

FOR EACH ROW

WHEN (OLD.REGION = 'SOUTH')

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)

VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);

END;

Disable and enable triggers:

Disable/Enable:

ALTER TRIGGER <TRIGGERNAME> DISABLE;

ALTER TRIGGER <TRIGGERNAME> ENABLE;

Disable / enable all the triggers present in the table:

ALTER TABLE <TABLE NAME> DISABLE ALL TRIGGERS;

ALTER TABLE <TABLE NAME> ENABLE ALL TRIGGERS;

Drop a trigger:

DROP  TRIGGER <TRIGGER NAME>;

Rename a trigger:

ALTER TRIGGER <OLD TRIGGER NAME> RENAME TO <NEW TRIGGER NAME>

PL/SQL - Collections

Collection:

• Collection is a composite datatype like Record.
• An Oracle PL/SQL collection is a single dimensional array
• It consist of one more elements accessible through an index value.
• All the elements have the same data type.

Advantages:

Collections are used in some of the important performance optimization features og PL/SQL,

• BULK collect : select statements that retrieve multiple rows with a single fetch.
• FORALL : inserts, updates and deletes that use collections to change multiple rows of data very quickly
• Table functions : functions that return collections and can be called in the FORM clause of a select statement.

Sparse vs dense collection:

If a collection contains all the elements is called dense. And a collection is sparse if there is atleast one index value between the lowest and highest defined index values that is not defined.

Collection methods:

A collection method is a procedure or function that either provide information about the collection or changes the contents of the collection. Methods are attached to the collection variable with dot notation, for example, my_collection.FIRST.

Types of collections:

• Associative Array or index by tables
• Nested tables
• VARRAY

Associative array:

This type of collection is indexed using BINARY_INTEGER values or VARCHAR2 values, which dont need to be consecutive. This were originally called as PL/SQL tables.

DECLARE

TYPE customer_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;

customer_table customer_type;

v_idx number;

BEGIN

customer_table(1):= 'John';

customer_table(2):= 'Rudy';

customer_table(3):= 'Rub';

customer_table(6):= 'Sri';

--delete 3rd element

customer_table.delete(3);

--traverse sparse collection

v_idx := customer_table.first;

WHILE v_idx IS NOT NULL 

LOOP

Dbms_output.put_line('customer name: ' || customer_table(v_idx));

v_idx := customer_table.next(v_idx);

END LOOP display_loop;

END;

Nested Tables:

• Nested table can be stored in a database.
• It can be sparse but are almost always dense.
• They can be indexed only by integer.
• We can use the MULTISET operator to perfoem set operations and to perform equality comparisons on nested tables.
• The elements entry must be sequential.

DECLARE

TYPE customer_type IS TABLE OF VARCHAR2(100);

-- initialize the collection

customer_table customer_type := customer_type();

v_idx number;

BEGIN

--you have to extend before using the table

customer_table.extend(4);

customer_table(1):= 'John';

customer_table(2):= 'Rudy';

customer_table(3):= 'Rub';

--customer_table(6):= 'Sri'; -- throws an error

customer_table(4):='Sri'; -- it must be sequential.

--delete 3rd element

customer_table.delete(3);

--traverse sparse collection

v_idx := customer_table.first;

WHILE v_idx IS NOT NULL 

LOOP

Dbms_output.put_line('customer name: ' || customer_table(v_idx));

v_idx := customer_table.next(v_idx);

END LOOP display_loop;

END;

/

VARRAY:

• A VARRAY is similar to a nested table except we must specify an uppet bound in the declaration.
• Like nested table, we can store in the tadabase.
• Unlike nested tables, individual elements cant be deleted so they remain same.

DECLARE

TYPE customer_type IS VARRAY(4) OF VARCHAR2(100);

-- initialize the collection

customer_table customer_type := customer_type();

v_idx number;

BEGIN

--you have to extend before using the table

customer_table.extend(4);

customer_table(1):= 'John';

customer_table(2):= 'Rudy';

customer_table(3):= 'Rub';

--customer_table(6):= 'Sri'; -- throws an error

customer_table(4):='Sri'; -- it must be sequential.

--can not delete an item. 

--customer_table.delete(3);

--traverse sparse collection

v_idx := customer_table.first;

WHILE v_idx IS NOT NULL 

LOOP

Dbms_output.put_line('customer name: ' || customer_table(v_idx));

v_idx := customer_table.next(v_idx);

END LOOP display_loop;

END;

/

Collection methods:

Multiset operator:

We can do following operations

• MULTISET UNION
• MULTISET UNION DISTINCT
• MULTISET EXCEPT
• MULTISET INTERSECT

DECLARE

TYPE t_tab IS TABLE OF NUMBER;

l_tab1 t_tab := t_tab(1,2,3,4,5,6);

l_tab2 t_tab := t_tab(5,6,7,8,9,10);

BEGIN

l_tab1 := l_tab1 MULTISET INTETSET l_tab2;

FOR i IN l_tab1.first .. l_tab1.last LOOP

Dbms_output.put_line(l_tab1(i));

END LOOP;

END;

/

PL/SQL - Cursors

Cursors:

Oracle creates a memory area, called as context area for processing an SQL statement, which contains all information needed for processing the statement.

A cursor is a pointer to this context area. PL/SQL controls the context area through cursor. A cursor holds the rows( one or many) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.

We can name a cursor so that we can use it in a program to fetch and process the rows returned by the SQL statements, one at a time,

There are two types of cursors:

• Implicit cursor
• Explicit cursor

Implicit cursor:

• Implicit cursors are automatically created by Oracle whenever an SQL statement(DML statements like INSERT, UPDATE AND DELETE) is executed. 
• For INSERT operations, the cutsor holds the data that needs to be inserted. For UPDATE AND DELETE operations, the cursors identifies the tows that would be affected.

In PL/SQL, we can refer to the most recent implicit cursor as the SQL CURSOR, which always has the following attributes:

%FOUND : Returns true of Select into, insert, update and delete statement affected one or more rows, else it returns FALSE.

%ISOPEN : always returns false, cause Oracle closes the sql cursor automatically after executing its associated sql statement.

%NOTFOUND : The logical opposite of %FOUND.

%ROWCOUNT: Returns the number of rows affected by an INSERT, UPDATE OR DELETE or returned by a SELECT INTO statement.

Example:

IF sql%found THEN

total_rows := sql%rowcount;

dbms_output.put_line( total_rows || 'customers selected ');

END IF;

Explicit cursor:

A cursor which we can create ourselves, not a default oracle one, is called explicit cursor. 

**A cursor is the name for a structure in memory, called a private SQL area, which the server allocates at runtime for each statement.

Steps to create an Explicit cursor:

• Declare
• Open
• Fetch one or more times
• Close

Example1: retrieving 1 row using explicit cursor:

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

--declaration of cursor

cursor c is SELECT first_name, country FROM customer WHERE customer_id = c_id;

BEGIN

open c; -- open cursor

-- fetching of data

fetch c INTO c_name, c_country;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

Close c; --closing cursor.

EXCEPTION

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN too_many_rows THEN

dbms_output.put_line('You got more than 1 row!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

/

Example2: retrieving more than 1 row using explicit cursor:

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

--declaration of cursor

cursor c is SELECT first_name, country FROM customer WHERE customer_id = c_id;

BEGIN

open c; -- open cursor

LOOP

-- fetching of data

fetch c INTO c_name, c_country;

EXIT WHEN C%NOTFOUND;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

END LOOP;

Close c; --closing cursor.

END;

Using Records in cursor:

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

--declaration of cursor

cursor c is SELECT first_name, country FROM customer WHERE customer_id = c_id;

c_rec customer%rowtype;

BEGIN

open c; -- open cursor

LOOP

-- fetching of data

fetch c INTO c_rec;

EXIT WHEN C%NOTFOUND;

dbms_output.put_line('Name: '|| c_rec.first_name);

dbms_output.put_line('Country: '|| c_rec.country);

END LOOP;

Close c; --closing cursor.

END;

Cursor For Loop:

With cursor FOR loop, the body of the loop is executed for each row returned by the sql query.

Syntax:

FOR customer_rec IN

(Select * from customer where customer_id = 10)

LOOP

Dbms_output.put_line(customer_rec.last_name);

END LOOP;

Example:

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

BEGIN

FOR c_rec IN (SELECT first_name, country FROM customer WHERE customer_id = c_id)

LOOP

dbms_output.put_line('Name: '|| c_rec.first_name);

dbms_output.put_line('Country: '|| c_rec.country);

END LOOP;

END;

Cursor variable and reference cursor:

A cursor variable is a variable that points to a cursor or a result set. We can pass a cursor variable as a arugment to a procedure or function.

Pass a cursor variable back to the host environment that called the program unit - the result set can be consumed for display or other processing.

Syntax:

C_variable SYS_REFCURSOR;

Example:

CREATE OR REPLACE FUNCTION get_names

(

     cust_id IN NUMBER

)

     RETURN SYS_REFCURSOR

IS

     l_return SYS_REFCURSOR;

BEGIN

     OPEN l_return FOR

          SELECT first_name,last_name FROM        customer WHERE customer_id = cust_id;

     RETURN l_return;

END get_names;

CREATE OR REPLACE PROCEDURE DISPLAY_NAMES

IS

C_REC SYS_REFCURSOR;

FNAME VARCHAR2(50);

LNAME VARCHAR2(50);

BEGIN

C_REC := GET_NAMES(10);

LOOP

FETCH C_REC INTO FNAME,LNAME;

EXIT WHEN C_REC%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(FNAME);

DBMS_OUTPUT.PUT_LINE(LNAME);

END LOOP;

CLOSE C_REC;

END;

EXECUTE DISPLAY_NAMES();

Exceptions for cursors:

CURSOR_ALREADY_OPEN: we get this exception when we try to open a cursor thats already open.

INVALID_CURSOR: If we try to reference a cursor that does not exist. This may have happened cause we have executed a FETCH cursor or CLOSE cursor before opening the cursor.

PL/SQL - Working with Records | ROWTYPE data type

Records:

• A record is a composite datatype, which means that it can hold more than one piece of information, as compared to a scalar datatype such as string or number.
• Tables are made up of rows of data, each consisting of one or more colunns, so it stands to reason that Oracle database would make it as easy as possible to work with those rows of data inside a PL/SQL program, and it does precisely that through its implementation of the record. 
• Dealing one row at a time rather than long list of columns or variables,  is far better performance and easy to maintain.

Example:

CREATE OR REPLACE PROCEDURE process_customer

(

c_id IN customer.customer_id%type

)

IS

c_rec customer%rowtype;

BEGIN

Select * into c_rec FROM customer WHERE customer_id = c_id;

Dbms_output.put_line('First Name: ' || c_rec.first_name);

Dbms_output.put_line('Last Name: ' || c_rec.last_name);

END;

Passing Records as parameters:

CREATE OR REPLACE PROCEDURE process_customer

(

c_id IN customer.customer_id%type

)

IS

c_rec customer%rowtype;

BEGIN

Select * into c_rec FROM customer WHERE customer_id = c_id;

show_customer(c_rec);

END;

CREATE PROCEDURE show_customer

(

customer_in IN customer%ROWTYPE

)

IS

BEGIN

Dbms_output.put_line('First Name: ' || customer_in.first_name);

Dbms_output.put_line('Last Name: ' || customer_in.last_name);

END;

/

Insert data using Records:

CREATE OR REPLACE PROCEDURE show_customer

(

customer_in IN customer%ROWTYPE

)

IS

BEGIN

INSERT INTO CUSTOMER VALUES CUSTOMER_IN;

COMMIT;

END;

/

Update data using Records:

CREATE OR REPLACE PROCEDURE update_customer

(

customer_in IN customer%ROWTYPE

)

IS

BEGIN

UPDATE CUSTOMER SET ROW =  CUSTOMER_IN WHERE CUSTOMER_ID = CUSTOMER_IN.CUSTOMER_ID;

COMMIT;

END;

/

User defined Record types:

So far whatever records we have used , are based on table or cursor. We can declare our own user defined record using TYPE....RECORD statement.

Some times business requirement is such that it does not meet with table columns, so in that case, we can create custom record to overconme such hardle.

Syntax:

TYPE customer_rec IS RECORD

(

customer_name varchar2(100),

total_sales number,

...

)

customer_info_rec1 customer_rec;

customer_info_rec2 customer_rec;

Example:

CREATE OR REPLACE PROCEDURE process_customer

(

c_id IN customer.customer_id%type

)

IS

TYPE customer_rec IS RECORD

(

first_name varchar2(100);

last_name varchar2(100);

)

c_rec customer_rec;

BEGIN

Select first_name,last_name into c_rec FROM customer WHERE customer_id = c_id;

Dbms_output.put_line('First Name: ' || c_rec.first_name);

Dbms_output.put_line('Last Name: ' || c_rec.last_name);

END;

PL/SQL - Packages

A Package is a schema object that groups logically related PL/SQL types, items and subprograms like procedures and functions. Package can hold other constructs too, such as exceptions, variables, cursors and type declarations.

Package have two parts:

• Package specification
• Package body

Advantages of packages:

• Modularity: packages let us encapsulate logically related types, items  and subprogrmas in a named PL/SQL module.
• Easier application design : when designing an application, initially need to mention only interface information in the package specs, we can define the detailed body code later.
• Information hiding : with packages, we can specify which types , items and subprograms are public or private.
• Better pefformance: when we call a packaged subprogram for the first time, the whole package is loaded into the memory. So, later calls to related subprograms in the package require no disk I/O.
• Added functionality: packaged public variables and cursors persist for the duration of a session so, they cab be shared by all the subprograms that execute in the environment.

Package Specification:

The package specification tells a user of the package what it cam do rather than how it will do it. The spec contains only the header of the program units rather any executable code. Its kind of like a declaration section for program units.

Syntax:

CREATE OR REPLACE PACKAGE package_name

AS

Program1_header;

Program2_header;

Program3_header;

END package_name;

/

Example:

CREATE OR REPLACE PACKAGE CUSTOMER_PACKAGE

AS

PROCEDURE ADD_CUSTOMER

(

c_id IN NUMBER,

c_fname IN VARCHAR2,

c_lname IN VARCHAR2,

c_addr IN VARCHAR2,

c_date_added IN DATE

);

PROCEDURE DISPLAY_NAMES;

PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

);

PROCEDURE show_customer

(

customer_in IN customer%ROWTYPE

);

FUNCTION get_names

(

custid IN NUMBER

)

RETURN SYS_REFCURSOR;

FUNCTION find_salescount

(

p_sales_date IN date

)

RETURN NUMBER;

END CUSTOMER_PACKAGE;

Package Body:

The package body contains the program unit bodies - the executable statements that correspond with the headers in the package specification. 

Syntax:

CREATE OR REPLACE PACKAGE BODY package_name

AS

-- complete subprogram code

program1_code;

program2_code;

program3_code;

END package_name;

/

Example:

CREATE OR REPLACE PACKAGE BODY customer_package

AS

PROCEDURE ADD_CUSTOMER

(

c_id IN OUT NUMBER,

c_fname IN VARCHAR2,

c_lname IN VARCHAR2,

c_addr IN VARCHAR2,

c_date_added IN DATE

)

AS

BEGIN

INSERT INTO CUSTOMER(customer_id, first_name, last_name, address, date_added) VALUES (c_id,c_fname,c_lname,c_address,c_date_added);

COMMIT;

dbms_output.put_line('Data successfully Inserted');

SELECT COUNT(1) INTO c_id FROM CUSTOMER;

END ADD_CUSTOMER;

PROCEDURE DISPLAY_NAMES

IS

C_REC SYS_REFCURSOR;

FNAME VARCHAR2(50);

LNAME VARCHAR2(50);

BEGIN

C_REC := GET_NAMES(10);

LOOP

FETCH C_REC INTO FNAME , LANME;

EXIT WHEN C_REC%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(FNAME);

DBMS_OUTPUT.PUT_LINE(LNAME);

END LOOP;

CLOSE C_REC;

END;

PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

ex_customer_id EXCEPTION;

BEGIN

IF c_id <=0 THEN

RAISE ex_customer_id;

SELECT first_name, country INTO c_name, c_country FROM customer WHERE customer_id = c_id;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

EXCEPTION

WHEN ex_customer_id THEN

dbms_output.put_line('ID must be greater than zero!');

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN too_many_rows THEN

dbms_output.put_line('You got more than 1 row!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

PROCEDURE show_customer

(

customer_in IN customer%ROWTYPE

)

IS

BEGIN

Update customer set row =customer_in where customer_id =customer_in.customer_id;

COMMIT;

END;

FUNCTION find_salescount

( p_sales_date IN date

) RETURN NUMBER

AS

no_of_sales number := 0;

BEGIN

SELECT count(*) INTO no_of_sales from sales where sales_date = p_sales_date;

RETURN no_of_sales;

END find_salescount;

FUNCTION get_names

(

custid IN NUMBER

)

RETURN SYS_REFCURSOR;

IS

l_return SYS_REFCURSOR;

BEGIN

OPEN l_return FOR

SELECT first_name, last_name FROM customer WHERE customer_id = custid;

RETURN l_return;

END get_names;

END customer_package;

How to execute sub programs present in the packages:

execute <package_name>.<resource_name>

Example:

execute.customer_package.display_names;

execute customer_package.get_customer(16);

PL/SQL - Exceptions

Exceptions:

An error condition during a program excution is called an exception in PL/SQL. We can catch such errors using EXCEPTION block in the program and an appropriate action is taken against the error condition.

There are two types of exceptions:

• System defined exceptions
• User defined exceptions

Syntax:

DECLARE

<declarations section>

BEGIN

<executable commands>

EXCEPTION

WHEN exception1 THEN

exception1-handling-statements

WHEN exception2 THEN

exception2-handling-statements

WHEN exception3 THEN

exception3-handling-statements

.......

WHEN others THEN

exception-handling-statements

END;

Exceptions example:

Fetch customer details based on customer id. There are 3 possibilities to get the responses.

1. Successfully fetched 1 customer 

2. Error with no customer 

3. Many customers records found.

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

BEGIN

SELECT first_name, country INTO c_name, c_country FROM customer WHERE customer_id = c_id;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

EXCEPTION

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN too_many_rows THEN

dbms_output.put_line('You got more than 1 row!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

/

Run the procedure:

Execute get_customer(10);

User defined exceptions:

PL/SQL allows us to define our own exceptions according to the need of our program. A user defined exception must be declared and then raised explicitly, using either a RAISE.

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

ex_customer_id EXCEPTION;

BEGIN

IF c_id <=0 THEN

RAISE ex_customer_id;

SELECT first_name, country INTO c_name, c_country FROM customer WHERE customer_id = c_id;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

EXCEPTION

WHEN ex_customer_id THEN

dbms_output.put_line('ID must be greater than zero!');

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN too_many_rows THEN

dbms_output.put_line('You got more than 1 row!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

/

System defined Exception List:

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system dependent limit. 

PL/SQL declares predefined exceptions globally in package STANDARD  which defines the PL/SQL environment. So we need not to declare them again and just to reuse them.

Few of them are below:

ZERO_DIVIDE : when your program attempts to divide a number by zero

VALUE_ERROR : when an arithmatic, conversion, truncation or size-constraint error occurs.

STORAGE_ERROR: PL/SQL runs out of memory ot memory has been corrupted.

ROWTYPE_MISMATCH : the host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types.

PL/SQL - Different types of Blocks

Anonymous Blocks:

An anonymous block is a PL/SQL block that has no name attached to it. They must be generated and utilized in the same session because they will not be saved as database objects on the server. 

If using anonymous blockes were the only way we could organize our statements, it would be hard to use PL/SQL to build robust, large and complex application. Instead, PL/SQL supports the definition of named blocks of code called procedures and Functions etc.

Example of anonymous block:

DECLARE

c_id number:=10;

c_name varchar2(50);

c_addr varchar2(50);

BEGIN

SELECT first_name, address INTO c_name, c_addr FROM customer WHERE cutomer_id := c_id;

DBMS_OUTPUT.PUT_LINE('Name: ' || c_name);

DBMS_OUTPUT.PUT_LINE('Address: ' || c_addr);

END;

/

What are Procedures?

A procedure is a group of PL/SQL statements that you can call by name. It can accepts values as input, process the data and return output if required.

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name

(parameter1 MODE DATATYPE [ DEFAULT EXPTESSION] , parameter2 MODE DATATYPE [ DEFAULT EXPTESSION], ...)

AS

[ variable1 DATATYPE;variable2 DATATYPE; ...]

BEGIN

executable_statements

[ EXCEPTION

WHEN

exception_name

THEN

executable_statements ]

END;

/

MODE:

Mode is usually one of the following : IN , OUT, or IN OUT.

IN: the caller supplies the value of the parameter.

OUT: the Procedure sets the value of the parameter  and the calling program can read it as response.

IN OUT: It means that the calling program may pass the argument and the stored procedure can modifiy the INOUT parameter and pass the new value back to the calling program.

Creating a Procedure:

CREATE OR REPLACE PROCEDURE ADD_CUSTOMER

(

c_id IN NUMBER,

c_fname IN VARCHAR2,

c_lname IN VARCHAR2,

c_addr IN VARCHAR2,

c_date_added IN DATE

)

AS

BEGIN

INSERT INTO CUSTOMER(customer_id, first_name, last_name, address, date_added) VALUES (c_id,c_fname,c_lname,c_address,c_date_added);

COMMIT;

dbms_output.put_line('Data successfully Inserted');

END ADD_CUSTOMER;

/

Output: Procedure ADD_CUSTOMER compiled. >> this means we just saved this code in the server database.

Calling a Procedure:

Way1:

BEGIN

ADD_CUSTOMER(1,'Sri', 'Das', '12 AE Salt lake Kolkata', SYSDATE);

END;

Way2: order of parameters are not important.

BEGIN

ADD_CUSTOMER

(

c_id => 1,

c_fname => 'Sri',

c_lname => 'Das',

c_addr => '12 AE salt lake kolkata',

c_date_added => SYSDATE

);

END;

Procedure with OUT mode:

CREATE OR REPLACE PROCEDURE ADD_CUSTOMER

(

c_id IN NUMBER,

c_fname IN VARCHAR2,

c_lname IN VARCHAR2,

c_addr IN VARCHAR2,

c_date_added IN DATE,

total_count OUT NUMBER

)

AS

BEGIN

INSERT INTO CUSTOMER(customer_id, first_name, last_name, address, date_added) VALUES (c_id,c_fname,c_lname,c_address,c_date_added);

COMMIT;

dbms_output.put_line('Data successfully Inserted');

SELECT COUNT(1) INTO total_count FROM CUSTOMER;

END ADD_CUSTOMER;

/

DECLARE

tcount NUMBER(10);

BEGIN

ADD_CUSTOMER(1,'Sri', 'Das', '12 AE Salt lake Kolkata', SYSDATE, tcount);

dbms_output.put_line('Total Records: ' || tcount);

END;

Procedure with IN OUT mode:

CREATE OR REPLACE PROCEDURE ADD_CUSTOMER

(

c_id IN OUT NUMBER,

c_fname IN VARCHAR2,

c_lname IN VARCHAR2,

c_addr IN VARCHAR2,

c_date_added IN DATE

)

AS

BEGIN

INSERT INTO CUSTOMER(customer_id, first_name, last_name, address, date_added) VALUES (c_id,c_fname,c_lname,c_address,c_date_added);

COMMIT;

dbms_output.put_line('Data successfully Inserted');

SELECT COUNT(1) INTO c_id FROM CUSTOMER;

END ADD_CUSTOMER;

/

DECLARE

tcount NUMBER(10):=120;

BEGIN

ADD_CUSTOMER(tcount,'Sri', 'Das', '12 AE Salt lake Kolkata', SYSDATE,);

dbms_output.put_line('Total Records: ' || tcount);

END;

What are functions?

• A stored function also called a user function or user defined function is a set of PL/SQL statements you can call by name.
• Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called.

Syntax:

CREATE [OR REPLACE] FUNCTION function_name

(parameter1 MODE DATATYPE [ DEFAULT EXPTESSION] , parameter2 MODE DATATYPE [ DEFAULT EXPTESSION], ...)

RETURN DATATYPE

AS

[ variable1 DATATYPE;variable2 DATATYPE; ...]

BEGIN

executable_statements

RETURN expression;

[ EXCEPTION

WHEN

exception_name

THEN

executable_statements ]

END;

/

Example:

CREATE OR REPLACE FUNCTION find_salescount

( p_sales_date IN date

) RETURN NUMBER

AS

no_of_sales number := 0;

BEGIN

SELECT count(*) INTO no_of_sales from sales where sales_date = p_sales_date;

RETURN no_of_sales;

END find_salescount;

Calling a function

Way1:

SELECT find_salescount(to_date('01-jan-2023','dd-mon-yyyy')) from dual

Way2:

DECLARE

salescount number := 0;

BEGIN

salescount := find_salescount(to_date('01-jan-2023','dd-mon-yyyy'));

dbms_output.put_line(salescount);

END;

PL/SQL - Reading data from databse and Inserting data into database

Reading data from database:

We can use the "SELECT INTO" statement of SQL to assign values to PL/SQL variables. For each item in the SELECT list , there must be a corresponding, type compatible variable in the INTO list.

Usecase: fetch the customer name and address from customer table for customer id =10

DECLARE

c_id number:=10;

c_name varchar2(50);

c_addr varchar2(50);

BEGIN

SELECT first_name, address INTO c_name, c_addr FROM customer WHERE cutomer_id := c_id;

DBMS_OUTPUT.PUT_LINE('Name: ' || c_name);

DBMS_OUTPUT.PUT_LINE('Address: ' || c_addr);

END;

/

Whats is %type:

DECLARE

c_id customer.customer_id%type:=10;

c_name customer.first_name%type;

c_addr customer.address%type;

BEGIN

SELECT first_name, address INTO c_name, c_addr FROM customer WHERE cutomer_id := c_id;

DBMS_OUTPUT.PUT_LINE('Name: ' || c_name);

DBMS_OUTPUT.PUT_LINE('Address: ' || c_addr);

END;

/

Inserting data into Database:

We use "INSERT INTO" statement to insert data into database table.

DECLARE

c_id customer.customer_id%type :=1;

c_fname customer.first_name%type := 'Sri';

c_lname customer.last_name%type :='Das';

c_addr customer.address%type := ' 22 AE block Salt Lake kolkata';

BEGIN

INSERT INTO CUSTOMER(customer_id, first_name, last_name, address) VALUES (c_id,c_fname,c_lname,c_address);

COMMIT;

dbms_output.put_line('Data successfully Inserted');

END;

/

Note: we can also perform update a record and delete a record in the database table.

PL/SQL Links

PL/SQL topics covered:

1. PL/SQL basics
   
2. PLSQL - Fetch data from database | Insert data into a database table
   
3. PLSQL - Different types of Blocks | Create a Procedure | Calling a procedure | Create a Function | Calling a function | Anonymous block
   
4. PLSQL - Exceptions
   
5. PL/SQL - Packages
   
6. PL/SQL - Working with Records.
   
7. PL/SQL - Cursors
   
8. PL/SQL - Collections
   
9. PL/SQL - Triggers
   
10. plsql-bulk-processing
    
11. PL/SQL - Large Objects
    

PL/SQL Basic

What is PLSQL:

• PL/SQL is the Oracle Procedural Language extension of SQL.
• A PL/SQL program can have both SQL statements and Ptocedural statements.
• In the PL/SQL program, the SQL is a popular language for both querying and updating data in a relational database management systems(RDBMS) while the procedural statements are used to process individual piece of data and control the program flow.
• PL/SQL is a highly structured and readable language.
• PL/SQL is a standard and portable language for Oracle Database development. If you develop a program that executes on an Oracle database, you can quickly move it to another compatablr Oracle database without any changes.

**Procedural refers to a series of ordered steps that the computer should follow to produce a result.

Why SQL alone is not enough?

• SQL does not support looping and condition statements etc.
• In SQL, you can not execute more than one statements at a time, so running more than one statements increases the network traffic.
• SQL always gives system defined error messages   when user perform any wrong transactions, no place for custom exceptions.
• SQL does not support procedural language features such as code reusability and modularity and some other features of OOPS.

To overcome all the above limitation of SQL, We use PL/SQL.

PL/SQL Advantages:

PL/SQL is a poweful, completely portable, high-performance transaction processing language that offers the following advantages:

• Support for SQL.
• Support for object oriented programming
• Better performance
• Higher Productivity
• Full Portability
• Tight integration with Oracle
• Tight Security

PL/SQL Structure:

PL/SQL is a block structured language, meaning that PL/SQL programs are divided and written in logical blocks of code.

Each block consists of three sub parts:

DECLARE

<declaration section>

BEGIN

<executable commands(s)>

EXCEPTION

<exception handling>

END;

First example:

DECLARE

BEGIN

dbms_output.put_line('Welcome to the world');

END;

** to view the output, SQL developer >> View tab >> Dbms Output >> click on plus button >> select the database where you will run the plsql code >> ok >> now execute the code and see the output.

Declaring Variable:

DECLARE section is used to declare all our variables.

Syntax:

<Variable Name> <Type>;

Example:

ordernumber number := 1001; 

Code:

DECLARE

ordernumber number :=1001;

orderid number default 1002;

customername varchar2(20):='John';

BEGIN

dbms_output.put_line(ordernumber);

dbms_output.put_line(orderid);

dbms_output.put_line(customername);

END;

output: 

1001

1002

John

**This is we declare a variable and assign 1001 value to it. We can also assign a value using default keyword like below

Orderid number default 1002;

** if we use constant keyword with a variable that means that variable cant be used as an assignment target.

ordernumber constant number:=1001;

Comments in PL/SQL:

Single line comments:

-- variable creation

multi line comments:

/* this is a multi line comment

We can write about what we are doing in the program

*/

Scope of variables:

Global variable vs Local variable

• The variable which is declared under the main block and is visible within the entire program is called Global variable.
• The variable which is declared under the sub block and is only visible within the sub block is called Local variable.

Example:

DECLARE

-- Global Variable

num1 number:=20;

BEGIN

dbms_output.put_line(' Outer variable num1: ' || num1)

DECLARE

-- Local Variable

num2 number:=10;

BEGIN

dbms_output.put_line(' Inner variable num1: ' || num1)

dbms_output.put_line(' Inner variable num2: ' || num2)

END;

END;

Output:

Outer variable num1: 20

Inner variable num1: 20

Inner variable: 10

IF then ELSE statement:

This is needed when we want output or action based on some condition. There are folowing 3 types of decision making statements:

Type1:

If <condition> 

then <action>

end if;

Case: if total amount > 100, give 10% discount.

DECLARE

total_amount number:= 102;

discount number:=0;

BEGIN

If total_amount > 100

Then discount := total_amount * .1;

End if;

dbms_output.put_line(discount);

end;

Output: 10.2

Type2:

If <condition>

Then <action>

else

<action>

end if;

Case : if total_amount > 100, give 10% else give 5 % discount

DECLARE

total_amount number:= 100;

discount number:=0;

BEGIN

If total_amount > 100

Then discount := total_amount * .1;

Else

Discount:= total_amount * .05;

End if;

dbms_output.put_line(discount);

end;

Output : 5

type3:

If <condition>

Then <action>

elsif <condition>

Then <action>

Else

<action>

end if;

Case: if total_amount >200, give 20%, if total_amount >=100 and total_amount <=200 give 10%, else give 5% discount.

DECLARE

total_amount number:= 100;

discount number:=0;

BEGIN

If total_amount > 200

Then discount := total_amount * .2;

Elsif total_amount >=100 and total_amount <=200

Then discount:= total_amount * .1;

Else

discount :=total_amount*.05;

End if;

dbms_output.put_line(discount);

end;

Output: 10

CASE statement:

CASE selector 
   WHEN 'value1' THEN S1; 
   WHEN 'value2' THEN S2; 
   WHEN 'value3' THEN S3; 
   ... 
   ELSE Sn;  -- default case 
END CASE;

Case: if total_amount >200, give 20%, if total_amount >=100 and total_amount <=200 give 10%, else give 5% discount.

DECLARE

total_amount number:= 100;

discount number:=0;

BEGIN

CASE

When total_amount > 200

Then discount := total_amount * .2;

When total_amount >=100 and total_amount <=200

Then discount:= total_amount * .1;

Else

discount :=total_amount*.05;

End CASE;

dbms_output.put_line(discount);

end;

Output: 10

WHILE Loop:

While loop is used to execute statements till a particular condition is met.

DECLARE

counter numer(2) := 10;

BEGIN

WHILE counter < 20

LOOP

dbms_output.put_line('value of counter: ' || counter);

counter := counter + 1;

END LOOP;

END;

/

Output:

value of counter : 10

value of counter : 11

value of counter : 12

value of counter : 13

value of counter : 14

value of counter : 15

value of counter : 16

value of counter : 17

value of counter : 18

value of counter : 19

FOR LOOP:

For loop allows us to execute code repeatedly for a fixed number of times.

DECLARE

counter numer(2) := 10;

BEGIN

FOR counter IN 10..20

LOOP

dbms_output.put_line('value of counter: ' || counter);

END LOOP;

END;

/

Output:

value of counter : 10

value of counter : 11

value of counter : 12

value of counter : 13

value of counter : 14

value of counter : 15

value of counter : 16

Value of counter : 17

value of counter : 18

value of counter : 19

value of counter : 20

To reverse the loop order:

DECLARE

counter numer(2) := 10;

BEGIN

FOR counter IN REVERSE 10..20

LOOP

dbms_output.put_line('value of counter: ' || counter);

END LOOP;

END;

/

OIC - Oracle Integration Gen 3 | what's new for the Oracle Integration 3

Reference:

https://docs.oracle.com/en/cloud/paas/application-integration/whats-new/index.html

BICC | BI Cloud Connector | Types of BICC Extract | Roles and Provisions required to access BICC console and UCM | Create your first BICC Extract FULL | Run for Incremental Extracts | Secure your BICC Extracts

BICC is a built in tool to extract data and store the sams data to different storages in the form of CSV file. This extracted data can be transformed and loaded to different warehouse tools for data analysis. The extracted data can be stored in 3 ways as depicted in the diagram.

Types of BICC extracts:

• FULL extract : first to to load full extract
• Incremental extract : daily extract

Roles and Provisions required to access BICC console and UCM:

A. Provisioning a User for BICC console access:

Role code: BIACM_ADMIN

Role Name: BIACM_ADMIN

Role type: BI - Abstract Roles

Roles Hierarchy:

1. ESS Administrator Role(ESSAdmin)

2. Application Implementation Administrator (ORA_ASM_APPLICATION_IMPLEMENTATION_ADMIN_ABSTRACT)

B. provision a user to access BICC content in UCM:

Role code: BICC_UCM_CONTENT_ADMIN

Role name: BICC_UCM_CONTENT_ADMIN

Role Type; BI - Abstract Roles

Roles Hierarchy: 

1. Upload and download data from on-premise system to cloud system(OBIA_EXTRACTTRANSFORMLOAD_RWD)

URLS:

https://<pod>/biacm

https://<pod>/cs

Detailed steps with screenshots:

Or

Create your first BICC Full Extract:

Highlevel steps:

• Create an offering and associate the PVOs to be extracted.
• Create a job and enabled for extract. You can also customize the PVO columns which are required and use filter.
• Create a job schedule to extract the data.

Public View Objects:

1.FscmTopModelAM.FinApInvTransactionsAM.InvoiceHeaderPVO

2.FscmTopModelAM.FinApInvTransactionsAM.InvoiceLinePVO

Detailed steps with screenshots:

Run for Incremental Extracts:

Prune time : In minutes default BICC incremental job will add a look back timeframe as defined in prune time setting. Since the extraction is done on live app db and not on a snapshot, look back or prune time is best practicr to ensure dependency synchronization across objects, Default works best for extracts with daily ot higher reoccurance. Prune time should be adjusted when extracts are scheduled more frequently or if downstream system can handle objects extracted in any order.

Secure BICC extract:

We can create a pgp public and private key and import the public key in the configure External storage UCM data encryption import option that will allow to extract secure gpg key encrypted data which later on we can decrypt with the private / secrect key.