Oranet launch IPM+

Oranet have launched an industry changing software onto the market place with IPM+.

IPM+ is an Intelligent Power Management software that provides businesses with up to 60% power usage savings on their technology hardware including PC’s, laptop’s, Monitor’s, Server’s etc.

Companies will not only reduce their power usage therefore reducing their electricity bill but all lower their carbon footprint by reducing the CO2 emissions allowing them to further add to their savings by dealing in Carbon Credits.

More infomation is available on our website page : Oranet IPM+

Oranet in talks with Innive Inc.

Oranet are in advanced talks with the U.S. company Innive Inc. to form a strategic partnership as we look to add our OneView application as a enhancement to their current Airport 360 product.

Oranet will also become Innive partner in the UK & Europe to help promote the Airport 360 product to all airports in this area of the world. Innive are well established in U.S. and Middle East airports and have been looking for the right partner to help them move forward within the UK & Europe.

With Oranet’s OneView offering they believe they have found that partner.

E.On relationship flourishing

Oranet’s on site support work with E.On, based in Nottingham, continues to flourish as the 1 year anniversary looms.

Oranet have been providing on site developer support for the vast Oracle database estate that E.On currently manage – the work started in November 2016 and Oranet are currently contracted to continue their expert service well into 2018.

With exciting new changes planned for the IT estate in 2018, Oranet are looking forward to helping E.On move forward with the plans around development and support.

– COMMENTS

Comments

Good practice for any developer in a commercial environment coding PLSQL or indeed any language is the use of comments. Annotating your code allows easy maintenance by yourself should you need to change the code in the future or by other developers.

Syntax

/* Comment in Code */

Example

CREATE OR REPLACE PROCEDURE prc_comment_example AS
-- -------------------------------------------------------------------------
-- PROCEDURE NAME : prc_comment_example
-- PURPOSE : To show how to use comments in PLSQL
--
-- DATE VERSION AUTHOR DESCRIPTION
-- 01/01/1900 1.0 J. Bloggs Initial Development
--
-- -------------------------------------------------------------------------
BEGIN
/* This is the start of the code and we’re showing a different comment */

-- Another comment we’re about to commit the work done.
COMMIT;
END prc_comment_example;

You may well see the /* */ style of comment used in a SQL statement directly after the SELECT reserved word, this is called a hint. It is used when you’re trying to make Oracle use a specific INDEX or manipulate the explain path Oracle takes.

– TRANSACTIONS

Transactions

A transaction in Oracle is like a personal workspace for a period of time – no other user can see your DML changes to the database until you COMMIT them to the database, you can also ROLLBACK any changes you’ve made or if an error occurs. Both of these statements essentially close of one transaction and start another session.

Syntax

COMMIT;
ROLLBACK;

Example

BEGIN
INSERT INTO employee ( employee_id,
first_name,
surname,
date_of_birth,
manager_ind )
VALUES ( 1234,
‘Jim’,
‘Bloggs’,
’02-MAY-89’,
‘N’ );
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;

The syntax for these commands are as simple as they appear – the above example is inserting a single record into the employee table, if it completes the insert successfully then it commits the change to the database. However, if the insert should fail for any reason the code will drop into the exception handler and rollback the transaction.

There are more advanced uses within the transaction space such as SAVEPOIINT and PRAGMA AUTONOMOUS_TRANSACTION – these allow a user to rollback to specific point in the code to avoid rollback all the work and when calling a procedure within a procedure AUTONOMOUS_TRANSACTION will allow you to commit that work in a separate transaction without affecting your ability to rollback or commit the main transaction. More information can be obtained by signing up to our DSaaS service.

– CONDITIONAL STATEMENTS

Conditional Statements

Within PLSQL conditional statements can be used to determine what action to take based on other actions or data retrieved. The main statements within this are the IF and CASE statements.

Syntax

IF
THEN

ELSE

END IF;
CASE [ expression ]
WHEN THEN
WHEN THEN
ELSE
END

Example

IF v_employee_surname = ‘Smith’
THEN
v_smith_count := v_smith_count + 1;
ELSE
V_other_count := v_other_count + 1;
END IF;
CASE
WHEN v_employee_surname = ‘Smith’
THEN v_smith_count := v_smith_count + 1;
ELSE v_other_count := v_other_count + 1;
END;

– LOOP STATEMENTS

Loop Statements

Loop statements are used to process numerous items of data until a specified finish clause is reached – these include CURSOR FOR LOOPS but can also be a simple LOOP with EXIT clause.

Syntax

LOOP
;
EXIT ;

END LOOP;

Example

There a few examples to show which basically do the same work.

STANDARD LOOP
LOOP
v_loop_counter := l_loop_counter + 1;
EXIT WHEN l_loop_counter = 100;
END LOOP;

FOR LOOP (do not confuse with CURSOR FOR LOOP)
FOR l_loop_counter IN 1 .. 100
LOOP
l_loop_counter := l_loop_counter + 1;
END LOOP;

