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.