Oracle 7Up to 10g – How time flies… Training for seasoned Oracle SQL and PL/SQL Developers

With the advent of the Oracle 7 RDBMS – 1993 – a new era dawned. Many of the key database functions and facilities, such as (Enforced) Integrity Constraints, Stored PL/SQL, Triggers, Snapshots, supplied packages and key SQL features were introduced in the Oracle 7 releases. Many Oracle developers also seem to be anchored in terms of knowledge and experience in the Oracle 7 database and the features and functions that release 7 had to offer. Even though we have moved on. And Oracle has moved on! It turns out that many very experienced developers are still developing in their Oracle 9i and 10g environments as though they live in the world of Oracle7. Every now and again they may accidentally make use of a more recent feature and of course they have read and heard about this and that new option, but Oracle7 is what’s at their fingertips.

By the, not too long ago, I found myself in that exact same position. Programming SQL and PL/SQL in a very backwards compatible way! Realizing that predicament, I embarked on a program to study and take in the Oracle 8i, 9i and more recently 10g stuff that somehow had escaped my notice. This then turned into a workshop for my colleagues at AMIS, initially three days, now four very full days. This Oracle 7Up Workshop allows experienced SQL and PL/SQL Developers to get acquainted with (most of) everything that was added in Oracle 8i, 9i and 10g that is of practical use. It is not a marketing-style new features overview, it is an serious discussion of important, useful, timesaving, performance enhancing functionality. We quickly realized how this training would not only benefit the staff at AMIS: we also offered it to our business partners. By now we have trained a substantial number of software developers, including Oracle DBAs, Oracle Application Developers and Java developers who wanted to better grasp the full potential of SQL in an Oracle database. Most of if not all attendant were able to apply knowledge and code samples from the training in their own jobs starting the day after the training.

Next week – on Thursday 20th april and Friday 21st April addressing SQL- and the week after – Thursday 28th and Friday 29th of April addressing PL/SQL- we have another edition of the Oracle 7Up Workshop (at our office in Nieuwegein, The Netherlands). And there are a few seats available for external participants. You can participate in one of the two blocks or attend both. Details on registration can be found here: AMIS Trainingen.

Topics in the 7Up Training

Now what are all these grand new features that we should know about, I hear you ask.

Well, some of the major ones in the SQL session are: Analytical Functions, Multi-Table Insert and Merge, In Line Views, FlashBack Queries, Materialized Views, Oracle Text, Advanced Constraints implementation, Database Design (Index Organized Tables, Temporary Tables, Function Based Indexes, Instead Of Triggers), Join Syntax, Hierarchical Queries.

The PL/SQL session discusses topics like: Collections and Object Types, bulk operations, dynamic SQL, fine grained access control and auditing, autonomous transactions, system event triggers, new PL/SQL functions and syntax.

For more details and examples of the slides and the labs used in this workshop, see Oracle 7Up Training – Sample of Materials.

Discussion of “7Up Topics” on the weblog

Many of topics that we discuss in the 7Up training have been subject of one or more articles on our weblog. Recent examples of such articles include:

Default Column Value – the syntax for defining default values in your table definitions is richer than you probably realize, including the option to dynamically set default values, depending on the context

How using ROWNUM in In-Line Views can speed up your query – ROWNUM seems like a pretty innocent, not very exciting ‘feature’  of SQL Queries. However, including ROWNUM in a query can have pretty drastic effects – and sometimes positve ones – on your query performance

Functions NVL2 and NULLIF – some new functions that can make constraint definitions and where clauses more compact

Finding overlap between time-periods using LAG and LEAD – Analytical functions allow you to look forward or backward in a set of rows, reducing the need for many complex joins!

Materialized view as mechanism for the declarative (PL/SQL free) implementation of complex business rules – Declarative implementation of business rules like Master must have a restricted number of details (no fewer than X
and not more than Y), The Sum of a certain Detail Column
is restricted within a master and The start-date and end-date of detail-records must be between the Master’s Startdate and Enddate: it can be done!

How we can edit files through simple insert and update operations – Peter Kok’s article on Updateable External Tables

Rapidly building a WebSite search engine using Oracle Text –

Investigating Bill of Materials – Juggling with tree-structures and hierarchical queries using Oracle 9i and 10g Connnect By enhancements

Aspect Oriented Programming in PL/SQL – Making surprising use of the After Create (on compile) Trigger

Merge! – a real li(f/v)e saver

and many more….