Sequences in PL/SQL Expressions

With oracle 11g you can finally use sequences directly in any pl/sql expression wherever a number expression can be used.

You no longer need to select from dual

Much cleaner than the pre 11g synax:



Posted in Oracle, SQL Developer Tagged with: , ,

SQL Developer does not remember location of Java executable

A few week ago I got myself new pc.

After re-intalling al my needed apps I got stuck with SQL developer asking me to point the location of the Java executable each time I started SQL developer. I was quite clueless as to why this was happening. There were some many factors that changed in respect to my new pc.

Finally I figured out that the problem was caused by Windows 8 security settings. I would normally put my SQL developer on a secondary hard-drive (d:). As I got myself a new SSD boot drive I chose to install SQL developer on the this drive (c:) as it significantly improves the start-up time of SQL developer. As I like to keep my boot drive nice and clean I chose to install SQL developer in the same location as Oracle Virtualbox. This made perfect sense. All Oracle client software in C:\program files\Oracle.

And that’s the problem. When SQL developer starts the first time it will prompt for the location of the Java executable (I got a 64 bit version, so I need a separate Java JRE/JDK) and tries to write that into a properties file in the installation directory. Windows doesn’t like programs writing data in the program files directory for security reasons. It expects programs to play nicely and write in user settings.

So the simple solution is to start your SQL developer once with the  “run as administrator” option. When run as administrator it does allow to write the location to the properties file and thus the next time you start SQL developer normally it won’t prompt for the location again.


Posted in Oracle, SQL Developer Tagged with:

CONTINUE Statement

The CONTINUE statement exits the current iteration of a loop and transfers control to the next iteration (in contrast with the EXIT statement, which exits a loop and transfers control to the end of the loop). The CONTINUE statement has two forms

  • CONTINUE; simply exits the iteration and transfers control to the next iteration
  • CONTINUE WHEN; conditionally exits the loop and transfers control to the next iteration

Read more ›

Posted in Oracle, Pl/Sql Tagged with: , ,

SIMPLE Data Types

Oracle introduced new datatype in release 1 of 11g. The simple stuf!


These simple datatypes have some extra restrictions, but if in your case these aren’t important they make up for it with a huge performance gain when compiled native. Steven Feuerstein reports 300% speeds gains when compiled native. When compiled interpreted the performance gain is far less, but still 6%.

SIMPLE type compared to their basetype

  • Same range as basetype
  • But added NOT NULL constraint
  • SIMPLE_INTEGER also has no overflow error

Advantages compared to basetype

  • Much faster when compiled native (PLSQL_CODE_TYPE=’NATIVE’)
  • Little faster when run interpreted (PLSQL_CODE_TYPE=’INTERPRETED’)

When should you use these datatypes?

  • You have some heavy arithmetic and your are sure you’re values will never be null
  • And in case of SIMPLE_INTEGER your values will never be out of range
  • (preferably when you run native code, which will bring you the most performance gain)

Posted in Pl/Sql Tagged with: , ,

Dynamic Wrap

The 2nd release of Oracle 10g added 2 new modules to the DMBS_DLL package.

  • WRAP; function returning an obfuscated version of your code
  • CREATE_WRAPPED; procedure compiling an obfuscated version of the code into the database

Both modules are overloaded to accept VARCHAR2, DBMS_SQL.VARCHAR2S & DBMS_SQL.VARCHAR2A as input

dbms_ddl.wrap example

dbms_ddl.create_wrapped example

Posted in Pl/Sql Tagged with: , ,

Automatic PL/SQL optimization

As of 10g Oracle introduced automatic PL/SQL optimization. Which has 3 settings:

PLSQL_OPTIMIZE_LEVEL=0 Optimization is disabled which might be in order if you find the optimization alters your programs behaviour
PLSQL_OPTIMIZE_LEVEL=1 Optimization will perform some optimizations, but will not change the order of your code
PLSQL_OPTIMIZE_LEVEL=2 Optimization will perform all possible optimizations. This is the DEFAULT setting

