Oracle VM Consulting
view counter

PL/SQL Feed

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

OEM Stop and Start

Somebody posed a question about starting and stopping the database from within the OEM tool as the SYSTEM rather than the SYS user. There’s actually a simple trick to do that, you connect to OEM as the SYSTEM user but when you startup or shutdown the database, you reconnect for those tasks as the SYS user.

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

view counter