tag:blogger.com,1999:blog-129508202012-01-26T05:10:15.031-07:00Helpful ideas and solutions for the Oracle application enthusiast. Check out the <a href="http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html">archives</a>Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.comBlogger1281100tag:blogger.com,1999:blog-12950820.post-27813460580632592162010-09-07T10:17:00.003-06:002010-09-07T10:22:45.105-06:00

Having chosen Oracle SQL Developer as your preferred Oracle database tool, do you have to install and learn a new technology for supporting your MS SQL databases? Nope! It's easy to connect SQL Developer to MS SQL databases, and I'll show you how.BackgroundFor years I worked in technical support for software vendors, and I never knew what client tool would be available when I accessed a

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com12tag:blogger.com,1999:blog-12950820.post-85719662454858791442010-08-11T13:35:00.002-06:002010-08-11T13:43:27.479-06:00

A colleague asked me some questions about FIRST_ROWS and ALL_ROWS, but I'm hesitant to blog about it because it's already been done so well by others -- the best example would probably be Sachin Arora.Nevertheless, it never hurts to lend another voice to the Oracle choir, so here's everything I know on the topic.FIRST_ROWS and ALL_ROWS are values for the optimizer setting OPTIMIZER_MODE. You can

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-32452860442745104532010-08-04T15:51:00.000-06:002010-08-04T15:52:29.547-06:00

Today, August 4th, shortly after lunch, ThinkOracle had it's one millionth visitor. Care for a stroll down memory lane?I started this site May 16, 2005, shortly after starting a new position with a company that made financial software. The idea was to make my own contribution to the growing Oracle community, expand my knowledge, improve my technical writing, and it never hurts to establish a

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-79683810744844016522010-08-02T19:30:00.002-06:002010-08-02T19:38:55.828-06:00

Last week I got a question about changing a user's default schema.My colleague is supporting a typical database application which is configured to use the user/schema that was created for its database. Many queries were written for this application that use that schema owner, but my colleague would like to run those queries with his own account instead - either because he doesn't want to log in

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-35405264156711930082010-03-25T10:00:00.002-06:002010-03-25T10:03:59.215-06:00

I was helping a colleague recently with some SQL. He had about a dozen SQL queries that all looked something like this:SELECT (some stuff),DECODE (status, 'A', 1, 'I', 0),(more stuff)FROM(wherever)I made a few observations about his DECODE statement:1. Sometimes he used DECODE and sometimes he used CASE2. Sometimes he would map ' ' to 0, and sometimes it would go to the default value of NULL,

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com10tag:blogger.com,1999:blog-12950820.post-76550925107182964782010-02-23T14:27:00.002-07:002010-02-23T14:32:51.867-07:00

Greetings! You probably found this page either through a Search Engine, an aggregator that doesn't delete blogs for inactivity, or possibly from a site with a very long blogroll, so let me introduce myself to you.My name is Robert Vollman. Those who were active in the on-line Oracle community from mid-2005 until mid-2007 may remember me either from this site or sites like it, from the Oracle

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-64429786362609624112009-02-24T09:17:00.002-07:002009-02-24T09:21:57.846-07:00

"My query isn't returning the right rows" is a problem we all hear often. Upon investigation, I find that the most common cause of a miswritten query is a misunderstanding and/or mishandling of NULLs.Well, you know what? I've had it. Let's get rid of NULL. There, I said it.I've written articles explaining NULL before, a couple of times in fact*. So have plenty of other Oracle specialists, in

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com8tag:blogger.com,1999:blog-12950820.post-3888491555535464422009-02-20T09:54:00.004-07:002009-02-20T09:57:38.970-07:00

"What's wrong?" the guru asked as he sat down next to the company's newest database analyst. "I heard you were having some trouble with the room booking application.""Yep," said Chad. "I just can't seem to get it right.""Don't put it all on yourself," said the guru. "What can I do to help?""Well, I've got it narrowed down to this one procedure, which is responsible for checking if the room is

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-76105311232492385042009-01-22T11:38:00.003-07:002009-01-22T11:43:30.605-07:00

The way he barely lifted his feet off the ground when he walked, I could hear him approaching from the other end of the hall. I knew he was coming, and I dreaded it. I had just gotten off the phone with a former teammate who was moved to another department. They had a new database analyst who was struggling with his new "database independent" application, and he was sent my way. It was just

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com9tag:blogger.com,1999:blog-12950820.post-16451379168791864792008-04-23T16:39:00.001-06:002008-04-24T07:51:51.285-06:00

Please review the following interesting article by Jonathan Holland.Why Relational Databases end up being the bottleneckFor the appropriate context of this debate, you can review this forum discussion on Joel's web site as just one example.Stored Proc to avoid frequent buildsAs is evident from the title and content of my blog, I'm a database professional. Nevertheless, I try to stay as objective

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com7tag:blogger.com,1999:blog-12950820.post-34944450064332125882008-04-18T10:01:00.003-06:002008-04-18T15:31:00.038-06:00

There are deployments and there are deprayments. What's the difference?A deployment is when you deploy a change and verify that it was successfully deployed and functioning properly.A deprayment is when you "deploy and pray." It's kind of like Dr. Evil in the Austin Powers movie: you don't actually witness that the deployment succeeded, you just walk away and assume everything went according to

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-50369749895292651622007-10-21T11:56:00.000-06:002007-10-21T11:59:30.029-06:00

I listened intently to the new Oracle programmer as he described all the struggles he's been having on his first big project. As I've done many times already in his short career, I interrupt with some words of wisdom."It's time to add Global Temporary Tables to your toolbelt.""What are those?" he asks, as he opens the directory with the Oracle documentation. I smile. He has already learned

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com13tag:blogger.com,1999:blog-12950820.post-80618084569193025972007-06-03T10:59:00.000-06:002007-06-06T11:09:59.684-06:00

