SQL & PL/SQL
Submitted by gojko on Sun, 2006-09-24 19:21
A common practice for handling errors in PL/SQL procedures is to catch all errors in the top-most database layer and convert them into error codes and human readable messages for client applications. This technique is a relict from the past and, in fact, a very bad practice from today's perspective, since it can lead to data corruption.
Submitted by Kevin Meade on Fri, 2006-09-15 01:53
Recently a friend asked me a simple question; "How do I display the results of a REFCURSOR through SQL*Plus?". The answer is of course is just as simple as the question, you "SELECT" it like anything else. He just had not seen the syntax before. Being me though, I wouldn't let him get away with such an easy offing, so I sent him instead a quick write-up on REFCURSORS and, the just as interesting, CURSOR EXPRESSIONS. This write-up shows various coding samples of how to use these to great effect. He liked it. He said it should be written up somewhere, and I knew just where. Hope you like it too. If you do, please add something in a reply, especially if you have a better or unique or interesting way of exploiting these features. I'd like to read about it, maybe even steal your code...
Submitted by kimant on Mon, 2006-08-28 03:20
Recently, I have been helping a company in redesigning their schema, and one issue was that they stored hieracal trees in varchar2(4000) column, one full branch per row.
Data was if this kind:
Their main problem was that searching in this was really ugly, as they had to full table/index scan a lot, when searching using like '%%'
I came up with this solution to convert this into a small table that can be queried using connect by instead:
CREATE OR REPLACE TYPE node_parent_type AS OBJECT ( lvl NUMBER, node VARCHAR2(200), PARENT VARCHAR2(200) )
Submitted by Kevin Meade on Thu, 2006-05-18 00:37
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.
Submitted by Kevin Meade on Tue, 2006-04-25 19:00
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.
Submitted by Bert Scalzo on Tue, 2006-02-28 07:27
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.
Submitted by Marcin Miller on Wed, 2006-02-01 18:00
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?
Submitted by Natalka Roshak on Sun, 2005-11-06 18:00
Everyone knows the basic features of sql*plus, but one underused feature that can make your scripts an order of magnitude more useful is the ability to store and reuse values, including values read from the database, in variables. This lets you use user-defined and database values not just in subsequent queries, but also in calls to other scripts and SQL*Plus's other functionality.
Submitted by Natalka Roshak on Sun, 2005-10-09 19:00
One of the primary tests for DBMS reliability is what's known as the ACID test. ACID-compliant systems are as close as you can get to guaranteed not to lose your data. Essentially, as long as your database files are intact, you are guaranteed that your data is consistent. This is not true for non-ACID compliant systems. Non-ACID-compliant systems are vulnerable to data inconsistency, and generally aren't taken seriously for any application where data integrity is important. Now, in 10gR2, Oracle offers us the option to break its ACID compliance.
Submitted by James Koopmann on Sun, 2005-10-02 16:00
There are always a few topics in regards to writing SQL that always seem to come up more often than others. Querying data with case insensitivity is one of those topics. And Oracle has addressed this issue in many of their release. But in Oracle 10gR2 they have reached new levels. This article takes a dive into case insensitivity and how it is now handled in 10gR2. For the better!