WHILE LOOP
WHILE l_loop_counter <= 100
LOOP
l_loop_counter := l_loop_counter + 1;
END LOOP;

– VARIABLES

Variables

The declaration section of the PLSQL bloke is where you can set up variables that will be used throughout the code – a variable is used to store temp data for use in that specific code execution.

Syntax

[ CONSTANT ] [ := ];

Example

When setting a variable within PLSQL you can set it to be a CONSTANT i.e. the specified initial value set cannot be changed. You can also set a default value for a standard variable.

v_employee_surname VARCHAR2(50) := ‘Smith’;

You can also set the datatype as a table type – to mimic what the table datatype is.

t_employee_name EMPLOYEE.surname%TYPE := ‘Smith’;

– CURSORS

Cursors

Cursor is the name given to SQL statements that are declared within a PLSQL code bloke. Like when you declare a function or procedure you can pass parameters into a cursor when opening it from the executable section of the bloke.

Cursors can be opened in a number or ways in PLSQL to retrieve the data :

CURSOR FOR LOOP
OPEN FETCH
SELECT INTO
EXECUTE IMMEDIATE

Example

We can show all of these examples in one block of code.

DECLARE
CURSOR c1 ( cp_emp_id NUMBER ) IS
SELECT first_name, surname
FROM employee
WHERE employee_id = cp_emp_id;
l_emp_id NUMBER;
l_first_name VARCHAR2(50);
l_surname VARCHAR2(50);
BEGIN
-- SELECT INTO example
SELECT employee_id
INTO l_emp_id
FROM employee
WHERE rownum < 2;
-- EXECUTE IMMEDIATE example
EXECUTE IMMEDIATE ‘SELECT employee_id
FROM employee
WHERE rownum < 2’
INTO l_emp_id;
-- CURSOR FOR LOOP example
FOR r1 IN c1 ( l_emp_id )
LOOP
l_first_name := r1.first_name;
l_surname := r1.surname;
END LOOP;
-- OPEN FETCH example
OPEN c1 ( l_emp_id );
FETCH c1 INTO l_first_name, l_surname;
CLOSE c1;
END;

The other main item to understand with cursors are the attributes – these will return a Boolean answer to the state of a given cursor:

%ISOPEN – TRUE if cursor is open, FALSE if closed.
%FOUND – TRUE if data has been found, FALSE if no row returned.
%NOTFOUND – TRUE is no row returned, FALSE if data has been found.
%ROWCOUNT – Returns number of rows fetched in cursor.

– TRIGGERS

Triggers

There are several Oracle database triggers available – triggers are compiled PL/SQL code that fire automatically on the database following a DML action on a table. Triggers available are :

BEFORE INSERT – Fires before an insert on a table.
AFTER INSERT – Fires after an insert on a table.
BEFORE UPDATE – Fires before an update on a table.
AFTER UPDATE – Fires after an update on a table.
BEFORE DELETE – Fires before a delete on a table.
AFTER DELETE – Fires after a delete on a table.

Syntax

CREATE [ OR REPLACE ] TRIGGER BEFORE INSERT | AFTER INSERT |
BEFORE UPDATE | AFTER UPDATE |
BEFORE DELETE | AFTER DELETE
ON [ FOR EACH ROW ]
DECLARE
[ declaration section ]
BEGIN
Execution section
[ EXCEPTION ]
Exception section
END ;

Example

The following code is a BEFORE INSERT trigger that will set a couple of admin columns on a table of CREATED USER & CREATED DATE.
CREATE OR REPLACE TRIGGER trg_before_ins_emp
BEFORE INSERT ON employee FOR EACH ROW
DECLARE
v_username VARCHAR2(30);
BEGIN
SELECT user INTO v_username
FROM dual;
:NEW.created_user := v_username;
:NEW.created_date := SYSDATE;
END trg_before_ins_emp;

The next example will use the AFTER DELETE trigger to create an audit record for any records deleted from the database.
CREATE OR REPLACE TRIGGER trg_after_del_emp
AFTER DELETE ON employee FOR EACH ROW
DECLARE
v_username VARCHAR2(30);
BEGIN
SELECT user INTO v_username
FROM dual;
INSERT INTO employee_audit
( employee_id,
First_name,
Surname,
Date_of_birth,
Manager_ind,
Deleted_by,
Deleted_date )
( :OLD.employee_id,
:OLD.first_name,
:OLD.surname,
:OLD.date_of_birth,
:OLD.manager_ind,
v_username,
SYSDATE );
END trg_after_del_emp;

Once compiled on the database a trigger can be DROPPED, ENABLED or DISABLED. The ability to enable and disable triggers is useful to avoid having to drop them and re-create. The syntax is as follow :
DROP TRIGGER ;
ALTER TRIGGER DISABLE;
ALTER TRIGGER ENABLE;