Category: Oracle

Written by Ian

Found a problem with the Oracle Documentation? Report it!

I was recently working through Chapter 9 of the 12c Database 2 Day Developer’s Guide and found the following bug in the sample code. Here is the original code: FUNCTION add_department ( p_department_name IN departments.department_name%TYPE, p_manager_id IN departments.manager_id%TYPE ) RETURN departments.department_id%TYPE IS l_department_id departments.department_id%TYPE; BEGIN INSERT INTO departments ( department_id, department_name, manager_id ) VALUES ( departments_sequence.NEXTVAL, […]

Written by Ian

The PL/SQL Continue statement

Introduced in Oracle Database 11.1 The PL/SQL CONTINUE statement allows you to skip the current  loop iteration. It can be used conditionally and unconditionally. Unconditional Example BEGIN FOR i IN 1 .. 10 LOOP IF i = 2 THEN CONTINUE; END IF; DBMS_OUTPUT.PUT(i || ‘, ‘); END LOOP; DBMS_OUTPUT.NEW_LINE; END; In the example above, there is […]

Written by Ian

No surprises with Oracle 12c Identity Column performance

Oracle 12c introduced the Identity Column. You can find out more about this feature in the new features guide and within the CREATE TABLE documentation. In this article I will use Tom Kyte’s run stats utility to compare the performance of the IDENTITY column with the explicit use of a sequence object. The script below will be used to […]

Written by Ian

The most important reason why you should know how to use PL/SQL Conditional Compilation.

….is that when you really need to, it allows you to turn up your applications instrumentation  to 11. A 30 second PL/SQL Conditional Compilation overview PL/SQL Conditional compilation was introduced with Oracle Database 10gR2. The official documentation  explains how to use the Conditional Compilation constructs but doesn’t give too many use cases. Fortunately the Oracle White Paper […]

Written by Ian

An introduction to Application Context

The inspiration for this article came from reading Mark Hoxey excellent post on avoiding ORA-04068: existing state of packages has been discarded One of the potential solutions to this problem suggested by Mark is to use Application Context. I didn’t know too much about Application Context so this post is my way of documenting and sharing what […]