Friday, February 24, 2006

The Oracle Effect

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, it was said so much better by (among other people) Howard Rogers.

Since the link will change soon, here is a JPG to mark my 15 minutes of nerd fame.

Wednesday, February 22, 2006

Never noticed this before

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)
FROM employees;

---------- --------- ------ ------
100 Mourgos 5800 5800
100 Vollman 6500 12300

I'm in the Oracle default HR schema! I'm employee 123 and I report directly to KING, the President! Of course, I'm one of the lowest paid managers, but still.

Of course, the first name is Shonta. But I can dream, right? I can pretend that I'm one of Lex de Haan's fictional colleagues, right?

I also saw this in the latest version of Oracle 9i documentation:
SQL Reference:
Page 365, 6-155 for the example of SUM.
Page 1532, 18-42 on using LEVEL Pseudo-Column.

and Sample Schemas:
Page 68, section 4-28 on Oracle's sample HR Schema

So it must have been around for awhile. I think that's so cool! I'm going to write Oracle and ask them to fix my first name. I urge you to do the same in your implementations:

UPDATE employees SET first_name = 'Robert' where last_name = 'Vollman';

Oracle Sequences

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 used, and do they roll over or run out?

SELECT cycle_flag, sequence_name FROM ALL_SEQUENCES;

2. Which sequences are being used as primary keys?

That's trickier. See, even if you're using a sequence as a primary key, there's no way to tell by just looking at a table somewhere. The INSERT commands could be calling that sequence directly. Edit Or, it could be inserted by a trigger. I'm not aware of any easy, reliable way to determine if sequences are being used as primary keys without looking table by table.

However, generally that is what people like to use sequences for, so there's a good chance that all the sequences are being used as keys somewhere.

3. Which sequences are in danger of running out?

SELECT sequence_name, (max_value - last_number)/increment_by sequences_left
ORDER BY sequences_left;

4. What happens when a sequence runs out?

Well that depends if its a roll-over or not.


CREATE TABLE sequence_table (roll NUMBER(1), runout NUMBER(1));

Run this three times:
INSERT INTO sequence_table (roll, runout) VALUES (rollover_seq.NEXTVAL, runout_seq.NEXTVAL);

On the fourth time:
ORA-08004: sequence RUNOUT_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

But the other one rolls over:
INSERT INTO sequence_table (roll, runout) VALUES (rollover_seq.NEXTVAL, 4);

scott@Robert> SELECT * FROM sequence_table;

---------- ----------
1 1
2 2
3 3
2 4

Why is it 2,4 instead of 1,4? Well we "used up" the 1 on the misfire, so now we have a "gap." Every time NEXTVAL is "called", the sequence increments. CURRVAL doesn't. Of course, CURRVAL gives you last_number, which is one that's presumably already in use.

By the way, as usual, you can get some better examples, and a better reference, from Dan Morgan's library.

Those looking for an equivalent to AUTONUMBER in other RDBMS may find that using an Oracle sequence is close to the same functionality. Edit Just use the sequence's NEXTVAL as your insert for your key, or in a trigger, like so:

-- Won't work:
CREATE TABLE MyTable (seq_id NUMBER(1) DEFAULT rollover_seq.NEXTVAL);
ORA-00984: column not allowed here

-- Will work:
CREATE TABLE MyTable (seq_id NUMBER(1));

SELECT rollover_seq.NEXTVAL into :new.seq_id FROM dual;

To prevent this post from getting longer than it needs to be, I'll just send you to AskTom to read a discussion about the performance of using Oracle sequences as primary keys.

One final word, about the NOCACHE command I used up there. The default CACHE value is 20, so I had to use NOCACHE because it would try to CACHE more values than the sequence had. CACHE actually CACHEs the next few values for quicker access. But if ever there is a system failure, those previously-retrieved sequence numbers are gone. You'll be left with a "gap."

Of course, gaps may not be the end of the world for you. If they are, you'll need to use ALTER SEQUENCE to fix them. I've already mentioned two ways to get gaps with sequences, here's one more. If you do a transaction with NEXTVAL and then rollback, the sequence doesn't roll back to where you started. That'll create a gap, too.

Tuesday, February 21, 2006

Oracle Interview Questions

"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 Oracle. Whatever an Oracle guy would need to know."

Pretty soon thereafter I figured out that it was a pointless conversation to continue, although I did love the way he summarized dozens of very different positions into that one term: "Oracle Guy."

Nevertheless, it got me thinking. What makes for a good technical question? I have conducted, or been invited to, several interviews, so it got me to thinking about which questions were most effective at getting to the heart of the matter: "Will this candidate succeed technically in this role?"