You pick up the candidate's resume and it proudly proclaims "SQL Expert: 10 Years." Your boss trusts you, as the technical expert on the team, to participate briefly in the interview to gauge this individual's knowledge of SQL. Where to begin?I have asked literally hundreds of different questions during interviews over the past decade. Some were simple questions that were nevertheless

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com25tag:blogger.com,1999:blog-12950820.post-16735310370799344272007-05-28T11:36:00.000-06:002007-05-28T14:13:18.907-06:00

While attempting to insert several rows into a table in our Oracle database, a colleague dutifully copied the exact ANSI/ISO SQL standard syntax for his purposes. Guess what happened?INSERT INTO table (column1, column2)VALUES (value1, value2), (value1, value2);ERROR at line 1:ORA-00933: SQL command not properly endedUnlike some other databases (DB2, PostgreSQL, MySQL), Oracle doesn't support

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com7tag:blogger.com,1999:blog-12950820.post-62066406386854809302007-05-25T12:27:00.000-06:002007-05-26T09:32:51.117-06:00

Along the side of my page, you'll see my favourite Oracle blogs listed. I carefully maintain this list of fellow enthusiasts whose opinions and insights I most especially want to follow among the seemingly hundreds of Oracle blogs that are out there. Studying them, I think you'll find that each of them share the same core qualities listed below.1. AccuracyAccuracy is an absolute must. Just

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com17tag:blogger.com,1999:blog-12950820.post-773628160297306672007-05-04T11:29:00.000-06:002007-05-04T13:07:34.590-06:00

Like most of us, I still join tables in my SQL queries the old-school way. Simply put:SELECT whateverFROM table1 t1, table2 t2WHERE t1.id = t2.idAND t1.value > 10;But increasingly often I run into people who use ANSI joins instead. They were either introduced to SQL with Oracle 9 (or Sybase 12, etc), and were taught to use the SQL standard way, or else they made the conversion at some point in

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com7tag:blogger.com,1999:blog-12950820.post-44216616343771342202007-04-05T14:20:00.000-06:002007-04-05T14:24:13.117-06:00

I've got very few beefs with Oracle. It is extremely complicated and tough to learn compared to other relational databases, but that's partially offset by the tremendous documentation, and the huge Oracle community.Don't get me wrong, I love Oracle. I'm regularly impressed by the sophisticated and often clever ways it handles the requirements of an RDBMS, and quick to recommend its use. But

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com5tag:blogger.com,1999:blog-12950820.post-74122555648270067552007-03-09T16:09:00.000-07:002007-03-13T19:13:24.426-06:00

Everybody learns their lessons, and so will you. The only variable is how expensive the lesson is. While there is no substitute for direct, first-hand experience, the cheapest way to learn a lesson is to benefit from the experience of others.My favourite source of cheap lessons is Ask Tom. I've compiled a sample collection of Tom's Wisdom from just the articles updated in the past week.

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com1tag:blogger.com,1999:blog-12950820.post-77356204390072904862007-02-26T12:54:00.000-07:002007-02-26T12:59:31.685-07:00

As devoted readers may have noticed, my new job doesn't involve nearly as much work with Oracle. I stay sharp by reading Ask Tom, the very site that has provided me with 90% of the answers that I can't find in Oracle documentation or figure out on my own.Those of you who may find it nerdly to spend lunch hours reading Oracle Q&A are actually really missing out. It's far more entertaining than

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com7tag:blogger.com,1999:blog-12950820.post-1169759019374316782007-01-25T14:00:00.000-07:002007-01-25T14:05:30.686-07:00

Here is another advanced concept that will come in useful when solving Oracle problems. Imagine you're trying to create a result set where the rows need to be columns, or vice versa. In essence, you need to "pivot" rows into columns, or vice versa. That is a very common requirement, and this is where you need to look at a pivot (or crosstab) query to get the job done.As always, when you want

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-1165262419503128922006-12-04T12:57:00.000-07:002006-12-04T13:04:56.113-07:00

Recently I invited anyone who has been considering joining the Oracle blogging community to jump right in. Please, help me encourage John H:Robert,Way cool blog. I really need to create one too. I like to adhere to best practices and love to read stuff from Tom K. and Steven F.!I have a question on regular expression check constraints and was wondering if you can help:alter table

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1163522940456166012006-11-14T09:48:00.000-07:002006-11-14T09:49:00.460-07:00

18 months.107 posts.110,000 reads.Not too long ago I reacted with humble incredulity that 10,000 people had read what I had to say about Oracle.Here I am, exactly one year later, and there have been 100,000 more visits. I'm almost afraid to continue, will I be talking about 1,000,000 in November 2007?I wonder why people are reading my blog because, despite how numerous we are, it seems like

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-1163195237998498962006-11-10T14:43:00.000-07:002006-11-10T14:47:18.043-07:00

You have a table with all your company's financial transactions. There is another table which references a subset of these financial transactions (ie. transactions with certain properties).Your current solution to maintain the integrity is to set up a foreign key, referencing the transactions table, and then write a trigger to make sure that any records reference only transactions that have the

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-1162339274715836552006-10-31T16:55:00.000-07:002006-12-07T15:03:44.183-07:00

"Becoming an Oracle guru doesn't take remarkable intelligence or a pricey Harvard degree, but it does take persistence, drive, and a dedication to excellence." - Don Burleson, How to become an Oracle GuruDespite the appearance of merely trying to promote his team (most notably Steve Karam), Mr. Burleson actually raises some interesting points on a thought-provoking question.What Makes an Oracle

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com16tag:blogger.com,1999:blog-12950820.post-1162245292755832132006-10-30T14:48:00.000-07:002006-10-30T14:54:52.786-07:00

Hallowe'en is a time of fright and mystery, and some of the most common, mysterious Oracle questions I collect involve passwords. Here are some tricks and treats:How do you change an Oracle password? ... If you/the user forgot it?How are Oracle passwords stored? Does it look the same for two different users using the same password?Do the SQL Trace files reveal your secret password?How do you

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com7tag:blogger.com,1999:blog-12950820.post-1161722546805851092006-10-24T14:41:00.000-06:002006-10-24T14:42:26.890-06:00

