Oracle VM Consulting
view counter

PL/SQL Feed

Oracle PL/SQL (Procedural Language/Structured Query Language) resources, news, and support articles.

Transactional DDL


Click the SQL for scripts

Iterative UPDATE with SKIP LOCKED option

Many times occur that we can not execute an UPDATE or DELETE statement, because a couple of rows are locked by an other session(s).
We can generate – and handle – exceptions, but this solution sometimes not enough.
Using the SKIP LOCKED clause – officially introduced in Oracle 11g- we can try to solve the problem incrementally.
Let’s suppose that other sessions sooner or later will finish their transactions. During their transactions we can modify the “free” (not locked) rows with this option.
Let’s see a simple case study:

PL/SQL Challenge Update: Roundtable discussion on naming conventions

The second Roundtable discussion at the PL/SQL Challenge focuses on an issue that every developer grapples with: how to name our identifiers. That is, what are our naming conventions?

There have been many approaches to creating names, including CamelCase and Hungarian notation.

A longtime PL/SQL Challenge player, John Hall, offers a very different approach:

Roundtable Discussion: Identifier Naming Conventions

Our second Roundtable discussion focuses on an issue that every developer grapples with: how to name our identifiers. That is, what are our naming conventions?

There have been many approaches to creating names, including CamelCase and Hungarian notation.

A longtime PL/SQL Challenge player, John Hall, offers a very different approach:

Dynamic SQL with FOR UPDATE clause

Many times we can hear that the Native Dynamic SQL can not be implemented with FOR UPDATE clause in a PL/SQL program.

In this article I will proof that we can associate a dynamic SQL statement with a weak ref cursor variable using FOR UPDATE clause.

Let us consider the following – simple- case study:
We would like to modify the salary column of an employees’s like table
(This means that the structure of the table is identical with HR’s employees table):
We increase the salary for those employees whose salary is less than a particular value.

DDL Dangers


Click the SQL for scripts

SQL Developer batch file?

It’s amazing to me that there is yet another outcome from installing Oracle Database 11g on Windows 7. This one installs without an error but then raises the following error message dialog when you try to launch SQL Developer from the menu. Naturally, it seems to depend on having more than one Java JVM installed when you run the Oracle Universal Installer (OUI) without setting the %JAVA_HOME% environment variable.

For some things you have to get ANSI


Click the SQL for scripts

What’s ORACLE_UNQNAME?

It started with a three part question: What’s the URL to run the DB Console for Oracle Database 11gR2 on Windows 7, and what’s the ORACLE_UNQNAME and why isn’t it defined by the installation. The first part is easy (shown further below), but the second and third parts were more involved.

The ORACLE_UNQNAME is an operating system environment variable that holds the database’s unique name value. You can find it with the following query as the SYSTEM user (through SQL*Plus):

view counter