Elements of a Good Technical Interview Question.

1. Must require knowledge of the area, including domain and philosophy, to solve.

I don't think it's enough that a candidate demonstrates proficiency with the technology. I like to see if they understand the overall philosophy of the product (Oracle, in this case): What needs was it meant to provide, what kind of problems was it designed to solve, how does it accomplish those tasks.

2. Must require overall technical skill/experience/understanding to solve.

I mean to say that a good question shows if the candidate understands (for example) relational databases themselves, not just a particular relational database. Carrying this example, does your C++ developer understand algorithms and software design?

3. Does not require knowledge of precise syntax

In my mind, anyone can look something up in a manual. You don't need to walk into an empty boardroom and know exactly how something is called. I don't think knowledge of syntax is a reliable indicator of the suitability of a candidate.

For example, you could have a good candidate "blank out" on the syntactic details, and you could also have a bad candidate who swallowed a reference manual the night before the interview.

Now, I would be worried if the candidate didn't know BASIC syntax. But I don't want to waste precious time asking basic questions, and if he is truly is that inexperienced, I should be able to figure it out in other ways.

4. Can be answered quickly.

Time is precious in an interview, and you shouldn't need long, convoluted questions to determine whether or not a candidate "gets it." A good question demonstrates quickly if the candidate is on the right path, or wouldn't get it regardless of how much time he had.

5. Is not a "gotcha"

I've met some interviewers that seem to use the opportunity not to evaluate the candidate, but to prove how clever they are (either to the candidate or the manager). They do this by asking really obscure tricks, sometimes referred to as "gotchas."

The problem with asking questions in the obscure corners is that even very experienced candidates may not have worked in that area and, if they have, may not have stumbled across that particular gem.

Just remember, the purpose of the interview isn't to make YOU look clever, and asking silly questions might make a great candidate think "what kind of clown show am I getting myself into?"

6. Has many possible solutions and approaches

The most effective questions I have ever asked, or been asked, were the ones that triggered lively technical discussions between the interviewer and the candidate. Why? You get to catch a glimpse not only of the candidates thinking process, but also how he communicates. I also like the added benefit of not punishing (in fact, rewarding) those that approach problems differently than the interviewer.

7. Requires asking for more information (or make assumptions).

Personally, I believe one of the keys to success in IT is to define a problem before approaching it. That's why I lean towards these types of questions. Did the candidate come back, or just try to solve it? If he came back, what kind of questions did he ask? In the face of an incompletely-defined problem, did he get stuck, or did he make some assumptions and continue? If so, what assumptions did he make?

8. Is relevant to the business/job being considered

Would you hire a cleaning service with award-winning carpet cleaning if you had hardwood floors? Would you hire a running back who excels in bad weather if you played in a dome? Would you hire an accomplished science-fiction writer to author your biography? No? Then why probe for technical skills that don't directly apply to the position you're attempting to fill?

Closing Thoughts

Incidentally, in the end I referred the manager in question to a couple of links which have hundreds of Oracle-interview questions. You can pick your favourites but, more likely, you can read them until you come up with good ideas that suit your needs.

As an aside, that last article was written by one of my preferred columnists, James Koopmann. He hasn't written much recently, but check out his archives, he's got some great articles there. For instance, check out his series on Oracle Session Tracing.

Tuesday, February 14, 2006


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 the end, you have a particularly-formatted XML file running on a BPEL Server.

To do this, Oracle provides the BPEL Process Manager. This is a BPEL IDE that will generate your BPEL XML file, and then run it on a BPEL Engine running on top of the Oracle Application Server. There are lots of other engines out there, BPEL and SOA is by no means unique to Oracle. In fact, here is an Open Source BPEL Server, ActiveBPEL.

BPEL has its roots in IBM and Microsoft's WSFL and XLANG. It was originally called BPEL4WS (BPEL for Web Services), but was renamed WS-BPEL. BPEL was meant to replace WSCI ("Whiskey"), which Oracle put forward with Sun and SAP a few years ago for the same purpose (designing the coordination of Web Services). And if that is not enough acronyms for you, I'll just note that BPEL uses WSDL to determine the format of the messages to be sent among the various Web Services.

For those wanting a quick hands-on "taste" of BPEL, here is what I did.

1. Start BPEL Server
2. Start the BPEL Designer, which is part of JDeveloper
3. Connected to the BPEL Console through a web browser
4. Create a new "BPEL Process Project" in JDeveloper
5. Made a few minor changes to the default project
6. Toggled to Source and saw my changes in the XML
7. Validated the changes using the "BPEL Validation Browser."
8. Generated a nice JPG of my BPEL business process