If you follow the various blogs on the right, you'll see that practically everybody (except me) is currently attending Oracle OpenWorld 2006. It's an annual convention held in San Francisco late in the year. Lasts about 4 days, and costs around 2000 USD$.I'd love to attend Oracle OpenWorld some day. Or practically any conference. I would especially like to visit the annual conference hosted

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1161299400130507492006-10-19T17:04:00.000-06:002006-10-19T17:10:00.153-06:00

Want to know what has been calling the PL/SQL Procedure you've written?Want to know how to tell how far along your long-running PL/SQL Procedure is?What to know how to write fast mass insert/deletes like a pro?I have found you the answer for all three, courtesy of my fine colleagues featured among my links. Allow me to guide you through these three great articles.Who Is Calling Your Procedure?

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com0tag:blogger.com,1999:blog-12950820.post-1161105650252794502006-10-17T11:07:00.000-06:002006-10-17T11:20:50.276-06:00

Having worked in technical support for software vendors for 4 years, I know a few strategies that can help customers get the best response.1. Get trainingRely on the assistance of the software vendor for as little as possible. Invest in training your own people by signing them up for courses and, more importantly, giving them the time to learn the product and how your company uses it. Once

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-1158186865856268212006-09-13T16:30:00.000-06:002006-09-13T16:34:25.873-06:00

If you're still using any of the default Oracle username/passwords on any of your databases, go change it now. I could blog about this for 1-2 pages, but for two reasons:1. I think it is an obvious (but oft-neglected) thing to do, and2. You can use the time you would have spent reading my persuasive arguments to actually go change your passwords.So go do it now.

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com0tag:blogger.com,1999:blog-12950820.post-1158094993264504992006-09-12T15:00:00.000-06:002006-09-12T15:03:13.300-06:00

What is a number? The definition is one of the longest I've seen, but generally it refers to a quantity (or sum, total, count) of units. Quantities can be subjected to all sorts of calculations, such as addition, subtraction, multiplication and division. Sometimes numbers are used for ordering, as well. So it makes sense to compare one number to another, not only as being greater or lesser

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com8tag:blogger.com,1999:blog-12950820.post-1157247744476899022006-09-02T19:38:00.000-06:002006-09-03T21:22:33.436-06:00

There are two broad categories of reasons why PL/SQL programmers wouldn't want others to read their code:Reason #1 ProtectionThe programmers wish to protect their code from theft, misuse or alteration (among other things).Reason #2 Malicious ReasonsThe code is meant to do harm, or may have some potentially dangerous errors they wish to cover up.I don't believe in hidden code, because open code is

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com4tag:blogger.com,1999:blog-12950820.post-1155306160536214302006-08-11T08:12:00.000-06:002006-08-11T08:22:40.570-06:00

Zsolt Lajosfalvi wrote such an interesting comment in response to my earlier post about PL/SQL Procedure Call Overhead that I felt it deserved its own space. What follows is exclusively the work of Zsolt. My results from verifying his tests follow at the end.Zsolt:In my experience the overhead of PLSQL procedure invocations is so small that you'll most probably never have to think about it. In

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1153873282632781262006-07-25T18:20:00.000-06:002006-07-25T18:21:48.860-06:00

In keeping with the theme of my most recent post, I just couldn't stop laughing at Tom's most recent response to a question about recursive sql.

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com10tag:blogger.com,1999:blog-12950820.post-1153342794396731052006-07-19T14:53:00.000-06:002006-07-19T14:59:54.436-06:00

QUESTIONHi Robert, I don't understand the meaning of any of the items on the query plan. Is there some document that would explain what they are? If there is, where can I get it? Thanks.ANSWERThat's the best kind of question. Instead of sending me a query plan and asking me to interpret it for you, you're asking me for the means to do it yourself. Which is good because there is so much I don't

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1152071352305799702006-07-04T21:35:00.000-06:002006-07-04T21:49:12.353-06:00

Last December I wrote about how to create a simple Perl application that connected to your Oracle database, it is high time that I showed you how to do the same in Java.What You'll Need1. An Oracle database. Make sure you can tnsping your ORACLE_SID. Better yet, make sure you can connect with sqlplus.2. Java. It's a free download from Sun. I'm using 1.5, but I've tested this on several

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com1tag:blogger.com,1999:blog-12950820.post-1151449708028072422006-06-27T17:04:00.000-06:002006-06-27T17:09:30.713-06:00