You might find reasons to reduce the optimization level if needed. Changing the optimization level is done via an alter statement:
alter session set PLSQL_OPTIMIZE_LEVEL = 1;

The optimization is achieved trough taking advantage of

  • changing the order of interpretation of code
  • rewriting code to a more optimal form
  • eliminate unreachable code
  • short circuit code

To view the level used for your code

NAME                           PLSQL_OPTIMIZE_LEVEL
------------------------------ ----------------------
PLSQLWEB                       2
DIV_SLIDE_INDEX                2  

To read in-depth explanation of all the methods the optimizer utilizes to achieve it’s optimization read the whitepapers on the Oracle 10g website

It doesn’t hurt to review your USER_PLSQL_OBJECT_SETTINGS for code that is compiled using PLSQL_OPTIMIZE_LEVEL <2. If you find some that are not intentional then there is a performance gain waiting for you.

Posted in Pl/Sql Tagged with: , ,

Enhancements to PL/SQL Native Compilation

This topic is rather scarcely explained. Simply because it’s part of a much larger topic of native compilation. So the only information here is what has been changed. Showing examples here would be to comprehensive.

With Oracle 10g the configuration of initialization parameters and the command setup for native compilation has been simplified.

  • The only required parameter is PLSQL_NATIVE_LIBRARY_DIR. The parameters related to the compiler, linker, and make utility have been obsoleted.
  • Native compilation is turned on and off by a separate initialization parameter, PLSQL_CODE_TYPE, rather than being one of several options in the PLSQL_COMPILER_FLAGS parameter, which is now deprecated.
  • The $ORACLE_HOME/plsql/spnc_commands file contains the commands and options for compiling and linking, rather than a makefile. The spnc_commands file.
  • A new script, dbmsupgnv.sql, has been provided to recompile all the PL/SQL modules in a database as NATIVE.
  • The dbmsupgin.sql script recompiles all the PL/SQL modules in a database as INTERPRETED.
  • A package body and its specification do not need to be compiled with the same setting for native compilation.
  • Natively compiled subprograms are stored in the database, and the corresponding shared libraries are extracted automatically as needed.
  • Any errors that occur during native compilation are reflected in the USER_ERRORS dictionary view and by the SQL*Plus command SHOW ERRORS.

Posted in Pl/Sql Tagged with: , ,

FORALL Support for Non-Consecutive Indexes

Prior to 10g any collection used in  a FORALL needed to be dense. Which forced you to write extra code to compress a sparse collection or worse skip the use of FORALL. Oracle introduced 2 new clauses which will help you overcome this limitation of FORALL.

  • INDICES OF; allows you to specify a collection which defined elements’ index point out the indexes of the collection being process by FORALL
  • VALUES OF; allows you to specify a collection of integers which values “point” to the indexes of the collection being processed by FORALL


Within the INDICES OF example a boolean collection was used. However it really doesn’t matter what type is used or what values is inside the element. All that matters is that the element (index) is defined. It merely uses that fact l_list_indices(N) exist. The actual value of N is not relevant.

VALUES OF example

Within the VALUES of example it’s the other way around. All that matters is the value of the element. It really doesn’t matter in which element(index) the value is defined. Al it uses is the values. Basically Oracle performs l_list(l_list_values(n)).

Posted in Pl/Sql Tagged with: , ,

New IEEE Floating-Point Types

With release 10 oracle introduced 2 new BINARY datatypes based on the IEEE-754 floating point standard: BINARY_FLOAT and BINARY_DOUBLE. They are available in both PL/SQL as well as in the database.

These new datatypes can offer high performance calculus, which might proof useful when writing heavy mathematical applications. The performance improvement depend heavily on your hardware and/or the computations involved.

An other reason for using these datatype might me simply that your are forced to conform to IEEE-754 standards. Which might proof usefull in communicating with other IEEE-754 applications and languages.