9. Deployed the project to the default BPEL Server I had started
10. Found the deployed service in the BPEL Console
11. From the BPEL Console, entered a parameter and initiated my BPEL process
12. Still from the BPEL Console, audited the flow and examined the XML messages that went back and forth

Thus concludes my introductory post on BPEL. Next I'm going to find some Web Services out there and build a more significant business process. When I do, I'll be sure to post an article with my JPG and what I thought of some of the bells and whistles.

Tuesday, February 07, 2006


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 the first list is longer than the second list, that means that some characters have no corresponding character. In that case, such characters are simply replaced with nothing (ie. deleted). So if you want to remove a character, put them at the end of your first list. It clearly doesn't make sense for the second list to be longer than the first list, nor does it make sense to have duplicates in the first list (although it certainly makes sense in the second list). If you do have inconsistent duplicates in your first list, Oracle seems to choose the character in the second list corresponding to its first occurence in the first list.

By the way, the two lists are actually passed as strings, but it makes more sense to picture them as lists. C programmers will be most comfortable, because they are used to interpreting strings as arrays of characters, and that's what we're dealing with here.

TRANSLATE is under chapter 6 (Functions) of Oracle's SQL Reference.

Let's look at a first, simple example to remove the dashes in a phone number and replace them with dots to form some kind of standard syntax.

scott@Robert> SELECT TRANSLATE('(619)455-1998', ')-(', '..') PHONE_NO FROM DUAL;


Complex constraints are a particularly good use for TRANSLATE. Let's presume you have a column that represents a special code in your company where the 4th character must be a digit, and the first digit. Well, you may know that INSTR can tell you where the first occurence of a particular character is, but you're looking for 10 characters, how can that be done? Quite easily, because with TRANSLATE you can change all characters to a single one.

CREATE TABLE MyTable (special_code VARCHAR2(32) CHECK (INSTR(TRANSLATE(special_code, '123456789', '000000000'), '0') = 4));

scott@Robert> INSERT INTO MyTable (special_code) VALUES ('abc123');

1 row created.

scott@Robert> INSERT INTO MyTable (special_code) VALUES ('abcd1234');
INSERT INTO MyTable (special_code) VALUES ('abcd1234')
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C008454) violated

That is an excellent technique of how TRANSLATE can be applied to a problem. As a side note, we can use RPAD to generate that second list for us if it is very long.

CREATE TABLE MyTable (special_code VARCHAR2(32) CHECK (INSTR(TRANSLATE(special_code, '123456789', RPAD('0', 9, '0')), '0') = 4));

Let's say you have a special column that should ONLY have numbers. How can you do that? Well how about you delete them all, and then see if you have an empty string? That would look something like this:

CREATE TABLE MyTable (numeric_only VARCHAR2(32) CHECK (TRANSLATE(numeric_only, '0123456789', '') IS NULL));

scott@Robert> INSERT INTO MyTable (numeric_only) VALUES ('abc123');

1 row created.

Oops? What happened? I'll show you this little feature of TRANSLATE:

scott@Robert> SELECT TRANSLATE('abc123', '0123456789', '') FROM DUAL;


The truth is, if the second list is empty, it seems to wipe out the entire string. Fortunately there is a pretty easy way around this. Just make sure the second string isn't empty. Map some non-important character to the same character, like so:

scott@Robert> SELECT TRANSLATE('abc123', '$0123456789', '$') FROM DUAL;


Let's try this new constraint:

CREATE TABLE MyTable (numeric_only VARCHAR2(32) CHECK (TRANSLATE(numeric_only, '$0123456789', '$') IS NULL));

scott@Robert> INSERT INTO MyTable (numeric_only) VALUES ('1234');

1 row created.

scott@Robert> INSERT INTO MyTable (numeric_only) VALUES ('abc123');
INSERT INTO MyTable (numeric_only) VALUES ('abc123')
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C008456) violated

Much better! You know, if we wanted to allow some non-numeric characters, but no more than 2, we could use LENGTH to do that.

CREATE TABLE MyTable (max_2 VARCHAR2(32) CHECK (LENGTH(TRANSLATE(max_2, '$0123456789', '$')) <= 2));

scott@Robert> INSERT INTO MyTable (max_2) VALUES ('abc123');
INSERT INTO MyTable (max_2) VALUES ('abc123')
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C008459) violated

scott@Robert> INSERT INTO MyTable (max_2) VALUES ('ab123');

