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 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.
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.
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.
/* Comment in Code */
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
/* This is the start of the code and we’re showing a different comment */
-- Another comment we’re about to commit the work done.
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.
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.
INSERT INTO employee ( employee_id,
VALUES ( 1234,
WHEN OTHERS THEN
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.
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.
CASE [ expression ]
IF v_employee_surname = ‘Smith’
v_smith_count := v_smith_count + 1;
V_other_count := v_other_count + 1;
WHEN v_employee_surname = ‘Smith’
THEN v_smith_count := v_smith_count + 1;
ELSE v_other_count := v_other_count + 1;
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.
There a few examples to show which basically do the same work.
v_loop_counter := l_loop_counter + 1;
EXIT WHEN l_loop_counter = 100;
FOR LOOP (do not confuse with CURSOR FOR LOOP)
FOR l_loop_counter IN 1 .. 100
l_loop_counter := l_loop_counter + 1;
WHILE l_loop_counter <= 100
l_loop_counter := l_loop_counter + 1;
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.
[ CONSTANT ] [ := ];
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’;
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
We can show all of these examples in one block of code.
CURSOR c1 ( cp_emp_id NUMBER ) IS
SELECT first_name, surname
WHERE employee_id = cp_emp_id;
-- SELECT INTO example
WHERE rownum < 2;
-- EXECUTE IMMEDIATE example
EXECUTE IMMEDIATE ‘SELECT employee_id
WHERE rownum < 2’
-- CURSOR FOR LOOP example
FOR r1 IN c1 ( l_emp_id )
l_first_name := r1.first_name;
l_surname := r1.surname;
-- OPEN FETCH example
OPEN c1 ( l_emp_id );
FETCH c1 INTO l_first_name, l_surname;
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.
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.
CREATE [ OR REPLACE ] TRIGGER BEFORE INSERT | AFTER INSERT |
BEFORE UPDATE | AFTER UPDATE |
BEFORE DELETE | AFTER DELETE
ON [ FOR EACH ROW ]
[ declaration section ]
[ EXCEPTION ]
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
SELECT user INTO v_username
:NEW.created_user := v_username;
:NEW.created_date := SYSDATE;
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
SELECT user INTO v_username
INSERT INTO employee_audit
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;