News, views, tips and tricks on Oracle and other fun stuff

Not a Fan of Public Synonyms, Here is Why ↗

Tom is not not a fan of public synonyms, here is why:

  • public synonyms pollute the namespace.
  • public synonyms can lead to security issues.
  • public synonyms can lead to a maintenance headache.
  • public synonyms are public – no one owns them.

So, instead of public synonyms… Continue reading…

2 Comments | Filed in Oracle | Tags:

SQL Injection Attacks Up 69%, Here is How to Protect Yourself

Emil Protalinski:

SQL injection attacks are becoming significantly more popular amongst hackers, according to recent data. Between Q1 2012 and Q2 2012, there has been an estimated 69 percent increase of this attack type.

SQL injection can be easily avoided. How? Just use bind variables. Here is a quote from Tom Kyte:

If you use bind variables, you cannot be SQL Injected – this is true for PL/SQL, for Java, for any and all languages. If you use bind variables you cannot be SQL Injected – period. It is that simple, really and truly.

Tom also links to an excellent paper on this subject written by Bryn Llewellyn: How to write SQL injection proof PL/SQL [PDF].

It baffles me how such a simple and easy solution to such a big security problem is not implemented in the code base of all of these big companies that have recently been hacked.

Comments Off | Filed in Oracle, Security | Tags:

Is ANSI SQL Join Syntax Your Default Approach to Writing Queries in Oracle? ↗


I think if Oracle had extended its ANSI SQL support beyond SELECT to the other DML commands the argument over whether to use the new syntax would have died out years ago.

It would have helped for sure. I also think that if you work in an environment where ANSI SQL is not the “standard” it is hard to adopt it as your main approach to writing queries.

Comments Off | Filed in Oracle | Tags: ,

Another Reason Why You Should Upgrade Your Oracle Client Software ↗


Apparently somewhere around the version 10 of sqlplus they changed the autotrace command to use DBMS_XPLAN.DISPLAY to show the plan of a query run with set autotrace on. But, I’ve never taken advantage of this feature because I’m using a 9.2 version of the Oracle client on my laptop.
Comments Off | Filed in Oracle | Tags:

See How Easily You Can Improve Performance by Using These Five Data Caching Techniques

I will be presenting a free Webinar, brought to you by ODTUG, on Tuesday, July 17, 9:00 AM – 10:00 AM PDT. I will be talking about and demonstrating SQL and PL/SQL caching techniques that will considerably improve the performance of your queries and programs. The Webinar will cover the following five features:

  • SQL Query Result Cache
  • PL/SQL Function Result Cache
  • Package-Based Cache
  • Deterministic Function Cache
  • Scalar Subquery Cache

Click here to register.

The webinar went very well. I thank all of you who attended and ODTUG for hosting it.
Click here to download a zip file containing the following files:

  • Power Point presentation
  • Corresponding white paper
  • All the demo scripts
3 Comments | Filed in Oracle | Tags: , ,

Row versus Set Processing, Surprise! ↗

Craig Shallahamer:

1. Set based processing will likely be much faster than row based processing. Our experiment of processing 100K rows showed row based processing was 3700 times slower than set based processing. Not twice as slower or even 10 times slower… 3700 times slower!

2. Compared to set based processing, row based processing times degrade much quicker than set based processing. That is, row based processing does not scale nearly as well as set based processing. We saw this in that the linear trend line for row based processing was 0.00259 compared to 0.00000 for set based processing.
Comments Off | Filed in Oracle | Tags: ,

Never Ever Do That ↗

ALTER TABLE sys.dual ADD (dummy2 VARCHAR2(1));

Tom Kyte:

You never, never never never, as in never ever – touch any of the SYS tables – not to insert into them, not to delete from them, not to update them and especially not to alter them.
Comments Off | Filed in Oracle, Tips | Tags:

Why You Must Use an ORDER BY Sometimes

Suresh submitted this question on the Oracle Community forum:

in oracle 10 g , the below query results in alphabetical order , there is no order by caluse. but when i am running the same query in oracle 11g , it gives me in random order. what is the problem here?

People, it is really simple: You want an ordered result set? use ORDER BY.

Tom Kyte wrote about it in this blog post and in his books:

Do not ever count on the order of rows from a query unless you have an ORDER BY statement on your query!

The only way to retrieve data from the database in some sorted order is to include an ORDER BY on your query. There is no substitute for ORDER BY.

By the way, the query in the forum post looks suspiciously Hibernated. Don’t let Jeff know :)

Comments Off | Filed in Oracle | Tags:

User Exceptions Gotcha, When an Error Appears as a Success

This is a guest post by David Clement, a veteran database engineer, a coworker and a friend of mine. You can find him online at

Here is an interesting feature that’s worth knowing about because of the potential for strange bugs. When you run a SQL script in the Unix shell, if you use a user-defined exception, you have to take care to prevent failure and success from returning the same result.

The following code snippet shows a normal way to return an error code to the Unix shell from a SQL script. I saved this as return_error_code.sql:

Continue reading…

3 Comments | Filed in Oracle, Tips | Tags: , , , , ,

Neat Oracle Database 11g Release 2 Feature: Preprocessing External Tables ↗

Arup Nanda:

External tables enable users to access data in text files, immediately eliminating the need to load input text files to intermediate tables for processing—saving both time and storage space. Now, with Oracle Database 11g Release 2, intermediate processing of any kind—such as decompression of compressed input files—is eliminated, further saving time and storage, not to mention a change to the existing code.

But the power of inline preprocessing is not limited to decompression alone. It can be applied to any type of prior processing required, as long as it produces an output that can be parsed by the external table. The directory listing is just one small demonstration of this rich capability of preprocessing in external tables. You can also use it to massage datafiles to fit a specific format, append or augment data from multiple sources dynamically—without creating any intermediate storage—or even send an e-mail when a specific text file is accessed by an external table. What you can do with inline preprocessing is limited only by your imagination.

Greg Rahn:

Before External Tables existed in the Oracle database, loading from flat files was done via SQL*Loader. One option that some used was to have a compressed text file and load it with SQL*Loader via a named pipe. This allowed one not to have to extract the file, which could be several times the size of the compressed file. As of, a similar feature is now available for External Tables (and will be in This enhancement is a result of Bug 6522622 which is mentioned in the Bugs fixed in the Patch Set note.

1 Comment | Filed in Links, Oracle | Tags: ,