1 row created.

As a quick aside, you'll notice that I specify my column names when I'm doing an insert. Why, you may ask, do I do that? Well don't you think what I'm doing is clearer to you, the reader, when I specify which values I'm inserting? Furthermore, if the schema of a table changes, my INSERT command will still work, assuming the schema change doesn't affect the columns I'm using, and that there are no new parameters that require a value.

You can do so much with TRANSLATE when combined with other functions, such as (to name just a few):

INSTR: gets the position of the first matching character
TRIM: take away spaces, or specific, single characters (also see LTRIM, RTRIM)
UPPER: converts all characters to upper case (also see LOWER)
RPAD: creates a string of the same character (also see LPAD)
LENGTH: calculates the length of a string

Despite the simplicity of the function, it comes in useful in complex constraints and transformations, both by itself and in concert with other Oracle functions.

As a final note, for those of you needing something other than the 1-to-1 switch that TRANSLATE allows, and instead needing to replace one substring with another, you want to use REPLACE. Doubtlessly that will be the topic of a later post.

Dan Morgan's Reference on TRANSLATE:

Thursday, February 02, 2006

Oracle and SOA

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 you may be used to. As opposed to large, proprietary applications that do everything, SOA is a design meant to try to integrate numerous and diverse software applications with common interfaces, in the name of code reuse/maintainability, and adaptibility. The notion of using a group of independent applications to accomplish a shared task is also sometimes referred to as grid computing.

Everyone knows that "Web Services" are one of the hottest things lately. An SOA is essentially a collection of such services, communicating with one another, generally through XML. (Of course I am over-simplifying things: SOA can involve any kind of self-contained service communicating in any way.)

SOA is not specific to any technology, indeed every "family" of technologies has its own SOA solution, and usually you can mix-and-match your own. However, open-source XML-based technologies such as BPEL, SOAP and WSDL are very commonly used.

For more information about SOA in general, visit OASIS's web site:
OASIS Open Standard for SOA

What is Oracle's SOA Solution?

It was inevitable that Oracle would join in the fray and devise SOA-based solutions. At the very least as part of its "Oracle Fusion" project to integrate PeopleSoft and JD Edwards. Notice the recent acquisitions of Kurian, Collaxa and Oblix were all steps along the SOA path.

Oracle's SOA solution leans heavily towards J2EE, their preferred language in which to develop your Web Services. They want you to use the perhaps poorly-named JDeveloper as your IDE for developing your Web Services with Oracle Containers (OC4J). JDeveloper includes the toolset Oracle Application Development Framework (ADF) which also includes Oracle TopLink for object-relational mapping. Of course they suggest you use the Oracle Application Server for these Web Services. Get more information on this from Oracle's whitepaper:
Oracle's JDeveloper White Paper

One of the new components is the BPEL Process Manager, acquired with Collaxa, which is an application that includes several tools to develop BPEL models and the underlying Web Services. This is where you define which services are called, and when. Grab this whitepaper for more on that:
Oracle's BPEL White Paper

For those who want more details, I am preparing a future post on BPEL, followed by some of these other acronyms I've mentioned. Note to Eddie: Half the presenters pronounced it "bipple" and the other half pronounced it "b-pull."

That summarizes my introductory post on Oracle and SOA. I will be writing articles with more meat and technical details over the next couple of weeks, but for those who are intrigued and just can't wait, here are some Oracle white papers on SOA:
Oracle E-Business and SOA
IDC: Oracle's SOA Platform

For more information about Oracle's SOA Solution, visit their web site:
Oracle's Main SOA Site


Have you been overlooking the wealth of handy tools and code on SourceForge? 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 what they did.

Want to avoid re-inventing a wheel? Find something suitable to your purposes at SourceForge.

Let's look at a quick example. The simplest example I could find is a little PL/SQL script that generates a package based on a table. I'm not promoting this particular script, I'm just using it as an example.

You can fetch the simple example here:

Download it, and inside the zip file you'll find some PL/SQL. Go ahead and run it: it will ask for a table name, and a package name. It will then generate some PL/SQL that you can use to generate a package for your table. Easy as that.

That is pretty typical of SourceForge. Hunt around and find some useful applications. There are PL/SQL Editors and PL/SQL Plug-Ins to various IDEs (like Eclipse). I've seen Java programs that can monitor your database. All sorts of things.

Might be a good place to share your open source tools, no? Especially the ones on which you're looking for feedback.

I recommend rooting through SourceForge from time to time if you don't already.

This page is powered by Blogger. Isn't yours?