Use suffixes to represent IEEE types (f for float, d for double)

l_bf      binary_float := 1.23f;
l_bd      binary_double := 1.23d;

Special literals for assigning “Not a Number” and infinity

l_bf_nan  binary_float := binary_float_nan;         --assign NAN value
l_bd_nan  binary_double := binary_double_nan;
l_bf_inf  binary_float := binary_float_infinity;    --assign infinity
l_bd_inf  binary_double := binary_double_infinity;

These last are accompanied by 2 new predicates to test for these circumstances

  • IS [NOT] NAN; allows you to test whether a value is or is not a number
  • IS [NOT] INFINITE; allows you to test whether a value is or is not infinite

A small demonstration of using IEEE datatypes

Test for not a number
l_df_nan is not a number
Test for infinity
l_df_inf is infinite
Be careful mixing floats and double
a difference in representation make these not equal
l_bf = 2.34500006E-001
l_bd = 2.3449999999999999E-001

Be aware that the way BINARY datatypes represent fractional information makes them less suitable for representing money values. So if your writing financial applications make sure you use a NUMBER (sub)type.

Posted in Pl/Sql Tagged with: , ,

Nested Table Enhancements

Prior to Oracle 10g tasks like comparing 2 nested tables for equality were code intensive and had to be performed at element level. With 10g new functionality was introduced that will finally treat nested tables as the sets they are:

  • compare nested tables for equality
  • test whether an element is a member of a nested table
  • test whether one nested table is a subset of another
  • perform set operations such as union and intersection

The table displayed contains a complete overview of all operators.

Operator Description Syntax Example
= True if two nested tables are exactly equal (number of elements, datatype and/or values). IF my_list = my_other_list THEN…
<>, != True if two nested tables are not exactly equal (difference in number of elements, datatype and/or values). IF my_list != my_other_list THEN…
ntt IS [NOT] A SET True if all elements in a nested table are unique values answer := my_list IS A SET
ntt IS [NOT] EMPTY True if the nested table is empty (has no elements) answer := my_list IS EMPTY;
expr [NOT] MEMBER [OF] ntt True if a value exists within any of the elements of a nested table answer := ‘book’ MEMBER OF my_list;
ntt1 [NOT] SUBMULTISET [OF] ntt2 true if all elements of ntt1 are at least in ntt2. Ntt2 can have more elements that do not exist in ntt1. answer := my_list SUBMULTISET your_list;
ntt1 [NOT] in (ntt2,ntt3,…) Test if ntt1 is equal to any of the nested tables in the list between ( ). answer := my_list IN (his_list, her_list)
nnt1 MULTISET EXCEPT [DISTINCT] ntt2 Performs a minus between 2 nested tables just like a table minus in SQL. Adding the DISTINCT keyword flushes out any remaining duplicates in the result set. my_list := my_list MULTISET EXCEPT my_other_list
ntt1 MULTISET INTERSECT [DISTINCT] ntt2 Performs an intersect on 2 nested tables just like an intersect in SQL. Adding the DISTINCT keyword flushes out any remaining duplicates in the result set. my_list := my_list MULTISET INTERSECT my_other_list
ntt1 MULTISET UNION [DISTINCT] ntt2 Performs a union between 2 nested tables just like a table union in SQL. Adding the DISTINCT keyword performs like a union all in SQL. my_list := my_list MULTISET UNION my_other_list;
SET(ntt) Returns a set with only the distinct values of the original set (basically a select distinct) my_list := SET(my_other_list);

NOTE: Since Oracle does not retain ordering and subscripts when a nested table is stored in / retrieved from  the database. All comparison between nested tables behave according to this. The order of elements and subscript are always ignored when comparison are made. So list(1,2,3) is equal to list(3,2,1)

Equality Example

NB: The last comparison shows that, as usual, a comparison with NULL always lead to “not equal”

Adding/Substracting Example

Checking the elements example

Posted in Pl/Sql