Search

Top 60 Oracle Blogs

Recent comments

Parameters

PL/SQL – Don’t mix and match scope

Here’s a simple little PL/SQL block where we call an inner procedure PARAMETER_TESTER from its parent block. Pay particular attention to the parameter we pass to the procedure, and it’s value throughout the execution of that procedure.

Preventing PL/SQL name clashes. You learn something new every day!

glasses-272399_1280-smallI mentioned in yesterday’s Oracle Midlands post, Bryn had an example of some syntax I had not seen in 20+ years of PL/SQL development.

You tend to name PL/SQL parameters and variables in such a way as to prevent name clashes with table columns. Let’s cause an obvious name clash…

The table DUAL has a column called DUMMY with a single row with the value ‘X’.

12c New Optimizer Features

Besides the officially available information about new optimizer features in 12c it is always a good idea to have a look at the internal optimizer parameters that show what features are enabled when running with OPTIMIZER_FEATURES_ENABLE = 12.1.0.1. Here is the list of internal optimizer parameters and fix controls that are different between 11.2.0.4 and 12.1.0.1:

Optimizer parameters:

#eeeeee; border: 1px dashed rgb(204, 204, 204); overflow: auto;">_optimizer_partial_join_eval           partial join evaluation parameter                            
_optimizer_unnest_scalar_sq            enables unnesting of of scalar subquery                     
_optimizer_ansi_join_lateral_enhance   optimization of left/full ansi-joins and lateral views      
_optimizer_multi_table_outerjoin       allows multiple tables on the left of outerjoin