Bulking up your PL/SQL
Posted by Matt Barker in Oracle
You don’t know what you don’t know. It’s one of those annoyingly obvious statements people will often say when you’ve discovered something new that you had no idea existed. In the vast world of Oracle, this can often be the case.
I had this exact same situation recently during a data conversion process. I needed a way to insert all the good records into a table, and identify those that failed.
My initial approach was to place the inserts in a for loop, wrapping the insert statement in its own block. When an exception is raised, it will be recorded and control will pass to the encompassing block allowing the loop to continue:
For i in 1..pi_employee_data.last loop
Begin
Insert into new_employees values pi_employee_data(i);
Exception
When others then
dbms_output.put_line('Insert data '||SQLERRM
||' Employee#'||pi_employee_data(i).employee_id);
End;
End loop;
commit;
Now, this is all well and good. But what if you want to stick to “best practice” and use bulk statements. I’d been making use of collections, so using a forall statement seemed a better approach. Problem being, you can’t wrap the insert statement into its own SQL block. This is where SQL%BULK_EXCEPTIONS comes in.
Note the addition of ‘save exceptions’ to the following forall statement. If any exceptions are encountered when inserting into the new_employees table, they will be saved to the SQL%BULK_EXCEPTIONS object. Only when the forall statement is finished will the exception handler be called. We’re then free to extract any data regarding the particular record that caused the exception.
function insert_data (pi_employee_data in employee_records)
return varchar2 is
l_no_errors number;
dml_errors exception;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
begin
forall i in 1..pi_employee_data.count save exceptions
insert into new_employees values pi_employee_data(i);
commit;
exception
when dml_errors then
l_no_errors := SQL%BULK_EXCEPTIONS.count;
for i in 1 .. l_no_errors loop
dbms_output.put_line('Bulk insert error: '||
SQLERRM(-1 * SQL%BULK_EXCEPTIONS(i).ERROR_CODE)||
' Employee:'||
pi_employee_data(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX).employee_id);
end loop;
when others then
dbms_output.put_line('Error:'||SQLERRM||
' '||dbms_utility.format_error_backtrace);
end;
As mentioned in a recent article by Tim Place, knowing the problem is one thing, knowing where to find the solution is the other. Using the Oracle documentation and having an idea which keywords to use when looking for a solution makes problems such as the above that much easier.
Conincidently, SQL%BULK_EXCEPTIONS also made an appearance in one of Steve Feuerstein’s talks at the recent InSync conference in Melbourne. As anyone who’s seen Steve before will know, collections and bulk operations should be two of the staples of any piece of PL/SQL code you write. Now with SQL%BULK_EXCEPTIONS, you’ll be able to use them much more effectively.
Tags: Oracle, plsql, technology
User Groups, Webinars, Training and Conferences
Posted by Rod Bishop in Events, Navigo
August, September and October will be busy months in the OrgPlus and HR tech circuit. Here’s a quick overview of upcoming OrgPlus and HR technology events around Australia this quarter:
OrgPlus Succession Planning Webinar
Tuesday 31st August, 12:00pm
Join us for a quick overview and discussion of the new features of OrgPlus Enterprise Succession. We’re now fully up to speed and supporting this new technology, with implementations now live at client sites in Australia.
The potential of this software cannot be overstated: It’s a very slick software solution which supports a streamlined succession planning process. The ramp up time is small indeed. For all Australian and New Zealand organisations missing a succession planning technique, this webinar is a must-see.
OrgPlus Public Training, Brisbane and Auckland
Brisbane: Thursday 16th and Friday 17th September, all day
Auckland: Thursday 14th and Friday 15th October, all day
Invest a little time, and we’ll show you how to save a lot more. Public training is the cost-effective way to up-skill yourself on OrgPlus.
Remember, seats are strictly limited to 10 people per session to ensure the best learning environment. These sessions will be the last in their respective cities this year. Please be sure to register early so that you don’t miss your chance to attend.
Register for OrgPlus Public Training
OrgPlus User Group, Melbourne
Wednesday 22nd September, 4pm
Join us at the first ever Australian OrgPlus User Group and have a fun, productive afternoon, learn from and network with other active members of Australia’s growing OrgPlus community.
Hosted by Andrew Westerman at Jemena, this first user group promises to be a great afternoon to meet other OrgPlus users, discuss solutions for common challenges, hear real life case studies, and share ideas on how to use OrgPlus is new, creative and more efficient ways.
AHRI HR Tech Conference, Sydney
Thursday 14th October, all day
Are you prepared for the next technology wave? At the 2010 AHRI HR Technology Conference you’ll hear from experts on the innovations and applications that are driving this change, and the implications for HR professionals, IT people and senior management.
Navigo will be there, presenting on OrgPlus with Andrew Robb from UWS, and highlighting their use of organisational charts as a business intelligence tool. We will also be taking pre-registrations for the Australian HR Tech Report 2011.
Now in it’s second year the HR Tech Report is the only dedicated research report into Australian HR technology, and we’ll be there seeking input from the industry on which ways to expand.
Learn more about the conference
OrgPlus User Group, Sydney
Wednesday 27th October, all day
The second ever Australian OrgPlus user group will be held in Sydney on this date… Details are yet to be confirmed, but watch this space and we’ll have them for you as soon as they are released.
Tags: Events, HR technology, Navigo
New Navigo Website
Posted by Rod Bishop in Navigo
This week we launched the new Navigo website.
In addition to being prettier than before, our new site has a wealth of great HR technology material including overviews of the solution areas we service, case studies of companies we’ve worked with, and an Alesco HRIS project portfolio which will grow to contain more of the project we’ve worked on for the HRIS.
Take a look http://www.navigo.com.au
Tags: Navigo
Auxiliary Reporting. Now Driven from Your Database
Posted by Rod Bishop in Organisational charting
In a great new feature released with OrgPlus Premium (and Professional) 8.3, OrgPlus will now drive alternate and dotted-line reporting directly from your data extract.
I read a lot of HR blogs that talk about alternative reporting structures. It’s good to see that with HumanConcept’s latest release the features in the technology are keeping track with shifting business norms.
The best way to understand it is to show it in action – so here we go:
Auxiliary Reports and The Beatles
Start with a simple data file. Here we have five (famous) employees. Note the auxiliary reporting relationship defined in AuxReportsTo.
A simple import produces a chart like the one below. Note the dotted line – OrgPlus has automatically recognised the hierarchy in AuxReportsTo.
Too Easy, but potentially very useful for managing complex relationships… Time to get even more complex.
Auxiliary Reports and Matrix Organisations
The new auxiliary reporting features can be used to build almost any chart direct from your data set. An ambitious example is a matrix organisation, where employees report up to their head of department, but also report to their project head.
New to matrix organisations? Read more here.
Again we start with a data file, this one more complex. Note the AuxReportsTo column, Departments and Projects. Our matrix organisation has department leaders, project leaders, and a number of employees who report to both.
See Jim Turner for example, an IT consultant in Project Alpha he reports to both Simon Dugas (Senior Consultant) and also George Creston (Project Leader on Project Alpha).
Complex? No worries! An import, some chart rules and conditional formats later, and we have this chart which explains it all easily.
Every employee reports up and left to their managers. Your matrix is decipherable at a glance.
Does your organisation have some complex relationships? Tell us your examples below…
Or if you’re interested in seeing how our Matrix chart was made, click through to page 2.
Tags: Organisational charting, OrgPlus
Business Development Manager Wanted
Posted by Peter Forbes in Navigo
This is a great opportunity for a results oriented sales person in the business applications space selling Enterprise Solutions to Government and Corporates to join a leading HR Technology software and services company based in our Melbourne office.
What we’re looking for
If you’re the person we’re looking for you will be a results oriented, persistent, self-starting achiever who knows how to sell.
You will be articulate, to the point and excellent at building rapport over the phone and in person presenting and speaking in corporate environments.
You will be a good problem solver, and be able to work unsupervised and efficiently. You will have practical understanding of corporate Information Systems and reporting concepts.
With a demonstrable record in sales, you will enjoy meeting your targets and working with others to achieve success.
Skills and attitude
* Excellent verbal, presentation and written skills
* Excellent computer skills and solid understanding of corporate IT
* Ability to prioritise leads and opportunities and appreciate business urgency
* Motivated self starter – we’ll let you get on with the job
* Ideally have tertiary qualification
If this position sounds like you then be prepared to sell yourself!
To apply, call 03 9517 7846
About Navigo
Navigo is a fast growing company providing HR Technology solutions to medium and large corporates. Our flagship solution is OrgPlus, an organisational charting and workforce planning tool. We also provide HRIS consulting and managed services.
We have a friendly and casual office environment and are looking for people who can grow with the company.
Upcoming HR Events
Posted by Rod Bishop in Events, HRIS, Navigo
The Australian HR and HR tech scene is lined up to be busy for the next months with a host of major events. A short list below, if you’d like us to add others we’ve missed please email us the details.
Members of our team will be in attendance at most of these: We hope to see you there!
HR Summit 2010
28 – 30 April 2010 at the Crystal Palace, Luna Park, Sydney
Free to attend and spread across two days, delegates will see the very best speakers from Australia”s leading companies, as well as the latest products, services and technologies available from a diverse range of industry specialists.
Learn more at http://sydney.hrsummit.com.au
Higher Education HR Conference 2010
28 – 30 April 2010 at the Sofitel, Brisbane
With the title “2020 Vision” this conference is all about planning the University of the future.
Learn more at http://www.aheia.edu.au/conf_2010
AHRI National Convention 2010
10 – 11 May 2010 at the Melbourne Convention and Exhibition Centre, Melbourne
Australia’s biggest event for HR and people managers featuring quality international and local speakers and the latest people management solutions.
Learn more at http://www.convention.ahri.com.au
CeBIT Australia 2010
24 – 26 May 2010 at the Sydney Convention and Exhibition Centre, Darling Harbour
Not strictly HR, but a must visit for anyone on the cusp of HR and business technology, CeBIT is returning to Sydney in under a month.
Learn more at http://www.cebit.com.au/
Oracle and OrgPlus Public Training 2010
Dates throughout April, May and 2010
Our cost-effective public training sessions are now in full swing for 2010, with our first session in Adelaide kicking off next week.
Learn more about Oracle Discoverer training workshops
Learn more about OrgPlus training workshops
.
Tags: Events, HR, HRIS, Navigo
The Australian HR Tech Report 2010 Now Available
Posted by Rod Bishop in HRIS, Navigo
Today we released the Australian HR Tech Report 2010, the latest primary research into HR systems and technology in Australia, including the uptake and satisfaction with HRIS and HR systems, motivators and roadblocks for improvement.
We’ve conducted this research with the aim of giving a little something back to HR in Australia. Many of our customers helped us by submitting their responses to our HR tech survey – to them a huge a thank you.
The report is 40 pages long with commentary, conclusions and recommendations from Peter Forbes, our co-founder and chief HR technology expert. Analysis is divided by industry category and organisation size (in number of employees) to allow you to benchmark your HR organisation against those with similar requirements.
The report is free to download, and we encourage all of our customers and followers of this blog to help themselves to a free copy and see how they compare with their peers in Australian HR.
Download your free copy of the Australian HR Tech Report 2010.
Have you read the report already?
Let us know what you thought of it in the comments below:
Tags: Australia, HR Tech Report, HR technology, HRIS, Navigo
Navigo Moving Offices Friday 19th March
Posted by Dan Scheltinga in Navigo
Just a quick notice to announce that we’re relocating our Melbourne office on Friday 19th March (tomorrow). We’re moving two kilometres down the road to new more spacious offices in Ringwood.
Due to the move our phone system will be diverted to our Sydney office until early next week and our technical support consultants may also be a little slow responding to emails during this time.
Rest assured things will be back to normal by early next week. If you need assistance urgently, please call your Navigo contact on their mobile, or contact Sydney and they can direct you to the best place.
Best regards from Navigo Melbourne.
Tags: Availability, Navigo
Senior Technical Consultant Wanted
Posted by Rod Bishop in Navigo
Another great opportunity from Navigo. This time for an experienced technical consultant who can provide practical and business focused solutions, to join our friendly and cutting-edge business solutions and services company.
The Person
We are looking for a tech consultant with loads of experience in Oracle technology to join us in our large and modern Ringwood office.
If you’re what we are looking for you will be expert in working with customers to understand and solve their business and software issues. You will be great at discovering requirements and translating business issues and motivations into practical solutions using Oracle tools and technologies including PL/SQL, JSF, Oracle Application Server and Database Server.
Additionally you need to be pro-active, organised and multi-skilled technically: We support an application based on SQL Server and IIS, and will need you to be able to learn the ropes and work with this application. Having experience in working with HR/Payroll systems would be advantageous.
The Role
As a small company, we like our consultants to be multi-skilled so the role covers consulting, development, support and training. You will:
- Perform professional engagements (onsite and remote) including project scoping, requirements analysis and functional specs
- Develop solutions in PL/SQL, JSF, Oracle Reports, Oracle Discoverer
- Perform Oracle software upgrades for Application and Database Server (10g, 11g)
- Perform scheduled maintenance tasks for our managed services clients
- Installation and Product consulting for OrgPlus (runs on SQL Server and IIS)
This is a great opportunity to put your diverse technical and consulting skills to work in a growing, forward-thinking organisation with a fun and stimulating environment.
How to Apply
Do NOT email your CV or Resume!
Instead, please give us a call. Phone 03 9517 7846 to leave a message on our voice mail system and tell us why you’re right for the job. We appreciate your participation, however only short-listed applicants will be contacted.
We look forward to hearing from you!
Navigo is Growing
Posted by Rod Bishop in Navigo
It’s been a busy two months at Navigo to start 2010. We have some great projects on the go and I’d like to take a quick moment to share them with you.
January and February have been busy in preparation of the Navigo Australian HR Tech Report 2010. This has been a mammoth project: For those not in the know, in November and December 2009 Navigo commissioned a survey of 165 Australian enterprise-scale organisations to capture the current state of their HR technology. For the first two months of this year we have been analysing and preparing this data. The finished Australian HR Tech Report is now approaching completion and will be distributed free to every one of our contributors. We hope to spark some discussion and are already looking forward to the possibility of a follow-up report next year.
We presented our first industry webinar during February to Universities and higher-education providers around Australia and New Zealand. With over 20 Universities using OrgPlus now we took the time to highlight some great industry-specific uses for the software. The feedback we had was all positive so we look forward to preparing more industry-focused webinars in the near future.
Just finished are our exhibitions at both Mastering SAP HR and the Talent2 User Group conferences. Paulus Briels presented his new talk on practical succession planning “How to drive faster succession planning through visualisation” continuing our focus that skilled visual reporting really can enable workforce decision-making in highly effective, efficient ways.
Our team and Melbourne operation are growing! We welcome this month two new staff members into our Melbourne team, Michael Penny and Erin Allwood. In addition, next week we relocate to our new spacious offices in Ringwood. So much room presents opportunities for us to host training and OrgPlus user group meetings on our own premises, so we look forward also to exploring these opportunities later this year.
In summary, a very busy start to 2010 presenting many opportunities for the future. To all of our valued customers – as our team, capabilities, HR technology solution and service offerings all continue to grow this year we look forward to introducing you to new opportunities for your workforce decision-making – all while maintaining that quality customer service you have come to expect.
We look forward to touching base with you again soon.
Tags: latest news, Navigo
