Blog Archives

Oracle 11g SQL: Named & Mixed Notation for PL/SQL in SQL Statements

In programming language theory, positional notation refers to matching the order of formal parameters and actual parameters.  Whereas named notation refers to matching the formal parameters and actual parameters explicitly usually (not necessarily) by some operator (in PL/SQL this operator is =>). Finally mixed notation refers using both together as a single notation.

Although there was no restriction on using any of those notations within PL/SQL blocks, prior to 11g named & mixed notations for PL/SQL subroutines were not allowed in SQL statements (Code Piece 1 and Code Piece 2). This is one of the most restrictive issues about SQL for developers. Luckily 11g solves that issue and now it is allowed to use named and mixed notations also in SQL statements.

Read rest of the paper…

Oracle 11g PL/SQL: Allow Sequences in PL/SQL Expressions

Yet Another Optimization or a Compiler Trick?

In Oracle 11g, Oracle finally allows PL/SQL developers to use sequences in PL/SQL expressions. Prior to that version developers should fetch the nextval or currval of a sequence by using a select … dual type of statements. By 11g you don’t need this anymore. You can simply refer to sequences anywhere you want within a PL/SQL expression.

In this paper you will find out basic usage example and whether this new syntax is a new PL/SQL optimization removing dual access necessity or it is only plastics.

Read the rest of paper…

Oracle 11g RMAN: BZIP2 vs. ZLIB

Prior to 11g Oracle RMAN had a single compression algorithm, called BZIP2. The algorithm has a very satisfactory compression ratio in terms of decreasing the size of RMAN output. However, high CPU cost makes algorithm not suitable for many sites especially for sites having CPU bottleneck (Data warehouse DBAs?!?:)). As a result people still use hardware compression capabilities of tape drivers (ratios like 1:3) to decrease the backup time and increase the effective write speed of backup drivers. By 11g Oracle introduces a new compression algorithm that is announced to be less compressive but less aggressive in terms of CPU. In this paper you will find comparison of two algorithms with no compressed case.

Read the rest of paper…