Oracle VM Consulting
view counter

PL/SQL Feed

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

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):

Verifying a Perl Socket

Using a lowercase hostname is typical but I got sloppy on a Windows 7 installation, after all Windows is case insensitive, and I used a mixed case hostname. It raised an interesting error when installing Oracle Database 11g Release 2.

Repeat until understood


Click the SQL for scripts

MySQL Workbench Scripts

It’s always interesting when somebody asks why they got an error message, and especially sweet when you’re working on something related that lets you answer the question. They were using MySQL Workbench and wanted to know why they couldn’t open a SQL script file by clicking on the Scripting menu option.

view counter