Top 60 Oracle Blogs

Recent comments

October 2009

How Can I unload data to a flat file

Many times we are asked

  • "Does anyone know an easy way of dumping the data from an Oracle table into a delimited(comma, tab etc) ascii file?"
  • "Does anyone know an easy way to unload data in a format for sqlldr to reload later?"

Well here is a way to do it into Excel or any spreadsheet that understands the industry standard SYLK file format.

Here is a pro*c program that does it to a flat file very fast.

and here is a PLSQL routine that uses utl_file to do the same

A reader (Andy Rivenes) offers this more "robust" plsql implementation based on the original code.

And lastly, SQLPlus can do this quite easily but it is a pain to have to write a script/table. What I've done is setup scripts for UNIX and NT that allow you from the command line to execute things like:

$ sqlldr_exp scott/tiger dept

How Can I find out who called me or what my name is

Many times we are asked "in a procedure/function, can I find out who called me" or "can I dynamically figure out the name of the procedure or package that is currently executing".

You can find it in the call stack returned by dbms_utility.format_call_stack. I wrote a small routine called who_called_me that returns this sort of information (it doesn't tell you who you are, it lets you know who called you). If you wrap who_called_me with a function who_am_i, you'll get what you need. If you create the who_called_me/who_am_i routines, you'll be able to:

SQL> create or replace procedure demo
2 as
3 begin
4 dbms_output.put_line( who_am_i );
5 end;
6 /

Procedure created.

SQL> exec demo;

In current releases of the database, this code has been incorporated into the OWA_UTIL package - you probably already have it in your database. If not, you can use this really old version that might need a tweak or two to work in your database release:

create or replace procedure who_called_me( owner out varchar2,
name out varchar2,
lineno out number,
caller_t out varchar2 )
call_stack varchar2(4096) default dbms_utility.format_call_stack;
n number;
found_stack BOOLEAN default FALSE;
line varchar2(255);
cnt number := 0;
n := instr( call_stack, chr(10) );
exit when ( cnt = 3 or n is NULL or n = 0 );
line := substr( call_stack, 1, n-1 );
call_stack := substr( call_stack, n+1 );
if ( NOT found_stack ) then

Unindexed Foreign Keys

Having Unindexed foreign keys can be a performance issue. There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.

To read about this issue, please see the Concepts Guide the section on Maintaining Data Integrity/Concurrency Control, Indexes, and Foreign Keys.

The second issue has to do with performance in general of a parent child relationship. Consider that if you have an on delete cascade and have not indexed the child table (eg: EMP is child of DEPT. Delete deptno = 10 should cascade to EMP. If deptno in emp is not indexed -- full table scan). This full scan is probably undesirable and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.

Also consider that for most (not all, most) parent child relationships, we query the objects from the 'master' table to the 'detail' table. The glaring exception to this is a code table (short code to long description). For master/detail relationships, if you do not index the foreign key, a full scan of the child table will result.

So, how do you easily discover if you have unindexed foreign keys in your schema? This script can help. When you run it, it will generate a report such as:

SQL> @unindex

---- ------------------------------ -------------------- --------------------

Base Conversion Routines

This set of routines is useful to convert between various 'bases' in Oracle. Once you install these functions, you will be able to perform operations such as:

SQL> select to_bin( 123 ) bin, to_hex( 123 ) hex, to_oct( 123 ) oct from dual
2 /

--------------- --------------- ---------------
1111011 7B 173

SQL> select to_dec( '1111011', 2 ) base2, to_dec( '7B' ) base16,
2 to_dec('173',8) base8
3 from dual
4 /

---------- ---------- ----------
123 123 123

Of course, with current releases - you would never user TO_HEX since TO_CHAR already does this (faster). Also TO_NUMBER would be used to convert HEX to decimal these days as well

ops$tkyte%ORA10GR2> select to_char( '1234', 'XXXX' ) from dual;


Here is the original code:

create or replace function to_base( p_dec in number, p_base in number )
return varchar2
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(16) default '0123456789ABCDEF';
if ( p_dec is null or p_base is null )
return null;
end if;
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
end if;
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_base;

create or replace function to_dec
( p_str in varchar2,
p_from_base in number default 16 ) return number


Generates needed package and triggers to support update cascade
in Oracle without removing or infringing on DECLARITIVE RI.

This package supports:

Cary on Joel on SSD

Joel Spolsky's article on Solid State Disks is a great example of a type of problem my career is dedicated to helping people avoid. Here's what Joel did:

  1. He identified a task needing performance improvement: "compiling is too slow."
  2. He hypothesized that converting from spinning rust disk drives (thanks mwf) to solid state, flash hard drives would improve performance of compiling. (Note here that Joel stated that his "goal was to try spending money, which is plentiful, before [he] spent developer time, which is scarce.")
  3. So he spent some money (which is, um, plentiful) and some of his own time (which is apparently less scarce than that of his developers) replacing a couple of hard drives with SSD. If you follow his Twitter stream, you can see that he started on it 3/25 12:15p and wrote about having finished at 3/27 2:52p.
  4. He was pleased with how much faster the machines were in general, but he was disappointed that his compile times underwent no material performance improvement.

Here's where Method R could have helped. Had he profiled his compile times to see where the time was being spent, he would have known before the upgrade that SSD was not going to improve response time. Given his results, his profile for compiling must have looked like this:

100%  Not disk I/O
0% Disk I/O
---- ------------

Profiling with my Boy

We have an article online called "Can you explain Method R so even my boss could understand it?" Today I'm going to raise the stakes, because yesterday I think I explained Method R so that an eleven year-old could understand it.

Yesterday I took my 11 year-old son Alex to lunch. I talked him into eating at one of my favorite restaurants, called Mercado Juarez, over in Irving, so it was a half hour in the car together, just getting over there. It was a big day for the two of us because we were very excited about the new June 17 iPhone OS 3.0 release. I told him about some of the things I've learned about it on the Internet over the past couple of weeks. One subject in particular that we were both interested in was performance. He likes not having to wait for click results just as much as I do.

According to Apple, the new iPhone OS 3.0 software has some important code paths in it that are 3× faster. Then, upgrading to the new iPhone 3G S hardware is supposed to yield yet another 3× performance improvement for some code paths. It's what Philip Schiller talks about at 1:42:00 in the WWDC 2009 keynote video. Very exciting.

Alex of course, like many of us, wants to interpret "3× faster" as "everything I do is going to be 3× faster." As in everything that took 10 seconds yesterday will take 3 seconds tomorrow. It's a nice dream. But it's not what seeing a benchmark run 3× faster means. So we talked about it.

Alive and Prutsing

As you might have noticed I haven’t been exactly busy posting new stories on my blog. The obvious reason for this is that I have been way too busy doing other things such as prutsing with Oracle11g Release 2. Two weeks ago I taught my first NF11g class, which includes all major new release 2 [...]

Oracle OpenWorld Unconference

One of the less “traditional” tracks at Oracle OpenWorld is the Oracle Unconference. The Unconference allows for a much more informal setting for sessions and generally is more interactive and audience driven compared to the usually amounts of PowerPoint sessions contain which can cause eyes to bleed. This year I’ve signed up to [...]

Cost and value

I just read a post by Seth Godin entitled "If Craigslist cost $1". I've used Craigslist and I agree with him that even a small charge for the service would likely clean it up a whole lot. At $1 per listing, most people with a legitimate need would still choose to use the service. But, for scammers and those with not so virtuous purposes, the small charge and thus the requirement for verifiable identification for the money exchange, would push them out of the game. Plus, the money coming in for using the service could go towards making the service better or making the owners richer or be used for philanthropic purposes or whatever.