PL/SQL is an excellent language for Oracle. Integrated with the database, highly useful extensions to SQL, extremely powerful when exploiting Oracle Objects, and in spite of all this, it is still way easy to learn. But like any procedural programming language, one can get lazy with it, tending to accept the first solution arrived at. Looping constructs in particular seem to be used as crutches rather than necessary components of a solution. Bad looping causing performance issues is a problem that liters the PL/SQL landscape. But it is easy to spot and fix.
In 10gR1, Oracle introduced the Scheduler, a new way to schedule jobs to run from within Oracle. The old DBMS_JOB mechanism is still there, but the Scheduler has several advantages over its predecessor. This article will take a quick look at those advantages and discuss transitioning from DBMS_JOB/ DBA_JOBS to the Scheduler. My next article will take a more in-depth look at some of the Scheduler's features.
OUTER-JOIN is a very handy feature of SQL. But its value at solving certain classes of SQL query problems aside, it is also one of the most error prone Oracle SQL extensions we can put to use. Even advanced developers can make these mistakes. So let us discuss the error prone nature of this feature, and how to fix it.
This article discusses the various types of primary keys and list the advantages and disadvantages they provide.
If you've ever gotten a phone call from an annoyed user whose transaction just won't go through, or from a developer who can't understand why her application sessions are blocking each other, you know how useful it can be to identify not just whose lock is doing the blocking, but what object is locked. Even better, you can identify the exact row that a session is waiting to lock.
External Tables let you query data in a flat file as though the file were an Oracle table. In 9i, only read operations were permitted; in 10g, you can also write out data to an external table, although you can't write to an existing table.
PL/SQL is a great language. It's relatively simple to learn, is well integrated with the Oracle database, and can often be the most efficient way to perform complex or large scale database operations. In fact PL/SQL is so useful, it's difficult to believe that its origin is SQL*Forms -- and that PL/SQL was once was an optional cost add-on to the database. Working with Oracle over the past few decades has come a long way, and PL/SQL had evolved into a mature, robust and highly functional database language.
There have been two high-profile Oracle security flaws in the last few months. The first, which everyone reading this article has probably heard of, is the Voyager worm. The second, which is slightly less well-known, is a very severe security hole that lets anyone with a valid logon to an Oracle database -- including an unprivileged account with nothing but CONNECT privs -- execute arbitrary code as SYS. In this article, I'll look at the two security flaws and outline the steps you need to take to protect your databases from them.
Top-n Query is one of the more advanced SQL problems. How does one retrieve N first (or least) rows from a record set? For example, how does one find the top five highest-paid employees in a given department?
10gR1 revamped Oracle clustered database management and features. 10gR2 builds on this success with a long list of improvements and enhancements. Oracle has streamlined the installation process and provided more filesystem options, made some performance and monitoring improvements, and improved manageability with a half-dozen administration enhancements. This article will take a look at the major changes.