When writing code to do repetitive tasks, the two primary approaches are iteration and recursion. Generally you can use either one interchangeably, but potentially with different performance and complexity.A recursive function calls itself (possibly more than once), with different parameters, and defines an exit clause that is guaranteed to be reached. CREATE OR REPLACE FUNCTION FIBONACCI_REC (

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com13tag:blogger.com,1999:blog-12950820.post-1150652392918061392006-06-18T11:14:00.000-06:002006-06-18T12:16:27.500-06:00

"You have redeemed my faith, after last year's presentation, which was such a disappointment."- One of Tom Kyte's biggest fans in COUGRenowned Oracle expert and author of highly popular blog, pictured here with Tom Kyte.I first met Tom Kyte about twenty minutes before his presentation about his favourite features in Oracle 10g. When I went up to introduce myself, I figured he was putting the

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com0tag:blogger.com,1999:blog-12950820.post-1150237285384800862006-06-13T16:18:00.000-06:002006-06-13T16:21:25.413-06:00

Moving data from one database to another is a very common task for a database administrator. So common, in fact, that I have never bothered to write about it, because most DBAs already have their own preferred method.If you haven't chosen your approach yet, allow me to show you the general flow, some points to keep in mind, and some of the decisions you'll have to make based upon the nature of

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com4tag:blogger.com,1999:blog-12950820.post-1149691797244009672006-06-07T08:40:00.000-06:002006-06-07T08:49:57.266-06:00

This morning I read that fellow Oracle blogger Tim Hall is looking for Oracle topics on which to base his next article. It occurs to me that since changing positions a few months ago that I have dealt with Oracle a lot less than previously, and am open to hearing some ideas.Virtually all my articles are based on questions I get from customers or colleagues, or problems I have to solve in my

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1149018261382105622006-05-30T13:10:00.000-06:002006-05-31T08:04:25.060-06:00

Answer: "Analytic Functions"Question: "I have become quite comfortable, perhaps even adept, with basic SQL queries. I am ready to learn how to do more with my data. What do you recommend I study?"Given how often I write about analytic functions, my answer may not come as a surprise. To those who have been studying along with me, I would like to add one more tool to our toolbelts. Following up

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com1tag:blogger.com,1999:blog-12950820.post-1147212642791945092006-05-09T15:57:00.000-06:002006-05-09T20:37:29.770-06:00

Today's question is how do you find rows that have values nearest the value of a given row? Although there are many different variations on this central theme, in order to solve problems of this nature, you'll need to know:1. The given column's value for the given row2. The difference between each row's value and the value of the given row3. That row's difference's relationship to all the other

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1145295857450266232006-04-17T11:39:00.000-06:002006-04-18T19:44:29.553-06:00

I was recently asked by a former colleague "Can you insert data in an Oracle table through a View?"In some cases, yes you can, and it actually serves as a handy technique to manage security on your data. Here's what I mean: you can restrict access to a base table, and then create a view containing only rows and columns that you wish to be accessible. That is just one of the many handy uses of

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-1144108071083934922006-04-03T17:40:00.000-06:002006-04-06T21:10:05.573-06:00

Pivot queries (also known as crosstab queries) are a special kind of query that can turn rows into columns for special reporting needs. For example, if you have a table/view with three columns (employee name, year, and salary), and you need a view that shows employee name and salary over the years (as a single row), then a pivot query is what you want. Essentially, you want to "pivot" the year

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com0tag:blogger.com,1999:blog-12950820.post-1143666774405989312006-03-29T14:10:00.000-07:002006-03-29T14:12:54.426-07:00

There is a great debate in the database application development community. I have encountered many developers who prefer putting all their business logic in the application code and using the database only as a glorified file system.You pick your battles carefully with these developers. I always thought the "line of last defense" was data integrity rules. When I encountered a particularly

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com5tag:blogger.com,1999:blog-12950820.post-1143566562408249922006-03-28T10:14:00.002-07:002010-08-11T13:44:53.363-06:00

To ease the task of browsing my archives, I've put together an organised list of my previous posts. Bookmark this, because I will keep it up to date. I will also include a link to this post under Archives.Note: If you comment on an older article, I will get an automatic email. So I will see it and I will respond to it, so please feel free to do so!For Newbies:Tuesday, December 20, 200520

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com1tag:blogger.com,1999:blog-12950820.post-1142015482034142272006-03-10T11:25:00.000-07:002006-03-10T11:31:22.926-07:00

Typically performance issues are handled by looking for common symptoms and trying common solutions. Given a sufficient level of experience, this is successful quite often with minimal effort. But there are times where this does not work and you need a more systematic approach.Let's take a look at one approach, bearing in mind:1. This is a rough, first, high-level pass2. It is NOT Oracle (or

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com4tag:blogger.com,1999:blog-12950820.post-1141239254354453212006-03-01T11:50:00.000-07:002006-03-05T20:30:16.623-07:00

I've got a checklist of tasks I like to complete before beginning a new project. One important item on my list is to decide on how my exceptions are going to be handled.By deciding in advance, I can save time by implementing exception handling only once, and also have much more consistent code. I also find that once I begin a project, I'm focusing on the logic and can easily miss handling

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com10tag:blogger.com,1999:blog-12950820.post-1140814168285967222006-02-24T13:42:00.000-07:002006-02-24T13:49:28.306-07:00

You thought the Tom Kyte Effect was the most powerful force in the Oracle Blogging Universe. But we were all mistaken! For today, I got a much higher spike than ever. The source? Oracle itself!I am especially grateful that Gary Myers caught my sloppy error before a bunch of people read it. My only regret is that I didn't really have anything particular intelligent to say, and even if I did,

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-1140647890326300742006-02-22T15:36:00.000-07:002006-02-22T15:38:10.366-07:00

So I was searching through the latest Oracle 10g SQL Reference, check out what I found on page 5-175:SELECT manager_id, last_name, salary, SUM(salary)OVER (PARTITION BY manager_id ORDER BY salary RANGE UNBOUNDED PRECEDING) l_csumFROM employees;MANAGER_ID LAST_NAME SALARY L_CSUM---------- --------- ------ ------100 Mourgos 5800 5800100 Vollman 6500 12300...I'm in the Oracle

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com1tag:blogger.com,1999:blog-12950820.post-1140647162653144452006-02-22T15:23:00.000-07:002006-02-23T07:40:44.760-07:00

Proactively maintaining your database ... something some people do only AFTER a problem of some kind.Case in point, a customer recently asked us what sequences are being used as primary keys, what are their maximum values, and are any in danger of running out. Guess what motivated that investigation?Most of this is rather trivial using only the ALL_SEQUENCES table.1. What sequences are being

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com20tag:blogger.com,1999:blog-12950820.post-1140552845285767812006-02-21T13:09:00.000-07:002006-02-21T13:14:05.313-07:00

"We're interviewing an Oracle guy tomorrow, can you give me a few questions to ask him?"Not an uncommon request. The problem is, there are literally thousands of potential Oracle questions, but it all depends on what are you trying to achieve. So I pushed back:"What kind of Oracle-related skills would the candidate need that you want to ask for?""You tell us. We just want to know if he knows

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com11tag:blogger.com,1999:blog-12950820.post-1139944004086938582006-02-14T12:03:00.000-07:002006-02-14T12:06:44.156-07:00

BPEL (Business Process Execution Language) is an xml-based language for use with the Service-Oriented Architecture (SOA) to software development. Simply put, BPEL is an XML-based standard used to define business processes, generally by demonstrating how relevant Web Services connect and communicate. You would use BPEL to layout the general business flow and how the Web Services are used. In

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1139355542007817932006-02-07T16:36:00.000-07:002006-02-07T16:45:09.910-07:00

TRANSLATE is a useful little function that can replace given characters in the first string with other given characters. TRANSLATE will go through the provided string looking for any instance of characters in the first list and, when found, replace them with the corresponding character in the second list. Any characters in the given string that don't show up in the first list are left alone.If

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com5tag:blogger.com,1999:blog-12950820.post-1138917779462534562006-02-02T14:58:00.000-07:002006-02-02T15:02:59.490-07:00

I went to a seminar yesterday in downtown Calgary where Oracle was unveiling its SOA Solution to all the big Canadian oil and gas giants. SOA is the latest buzz-acronym for "Service-Oriented Architecture." What is SOA?SOA is not a technology or something you install. It is a concept, or rather an approach to modelling your system, and one that is different from the standard client/server model

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com4tag:blogger.com,1999:blog-12950820.post-1138910186461326852006-02-02T12:55:00.000-07:002006-02-02T12:56:26.476-07:00

Have you been overlooking the wealth of handy tools and code on SourceForge?SourceForge.net is a web site that hosts literally hundreds of thousands of projects. Its an excellent source of open source development projects. You can download software and source code and collaborate with others on projects.Having trouble designing your software? Search SourceForge for similar projects and see

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com1tag:blogger.com,1999:blog-12950820.post-1138144324734025432006-01-24T16:09:00.000-07:002006-01-24T16:12:04.810-07:00

I wanted to briefly follow up on Tip #2 on my recent post about PL/SQL Coding.http://thinkoracle.blogspot.com/2005/12/20-plsql-coding-tips.htmlI am not intending to convince anyone WHY they should spend time gathering requirements, but rather simply HOW to do it. However, let it be known that there are people whose careers are entirely based on the requirements gathering process, that there are

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-1137801702580353332006-01-20T14:23:00.000-07:002006-01-20T17:01:42.720-07:00

PL/SQL vs J2EE, Part 2I knew there was going to be some discussion following up my previous post on using stored procedures instead of embedding SQL in your J2EE application. Some of it you can read in the comments, others you can find on other people's blogs. Original ArticleTim Hall's Response (and Here).Harry Boswell's ResponseDizwell Discussion: Database IndependenceI also saw a similar

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com0tag:blogger.com,1999:blog-12950820.post-1137631344286445042006-01-18T17:35:00.000-07:002006-01-18T17:42:24.320-07:00

Let's say you're designing an enterprise-wide OLTP Web Application with Data Warehousing, Reporting and multiple interfaces to various external systems for large volumes of data. Where do you put your business logic? Do you embed your SQL into your Java code, or do you keep your SQL in the database, and write stored procedures?Regardless of your answer, you are wandering into one of the older

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com22tag:blogger.com,1999:blog-12950820.post-1137524537347643682006-01-17T11:57:00.000-07:002006-01-17T12:02:17.376-07:00

When writing your PL/SQL stored procedure bear in mind that SQL statements are still sent to the SQL Engine as opposed to the PL/SQL Engine. Therefore in cases where you are executing several SQL statements in a loop, the resulting context switches could cause a noticeable performance problem.One solution to this performance solution is the use of "bulk binding." What is that? Well first, you

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com1tag:blogger.com,1999:blog-12950820.post-1137379650484707402006-01-15T19:32:00.000-07:002006-01-15T19:47:30.626-07:00

Here is another interesting discussion taking place on AskTom about Oracle DO NOTs:http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:53901314988148After Tom responded, a lot of people got into it. I had some ideas myself, but decided not to make one of my famous Top 20 Lists after all, since it wound up similar to this one:http://thinkoracle.blogspot.com/2005/12/20-

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com0tag:blogger.com,1999:blog-12950820.post-1135212674353481462005-12-21T17:46:00.000-07:002005-12-21T17:51:14.380-07:00

I got named in ComputerWorld's "Best IT Blogs on the Net." Granted, only in the "buffer overflow" section, but still - that's pretty cool!http://www.computerworld.com/blogs/node/1466?source=nlt_blgMaybe it is another consequence of the Thomas Kyte Effect?http://thinkoracle.blogspot.com/2005/12/thomas-kyte-effect.htmlAnyway, I feel very fortunate for having this new audience, but I feel bad for

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1135115784285534922005-12-20T14:49:00.000-07:002005-12-20T14:56:24.323-07:00

Good grief! I saw a spike in my statcounter and I checked it out to see if I got spammed. Instead I am the newest beneficiary of the "Thomas Kyte Effect." Well, strictly speaking, the Thomas Kyte Effect is when your blog is referenced in a blog article of his, but in my case I was simply added to his list of links. And then bam - several hundred hits today.With all due respect to Doug, Lisa

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com1tag:blogger.com,1999:blog-12950820.post-1135103350306358962005-12-20T11:26:00.000-07:002005-12-21T08:44:44.243-07:00

Continuing in my newfound tradition for lists of 20, here is a "cheat sheet" of 20 common beginner Oracle questions.Before we begin, one simple warning. Very few of these are complete answers, consider them pointers or starting points or else the incomplete understanding could be dangerous.Edit: This has been highly edited since its original version.Oracle General:1. What are the key environment

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com14tag:blogger.com,1999:blog-12950820.post-1135038035898644362005-12-19T17:15:00.000-07:002005-12-19T17:20:35.926-07:00

Gary pointed me to a recent AskTom thread that contained a list of PL/SQL Coding Practises. The ensuing discussion added a few more, and I'd like to throw in a few of my own as well. I also think most of these tips are useful for other languages, but this is first and foremost an Oracle developer's blog.Here they are, with the order and wording slightly revised in some cases (removed the

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com17tag:blogger.com,1999:blog-12950820.post-1134680302504851132005-12-15T13:45:00.000-07:002005-12-15T13:59:10.640-07:00

I work with several different databases for several different clients on several different operating systems, often with only remote access to a shell (as opposed to a desktop). Therefore I like to become familiar with very common and simple technologies, like vi, sqlplus (for Oracle) and Perl.Writing my useful tools in Perl is very handy because I can run them anywhere, and they can work for

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1132874261701024412005-11-24T16:11:00.000-07:002005-11-24T16:17:41.720-07:00

You would like to communicate with a 3rd-party application from your PL/SQL program, for example you want to run a UNIX command. Or perhaps you'd like to communicate directly with another Oracle session. Whatever your specific communication needs are, DBMS_PIPE is your solution.What is DBMS_PIPE?DBMS_PIPE is a package provided by Oracle that allows two or more sessions in the same instance to

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com4tag:blogger.com,1999:blog-12950820.post-1132619011400618472005-11-21T17:22:00.000-07:002005-11-21T17:23:31.420-07:00

Earlier I mentioned the idea of using a RAW datatype for your table's primary key. I stumbled upon a minor inconvenience with using the RAW datatype today, so I thought I would take a step back and talk a little bit more about the RAW datatype.http://thinkoracle.blogspot.com/2005/06/natural-vs-synthetic-keys.htmlRAW(size) is used for raw binary data or byte strings of length "size" bytes (must

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com5tag:blogger.com,1999:blog-12950820.post-1131749140096179632005-11-11T15:42:00.000-07:002005-11-11T15:45:40.113-07:00

I recently posted an assorted list of Oracle essays from some of my favourite Oracle authors:http://thinkoracle.blogspot.com/2005/11/oracle-essays.htmlThere are so many other great Oracle writers out there pleased to provide free insights into Oracle. So I thought I'd share a few more with Troy and everyone else. Remember that each of these authors have written several papers, let me know if

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1131643575495464512005-11-10T10:24:00.000-07:002005-11-10T10:26:15.516-07:00

What is the DUAL table?The DUAL Dummy table (as it is sometimes called) is an automatically-generated table assigned to SYS, but accessible to all users. It has a single column "DUMMY" of type VARCHAR2(1) which has a single row with a value of 'X'. scott@Robert> SELECT * FROM DUAL;D-Xscott@Robert> DESC DUAL; Name Null? Type ---

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1131579078327364412005-11-09T16:25:00.000-07:002005-11-09T16:31:18.340-07:00

This marks my 60th post since I started in May, and, according to my stat counter I have had 10,000 hits. I was expecting 100, and even if 100 was the current total, it would still be worthwhile for me to share my thoughts. Knowing that my ideas have been read 10,000 times makes me feel more excited about sharing them.So whoever all of you are, you have my thanks, and I will definitely continue

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-1131125490540832032005-11-04T10:29:00.000-07:002005-11-04T10:31:30.556-07:00

Recently I recommended "Oracle Insights," a book containing a series of Oracle-related essays by some of the top authors in our community. For those who are interested in reading some more essays, consider some of the following samples from a variety of my favourite authors on a variety of topics. The best part is that these are free!Re-building Indexes: When, How, Why by Jonathan Lewishttp://

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com0tag:blogger.com,1999:blog-12950820.post-1130978890118026932005-11-02T17:41:00.000-07:002005-11-02T17:48:10.140-07:00

A recent book review of "Oracle Insights: Tales of the Oak Table" by Doug Burns, combined with the introduction of Mogens Norgaard's new blog motivated me to finally finish my own review of his book, which has sat incomplete since I first read it 6 months ago.Doug Burns' Book Review of "Oracle Insights:"http://oracledoug.blogspot.com/2005/10/book-review-oracle-insights-tales-of.htmlMogens

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1130795206246052422005-10-31T14:43:00.000-07:002005-10-31T14:46:46.250-07:00

What is a "package"? According to the PL/SQL User's Guide and Reference, "A package is a schema object that groups logically related PL/SQL types, items and subprograms."But I believe a package is far more than just a way of logically grouping objects together. Before I digress, let's very briefly understand what a package is. It's probably easiest if you take a look at Oracle's documentation,

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com7tag:blogger.com,1999:blog-12950820.post-1129137890494824992005-10-12T11:20:00.000-06:002005-10-12T14:57:44.866-06:00

DBMS_OUTPUT.PUT_LINE allows you to write information to a buffer throughout the execution of a trigger/procedure. That information is available to be read by a trigger/procedure (using GET_LINE(S)), or dumped to SQL*Plus upon completion of execution.One of the most common misconceptions is that PUT_LINE writes data immediately to SQL*Plus. That is not true. PUT_LINE only puts it in the buffer.

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com12tag:blogger.com,1999:blog-12950820.post-1128620618191722702005-10-06T11:42:00.000-06:002005-10-06T11:43:38.200-06:00

Questions:How do I limit the number of rows returned by a query?How do I write a query to get the Top-N salaries from the employee table?How can I add unique, sequential numbers to an existing table?How can I differentiate between two completely identical rows?How can I find a faster way to retrieve a queried row?How can I find the last row processed in a big batch?There is one thing all these

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com31tag:blogger.com,1999:blog-12950820.post-1128465409989901422005-10-04T16:28:00.000-06:002005-10-05T15:09:37.326-06:00

Not long ago, I mentioned that if you do a COUNT on a column (as opposed to on * or a constant), the result will not include rows that have a NULL value for that column.http://thinkoracle.blogspot.com/2005/09/nulls-in-count.htmlApparently you can exploit this situation, by using DECODE, and improve the efficiency of your queries.http://www.akadia.com/services/ora_decode.htmlI found that very

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com8tag:blogger.com,1999:blog-12950820.post-1128462985958413082005-10-04T15:52:00.000-06:002005-10-04T15:58:24.233-06:00

Recently Dr. Tim Hall posted a brief blog on SQL Tuning. He may have based it on a number of questions on the topic that have popped up recently on some of the Oracle forums.http://oracle-base.blogspot.com/2005/10/no-shortcuts-for-sql-tuning.htmlYou might also have seen Steve Callan's 2-part article on Oracle Performance Tuning on DBASupport.com:http://www.dbasupport.com/oracle/ora10g/

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1128121849631204082005-09-30T17:08:00.000-06:002005-09-30T17:10:49.643-06:00

Is there much overhead in calling PL/SQL procedures? I assume that if the answer is "yes," you'll want to avoid procedure calls, which would likely mean making your procedures bigger (by combining several into one). That makes me shudder because clean, modular code is easier to read and maintain, not to mention making it easier to develop new code if its based on reliable, tested code.I assume

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com4tag:blogger.com,1999:blog-12950820.post-1127435083435230752005-09-22T18:19:00.000-06:002005-09-22T18:24:43.443-06:00

Consider the situation where you are writing a stored procedure that takes a column name as a variable, and then does some work based on a query that uses that column name. How would you do it?Let's consider a hypothetical situation. Say you have a table with all your employees. Some of the columns are responsible for their pay. Employees can get paid in different ways, for example: base

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com4tag:blogger.com,1999:blog-12950820.post-1127250454158008832005-09-20T15:03:00.000-06:002005-09-20T15:07:34.163-06:00

Please excuse the spammy post today. But I would like to ask anyone who is reading this who may have unwanted Oracle (8 and up) or General DB (College) books to contact me (email address is in my profile).Rather than gather dust on your shelf, I can give them a good home. I will pay for shipping costs to Canada, and you will have my heartfelt appreciation.Many thanks!

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-1127172879732966412005-09-19T17:33:00.000-06:002005-09-19T17:34:39.740-06:00

I read this interesting exchange on Steven Feuerstein's Q&A: http://htmldb.oracle.com/pls/otn/f?p=2853:4:1727923121986559057::NO::P4_QA_ID:246 Essentially the question is where to stored your PL/SQL stored procedures. H. Sheehan, Gary Myers, William Robertson, Pete Scott, Scott Swank, A. Nadrian and I discussed this on the Dizwell Forum.http://www.phpbbserver.com/phpbb/viewtopic.php?t=458&mforum

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1126735960541184372005-09-14T16:06:00.000-06:002005-09-14T16:12:40.550-06:00

Alternate title: Keeping Tables Small, RevisitedIn an earlier article I spoke about how removing old data can help speed up table scans:http://thinkoracle.blogspot.com/2005/08/keeping-tables-small.htmlDuring a test in that article, I seemed to detect that querying a view composed of the 90/10 split of a large table was much faster than querying that table directly.I was only trying to demonstrate

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-1126551680784754262005-09-12T12:54:00.000-06:002005-09-12T13:01:20.796-06:00

I started using Oracle with version 8 in 1999. After a few years I changed companies to a Sybase/SQL-Server shop. But the past year has found me back working with Oracle, this time version 8, and 9.It has been an interesting time getting myself back into "game shape" with Oracle, and digging into version 9. If you've been reading this blog, you've been able to follow along with me in my

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com5tag:blogger.com,1999:blog-12950820.post-1126308089307757292005-09-09T17:20:00.000-06:002005-09-09T17:21:29.313-06:00

Quick, easy one, but it trips up beginners. Something of which to be mindful. Observe:SELECT COUNT (*) FROM EMP; COUNT(*)---------- 14SELECT COUNT (COMM) FROM EMP;COUNT(COMM)----------- 4Note: 4 instead of 14. But if we do this:SELECT COMM FROM EMP; COMM---------- 300 500 1400 014 rows selected.We get all 14.This is expected behaviour.

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-1125607336831479862005-09-01T14:38:00.000-06:002005-09-01T14:42:16.840-06:00

Here is another advanced concept that will come in useful when solving Oracle problems. Imagine you're trying to create a result set where the rows need to be columns, or vice versa. In essence, you need to "pivot" rows into columns, or vice versa. That is a very common requirement, and this is where you need to look at a pivot (or crosstab) query to get the job done.As always, when you want

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com37tag:blogger.com,1999:blog-12950820.post-1125595274548064532005-09-01T11:19:00.000-06:002005-09-01T11:21:14.550-06:00

Another great way to learn Oracle is to study mistakes.Generally people have (misguided) reasons behind their mistakes, and studying them can improve your understanding of Oracle. As such I'd like you to check out a new blog out there dedicated to the most spectacular misunderstandings of Oracle:http://oracle-wtf.blogspot.com/It's written by William Robertson, James Padfield, Thai Rices, Adrian

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1125352581424043052005-08-29T15:55:00.000-06:002005-08-29T15:57:30.416-06:00

Not long ago, I wrote a blog on all the Oracle Blogs out there:http://thinkoracle.blogspot.com/2005/07/oracle-blogs.htmlNot long after that, I included a list of links to all the blogs I follow (see the side, below links).Well there have been a couple of changes and new additions. The most exciting of which is I found a blog by Laurent Schneider. He is one of my favourite posters from the

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1124918142904617852005-08-24T15:14:00.000-06:002005-08-24T16:03:51.013-06:00

Let's say you wanted to write a query that contained an aggregate (eg: sum, min, max, count). No problem, you can use 'GROUP BY'.But suppose you wanted to write a query that was a report containing both the aggregate and the detail. That makes it trickier.You may even know how to do it in other databases:http://sybasease.blogspot.com/2005/08/compute.htmlThere is a simple way of accomplishing

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com7tag:blogger.com,1999:blog-12950820.post-1124319080001241932005-08-17T16:47:00.000-06:002005-08-17T16:51:20.010-06:00

David Aldridge recently put together a list of ways to make table scans faster:http://oraclesponge.blogspot.com/2005/08/list-ways-to-scan-table-faster.htmlThere is one really simple, effective but often overlooked way of decreasing the time it takes to scan a table that I want to elaborate on. It's based on this simple premise:Fewer Rows = Faster Scans.The time it takes to scan a table can be

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com5tag:blogger.com,1999:blog-12950820.post-1124066347611135322005-08-14T18:37:00.000-06:002005-08-14T18:39:07.620-06:00

Let's say you want to pull some real-time information off the Internet and put it in your database. For example:- Stock Market Quotes- Temperature- Sports scoresNo need to write a separate application in Java or whatever, you can do it all directly in Oracle.Your solution will involve using one of the many handy Oracle built-in utilities: UTL_HTTP.For reference, flip open Chapter 78 of the '

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com11tag:blogger.com,1999:blog-12950820.post-1123696683972689902005-08-10T11:53:00.000-06:002005-08-11T10:44:29.563-06:00

You want to write a query that contains the rows from 2 or more tables. What you want to use is one of Oracle's set operators: UNION, INTERSECT, or MINUS. (Note: in the ANSI SQL standard, MINUS is referred to as EXCEPT). My example will deal with UNION.You may have tables containing your employees, contractors and clients, each with their own unique and appropriate columns. For illustrative

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com9tag:blogger.com,1999:blog-12950820.post-1123608411362959372005-08-09T11:24:00.000-06:002005-08-09T11:26:51.370-06:00

Brian Duff maintains a blog that brings together all the feeds from the various Oracle blogs that are out there.http://www.orablogs.com/orablogs/There are some fine blogs that are not included because they do not support RSS 2.0 feed. Mine was one such case, I used atom. Peter Scott and Doug Burns then pointed me to Feedburner. It converts your feed from one format into another. http://

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com0tag:blogger.com,1999:blog-12950820.post-1122928079973926792005-08-01T14:27:00.000-06:002005-08-01T14:27:59.980-06:00

I recently installed a new Oracle instance on my laptop. I wanted to migrate my small development database (complete with structure and data) from my main workstation to my laptop.It was very easy using the import (IMP) and export (EXP) tools. They are described in Chapter 1 (Export) and Chapter 2 (Import) of the Oracle Utilities Guide, available here:http://www.oracle.com/technology/

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com4tag:blogger.com,1999:blog-12950820.post-1122670821253788022005-07-29T14:44:00.000-06:002005-07-29T15:01:12.356-06:00

I would like to elaborate on one point from my recent blog on Using Views:http://thinkoracle.blogspot.com/2005/07/use-views.htmlThe example in question is a recent case where I used Views to very easily perform a complex query.Let's set it up. We have a simple company with expenses and revenues broken up by type and department. I'm ignoring many columns/constraints and the expensetype table for

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com2tag:blogger.com,1999:blog-12950820.post-1122418320592990422005-07-26T16:48:00.000-06:002005-07-26T16:52:00.603-06:00

I was ready to explain to you why you should take advantage of Oracle's ability to manage the integrity of your data rather than rely on your applications, but I found a much better explanation in the Oracle Data Warehousing Guide. Read the section "Why Integrity Constraints are Useful in a Data Warehouse" in Chapter 7 on "Integrity Constraints."So instead, let me give you a just a really quick

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3tag:blogger.com,1999:blog-12950820.post-1122323399937055232005-07-25T14:28:00.000-06:002005-07-25T14:29:59.943-06:00

Very often the solution to a problem involves using a view. Use Views!What is a View?Most people think of a view either as a stored, named query, or as a "virtual table."Here are two definitions from Oracle, along with references to the key documents on Views to review."A logical representation of a table or combination of tables." - Oracle Application Developer's Guide, Section 2.11 (Views)"A

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com7tag:blogger.com,1999:blog-12950820.post-1122166618954760302005-07-23T18:52:00.000-06:002005-07-23T18:58:12.020-06:00

There is no BOOLEAN datatype in Oracle, as far as tables are concerned.CREATE TABLE BooleanTable (MyBool BOOLEAN);ORA-00902: invalid datatypeBut there is a BOOLEAN datatype in PL/SQL.CREATE OR REPLACE PROCEDURE BoolProc (in_bool IN BOOLEAN)AS my_bool BOOLEAN := TRUE;BEGIN IF (in_bool = my_bool) THEN DBMS_OUTPUT.PUT_LINE('True'); ELSE DBMS_OUTPUT.PUT_LINE('False or NULL'); END IF;EXCEPTION

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com9tag:blogger.com,1999:blog-12950820.post-1121977237659287932005-07-21T14:18:00.000-06:002005-07-21T14:20:37.673-06:00

If you enjoy this blog, there may be other Oracle-related blogs you will also enjoy reading. You may be surprised to learn just how many good Oracle blogs are out there. I follow quite a few on a daily or casual basis. Here are my favourites.Brian Duff hosts OraBlogs, which picks up several of the below Oracle blogs, so it can be a one-stop blog.http://www.orablogs.com/orablogs/Tom Kyte,

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com6tag:blogger.com,1999:blog-12950820.post-1121731509479154282005-07-18T18:00:00.000-06:002005-07-25T11:15:24.030-06:00

Here's an easy one a colleague asked me.You are logged into your default session (example: sqlplus scott/tiger with no @instance) You want to know which instance you are logged into. You can't query V$ tables directly, because you're not logged in as SYS. So while looking around at similar questions on Jonathan Lewis' cooperative FAQ: http://www.jlcomp.demon.co.uk/faq/MySessID.html I saw

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com1tag:blogger.com,1999:blog-12950820.post-1121360202118403462005-07-14T10:54:00.000-06:002005-07-25T11:16:38.936-06:00

We have seen articles on where to go for help, and how to ask for help:http://thinkoracle.blogspot.com/2005/06/asking-for-help.htmlRecently Tom Kyte wrote an article on Reading the F'n Manual:http://tkyte.blogspot.com/2005/07/rtfm.htmlWhich begs the F'n question. Where are the F'n manuals? Which manuals should you F'n read?The first question is easy, it's all here:http://www.oracle.com/

Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com5