HTTP Thinkoracle - Blogspot
Uploaded by
api-3823655HTTP Thinkoracle - Blogspot
Uploaded by
api-3823655OracleBlog: February 2006
OracleBlog
I love data. Collecting, storing, manipulating, studying and using data. Oracle may be
complicated, but it is the most powerful tool when it comes to data. That's why when I think
data, I think Oracle. I also enjoy writing, so I use this format to organise my thoughts. Please
feel free to discuss any thoughts you may have on the same topics, even old ones (I will see and
respond to such comments). You may want to start with "LIST ALL ARTICLES" under Archives.
Friday, February 24, 2006
About Me
The Oracle Effect Name: Robert
You thought the Tom Kyte Effect was the most Vollman
powerful force in the Oracle Blogging Universe. But Location: Calgary,
we were all mistaken! For today, I got a much higher Alberta, Canada
spike than ever. The source? Oracle itself!
I was born and raised in Ottawa,
and have lived in Calgary since
1991. I like playing sports
(hockey, soccer, ultimate,
basketball, you name it) and
military board games. I also
I am especially grateful that Gary Myers caught my enjoy reading, walking, and
sloppy error before a bunch of people read it. My playing with my 2 cats Lilly and
only regret is that I didn't really have anything Brutus. I'm a database
particular intelligent to say, and even if I did, it was application specialist, whatever
said so much better by (among other people) that is.
Howard Rogers.
View my complete profile
Since the link will change soon, here is a JPG to
mark my 15 minutes of nerd fame.
Best Links
● Ask Tom Kyte
● Oracle Docs
● Dan Morgan and PSOUG
● Steven Feuerstein
● Jonathan Lewis
● FAQ
● Connor McDonald
http://thinkoracle.blogspot.com/2006_02_01_archive.html (1 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
The Oak Table
●
● Cary Millsap and Hotsos
● Steve Adams and Ixora
● Anjo Kolk and OraPerf
● Dizwell Oracle Wiki
● My Personal Blog
Aggregators
Brian Duff's OraBlogs
❍
❍ Eddie Awad's OracleNA
❍ Pete Finnigan's Aggregator
❍ Oracle's Bloglist
// posted by Robert Vollman @ Friday, February 24, 2006 3 ❍ Oracle Base Aggregator
comments Top Blogs
❍ Oracle's Ask Tom Kyte
❍ Oracle Guru Jonathan Lewis
Wednesday, February 22, 2006
Blogger of the Year Eddie Awad
Never noticed this before
❍
Data Warehouser David Aldridge
So I was searching through the latest Oracle 10g
❍
Oracle Geek Lewis Cunningham
SQL Reference,
❍
❍ Database Expert James Koopmann
check out what I found on page 5-175: ❍ Dizwell's Howard Rogers
SELECT manager_id, last_name, salary, SUM(salary) ❍ Oracle Master Laurent Schneider
OVER (PARTITION BY manager_id ORDER BY salary ❍ Security Expert Pete Finnigan
RANGE UNBOUNDED PRECEDING) l_csum ❍ Oracle Award Winner Mark
FROM employees; Rittman
❍ Doug Burns
MANAGER_ID LAST_NAME SALARY L_CSUM ❍ Oracle ACE of the Year Dr. Tim
---------- --------- ------ ------ Hall
100 Mourgos 5800 5800 ❍ UKOUG's Andrew (Arfur C.) Clarke
100 Vollman 6500 12300 ❍ Newbie DBA Lisa Dobson
...
❍ Coffee-Drinking DBA Jon Emmons
I'm in the Oracle default HR schema! I'm employee ❍ Chris Foot
123 and I report directly to KING, the President! Of ❍ The Pythian DBA Team Blog
course, I'm one of the lowest paid managers, but ❍ DBA Don Seiler
still. ❍ DBA Coskan Gundogar
❍ Oracle WTF
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?
ARCHIVES
❍ LIST ALL ARTICLES
I also saw this in the latest version of Oracle 9i ❍ May 2005
http://thinkoracle.blogspot.com/2006_02_01_archive.html (2 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
documentation: ❍ June 2005
SQL Reference: ❍ July 2005
Page 365, 6-155 for the example of SUM. ❍ August 2005
Page 1532, 18-42 on using LEVEL Pseudo-Column. ❍ September 2005
❍ October 2005
and Sample Schemas: ❍ November 2005
Page 68, section 4-28 on Oracle's sample HR ❍ December 2005
Schema ❍ January 2006
So it must have been around for awhile. I think ❍ February 2006
that's so cool! I'm going to write Oracle and ask ❍ March 2006
them to fix my first name. I urge you to do the ❍ April 2006
same in your implementations: ❍ May 2006
❍ June 2006
UPDATE employees SET first_name = 'Robert' where ❍ July 2006
last_name = 'Vollman'; ❍ August 2006
❍ September 2006
// posted by Robert Vollman @ Wednesday, February 22, 2006 1
❍ October 2006
comments ❍ November 2006
❍ December 2006
❍ January 2007
Oracle Sequences ❍ February 2007
Proactively maintaining your database ... something ❍ March 2007
some people do only AFTER a problem of some kind. ❍ April 2007
❍ May 2007
Case in point, a customer recently asked us what
June 2007
sequences are being used as primary keys, what are
❍
October 2007
their maximum values, and are any in danger of
❍
running out. Guess what motivated that
Current Posts
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
http://thinkoracle.blogspot.com/2006_02_01_archive.html (3 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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
FROM ALL_SEQUENCES
ORDER BY sequences_left;
4. What happens when a sequence runs out?
Well that depends if its a roll-over or not.
CREATE SEQUENCE rollover_seq MINVALUE 1 START
WITH 1 INCREMENT BY 1 MAXVALUE 3 CYCLE
NOCACHE;
CREATE SEQUENCE runout_seq MINVALUE 1 START
WITH 1 INCREMENT BY 1 MAXVALUE 3 NOCYCLE
NOCACHE;
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;
ROLL RUNOUT
---------- ----------
1 1
2 2
3 3
2 4
http://thinkoracle.blogspot.com/2006_02_01_archive.html (4 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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));
CREATE OR REPLACE TRIGGER trig_seq BEFORE
INSERT ON MyTable
FOR EACH ROW
BEGIN
SELECT rollover_seq.NEXTVAL into :new.seq_id
FROM dual;
END;
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
http://thinkoracle.blogspot.com/2006_02_01_archive.html (5 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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.
// posted by Robert Vollman @ Wednesday, February 22,
2006 17 comments
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.
http://thinkoracle.blogspot.com/2006_02_01_archive.html (6 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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"
http://thinkoracle.blogspot.com/2006_02_01_archive.html (7 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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
http://thinkoracle.blogspot.com/2006_02_01_archive.html (8 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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.
http://www.orafaq.com/forum/t/9760/2/
http://www.databasejournal.com/features/oracle/
article.php/3085171
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.
// posted by Robert Vollman @ Tuesday, February 21, 2006 3
comments
Tuesday, February 14, 2006
BPEL
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
http://thinkoracle.blogspot.com/2006_02_01_archive.html (9 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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
http://thinkoracle.blogspot.com/2006_02_01_archive.html (10 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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.
// posted by Robert Vollman @ Tuesday, February 14, 2006 1
comments
Tuesday, February 07, 2006
TRANSLATE
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,
http://thinkoracle.blogspot.com/2006_02_01_archive.html (11 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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;
PHONE_NO
------------
619.455.1998
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:
http://thinkoracle.blogspot.com/2006_02_01_archive.html (12 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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;
T
-
The truth is, if the second list is empty, it seems to
http://thinkoracle.blogspot.com/2006_02_01_archive.html (13 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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;
TRA
---
abc
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));
http://thinkoracle.blogspot.com/2006_02_01_archive.html (14 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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
http://thinkoracle.blogspot.com/2006_02_01_archive.html (15 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
substring with another, you want to use REPLACE.
Doubtlessly that will be the topic of a later post.
Dan Morgan's Reference on TRANSLATE:
http://www.psoug.org/reference/translate_replace.
html
// posted by Robert Vollman @ Tuesday, February 07, 2006 5
comments
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
http://thinkoracle.blogspot.com/2006_02_01_archive.html (16 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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,
http://thinkoracle.blogspot.com/2006_02_01_archive.html (17 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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
// posted by Robert Vollman @ Thursday, February 02, 2006 3
comments
SourceForge
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 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:
http://sourceforge.net/projects/plsqlgenpkg/
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
http://thinkoracle.blogspot.com/2006_02_01_archive.html (18 of 19)1/9/2008 2:49:06 AM
OracleBlog: February 2006
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.
// posted by Robert Vollman @ Thursday, February 02, 2006 1
comments
http://thinkoracle.blogspot.com/2006_02_01_archive.html (19 of 19)1/9/2008 2:49:06 AM
OracleBlog: The Thomas Kyte Effect
OracleBlog
I love data. Collecting, storing, manipulating, studying and using data. Oracle may be
complicated, but it is the most powerful tool when it comes to data. That's why when I
think data, I think Oracle. I also enjoy writing, so I use this format to organise my
thoughts. Please feel free to discuss any thoughts you may have on the same topics,
even old ones (I will see and respond to such comments). You may want to start with
"LIST ALL ARTICLES" under Archives.
Tuesday, December 20, 2005
About Me
The Thomas Kyte Name: Robert Vollman
Effect Location: Calgary, Alberta,
Good grief! Canada
I saw a spike in my statcounter and I was born and raised in
I checked it out to see if I got Ottawa, and have lived in Calgary since
spammed. Instead I am the newest 1991. I like playing sports (hockey, soccer,
beneficiary of the "Thomas Kyte ultimate, basketball, you name it) and
Effect." military board games. I also enjoy reading,
walking, and playing with my 2 cats Lilly
Well, strictly speaking, the Thomas and Brutus. I'm a database application
Kyte Effect is when your blog is specialist, whatever that is.
referenced in a blog article of his,
but in my case I was simply added
View my complete profile
to his list of links. And then bam -
several hundred hits today.
With all due respect to Doug, Lisa
and Eddie, in one day I've got more
Best Links
● Ask Tom Kyte
referrals from his blog than Oracle Docs
everyone else put together.
●
● Dan Morgan and PSOUG
I told Tom we could "cash in" on the ● Steven Feuerstein
"Thomas Kyte Effect" with a few ● Jonathan Lewis
well-chosen ads, but sadly he ● FAQ
wouldn't go for it. ● Connor McDonald
http://thinkoracle.blogspot.com/2005/12/thomas-kyte-effect.html (1 of 3)1/9/2008 2:49:10 AM
OracleBlog: The Thomas Kyte Effect
The Oak Table
●
Well, as long as you're all here, I ● Cary Millsap and Hotsos
would like to say two things: ● Steve Adams and Ixora
1. Welcome to my blog! ● Anjo Kolk and OraPerf
2. Please look through my archives,
Dizwell Oracle Wiki
some of my more interesting reads
●
My Personal Blog
are in July/August.
●
Comments are very welcome, even
on older posts (I get auto-emailed
when someone posts something). Aggregators
Brian Duff's OraBlogs
❍
Also, I like to provide something Eddie Awad's OracleNA
useful in every post, so here it is: a
❍
Pete Finnigan's Aggregator
link to an article by Steven
❍
Oracle's Bloglist
Feuerstein about how to hide your
❍
Oracle Base Aggregator
code: ❍
http://htmldb.oracle.com/pls/otn/
f? Top Blogs
p=2853:4:6669219410898182474:: ❍ Oracle's Ask Tom Kyte
NO::P4_QA_ID:4102 ❍ Oracle Guru Jonathan Lewis
❍ Blogger of the Year Eddie Awad
// posted by Robert Vollman @ Tuesday, ❍ Data Warehouser David Aldridge
❍ Oracle Geek Lewis Cunningham
December 20, 2005 ❍ Database Expert James Koopmann
❍ Dizwell's Howard Rogers
Oracle Master Laurent Schneider
Comments: ❍
i feel that too, being listed on tom ❍ Security Expert Pete Finnigan
kyte blog generates much more ❍ Oracle Award Winner Mark Rittman
traffic than orablogs and google ❍ Doug Burns
together ;-) ❍ Oracle ACE of the Year Dr. Tim Hall
# posted by Laurent Schneider : ❍ UKOUG's Andrew (Arfur C.) Clarke
Wednesday, 21 December, 2005 ❍ Newbie DBA Lisa Dobson
❍ Coffee-Drinking DBA Jon Emmons
❍ Chris Foot
Post a Comment ❍ The Pythian DBA Team Blog
❍ DBA Don Seiler
<< Home ❍ DBA Coskan Gundogar
❍ Oracle WTF
ARCHIVES
❍ LIST ALL ARTICLES
http://thinkoracle.blogspot.com/2005/12/thomas-kyte-effect.html (2 of 3)1/9/2008 2:49:10 AM
OracleBlog: The Thomas Kyte Effect
❍ May 2005
❍ June 2005
❍ July 2005
❍ August 2005
❍ September 2005
❍ October 2005
❍ November 2005
❍ December 2005
❍ January 2006
❍ February 2006
❍ March 2006
❍ April 2006
❍ May 2006
❍ June 2006
❍ July 2006
❍ August 2006
❍ September 2006
❍ October 2006
❍ November 2006
❍ December 2006
❍ January 2007
❍ February 2007
❍ March 2007
❍ April 2007
❍ May 2007
❍ June 2007
❍ October 2007
http://thinkoracle.blogspot.com/2005/12/thomas-kyte-effect.html (3 of 3)1/9/2008 2:49:10 AM
OracleBlog: The Oracle Effect
OracleBlog
I love data. Collecting, storing, manipulating, studying and using data. Oracle may be
complicated, but it is the most powerful tool when it comes to data. That's why when I
think data, I think Oracle. I also enjoy writing, so I use this format to organise my
thoughts. Please feel free to discuss any thoughts you may have on the same topics,
even old ones (I will see and respond to such comments). You may want to start with
"LIST ALL ARTICLES" under Archives.
Friday, February 24, 2006
About Me
The Oracle Effect Name: Robert
You thought the Tom Kyte Effect was the Vollman
most powerful force in the Oracle Blogging Location: Calgary,
Universe. But we were all mistaken! For today, Alberta, Canada
I got a much higher spike than ever. The
source? Oracle itself! I was born and raised in Ottawa,
and have lived in Calgary since
1991. I like playing sports
(hockey, soccer, ultimate,
basketball, you name it) and
military board games. I also
enjoy reading, walking, and
I am especially grateful that Gary Myers playing with my 2 cats Lilly and
caught my sloppy error before a bunch of Brutus. I'm a database
people read it. My only regret is that I didn't application specialist, whatever
really have anything particular intelligent to that is.
say, and even if I did, it was said so much
better by (among other people) Howard View my complete profile
Rogers.
Since the link will change soon, here is a JPG
to mark my 15 minutes of nerd fame.
Best Links
● Ask Tom Kyte
● Oracle Docs
● Dan Morgan and PSOUG
http://thinkoracle.blogspot.com/2006/02/oracle-effect.html (1 of 3)1/9/2008 2:49:14 AM
OracleBlog: The Oracle Effect
● Steven Feuerstein
● Jonathan Lewis
● FAQ
● Connor McDonald
● The Oak Table
● Cary Millsap and Hotsos
● Steve Adams and Ixora
● Anjo Kolk and OraPerf
● Dizwell Oracle Wiki
● My Personal Blog
Aggregators
// posted by Robert Vollman @ Friday, February 24, 2006 Brian Duff's OraBlogs
❍
❍ Eddie Awad's OracleNA
❍ Pete Finnigan's Aggregator
❍ Oracle's Bloglist
Comments: ❍ Oracle Base Aggregator
> got a much higher spike than ever
Nice to read, i'd be interested about how Top Blogs
many accesses you are talking in more ❍ Oracle's Ask Tom Kyte
concrete numbers. ❍ Oracle Guru Jonathan Lewis
Blogger of the Year Eddie Awad
BR,
❍
Data Warehouser David Aldridge
Martin
❍
❍ Oracle Geek Lewis Cunningham
# posted by Anonymous : Friday, 24 February, 2006
❍ Database Expert James Koopmann
❍ Dizwell's Howard Rogers
Martin, ❍ Oracle Master Laurent Schneider
❍ Security Expert Pete Finnigan
Very roughly speaking, it looks like I've gotten ❍ Oracle Award Winner Mark
about 300 unique visitors in the last 2 days Rittman
from Oracle's web site. ❍ Doug Burns
❍ Oracle ACE of the Year Dr. Tim
Robert Hall
# posted by Robert Vollman : Friday, 24 February, ❍ UKOUG's Andrew (Arfur C.) Clarke
2006 ❍ Newbie DBA Lisa Dobson
❍ Coffee-Drinking DBA Jon Emmons
Chris Foot
FYI, your link remains to be on the http://
❍
❍ The Pythian DBA Team Blog
http://thinkoracle.blogspot.com/2006/02/oracle-effect.html (2 of 3)1/9/2008 2:49:14 AM
OracleBlog: The Oracle Effect
www.oracle.com/technology/index.html site, DBA Don Seiler
❍
as of 2:09 P.M. EST on 20060226. ❍ DBA Coskan Gundogar
# posted by Hae-Kwang : Sunday, 26 February, 2006 ❍ Oracle WTF
ARCHIVES
❍ LIST ALL ARTICLES
Post a Comment ❍ May 2005
June 2005
<< Home
❍
❍ July 2005
❍ August 2005
❍ September 2005
❍ October 2005
❍ November 2005
❍ December 2005
❍ January 2006
❍ February 2006
❍ March 2006
❍ April 2006
❍ May 2006
❍ June 2006
❍ July 2006
❍ August 2006
❍ September 2006
❍ October 2006
❍ November 2006
❍ December 2006
❍ January 2007
❍ February 2007
❍ March 2007
❍ April 2007
❍ May 2007
❍ June 2007
❍ October 2007
http://thinkoracle.blogspot.com/2006/02/oracle-effect.html (3 of 3)1/9/2008 2:49:14 AM
OracleBlog: Bookmark This Page
OracleBlog
I love data. Collecting, storing, manipulating, studying and using data. Oracle may be
complicated, but it is the most powerful tool when it comes to data. That's why when I
think data, I think Oracle. I also enjoy writing, so I use this format to organise my
thoughts. Please feel free to discuss any thoughts you may have on the same topics,
even old ones (I will see and respond to such comments). You may want to start with
"LIST ALL ARTICLES" under Archives.
Tuesday,
About Me
March 28, 2006
Name: Robert Vollman
Bookmark Location: Calgary, Alberta, Canada
This Page
To ease the I was born and raised in Ottawa, and have lived in
task of Calgary since 1991. I like playing sports (hockey,
browsing my soccer, ultimate, basketball, you name it) and military board games.
archives, I've I also enjoy reading, walking, and playing with my 2 cats Lilly and
put together Brutus. I'm a database application specialist, whatever that is.
an organised
list of my View my complete profile
previous posts.
Bookmark this,
because I will
keep it up to
Best Links
date. I will also ● Ask Tom Kyte
include a link ● Oracle Docs
to this post ● Dan Morgan and PSOUG
under Archives. ● Steven Feuerstein
● Jonathan Lewis
Note: If you FAQ
comment on
●
Connor McDonald
an older
●
The Oak Table
article, I will
●
Cary Millsap and Hotsos
get an ●
automatic ● Steve Adams and Ixora
email. So I will ● Anjo Kolk and OraPerf
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (1 of 21)1/9/2008 2:49:16 AM
OracleBlog: Bookmark This Page
see it and I will ● Dizwell Oracle Wiki
respond to it, ● My Personal Blog
so please feel
free to do so!
For Newbies: Aggregators
Tuesday, Brian Duff's OraBlogs
❍
December 20, ❍ Eddie Awad's OracleNA
2005 ❍ Pete Finnigan's Aggregator
20 Beginner ❍ Oracle's Bloglist
Oracle ❍ Oracle Base Aggregator
Questions
Top Blogs
Friday, June ❍ Oracle's Ask Tom Kyte
17, 2005 Oracle Guru Jonathan Lewis
Asking For
❍
❍ Blogger of the Year Eddie Awad
Help - Tips on ❍ Data Warehouser David Aldridge
where to go Oracle Geek Lewis Cunningham
and how to do
❍
Database Expert James Koopmann
it.
❍
❍ Dizwell's Howard Rogers
Wednesday, ❍ Oracle Master Laurent Schneider
July 19, 2006 ❍ Security Expert Pete Finnigan
Finding ❍ Oracle Award Winner Mark Rittman
Information - ❍ Doug Burns
How do you ❍ Oracle ACE of the Year Dr. Tim Hall
find answers to ❍ UKOUG's Andrew (Arfur C.) Clarke
other ❍ Newbie DBA Lisa Dobson
questions? ❍ Coffee-Drinking DBA Jon Emmons
❍ Chris Foot
Thursday, July
The Pythian DBA Team Blog
14, 2005
❍
DBA Don Seiler
Oracle Docs -
❍
DBA Coskan Gundogar
For when
❍
Oracle WTF
people tell you
❍
to RTFM!
ARCHIVES
Best Practises: ❍ LIST ALL ARTICLES
May 2005
Thursday,
❍
June 2005
October 19,
❍
July 2005
2006
❍
❍ August 2005
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (2 of 21)1/9/2008 2:49:16 AM
OracleBlog: Bookmark This Page
3 Easy Ways to ❍ September 2005
Improve Your ❍ October 2005
PL/SQL - ❍ November 2005
Improving your ❍ December 2005
code through ❍ January 2006
instrumentation ❍ February 2006
and bulk ❍ March 2006
processing. ❍ April 2006
May 2006
Friday, March
❍
09, 2007 ❍ June 2006
40 Tips From ❍ July 2006
Tom (Kyte) ❍ August 2006
❍ September 2006
Tuesday, June ❍ October 2006
14, 2005 ❍ November 2006
Bind Variables ❍ December 2006
in PL/SQL - ❍ January 2007
Short answer: ❍ February 2007
PL/SQL binds ❍ March 2007
all variables ❍ April 2007
(with some ❍ May 2007
exceptions like ❍ June 2007
dynamic SQL) ❍ October 2007
Tuesday,
January 24,
2006
Gathering
Requirements
Wednesday,
March 01, 2006
Handling
exceptions - A
how-to guide.
Friday, March
10, 2006
Handling
Performance
Issues
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (3 of 21)1/9/2008 2:49:16 AM
OracleBlog: Bookmark This Page
Wednesday,
August 17,
2005
Keeping Tables
Small - In
terms of
number of
rows, not
columns.
Improve
performance.
Monday,
October 31,
2005
Oracle
Packages -
And why/how
you should use
them.
Monday, July
11, 2005
Specifying
INSERT
Columns - Why
it's a good
habit.
Wednesday,
May 18, 2005
Steven
Feuerstein on
Refactoring
Tuesday, July
26, 2005
Use
Constraints - A
how-to guide
for people to
whom I BEG to
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (4 of 21)1/9/2008 2:49:16 AM
OracleBlog: Bookmark This Page
let the
database
handle the
data's integrity.
Monday, July
25, 2005
Use Views -
Why they're
handy.
Tuesday,
September 12,
2006
Using
Numerical
Fields - Don't
use them for
fields that
aren't real
numbers.
Oracle
Packages:
Wednesday,
October 12,
2005
DBMS_OUTPUT.
PUT_LINE
Thursday,
November 24,
2005
DBMS_PIPE -
For
communication
between
sessions
Sunday,
August 14,
2005
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (5 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
UTL_HTTP -
Including an
example of
how to get a
stock quote
from the
Internet.
Top 20 Lists:
Sunday,
January 15,
2006
Oracle DO
NOTs - From
AskTom
Tuesday,
December 20,
2005
20 Beginner
Oracle
Questions
Monday,
September 12,
2005
20 Oracle
Lessons - After
my first few
months of
blogging.
Monday,
December 19,
2005
20 PL/SQL
Coding Tips -
Inspired by an
AskTom thread
Tuesday,
October 17,
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (6 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
2006
Software
Vendor
Customer
Support - How
to improve
your luck when
dealing with
support (ok
there are only
14)
Book Reviews:
Wednesday,
June 22, 2005
Expert One-on-
One - A couple
of mistakes
from my
favourite
Oracle book.
Monday, May
16, 2005
Optimizing
Oracle
Performance
(Millsap, Holt)
Wednesday,
November 02,
2005
Oracle
Insights: Tales
of the Oak
Table
Thursday, June
23, 2005
PL/SQL Books
- A list of my
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (7 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
three favourite
PL/SQL books
Templates:
Thursday, June
30, 2005
OOP in PL/
SQL? Yep
Wednesday,
July 13, 2005
Stored
Procedure
template
Great Debates:
Tuesday, June
21, 2005
Natural vs
Synthetic keys
- Choosing
primary keys.
Wednesday,
March 29, 2006
Optimizer -
Should we be
overriding it in
our application?
Monday,
September 19,
2005
PL/SQL Code
Storage: Files
vs In-DB
Packages
Wednesday,
January 18,
2006
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (8 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
PL/SQL vs J2EE
- Where should
you put the
business logic?
NULLs:
Friday,
September 09,
2005
NULLs in
COUNT - Why
counting on a
column might
get you a
different total.
Friday, June
10, 2005
NULLs in Oracle
Tuesday, May
17, 2005
NULL vs
Nothing - ANSI
SQL is unlike
programming
languages
because NULL
is not nothing.
Gotchas!
Monday, June
13, 2005
Blank Lines
and SQLPlus
Friday, May 20,
2005
Multiple
Foreign Keys
on the Same ID
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (9 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
Monday, July
04, 2005
SQLCODE and
SQLERRM in
INSERTs
How-To
Guides:
Wednesday,
September 14,
2005
Analyzing
Query
Performance -
using
SQLTRACE and
TKPROF
Friday, May 04,
2007
ANSI Joins - Or
do you want to
stick with the
old school
style?
Tuesday,
February 14,
2006
BPEL - For SOA
Tuesday,
January 17,
2006
Bulk Binding:
FORALL -
Improve
performance of
bulk updates.
Thursday,
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (10 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
September 22,
2005
Column Name
as a Variable
Thursday, June
16, 2005
Common Table
Column Types
- Two tables
with columns
on the same
type, but not
actually related.
Wednesday,
August 24,
2005
COMPUTE -
How to
emulate a
feature found
in other
languages
Saturday, June
18, 2005
Connect By -
Heirarchical
queries,
something you
need to know.
Monday, June
20, 2005
Decode -
CASE's
precursor.
Thursday,
November 10,
2005
DUAL Table -
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (11 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
You've seen it,
what is it?
Thursday, May
19, 2005
Dynamically
assigning size
of varchar2 -
You do it in
other
languages, can
you do it in PL/
SQL (and how)?
Wednesday,
May 25, 2005
ENUM in Oracle
- Emulating a
common
programming
feature in PL/
SQL.
Friday, July 01,
2005
Extra Columns
in a GROUP BY
Tuesday, May
09, 2006
Finding Nearby
Rows. Three
methods of
solving similar
requirements.
Monday,
August 01,
2005
Import Export
Monday, May
28, 2007
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (12 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
Multirow
Inserts - Does
Oracle support
the ANSI SQL
standard of
inserting
multiple rows?
No. But here's
how you can
fake it.
Thursday, May
26, 2005
NOCOPY Hint -
Improve
performance
by changing
how variables
are passed to
PL/SQL
procedures.
Saturday, July
23, 2005
Oracle
BOOLEAN - PL/
SQL has
BOOLEAN,
here's how to
emulate it in
Oracle's SQL
Friday, July 29,
2005
Oracle By
Example -
Bringing
several
concepts
together to
solve a
problem.
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (13 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
Friday, June
24, 2005
Oracle Client -
How to install
Tuesday, July
04, 2006
Oracle and Java
Monday,
October 30,
2006
Oracle
Passwords -
Answering
your common
questions
Thursday,
December 15,
2005
Oracle and Perl
Thursday,
February 02,
2006
Oracle and
SOA - Covers
Oracle's
Service-
Oriented
Architecture at
a high level
Wednesday,
February 22,
2006
Oracle
Sequences -
This is the one
Oracle carried
on its main
page
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (14 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
Thursday,
September 01,
2005
Pivot and
Crosstab
Queries - A
very useful
technique for
turning rows
into columns,
and vice versa
Monday, April
03, 2006
Pivot Queries
Using Variable
Number of
Columns - Part
2 on pivot
queries, when
you don't know
how many
columns you
need in
advance.
Friday,
September 30,
2005
PL/SQL
Procedure Call
Overhead - Is
there one?
Friday, August
11, 2006
PL/SQL
Procedure Call
Overhead Re-
visited - By
Zsolt Lajosfalvi
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (15 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
Saturday,
September 02,
2006
Protecting PL/
SQL Code -
Using wrap.
Tuesday, May
24, 2005
Random
Numbers -
How to
generate them.
Monday,
November 21,
2005
RAW Datatype
Tuesday, June
27, 2006
Recursion vs
Iteration -
What are they,
what are the
advantages of
each one?
Tuesday, June
13, 2006
Refreshing
Data - A High-
level picture of
the flow and
what to keep in
mind when
designing your
data import
strategy
Thursday,
October 06,
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (16 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
2005
ROWNUM and
ROWID - And
how they're
used to solve
various issues,
and improve
retrieval times.
Tuesday,
February 07,
2006
TRANSLATE -
What it is, how
to use it.
Wednesday,
August 10,
2005
UNION ALL -
How and when
to avoid
performance
hits
Monday, April
17, 2006
Updating Views
- Can you do
it, and if so,
when.
Monday, June
27, 2005
Using Bad
Names in
Oracle
Tuesday,
October 04,
2005
Using DECODE
to exploit
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (17 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
COUNT/NULL
feature -
Applying
DECODE and
our knowledge
of COUNT/
NULL together
in a little trick
to speed up a
query.
Wednesday,
June 15, 2005
Variable
Constraints -
Can you use
variables in
constraints?
How?
Friday,
November 10,
2006
View
Constraints -
Can you
manage
integrity using
views?
Monday, July
18, 2005
Which instance
am I in?
Tuesday, May
30, 2006
Windowing
Clauses - How
they are used
to empower
your analytic
functions.
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (18 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
Other:
Monday,
February 26,
2007
Fun With Tom
Kyte - Get a
laugh out of
Oracle's king
of wit
Thursday, April
05, 2007
Oracle Beefs -
Here are mine.
What are yours?
Tuesday,
October 31,
2006
Oracle Gurus -
What makes an
Oracle guru?
Tuesday,
February 21,
2006
Oracle
Interview
Questions -
How to come
up with useful
ones.
Sunday, June
03, 2007
SQL Interview
Questions -
Here's what I
ask. Prepare
for your
interviews.
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (19 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
Friday, May 25,
2007
What Makes a
Great Oracle
Blog?
// posted by
Robert
Vollman @ Tuesday,
March 28, 2006
Comments:
Fear not, I've
got two posts
coming up
That's good.
For a moment I
thought you're
experiencing
the blogger
burnout effect.
Good luck in
your new job.
I've put
together an
organised list
of my previous
posts
Unlike other
blogging
platforms (like
WordPress for
example), one
of the
disadvantages
of Google's
Blogger is its
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (20 of 21)1/9/2008 2:49:17 AM
OracleBlog: Bookmark This Page
inability to
categorize
posts so that
you can easily
and
automatically
view an
archives page
just like the
one you just
posted (nicely
organized BTH).
# posted by
Eddie Awad :
Tuesday, 28
March, 2006
Post a
Comment
<< Home
http://thinkoracle.blogspot.com/2006/03/bookmark-this-page.html (21 of 21)1/9/2008 2:49:17 AM
OracleBlog: May 2005
OracleBlog
I love data. Collecting, storing, manipulating, studying and using data. Oracle may be
complicated, but it is the most powerful tool when it comes to data. That's why when I
think data, I think Oracle. I also enjoy writing, so I use this format to organise my
thoughts. Please feel free to discuss any thoughts you may have on the same topics,
even old ones (I will see and respond to such comments). You may want to start with
"LIST ALL ARTICLES" under Archives.
Thursday, May 26, 2005
About Me
On Vacation Name: Robert Vollman
I am taking a much needed vacation Location: Calgary, Alberta,
to Italy for my brother's wedding. Canada
I'll post new material on June 13th, so
please check back then. I was born and raised in
Ottawa, and have lived in Calgary since
In the mean time, here is how you 1991. I like playing sports (hockey,
can pass your break time, apart from soccer, ultimate, basketball, you name it)
reading my recent posts (including and military board games. I also enjoy
one today) or visiting the links on the reading, walking, and playing with my 2
right: cats Lilly and Brutus. I'm a database
application specialist, whatever that is.
1. The Dizwell Forum has some great
Oracle discussions on many topics. View my complete profile
http://www.phpbbserver.com/phpbb/
index.php?mforum=dizwellforum
Best Links
2. Oracle magazine has great articles, ● Ask Tom Kyte
especially Ask Tom and Steven ● Oracle Docs
Feuerstein's PL/SQL Best Practises: ● Dan Morgan and PSOUG
● Steven Feuerstein
http://www.oracle.com/technology/ ● Jonathan Lewis
index.html ● FAQ
Connor McDonald
3. Lots of other Oracle professionals
●
http://thinkoracle.blogspot.com/2005_05_01_archive.html (1 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
have blogs. I enjoy many of them, but The Oak Table
●
based on frequency of updates and ● Cary Millsap and Hotsos
number of Oracle-related posts, my ● Steve Adams and Ixora
favourites are David Aldridge and ● Anjo Kolk and OraPerf
Niall Litchfield: ● Dizwell Oracle Wiki
My Personal Blog
http://oraclesponge.blogspot.com/
●
http://www.niall.litchfield.dial.pipex.
com/
Please look under "Comments" for
Aggregators
more ideas, and of course contribute Brian Duff's OraBlogs
❍
your own. ❍ Eddie Awad's OracleNA
❍ Pete Finnigan's Aggregator
Arrivederci! ❍ Oracle's Bloglist
❍ Oracle Base Aggregator
// posted by Robert Vollman @ Thursday, May
26, 2005 1 comments Top Blogs
❍ Oracle's Ask Tom Kyte
❍ Oracle Guru Jonathan Lewis
NOCOPY Hint ❍ Blogger of the Year Eddie Awad
What is NOCOPY? ❍ Data Warehouser David Aldridge
❍ Oracle Geek Lewis Cunningham
'NOCOPY' is an optional 'hint' to tell Database Expert James Koopmann
the PL/SQL 'compiler' not to go
❍
Dizwell's Howard Rogers
through the overhead of making a
❍
Oracle Master Laurent Schneider
copy of the variable, instead just send
❍
Security Expert Pete Finnigan
a reference. This is generally because ❍
we don't plan on modifying it within ❍ Oracle Award Winner Mark Rittman
the procedure. ❍ Doug Burns
❍ Oracle ACE of the Year Dr. Tim Hall
My first surprise was that you ❍ UKOUG's Andrew (Arfur C.) Clarke
couldn't use "IN NOCOPY." Isn't ❍ Newbie DBA Lisa Dobson
NOCOPY your way of telling Oracle ❍ Coffee-Drinking DBA Jon Emmons
you don't plan on messing around Chris Foot
with the parameter? Yes, but you
❍
The Pythian DBA Team Blog
CAN'T mess with IN parameters, try it!
❍
❍ DBA Don Seiler
CREATE OR REPLACE PROCEDURE ❍ DBA Coskan Gundogar
MyProc (in_value IN number) ❍ Oracle WTF
ARCHIVES
AS
BEGIN
in_value := 3; ❍ LIST ALL ARTICLES
http://thinkoracle.blogspot.com/2005_05_01_archive.html (2 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
END; ❍ May 2005
June 2005
PLS-00363: expression 'IN_VALUE'
❍
July 2005
cannot be used as an assignment
❍
August 2005
target
❍
❍ September 2005
Therefore, it is always safe to send IN ❍ October 2005
parameters by reference, making ❍ November 2005
NOCOPY redundant. ❍ December 2005
❍ January 2006
My second surprise was that you had ❍ February 2006
to specify NOCOPY for an OUT
March 2006
parameter. Because by definition isn't
❍
April 2006
an OUT parameter stating that you
❍
May 2006
plan on modifying the variable? Why ❍
would it be an OUT variable if you ❍ June 2006
weren't touching it? So why would ❍ July 2006
you NOT want NOCOPY? The answer ❍ August 2006
(like so many) comes from Ask Tom: ❍ September 2006
❍ October 2006
http://asktom.oracle.com/pls/ask/f? ❍ November 2006
p=4950:8::::: ❍ December 2006
F4950_P8_DISPLAYID:2047154868085 ❍ January 2007
❍ February 2007
Tom explains one situation where March 2007
you want a copy rather than a
❍
April 2007
reference for an OUT or IN OUT
❍
May 2007
parameter. When you change a
❍
June 2007
NOCOPY parameter, it changes right ❍
away, instead of upon successful ❍ October 2007
completion of the stored procedure.
❍ Current Posts
Imagine you modified the parameter,
but threw an exception before
successful completion. But that
parameter has been changed and the
calling procedure could be stuck with
a bogus value.
Despite how much I trust Tom,
everybody knows that I don't believe
things until I see for myself. And
neither should you! Besides, things
change. Here's my example.
http://thinkoracle.blogspot.com/2005_05_01_archive.html (3 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
CREATE OR REPLACE PROCEDURE
NoCopyProc (in_value IN OUT
NOCOPY number)
AS
x number;
BEGIN
DBMS_OUTPUT.PUT_LINE(in_value || '
NoCopyProc');
in_value := 2;
x := 1/0;
END;
CREATE OR REPLACE PROCEDURE
CopyProc (in_value IN OUT number)
AS
x number;
BEGIN
DBMS_OUTPUT.PUT_LINE(in_value || '
CopyProc');
in_value := 4;
x := 1/0;
END;
CREATE OR REPLACE PROCEDURE
InterProc (in_value IN OUT NOCOPY
number)
AS
BEGIN
IF (in_value = 1) THEN NoCopyProc
(in_value);
ELSE CopyProc(in_value);
END IF;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
CREATE OR REPLACE PROCEDURE
MyProc
AS
the_value NUMBER(1);
BEGIN
the_value := 1;
InterProc(the_value);
http://thinkoracle.blogspot.com/2005_05_01_archive.html (4 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
DBMS_OUTPUT.PUT_LINE(the_value);
the_value := 3;
InterProc(the_value);
DBMS_OUTPUT.PUT_LINE(the_value);
END;
BEGIN MyProc; END;
1 NoCopyProc
2
3 CopyProc
3
For an excellent and more detailed
overview of NOCOPY, complete with
examples, restrictions and
performance analysis, I once again
refer you to Steven Feuerstein's
writings. Although I encourage you to
add his books to your collection, this
chapter happens to be on-line for
free:
Oracle PL/SQL Programming Guide to
Oracle8i Features
http://www.unix.org.ua/orelly/oracle/
guide8i/ch10_01.htm
So what is a guy to do?
Well, first of all, it was suggested to
me that I should find a more gender-
neutral way of summing up an article.
Allow me to rephrase.
So what should we do?
1. Understand what NOCOPY means
and its uses and restrictions (by
following those links)
2. Take advantage of NOCOPY when
you want the performance advantage
of avoiding the cost of the temporary
http://thinkoracle.blogspot.com/2005_05_01_archive.html (5 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
storage for OUT or IN OUT
parameters.
3. Avoid NOCOPY when you don't
want the side effects if the procedure
fails early.
Remember, in the end, that NOCOPY
is just a "hint" and Oracle will do
whatever it wants anyway. Like all
hints, you have to ask yourself what
makes it necessary, and what makes
you think Oracle is going to choose
incorrectly.
// posted by Robert Vollman @ Thursday, May
26, 2005 2 comments
Wednesday, May 25, 2005
ENUM in Oracle
What is ENUM?
ENUM is short for enumeration. Its a
useful programming type that
contains an ordered list of string
values. The programmer can define
the valid values depending on their
application.
Some good examples of ENUMs
would be days and months, or
something like directions ('North',
'South', 'East', 'West').
Is there an Oracle 'ENUM' type?
No, not really. But there are other
ways of accomplishing the same
thing.
For tables, just set it to a string and
add a constraint that it is within a
certain set.
http://thinkoracle.blogspot.com/2005_05_01_archive.html (6 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
CREATE TABLE atable (
col1 varchar2(10),
CONSTRAINT cons_atable_col1
CHECK (col1 IN ('Monday', 'Tuesday',
'Wednesday', 'Thursday',
'Friday', 'Saturday', 'Sunday'))
);
SQL> INSERT INTO atable (col1)
VALUES ('Monday');
1 row created.
SQL> INSERT INTO atable (col1)
VALUES ('Blingday');
insert into atable (col1) values
('Blingday')
*
ERROR at line 1:
ORA-02290: check constraint
(ROBERT.CONS_ATABLE_COL1)
violated
What happens if you use this type in
a procedure? Will the constraint be
checked? No.
CREATE OR REPLACE PROCEDURE
MyProc (in_col IN atable.col1%TYPE)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE(in_col);
END;
SET SERVEROUTPUT ON;
EXEC MyProc('Monday');
EXEC MyProc('Blingday');
So can you create a package subtype
for this? That would be more elegant
anyway.
But according to Oracle PL/SQL
http://thinkoracle.blogspot.com/2005_05_01_archive.html (7 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
Programming by Steven Feuerstein
Chapter 4, I don't think you can
(check comments for any refutations
to this).
http://www.amazon.com/exec/
obidos/ASIN/0596003811/
qid=1117039808/sr=2-1/
ref=pd_bbs_b_2_1/102-9543590-
3979349
I think the best thing to do in this
case is to create a procedure to
validate your input.
CREATE OR REPLACE PROCEDURE
MyCheck (in_col IN atable.col1%TYPE)
AS
BEGIN
IF (in_col NOT IN ('Monday', 'Tuesday',
'Wednesday', 'Thursday', 'Friday',
'Saturday', 'Sunday')) THEN
-- Throw Exception here, be sure to
catch it in MyProc!!
NULL;
END IF;
END;
This approach is consistent with
Steven Feuerstein's approach to
programming. He suggests
separating these things into separate
procedures. Then when a future
release of Oracle supports a concept,
or when you figure out how to do it,
you can make the change in a single
place.
So what is a guy to do?
1. If you want to use enum in a table,
use a check constraint.
2. If you want to use enum in a
stored procedure, write a separate
http://thinkoracle.blogspot.com/2005_05_01_archive.html (8 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
procedure to validate the input.
// posted by Robert Vollman @ Wednesday,
May 25, 2005 1 comments
Tuesday, May 24, 2005
Random Numbers
Let me get your advice on this one.
Here's the situation, you need an
evenly distributed sequence of
random integers from 1 to 20. You
decided to use the Oracle random
number package 'dbmsrand'.
Incidentally, I decided to do it this
way after searching Ask Tom.
http://asktom.oracle.com/~tkyte/
Misc/Random.html
http://asktom.oracle.com/pls/ask/f?
p=4950:8:::::
F4950_P8_DISPLAYID:831827028200
There is a link to his web site to the
right. Bookmark it.
While you're at it, bookmark Dan
Morgan's page (link on the right).
http://www.psoug.org/reference/
dbms_random.html
Ok, back to the story.
After you read the instructions on
using it, you probably write
something like this in your PL/SQL
stored procedure:
AS
http://thinkoracle.blogspot.com/2005_05_01_archive.html (9 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
random_value number(2,0);
BEGIN
random_value := dbms_random.value
(0,20);
Now you notice whoops, you're
getting some 0s. You don't want 0.
Plus you're not getting very many
20s. So you do this instead
random_value := dbms_random.value
(1,21);
Much better! But whoops - you're
getting the occasional 21! So you
scratch your brain and do this:
random_value := dbms_random.value
(1,21);
random_value := MOD(random_value,
20) + 1;
That should pour the 21s into the 1
pile, which you noticed is very, very
slightly lower than the others.
Now you think you're getting the
values you want. So here are my
questions:
1. Will this truly generate an even
sample of numbers from 1 to 20? Will
there be the right number of 1s and
20s?
2. Is there a better way?
3. Say you're generating a very large
sequence, thinking about
performance, do you see any
problems with this approach I should
consider?
Here is the complete solution:
--------------------
@utlraw
http://thinkoracle.blogspot.com/2005_05_01_archive.html (10 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
@prvtrawb.plb
@dbmsoctk
@prvtoctk.plb
@dbmsrand
create table value_holder as (f_value
NUMBER(2,0));
create or replace procedure
ValuePlacer (number_to_generate IN
number)
AS
random_value number(2,0);
BEGIN
for x in 1..number_to_generate
LOOP
random_value := dbms_random.value
(1,21);
random_value := MOD(random_value,
20) + 1;
insert into value_holder values
(random_value);
END LOOP;
END;
exec ValuePlacer(1000);
select f_value, count (f_value) from
value_holder group by f_value;
-----------------
Thanks!
// posted by Robert Vollman @ Tuesday, May
24, 2005 1 comments
Friday, May 20, 2005
Multiple Foreign Keys
on the Same ID
You can't set the same foreign key for
two different columns with the same
constraint:
http://thinkoracle.blogspot.com/2005_05_01_archive.html (11 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
create table atable (
id varchar2(65) primary key);
create table btable (
a_id1 varchar2(65),
a_id2 varchar2(65));
ALTER TABLE btable
ADD CONSTRAINT btable_fkey_both
FOREIGN KEY (a_id1, a_id2)
REFERENCES atable(id, id)
ON DELETE SET NULL;
ERROR at line 1:
ORA-00957: duplicate column name
ALTER TABLE btable
ADD CONSTRAINT btable_fkey_both
FOREIGN KEY (a_id1, a_id2)
REFERENCES atable(id)
ON DELETE SET NULL;
ERROR at line 1:
ORA-02256: number of referencing
columns must match referenced
columns
But you can do it if you break it up
into separate constraints:
ALTER TABLE btable
ADD CONSTRAINT btable_fkey1
FOREIGN KEY (a_id1)
REFERENCES atable (id)
ON DELETE SET NULL;
Table altered.
ALTER TABLE btable
ADD CONSTRAINT btable_fkey2
FOREIGN KEY (a_id2)
REFERENCES atable (id)
ON DELETE SET NULL;
http://thinkoracle.blogspot.com/2005_05_01_archive.html (12 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
Table altered.
Note: Monday is a holiday in Canada,
no update until Tuesday.
// posted by Robert Vollman @ Friday, May 20,
2005 0 comments
Thursday, May 19, 2005
Dynamically assigning
size of varchar2
I thought I would declare my variable-
lengthed variables with a pre-defined
constant. Why?
1. I'd be sure they matched up
everywhere.
2. I could change them all in a single
place if required.
But I don't think you can do that in PL/
SQL.
CREATE OR REPLACE PACKAGE
test_constants IS
MAX_LEN CONSTANT NUMBER := 32;
END test_constants;
Package created.
SET SERVEROUTPUT ON
-- Anonymous block
DECLARE
x VARCHAR2(test_constants.
MAX_LEN); -- Can't do this
BEGIN
x := 'Test ' || test_constants.MAX_LEN;
dbms_output.put_line(x);
END;
ERROR at line 2:
http://thinkoracle.blogspot.com/2005_05_01_archive.html (13 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
ORA-06550: line 2, column 31:
PLS-00491: numeric literal required
But what about other types?
Substitute NVARCHAR2, VARCHAR,
CHAR, NCHAR, or STRING if you like,
same result. Incidentally the "N" just
means you are specifying the length
in bytes instead of characters.
So what is a guy to do?
1. Just put a comment before every
number, and make it a practise to
make sure they all match.
DECLARE
x VARCHAR2(32); -- test_constants.
MAX_LEN = 32
But here is another option.
2. Create a table with the types you
want, and then use those types.
CREATE TABLE TEST_CONSTANTS (
MAX_LEN VARCHAR2(32)
);
Table created.
DECLARE
x TEST_CONSTANTS.MAX_LEN%TYPE;
BEGIN
x := 'Test';
dbms_output.put_line(x);
END;
// posted by Robert Vollman @ Thursday, May
19, 2005 4 comments
Wednesday, May 18, 2005
http://thinkoracle.blogspot.com/2005_05_01_archive.html (14 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
Steven Feuerstein on
Refactoring
Refactoring for PL/SQL Developers
By Steven Feuerstein
From Oracle Magazine January/
February 2005
http://www.oracle.com/technology/
oramag/oracle/05-jan/o15plsql.html
Steven Feuerstein is one of my
favourite PL/SQL authors, especially
when he lays off the socio-political
tangents.
I especially enjoyed his article on
refactoring PL/SQL code. I had a few
thoughts to add to it.
My additional thoughts on
Refactoring:
1. One of the advantages that also
bears note is that re-factoring your
code can encourage code re-use.
Clean, modular, well-written code is
easy to re-use, reducing future
programming efforts.
2. However, one of the disadvantages
of code re-factoring is that you can
introduce unknown factors to a
(presumably) working, trusted
procedure. There is an expense to re-
testing and re-validating the
modified code. Even if you FIXED
errors, some other procedures might
have been written in such a way that
they depend on them, and they will
now fail.
My additional imperfections in the
http://thinkoracle.blogspot.com/2005_05_01_archive.html (15 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
unfactored code sample:
1. Inline documentation
I think Steven failed to point out the
most egregious transgression: lack of
inline documentation! I believe
almost every procedure needs it, at
LEAST a 'header' but preferrably also
some comments that explain in plain
language what was intended by the
code. Steven did go ahead and add
some in-line documentation, but
didn't draw any attention to that.
* Note: Perhaps due to his friendly
nature, I am referring to him in this
post in the familiar. Certainly no
disrespect is intended.
2. Bad variable names
If this article was to be re-written, I
would change the initial code sample
to use very bad variable names and
then re-name them as part of the
"refactored" version. I think that's a
very common problem.
My comments on the refactored
version:
1. utl_file_constants
Rather than define my own file
utl_file_constants, I would use
something that was already defined
and possibly tied in. I mean, there's a
REASON that 32767 was used. You
are bound by a particular (probably
OS-level, or DB-level) limitation that
is probably defined in some package
or some configuration value, or right
in UTL_FILE.
http://thinkoracle.blogspot.com/2005_05_01_archive.html (16 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
If you define your own and then all of
a sudden the 32767 limitation is
changed at the OS or DB level, you
STILL have to go change all your
private constant packages. I mean
that's a bit better than having to
change magic numbers in your code,
but we can do a little better, I think.
get_next_line_from_file isn't even
aware of that limitation. How would
this program fail if you set the value
to, say, '4' instead of '32767' in
utl_file_constants?
It appears like Steven didn't even use
his utl_file_constant for 32767 in the
final version (Code Listing 7). Why
not? The answer is probably that you
can not dynamically define the length
of a string. That is, of course, a PL/
SQL restriction. I suppose we should
be happy with whatever magic
numbers we can remove.
2. compare_latest_read
I'm really not sure I would have
created "compare_latest_read". That
seems like we are overdoing it. I
mean this is not a big procedure and
its entire point is to do what is in that
procedure anyway. Why introduce the
overhead of another procedure
(assuming there is any overhead)?
Think of it this way, you are passing
in the lines. Instead of that, you could
pass in the files and
compare_latest_read can call
get_next_line_from_file to get the
lines. That would be reasonable. But
now, all of a sudden, the main
http://thinkoracle.blogspot.com/2005_05_01_archive.html (17 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
procedure is doing nothing but
initialising and calling a procedure.
So basically "compare_latest_read" is
one reasonable change away from
already doing all the work.
So why is it even necessary? It is only
done once ... How is the purpose of
this internal procedure significantly
different from the purpose of the
overall procedure? I suppose this is
just an opinion, and a matter of taste,
but I really think the extra procedure
is unnecessary.
Other comments and questions:
1. VARCHAR2 length
In the procedure Steven uses
VARCHAR2 as a variable-lengthed
string even though we know the
maximum size (32767). Is there any
value in specifying VARCHAR2
(32767) instead of VARCHAR2 in the
final version (as the IN or OUT
parameters)? Is there any advantage
to that? Is it even possible?
2. Procedures vs Functions
Any reason why Steven uses
procedures instead of functions?
3. Cleanup: closing an unopened file
In "cleanup", will there be a problem
if you UTL_FILE.fclose a file that
hasn't been opened? Because that will
happen if there is an exception while
opening the first file when cleanup
closes both.
4. Handling "WHEN OTHERS"
http://thinkoracle.blogspot.com/2005_05_01_archive.html (18 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
WHEN OTHERS - cleanup .... without
any error message written to screen
or log?? For shame! Steven said
earlier in the article you would re-
raise the exception, but in the end he
didn't.
Plus the problem with re-raising an
exception is you are actually getting a
NEW exception. If it is checked later,
the error messages will say the error
was created at this new line number
instead of the original spot. Might as
well fully handle the exception right
here when we catch it.
Conclusion:
A sure sign of a good article is that it
gets you thinking. Steven Feuerstein
rarely fails to achieve that. I have
shared these thoughts with him, and
I'll follow-up with any answers or
clarification he provides.
I noticed Harm Vershuren had some
thoughts in his blog as well:
http://technology.amis.nl/blog/index.
php?p=317
// posted by Robert Vollman @ Wednesday,
May 18, 2005 1 comments
Tuesday, May 17, 2005
NULL vs Nothing
In Oracle, there is a difference
between 'null' and nothing at all. Here
is my story.
I discovered this when playing with
default values. First, I created a table
that had a default value for one
http://thinkoracle.blogspot.com/2005_05_01_archive.html (19 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
column. Then I tried to insert a row
that had nothing at all, and it
wouldn't use the default value. So I
tried inserting a row with null, and it
didn't assign the default value.
Observe.
SQL> create table atable (
2 last_name varchar2(32) not null,
3 first_name varchar2(32) not null,
4 rating smallint default 0);
Table created.
SQL> insert into atable values
('Smith', 'John', null);
1 row created.
SQL> select * from atable;
LAST_NAME FIRST_NAME RATING
--------------------------------
--------------------------------
----------
Smith John
Notice it inserted NULL, and not the
default value.
SQL> insert into atable values
('Smith', 'John');
insert into atable values ('Smith',
'John')
*
ERROR at line 1:
ORA-00947: not enough values
Why doesn't this work? You may
know this already, but in the words of
Dan Morgan: "By not specifying
column names you are signifying that
you are providing values for ALL
columns. This is why it is a very bad
practice as doing an ALTER TABLE
http://thinkoracle.blogspot.com/2005_05_01_archive.html (20 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
ADD immediately invalidates all SQL
statements."
So let's do it the proper way and see
if there is a difference.
SQL> insert into atable (last_name,
first_name) values ('Smith', 'John');
1 row created.
SQL> select * from atable;
LAST_NAME FIRST_NAME RATING
--------------------------------
--------------------------------
----------
Smith John
Smith John 0
And there is a difference. Excellent.
Out of curiousity between the two
ways of inserting rows, I tried this:
SQL> insert into atable (last_name,
first_name, rating) values ('Smith',
'John', null);
1 row created.
SQL> select * from atable;
LAST_NAME FIRST_NAME RATING
--------------------------------
--------------------------------
----------
Smith John
Smith John 0
Smith John
So I was thinking, why isn't this null
being replaced with the default value?
Isn't the default value there for
instances to replace null? If you're as
skeptical as I am, it will make sense
http://thinkoracle.blogspot.com/2005_05_01_archive.html (21 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
after this second test, where I add the
"NOT NULL" constraint.
drop table atable;
create table atable (
last_name varchar2(32) not null,
first_name varchar2(32) not null,
rating smallint default 0 NOT NULL);
SQL> insert into atable (last_name,
first_name, rating) values ('Smith',
'John', null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into
("ROBERT"."ATABLE"."RATING")
That's when I figured out where I was
confused. There are 3 things you can
assign to a column when doing an
insert:
1. a value (eg: 0)
2. null
3. nothing at all
See, there is a difference between #2
(null) and #3 (nothing at all). To wit:
1. Using null (#2) is 'ok' if you are
inserting without specifying columns.
Nothing at all (#3) is not.
2. Null (#2) will not be replaced by a
default value when inserting, whereas
nothing at all (#3) will.
When I started looking at stored
procedures, I found even more
difference between NULL and
nothing. My second story starts off as
an investigation of passing NULL to
stored procedures.
There is no such thing as "NOT NULL"
for stored procedure parameters.
http://thinkoracle.blogspot.com/2005_05_01_archive.html (22 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
I noticed that the "NOT NULL"
keyword can't be used in procedure
parameters (nor can NULL).
Apparently that is for creating tables
only. You can not force input
parameters to a procedure to be non-
null. All you can do is start your
procedure by verifying the input
parameters.
SQL> create or replace procedure
MyProc1 (some_value IN number NOT
NULL)
2 AS
3 BEGIN
4 NULL;
5 END;
6/
Warning: Procedure created with
compilation errors.
Nor can you allow it to be NULL.
SQL> create or replace procedure
MyProc2 (some_value IN number
NULL)
2 AS
3 BEGIN
4 NULL;
5 END;
6/
Warning: Procedure created with
compilation errors.
You can, however, assign default
values. However, note the difference
between assigning "NULL" and
assigning nothing at all.
SQL> create or replace procedure
MyProc3 (some_value IN number := 0)
http://thinkoracle.blogspot.com/2005_05_01_archive.html (23 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
2 AS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE
(some_value);
5 NULL;
6 END;
7/
Procedure created.
SQL> exec MyProc3();
0
PL/SQL procedure successfully
completed.
SQL> exec MyProc3(NULL);
PL/SQL procedure successfully
completed.
SQL> exec MyProc3(1);
1
PL/SQL procedure successfully
completed.
Bottom line, there is a difference
NULL and nothing at all for both
tables and stored procedures. So
what is a guy to do?
1. Understand there is a difference
between NULL and nothing
2. For tables, use "NOT NULL" and for
stored procedures, verify the input
manually
3. Use default values for both tables
and stored procedures when passing
nothing at all.
// posted by Robert Vollman @ Tuesday, May
17, 2005 3 comments
http://thinkoracle.blogspot.com/2005_05_01_archive.html (24 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
Monday, May 16, 2005
Optimizing Oracle
Performance (Millsap,
Holt)
There are certain "camps" in the
worldwide Oracle community. For
example, there is the "Oak Table
Network" of "Oracle scientists" who
seek thorough understandings of
issues backed up by details, tests and
proofs. Contrasting is the "Silver
Bullet" family of field-tested generals
who prefer rules of thumb and quick
fixes even it means some false
understandings and occasionally
being wrong. Cary Millsap (of the Oak
Table Network) stands as someone
respected by both sides.
Cary Millsap worked at Oracle for 10
years on system performance before
co-founding Hotsos in 1999 (http://
www.hotsos.com - register for free).
He is one of the most trusted sources
on matters of Oracle system
performance, and "Optimizing Oracle
Performance" is considered his finest
work (4.5 out of 5 stars on Amazon).
The best way to learn more about
him is to see for yourself. Here are
some of his most popular articles:
"Diagnosing Performance Problems"
from Oracle Magazine. A brief
summary of what is covered in this
book:http://www.oracle.com/
technology/oramag/oracle/04-jan/
o14tech_perf.html
"Introduction", the first chapter from
"Optimizing Oracle
Performance."Chapter 1: http://www.
http://thinkoracle.blogspot.com/2005_05_01_archive.html (25 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
oreilly.com/catalog/optoraclep/
chapter/ch01.pdf
"Case Study", the 12th chapter from
"Optimizing Oracle
Performance."Chapter 12 (Case
Study): http://www.oreillynet.com/
pub/a/network/excerpt/
optimizing_oracle_chap12/index.html
"Performance Management: Myths
and Facts." One of his most popular
articles.https://secure.hotsos.com/
downloads/visitor/00000024.pdf
"Why a 99%+ Database Buffer Cache
Hit Ratio is Not Ok." Another of his
more popular articles.http://www.
oradream.com/pdf/Why%20a%2099%
20Cahe%20Hit%20Ratio%20is%20Not%
20OK.pdf
While everyone will have their own
favourite parts of the book, I think
most readers would agree that
getting a good taste of the author's
performance tuning philosophy is
one of the highlights. "Method R", not
to be confused with "System R" (ie.
SQL), is not about looking at
STATSPACK, cache hit ratios, or V$
tables and guessing. The author
wanted to devise a system to identify
and resolve the top performance
concerns of an organisation with
reliable, predictable results. The first
few chapters put this method in
writing in perhaps the best way since
the introduction of "YAPP" (Anjo Kolk).
"The performance enhancement
possible with a given improvement is
limited by the fraction of the
http://thinkoracle.blogspot.com/2005_05_01_archive.html (26 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
execution time that the improved
feature is used." - Amdahl's Law
After several years of research, the
author discovered that Extended SQL
Trace Data was at the centre of
"Method R". Some of the articles
should give you a good taste of what
Extended SQL Trace data is, if you
didn't know already. By the time you
finish reading this book you will
know exactly how to collect and
interpret all the little "ela=17101
p1=10 p2=2213 p3=1 ..." within into
something meaningful. For some,
that justifies the price tag right there.
So in essence I would have re-named
this book "Method R: Optimizing
Oracle Performance Using Extended
SQL Trace Data," because that is
basically what this book is about.
There are some reasonably "stand-
alone" chapters on other topics, for
instance on the Oracle Fixed View
tables (Chapter 8) and on Queueing
Theory (Chapter 9), but that is not
the primary focus of the book.
Those that are expecting a more
broad treatment of the subject of
performance tuning may be
justifiably disappointed that it
basically covers only this narrow
aspect. However, it is covered very
well, and it isn't really covered
anywhere else. The author makes no
apologies for this, claiming that
extended SQL trace data is the only
resource you will ever need for
diagnosing and solving performance
problems.
"You cannot extrapolate detail from
http://thinkoracle.blogspot.com/2005_05_01_archive.html (27 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
an aggregate." - Cary Millsap's
preference of SQL extended trace
data over fixed views (system-wide
average performance characteristics)
Indeed, some people might contend
that the author spends a little too
much time stating his beliefs,
defending them, and patting himself
on the back. But I think it adds a
certain flavour to the book, and I
respect an author who backs up his
statements.
"Proving that V$ data are superior to
extended SQL trace data because of
the 'missing time' issue is analagous
to proving that its safer to be in a
room with a hungry bear if you'll just
close your eyes." - Cary Millsap
The book can be a tough read in the
sense that the author goes very deep
into the material, and generally each
subject is treated thoroughly.
Chapter 9 on Queueing Theory can
be a particularly overwhelming
chapter. But the material is served in
bite-size pieces, and broken up with
tips, tricks, stories, diagrams and
code (sometimes 3+ pages worth at a
time, embedded directly in the
middle of a chapter). There are even
worthwhile exercises at the end of
each chapter.
In the end, I enjoyed this book and
I'm glad I got it. I don't consider it a
"must have" for your Oracle
collection, but I definitely feel it is
quite worthwhile. I recommend it
especially to those who read his
articles and were very comfortable
with his writing style and philosophy,
http://thinkoracle.blogspot.com/2005_05_01_archive.html (28 of 29)1/9/2008 2:49:22 AM
OracleBlog: May 2005
and also to those that need a book on
extended SQL trace data (because
this is basically the only one). But
even those in the "Silver Bullet" camp
will be glad to add another tool to
their belt.
Thumbs up.
http://www.coug.ab.ca/Resources/
BookReviews/MillsapsOOPByRVollman.
htm
// posted by Robert Vollman @ Monday, May
16, 2005 0 comments
http://thinkoracle.blogspot.com/2005_05_01_archive.html (29 of 29)1/9/2008 2:49:22 AM
OracleBlog: Never noticed this before
OracleBlog
I love data. Collecting, storing, manipulating, studying and using data. Oracle may be
complicated, but it is the most powerful tool when it comes to data. That's why when I
think data, I think Oracle. I also enjoy writing, so I use this format to organise my
thoughts. Please feel free to discuss any thoughts you may have on the same topics,
even old ones (I will see and respond to such comments). You may want to start with
"LIST ALL ARTICLES" under Archives.
Wednesday,
About Me
February 22, 2006
Name: Robert Vollman
Never Location: Calgary, Alberta, Canada
noticed
this before I was born and raised in Ottawa, and have lived in
So I was Calgary since 1991. I like playing sports (hockey,
searching soccer, ultimate, basketball, you name it) and military board
through the games. I also enjoy reading, walking, and playing with my 2 cats
latest Oracle 10g Lilly and Brutus. I'm a database application specialist, whatever
that is.
SQL Reference,
check out what I
View my complete profile
found on page 5-
175:
SELECT
manager_id,
Best Links
● Ask Tom Kyte
last_name, Oracle Docs
salary, SUM
●
Dan Morgan and PSOUG
(salary)
●
Steven Feuerstein
OVER (PARTITION
●
BY manager_id ● Jonathan Lewis
ORDER BY salary ● FAQ
RANGE ● Connor McDonald
UNBOUNDED ● The Oak Table
PRECEDING) ● Cary Millsap and Hotsos
l_csum ● Steve Adams and Ixora
http://thinkoracle.blogspot.com/2006/02/never-noticed-this-before.html (1 of 4)1/9/2008 2:49:25 AM
OracleBlog: Never noticed this before
FROM employees; ● Anjo Kolk and OraPerf
● Dizwell Oracle Wiki
MANAGER_ID ● My Personal Blog
LAST_NAME
SALARY L_CSUM
----------
---------
------ ------
Aggregators
100 Mourgos Brian Duff's OraBlogs
❍
5800 5800 ❍ Eddie Awad's OracleNA
100 Vollman ❍ Pete Finnigan's Aggregator
6500 12300 ❍ Oracle's Bloglist
... ❍ Oracle Base Aggregator
I'm in the Oracle
default HR
Top Blogs
Oracle's Ask Tom Kyte
schema! I'm ❍
employee 123 ❍ Oracle Guru Jonathan Lewis
and I report ❍ Blogger of the Year Eddie Awad
directly to KING, ❍ Data Warehouser David Aldridge
the President! Of ❍ Oracle Geek Lewis Cunningham
course, I'm one ❍ Database Expert James Koopmann
of the lowest ❍ Dizwell's Howard Rogers
paid managers, ❍ Oracle Master Laurent Schneider
but still. ❍ Security Expert Pete Finnigan
Oracle Award Winner Mark Rittman
Of course, the
❍
Doug Burns
first name is ❍
Shonta. But I can ❍ Oracle ACE of the Year Dr. Tim Hall
dream, right? I ❍ UKOUG's Andrew (Arfur C.) Clarke
can pretend that ❍ Newbie DBA Lisa Dobson
I'm one of Lex de ❍ Coffee-Drinking DBA Jon Emmons
Haan's fictional ❍ Chris Foot
colleagues, right? ❍ The Pythian DBA Team Blog
❍ DBA Don Seiler
I also saw this in DBA Coskan Gundogar
the latest version
❍
Oracle WTF
of Oracle 9i
❍
documentation:
SQL Reference: ARCHIVES
Page 365, 6-155 ❍ LIST ALL ARTICLES
for the example ❍ May 2005
of SUM. ❍ June 2005
Page 1532, 18- ❍ July 2005
http://thinkoracle.blogspot.com/2006/02/never-noticed-this-before.html (2 of 4)1/9/2008 2:49:25 AM
OracleBlog: Never noticed this before
42 on using ❍ August 2005
LEVEL Pseudo- ❍ September 2005
Column. ❍ October 2005
November 2005
and Sample
❍
December 2005
Schemas:
❍
January 2006
Page 68, section
❍
February 2006
4-28 on Oracle's
❍
March 2006
sample HR ❍
Schema ❍ April 2006
❍ May 2006
So it must have ❍ June 2006
been around for ❍ July 2006
awhile. I think ❍ August 2006
that's so cool! I'm ❍ September 2006
going to write ❍ October 2006
Oracle and ask
November 2006
them to fix my
❍
December 2006
first name. I urge
❍
January 2007
you to do the ❍
same in your ❍ February 2007
implementations: ❍ March 2007
❍ April 2007
UPDATE ❍ May 2007
employees SET ❍ June 2007
first_name = ❍ October 2007
'Robert' where
last_name =
'Vollman';
// posted by Robert
Vollman @ Wednesday,
February 22, 2006
Comments:
Oracle: Sure we
can..but are you
sure you dont
want salary
column updated ?
# posted by
http://thinkoracle.blogspot.com/2006/02/never-noticed-this-before.html (3 of 4)1/9/2008 2:49:25 AM
OracleBlog: Never noticed this before
Anonymous :
Thursday, 23
February, 2006
Post a
Comment
<< Home
http://thinkoracle.blogspot.com/2006/02/never-noticed-this-before.html (4 of 4)1/9/2008 2:49:25 AM
OracleBlog: June 2005
OracleBlog
I love data. Collecting, storing, manipulating, studying and using data. Oracle may be
complicated, but it is the most powerful tool when it comes to data. That's why when I think data,
I think Oracle. I also enjoy writing, so I use this format to organise my thoughts. Please feel free
to discuss any thoughts you may have on the same topics, even old ones (I will see and respond
to such comments). You may want to start with "LIST ALL ARTICLES" under Archives.
Thursday, June 30, 2005
OOP in PL/SQL? Yep
I was recently speaking with someone who was stunned to find out that object-
oriented programming is available in PL/SQL (as of version 9, basically).
I guess I was stunned he didn't know this, but then again, I guess there isn't much
fanfare about it.
Inheritance? Yep.
Polymorphism? Yep.
Encapsulation? Yep.
I gave him this basic, bare-bones template. It isn't much, but it can get someone
started.
CREATE OR REPLACE TYPE some_object AS OBJECT (
some_variable NUMBER(10),
MEMBER FUNCTION member_function RETURN NUMBER,
MEMBER FUNCTION member_function (l_overloading IN NUMBER) RETURN NUMBER,
MEMBER PROCEDURE member_procedure,
-- Static functions can be used with an instance of the object
-- They can NOT reference any other non-static member functions or variables
STATIC FUNCTION static_function (l_value IN NUMBER DEFAULT 1) RETURN
NUMBER,
-- Constructors must return self. Constructors are optional
CONSTRUCTOR FUNCTION some_object (some_variable NUMBER) RETURN SELF AS
RESULT,
-- Used for comparison purposes: GROUP BY, ORDER BY, DISTINCT
http://thinkoracle.blogspot.com/2005_06_01_archive.html (1 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
-- No parameters allowed, returns NUMBER, DATE, VARCHAR2, CHAR or REAL
MAP MEMBER FUNCTION map_member_function RETURN NUMBER
-- ORDER takes one parameter of same type, and returns NUMBER
-- You may only have EITHER MAP OR ORDER
-- ORDER MEMBER FUNCTION order_member_function (some_other_object IN
some_object) RETURN NUMBER
)
INSTANTIABLE -- Or "NOT INSTANTIABLE" if this is a base class only
NOT FINAL -- Or "FINAL" if this class will NOT have a sub-class
;
CREATE OR REPLACE TYPE composition_object AS OBJECT (
composed_object some_object
);
CREATE OR REPLACE TYPE derived_object
UNDER some_object (
OVERRIDING MEMBER PROCEDURE member_procedure
);
CREATE OR REPLACE TYPE BODY some_object AS
MEMBER FUNCTION member_function RETURN NUMBER
IS
BEGIN
-- The "SELF" isn't necessary, but is always available by default in member
functions
RETURN SELF.some_variable;
END member_function;
MEMBER FUNCTION member_function (l_overloading IN NUMBER) RETURN NUMBER
IS
BEGIN
RETURN l_overloading;
END member_function;
MEMBER PROCEDURE member_procedure
IS
BEGIN
NULL;
END member_procedure;
-- Note: Unlike with packages, no private functions or declarations are allowed.
http://thinkoracle.blogspot.com/2005_06_01_archive.html (2 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
-- MEMBER FUNCTION hidden_proc RETURN NUMBER ...
-- Remember, static functions can't access SELF variables
STATIC FUNCTION static_function (l_value IN NUMBER DEFAULT 1) RETURN NUMBER
IS
BEGIN
RETURN l_value;
END static_function;
CONSTRUCTOR FUNCTION some_object (some_variable NUMBER) RETURN SELF AS
RESULT
AS
BEGIN
SELF.some_variable := some_variable;
-- It will automatically return self, don't even try to return anything else
RETURN;
END some_object;
MAP MEMBER FUNCTION map_member_function RETURN NUMBER IS
BEGIN
RETURN SELF.some_variable;
END map_member_function;
-- ORDER MEMBER FUNCTION order_member_function (some_other_object IN
some_object) RETURN NUMBER IS
-- BEGIN
-- IF some_other_object.some_variable < SELF.some_variable THEN RETURN 1;
-- ELSIF some_other_object.some_variable > SELF.some_variable THEN RETURN -1;
-- ELSE RETURN 0;
-- END IF;
-- END order_member_function;
END;
CREATE OR REPLACE TYPE BODY derived_object AS
OVERRIDING MEMBER PROCEDURE member_procedure
IS
BEGIN
NULL;
END member_procedure;
END;
-- Test!
DECLARE
http://thinkoracle.blogspot.com/2005_06_01_archive.html (3 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
-- You MUST instantiate it to use it. A NULL object is hard to use.
my_some_object some_object := some_object(0);
my_composition_object composition_object := composition_object
(my_some_object);
my_number NUMBER;
BEGIN
my_number := my_composition_object.composed_object.member_function;
my_number := some_object.static_function(my_number);
END;
// posted by Robert Vollman @ Thursday, June 30, 2005 2 comments
Constraints
No blog from me. But here is an excellent one from Jeff Hunter that I wish I'd
written.
http://marist89.blogspot.com/2005/06/deferrable-constraints_29.html
Also, check his Comments to see Doug Burns' link to an article by Chris Date.
// posted by Robert Vollman @ Thursday, June 30, 2005 1 comments
Monday, June 27, 2005
Using Bad Names in Oracle
In Oracle, you can't start a table, procedure or variable name with a number.
SQL> CREATE TABLE 123Table (aval NUMBER);
CREATE TABLE 123Table (aval NUMBER)
*
ERROR at line 1:
ORA-00903: invalid table name
You can get around this simply using quotes.
SQL> CREATE TABLE "123Table" (aval NUMBER);
Table created.
This will take the name very literally. So literally, in fact, that you can no longer rely
on Oracle's typical case-insensitivity.
SQL> drop table "123TABLE";
drop table "123TABLE"
*
ERROR at line 1:
http://thinkoracle.blogspot.com/2005_06_01_archive.html (4 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
ORA-00942: table or view does not exist
You can also use quotes to use reserved words.
SQL> CREATE TABLE "TABLE" ("NUMBER" NUMBER);
Table created.
// posted by Robert Vollman @ Monday, June 27, 2005 0 comments
Friday, June 24, 2005
Oracle Client
Here are the simple steps required for setting up an Oracle Client on a PC.
You will need:
- A working Oracle Server :)
- A compatible Oracle Client
- The service name, domain name, host name and port
1. Install Oracle SQL*Plus client on your PC
This should be quick and easy, accept all defaults.
2. Modify %ORACLE_HOME%\network\admin\tnsnames.ora
SRV_NAME.WHATEVER.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = srv_name)
)
)
3. Check %ORACLE_HOME%\network\admin\sqlnet.ora
NAMES.DEFAULT_DOMAIN = whatever.com
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
4. Modify %ORACLE_HOME%\network\admin\listener.ora
http://thinkoracle.blogspot.com/2005_06_01_archive.html (5 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = srv_name)
)
5. Test connection
sqlplus username/password@srv_name
// posted by Robert Vollman @ Friday, June 24, 2005 0 comments
Thursday, June 23, 2005
PL/SQL Books
There are 3 PL/SQL books considered above the pack.
I settled on Scott Urman's book, but that's only because I got a deal on it when
Nexus Computer Books in Calgary went out of business. I like it, and it is ranked
#1 among PL/SQL books on Amazon.com.
Oracle9i PL/SQL Programming
Scott Urman
32.99
664 Pages
4 on 7 reviews
Rank in sales: #5460
I really like Steven Feuerstein's work, most of which can be found on the web. He's
got Q&A, Puzzlers, and regular articles on Oracle's site. You can find sample
chapters on-line. Despite the fact that I already have a good PL/SQL book, I might
get this one, too. He also has a "Best Practises" book.
Oracle PL/SQL Programming, Third Edition
Steven Feuerstein
34.62
1018 Pages
4 on 66 reviews
Rank in sales: #17659
Connor McDonald of the Oak Table Network has a highly rated PL/SQL book as
well. He also has an Internet presense. His site is awesome, he's got some great
stuff, so his book ought to be great, too. I wouldn't mind picking this up.
Mastering Oracle PL/SQL: Practical Solutions
http://thinkoracle.blogspot.com/2005_06_01_archive.html (6 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
Connor McDonald
32.99
648 Pages
4.5 on 5 reviews
Rank in sales: #106559
There are other PL/SQL books out there, but any of these 3 is probably your best
bet. Leave some comments if you have a preference for the 3, or know of any other
really great PL/SQL books to consider.
// posted by Robert Vollman @ Thursday, June 23, 2005 0 comments
Wednesday, June 22, 2005
Expert One-on-One
The general consensus is that Tom Kyte's "Expert One-on-One Oracle" is the best
Oracle book available, and most Oracle professionals have a copy on their shelves.
In Chapter 1 of the first edition, you'll see a scheduling algorithm that is supposed
to avoid double-bookings. Here is an example of how to double-book a room:
CREATE TABLE resources (resource_name VARCHAR2(25) primary key);
CREATE TABLE schedules (resource_name VARCHAR2(25) references resources,
start_time DATE, end_time DATE);
INSERT INTO resources (resource_name) VALUES ('Room A');
INSERT INTO schedules (resource_name, start_time, end_time) VALUES ('Room A',
'01-Jan-04', '04-Jan-04');
VARIABLE new_start_time VARCHAR2(32)
EXEC :new_start_time := '02-Jan-04'
VARIABLE new_end_time VARCHAR2(32)
EXEC :new_end_time := '03-Jan-04'
VARIABLE room_name VARCHAR2(25)
EXEC :room_name := 'Room A'
-- If the count comes back 0, the room is yours
SELECT COUNT (*) FROM schedules WHERE resource_name = :room_name
AND (start_time BETWEEN :new_start_time AND :new_end_time
OR
end_time BETWEEN :new_start_time AND :new_end_time);
This returns 0, which means the room is double-booked!!
Here is the fixed version from the 2nd edition:
http://thinkoracle.blogspot.com/2005_06_01_archive.html (7 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
ops$tkyte@ORA10GR1> select count(*)
2 from schedules
3 where resource_name = :room_name
4 and (start_time <= :new_end_time)
5 and (end_time >= :new_start_time);
According to Tom, "the only [other] notable fix is with regards to function based
indexes where I said erroneously that the to_date() function was "broken" with
respect to the YYYY format - it is not (can you see why :)"
Here it is:
CREATE TABLE t (y VARCHAR2(32));
CREATE INDEX t2 ON t(to_date(y,'yyyy'));
ORA-01743: only pure functions can be indexed.
As a workaround in the book, Tom created his own "to_date" function. But there is
a far simpler reason why this doesn't work (and a far simpler solution). Even
though it was staring us all in the very same pages, not very many people could
figure out why this function-based index was disallowed:
"My conclusion in the book is wrong because to_date with the 'yyyy' format is not
deterministic."
That means that for the same input, you can get a different output. But how can
that be?
ops$tkyte@ORA10GR1> select to_date( '2005', 'yyyy' ) from dual;
TO_DATE('
---------
01-JUN-05
"Today, in june, to_date(2005) returns 01-jun, last month, same function, same
inputs - would return 01-may"
Wow. Never would have guessed to_date(year) would be non-deterministic.
Tom also clarified that the following function would be deterministic and therefore
eligible for a function-based index:
create index t2 on t( to_date( '01'||y, 'mmyyyy') );
That, of course, would force it to choose January no matter what time of year you
called the function.
To me, it is very funny that to_date:
1. IS deterministic if you leave out the day (it will always choose the 1st day)
2. IS NOT deterministic if you leave out the month (it will not choose the 1st
http://thinkoracle.blogspot.com/2005_06_01_archive.html (8 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
month, but rather the current month).
Often in Oracle there is method behind the madness, but I do now know why there
is this difference.
So I guess this issue is just another interesting footnote in some nerd's blog...
// posted by Robert Vollman @ Wednesday, June 22, 2005 2 comments
Tuesday, June 21, 2005
Natural vs Synthetic keys
Primary keys should be:
1. Unique
2. Never changed (or very infrequently)
This is because they are used in other tables to reference a single row.
(Definitions from www.orafaq.com)
Natural Key: A key made from existing attributes.
Surrogate Key: A system generated key with no business value. Usually
implemented with database generated sequences.
This topic has been discussed at great length many times. We recently re-hashed it
on the Dizwell Forum.
Disadvantages:
EDIT: Please note: There are some potentially important corrections and
clarifications to the below. Please see Howard's comments and follow the links to
his blog or the forum discussion.
Natural keys:
- May require the concatentation of many columns, so it gets very long
- Sometimes a natural key can be hard to find (in example: names can change)
- In a sense, you are duplicating data
- Can lead to future conflicts when the database expands beyond original
requirements
- Care must be taken to avoid block-splitting
- Care must be taken to avoid index range scans
- Tend to change, albeit infrequently
"You nearly always end up having to append a number to guarantee both
uniqueness and existence. If I always have to append a guaranteed unique existing
number to ensure I meet my requirements, why not make it the key itself."
- Niall Litchfield
Synthetic/Surrogate keys:
http://thinkoracle.blogspot.com/2005_06_01_archive.html (9 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
- Meaningless by itself (ie. no relationship to the data to which it is assigned)
- Implementation is database-dependent
- Care must be taken to avoid contention (monotically increasing and smaller
indexes)
- Similar rows would not have similar keys
Gauss suggested considering using a GUID for the surrogate key. No trigger is
required, uniqueness is guaranteed over space and time, but it is 16 bytes.
SQL> CREATE TABLE atable (pk RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
2 a_str VARCHAR2(32));
Table created.
SQL> INSERT INTO atable (a_str) VALUES ('One');
1 row created.
SQL> INSERT INTO atable (a_str) VALUES ('Two');
1 row created.
SQL> SELECT * FROM atable;
PK A_STR
-------------------------------- --------------------------------
5C3BCF77D55B41E78DE4016DFBE25FFA One
D3B959F3010745D3854F8FC2B09A18F3 Two
// posted by Robert Vollman @ Tuesday, June 21, 2005 9 comments
Monday, June 20, 2005
Decode
Here is another handy Oracle-only SQL tool: DECODE.
Decode allows you to do if/elseif/else logic within an SQL query, similar to a CASE
WHEN statement in a PL/SQL procedure.
DECODE (expression, if_equal_to_this_1, give_me_this_1, else_if_equal_to_this_2,
give_me_this_2, ..., default)
Dan Morgan's Reference on DECODE:
http://www.psoug.org/reference/decode_case.html
The default value is optional, if it is left out, it defaults to NULL. And the maximum
number of comparisons is 255.
It works very much like a CASE WHEN statement, so you can already think of uses,
especially if you nest your DECODEs.
Remember the article I wrote on NULL vs Nothing and on NULLs in general?
http://thinkoracle.blogspot.com/2005_06_01_archive.html (10 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html
http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html
Well, you can actually use DECODE like NVL. Don't believe me? I don't blame you,
because I told you that NULL is not equal to NULL. But here is a quote right from
the Oracle 9i SQL Reference:
"In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null,
then Oracle returns the result of the first search that is also null."
And here is the example because like me you don't believe anything without proof:
SQL> CREATE TABLE atable (avalue VARCHAR2(32));
Table created.
SQL> INSERT INTO atable (avalue) VALUES (NULL);
1 row created.
SQL> INSERT INTO atable (avalue) VALUES ('This is not NULL');
1 row created.
SQL> SELECT DECODE(avalue, NULL, 'This is NULL', avalue) avalue
2 FROM atable;
AVALUE
--------------------------------
This is NULL
This is not NULL
If you don't believe how useful it can be, here is a FAQ that shows how to use
DECODE for (among other things) avoiding divide-by-zero errors:
http://www.db.cs.ucdavis.edu/public/oracle/faq/decode.html
In practise, I have seen DECODE used for the ORDER BY clause.
If you are still not convinced, you can "Ask Tom" how DECODE can be used to have
a conditional foreign key. That is, a foreign key that only applies to certain rows!
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::
F4950_P8_DISPLAYID:1249800833250
// posted by Robert Vollman @ Monday, June 20, 2005 4 comments
Saturday, June 18, 2005
http://thinkoracle.blogspot.com/2005_06_01_archive.html (11 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
Connect By
I couldn't resist sharing this one, which I found on DBA-Support recently:
http://www.dbasupport.com/forums/showthread.php?t=47760
The question was how to create a column as the concatentation of many rows into
one, without using PL/SQL: just a single query.
Here is the example given:
CREATE TABLE t1 (col1 VARCHAR2(1));
INSERT INTO t1 VALUES ('a');
INSERT INTO t1 VALUES ('b');
INSERT INTO t1 VALUES ('c');
INSERT INTO t1 VALUES ('d');
INSERT INTO t1 VALUES ('e');
Desired result of an SQL statement on test:
abcde
The answer came from Tamil Selvan:
1 SELECT REPLACE(col1, ' ')
2 FROM (SELECT SYS_CONNECT_BY_PATH(col1,' ') col1, level
3 FROM t1
4 START WITH col1 = (select min(col1) from t1) -- 'a'
5 CONNECT BY PRIOR col1 < col1
6 ORDER BY level DESC)
7* WHERE rownum = 1
SQL> /
REPLACE(COL1,'')
----------------------------------------
abcde
Here are the things that may be new to someone new to Oracle (especially Oracle 9
or 10):
- SYS_CONNECT_BY_PATH
- CONNECT BY
This is an interesting example, but it is just one use of these features.
Here is a link to Dan Morgan's reference on CONNECT BY
http://www.psoug.org/reference/connectby.html
And here is an Ask Tom article.
http://www.oracle.com/technology/oramag/oracle/05-may/o35asktom.html
http://thinkoracle.blogspot.com/2005_06_01_archive.html (12 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
// posted by Robert Vollman @ Saturday, June 18, 2005 5 comments
Friday, June 17, 2005
Asking For Help
There are a lot of people who know Oracle. It makes sense to leverage their
abilities when you're stuck. Why re-invent the wheel?
First of all, often answers are easy to come by yourself. For example, here is Jeff
Hunter's suggestions on where to find answers:
http://marist89.blogspot.com/2005/06/where-can-i-find-help.html
If you want to reach a large body of Oracle professionals, the best approach is to
post it to a popular newsgroup or forum. Here are a few of my favourites.
Oracle Technology Network:
http://forums.oracle.com/forums/forum.jsp?forum=75
comp.databases.oracle.server:
http://groups-beta.google.com/group/comp.databases.oracle.server
DBA-Support:
http://www.dbasupport.com/forums/
Dizwell Forum:
http://www.phpbbserver.com/phpbb/viewforum.php?f=2&mforum=dizwellforum
It is tempting to contact certain professionals directly. After all, there are several of
them that spend seemingly hours answering questions in these forums, and
putting together web pages to assist fellow Oracle professionals.
But be aware that some of them do not have the time (or sometimes the interest)
in answering questions, like Mark Rittman or Duncan Mills.
http://www.rittman.net/archives/001276.html
http://www.groundside.com/blog/content/DuncanMills/
2005/06/16/Questions_Questions.html
And be aware that those that do like to receive questions may have a specific
method of submitting them, such as Tom Kyte and Steven Feuerstein:
http://asktom.oracle.com
http://www.oracle.com/technology/pub/columns/plsql/index.html
If you do ask someone a question, it sounds like these are the ground rules to
follow:
http://thinkoracle.blogspot.com/2005_06_01_archive.html (13 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
1. Don't even bother submitting urgent questions with time deadlines.
2. Even if English isn't your first language, try to make the question as clear as
possible.
3. Try to keep the question brief, but ...
4. Try to provide all necessary information, including examples and error messages
5. Don't ask questions that are obviously school assignments
6. Include "why" you are doing something.
http://tkyte.blogspot.com/2005/05/why.html
But the most important thing is:
Always try to find the answer yourself first!
Loosely Related:
http://vollman.blogspot.com/2005/04/roberts-tips-on-asking-for-help-1.html
// posted by Robert Vollman @ Friday, June 17, 2005 4 comments
Thursday, June 16, 2005
Common Table Column Types
I have two tables which are created in an SQL file, and I want to have a column in
common in both.
CREATE TABLE atable (atable_id VARCHAR2(32), atable_value NUMBER);
CREATE TABLE btable (btable_id VARCHAR2(32), btable_name VARCHAR2(32));
However, I don't want to have to rely on manual verification to make sure they are
the same type. And if I want to change them for a future version, I'd rather only
change it in one place.
I can't do this (abbreviated example):
CREATE TABLE atable (atable_id VARCHAR2(32));
SQL> CREATE TABLE btable (btable_id atable.atable_id%TYPE);
CREATE TABLE btable (btable_id atable.atable_id%TYPE)
*
ERROR at line 1:
ORA-00911: invalid character
And I can't do this:
CREATE OR REPLACE PACKAGE table_types
AS
SUBTYPE table_id IS VARCHAR2(32);
END;
http://thinkoracle.blogspot.com/2005_06_01_archive.html (14 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
SQL> CREATE TABLE atable (atable_id table_types.table_id);
CREATE TABLE atable (atable_id table_types.table_id)
*
ERROR at line 1:
ORA-00902: invalid datatype
So what should we do?
If there is a logical link between these two fields, we can use referential integrity
constraints (thanks "hsheehan"):
SQL>CREATE TABLE atable(atable_id VARCHAR2(32), atable_value NUMBER);
Table created.
SQL>ALTER TABLE atable ADD UNIQUE(atable_id);
Table altered.
SQL>CREATE TABLE btable(btable_id REFERENCES atable(atable_id), btable_name
VARCHAR2(32));
Table created.
However, this will require that every atable.atable_id be unique (ORA-02270), and
it will also require that every btable.btable_id exists in atable.atable_id (ORA-
02291)
This may not always be appropriate, because sometimes there is no such
relationships between columns. You may simply want all columns in a database
that are somehow similar (example: people's names) to be the same type. You may
be doing this for convenience or because you have common functions on these
columns.
In that case, you may need to resort to a 3rd-party schema modeller. After all,
that's what they're for.
Otherwise, you can create a file of tags, use these tags in a "pre-SQL" file, then
write a Perl script to do text substitutions to generate the SQL file. For example:
Tags.txt:
ID_TYPE VARCHAR2(32)
CreateTable.pre:
CREATE TABLE ATABLE (atable_id ID_TYPE, atable_value NUMBER);
CREATE TABLE BTABLE (btable_id ID_TYPE, btable_name VARCHAR2(32));
Execute your Perl script here
CreateTable.sql:
CREATE TABLE ATABLE (atable_id VARCHAR2(32), atable_value NUMBER);
CREATE TABLE BTABLE (btable_id VARCHAR2(32), atable_name VARCHAR2(32));
http://thinkoracle.blogspot.com/2005_06_01_archive.html (15 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
Thanks to the folks at the Dizwell Forum for helping me think this one through.
http://www.phpbbserver.com/phpbb/viewtopic.php?
t=179&mforum=dizwellforum&sid=6802
Here are some loosely related previous blogs on this:
http://thinkoracle.blogspot.com/2005/05/dynamically-assigning-size-of-
varchar2.html
http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html
// posted by Robert Vollman @ Thursday, June 16, 2005 0 comments
Wednesday, June 15, 2005
Variable Constraints
You can't use variables in constraints.
CREATE OR REPLACE PACKAGE test_constants IS
test_val CONSTANT NUMBER := 20;
END test_constants;
CREATE TABLE atable (
a_id NUMBER,
CONSTRAINT atable_test CHECK (a_id > test_constants.test_val)
);
ORA-00904: "TEST_CONSTANTS"."TEST_VAL": invalid identifier
Check Constraints can NOT contain subqueries, so forget about doing it that way.
It is basically only for comparisons and simple operations.
How about this convoluted way of achieving the goal. Create a trigger that will
check the value upon insert or update, and then trigger a special constraint.
SQL> CREATE TABLE atable (
2 a_id NUMBER,
3 a_error NUMBER,
4 CONSTRAINT id_too_high CHECK (a_error <> 1)
5 );
Table created.
SQL> CREATE OR REPLACE TRIGGER atable_trig
2 BEFORE INSERT OR UPDATE OF a_id ON atable FOR EACH ROW
3 BEGIN
4 IF :new.a_id > test_constants.test_val THEN :new.a_error := 1;
5 END IF;
http://thinkoracle.blogspot.com/2005_06_01_archive.html (16 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
6 END;
7/
Trigger created.
SQL> INSERT INTO atable (a_id) VALUES (1);
1 row created.
SQL> INSERT INTO atable (a_id) VALUES (21);
INSERT INTO atable (a_id) VALUES (21)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.ID_TOO_HIGH) violated
Check the comments to see if someone has a better way.
Related links:
http://thinkoracle.blogspot.com/2005/05/enum-in-oracle.html
// posted by Robert Vollman @ Wednesday, June 15, 2005 1 comments
Tuesday, June 14, 2005
Bind Variables in PL/SQL
If you learn nothing else from the Oracle experts out there but want to improve
your Oracle application development knowledge, then read up on bind variables.
Bind variables are already very well explained, here are my favourite links on the
subject:
Mark Rittman's Bind Variables Explained
http://www.rittman.net/archives/000832.html
Tom Kyte is one of the champions of using Bind Variables. Links to his articles and
books can be found at the bottom of Mark's article.
After reading these articles, I understood that PL/SQL automatically binds
variables, so you really don't have to worry. But I was still concerned that I might
be missing something.
Tom has a query that you can run to determine if you are currently using bind
variables or not. Find it here (Hint: select sql_test from v$sqlarea):
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::
F4950_P8_DISPLAYID:1163635055580.
I posted the following question to the Dizwell forum, which is an excellent place to
tap the minds of Oracle gurus directly.
http://thinkoracle.blogspot.com/2005_06_01_archive.html (17 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
http://www.phpbbserver.com/phpbb/viewtopic.php?t=169&mforum=dizwellforum
Is there any possible way to NOT use bind variables in a PL/SQL stored procedure
WITHOUT using 'execute immediate' or DBMS_SQL package?
I just want to make sure that there are only 2 situations I have to look out for in
my PL/SQL code with regards to bind variables.
A developer from Australia going by "gamyers" responded with ref cursors:
DECLARE
v_cur sys_refcursor;
BEGIN
OPEN v_cur FOR 'select 1 from dual where 1=2';
CLOSE v_cur;
END;
Also, you could code
IF v_value =1 then
select ... where col_a = 1;
ELSIF v_value = 2 then
select ... where col_a = 2;
....
But with that sort of code you are probably dealing with a very small set of SQL
statements and are specifically wanting different plans etc.
And the champion himself, Tom Kyte, had this reply and defined some new terms:
"Overbind" and "Underbind." Using these terms I was asking whether it was
possible to "Underbind" using PL/SQL.
The only way to improperly bind in PL/SQL is when you use dynamic sql.
Else, you can "overbind" -- bind when you didn't need to, but you cannot
"underbind", not binding when you should!
eg:
for x in ( select * from all_objects where object_type = 'TABLE' )
loop
is perfectly ok, you do NOT need to:
declare
l_otype varchar2(25) := 'TABLE';
begin
for x in ( select * from all_objects where object_type = l_otype )
loop
http://thinkoracle.blogspot.com/2005_06_01_archive.html (18 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
You do not need to bind 'TABLE' in that query since no matter how many times you
run that query, it'll be 'TABLE' over and over.
But with static SQL, it's not possible to "underbind"
As an aside, visit Jeff Hunter's Oracle blog, which is fast becoming one of my
favourites. Here is his recent post on where Oracle DBAs can get help:
http://marist89.blogspot.com/2005/06/where-can-i-find-help.html
// posted by Robert Vollman @ Tuesday, June 14, 2005 0 comments
Monday, June 13, 2005
Blank Lines and SQLPlus
Try creating the following table using SQLPlus Worksheet.
create table atable (
aint integer
);
You will get this error:
SP2-0734: unknown command beginning "aint integ..." - rest of line ignored.
It thinks "aint integer" is a new command. What is the problem? Repeat this test
using SQLPlus at a prompt:
SQL> create table atable
2(
3
SQL>
The blank line stops the statement.
This doesn't apply to all statements, but it does apply to select/insert/update/
delete queries. It doesn't apply to creating procedures:
SQL> CREATE OR REPLACE PROCEDURE MyProc
2
3 AS
4
5 BEGIN
6
7 NULL;
8
9 END;
http://thinkoracle.blogspot.com/2005_06_01_archive.html (19 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
10 /
Procedure created.
At first I just made the blank lines a comment:
create table atable (
--
aint integer
--
);
Table created.
But eventually I took a closer look and arrived at the correct solution. Here it is.
set sqlblanklines on;
SQL> create table atable
2(
3
4 aint integer
5
6 );
Table created.
Its just a little SQLPlus foible.
If you see something similar and you aren't using blank lines, see if you're using
any special symbols and look at "set sqlprefix" instead.
So what should we do?
1. Put in comments to avoid the blank lines.
2. Use something other than sqlplus.
3. Explicitly set blanklines on at the top of your statement.
// posted by Robert Vollman @ Monday, June 13, 2005 1 comments
Friday, June 10, 2005
NULLs in Oracle
If you read only one sentence per posting, read this:
NULLs may not behave as you'd expect in Oracle, and as a result, NULLs are the
cause of many application errors.
I got a fair bit of private feedback on my recent article on the differences between
http://thinkoracle.blogspot.com/2005_06_01_archive.html (20 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
"NULL" and nothing. Here is the link to the original article, and check out Howard
Rogers' comments.
http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html
Let's start with an example to demonstrate a simple property of NULL: that NULL is
not equal to anything, including NULL itself.
SELECT * FROM dual WHERE NULL = NULL;
No rows selected.
SELECT * FROM dual WHERE NULL <> NULL;
No rows selected.
What happened here? How can something be both not equal and not not equal to
something else at the same time? That probably doesn't make much sense, does it?
Essentially NULL means you don't know the value. Basically, you can't say whether
its equal or not equal to anything else. You need to abandon the binary logic you
learned in first-year math and embrace tri-value logic.
That was also a clear demonstration of how you should be careful using equals or
not equals when dealing with NULLs. Instead, use "IS". Observe:
SELECT * FROM dual WHERE NULL IS NULL;
D
-
X
As an aside, you may be wondering what the heck "dual" is. And what do we do
when we have a question? We ask Tom!
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::
F4950_P8_DISPLAYID:1562813956388
In my mind, dual is simply a table with a single row that is guaranteed to be there.
And when you use it, it makes you look clever because that's what the experts
use. :)
While I'm blowing your mind with the behaviour of NULL, check out this other case,
kindly provided by Tom Kyte:
IF (x = 'A') THEN something
ELSE something else
END IF;
IF NOT(x = 'A') THEN something else
http://thinkoracle.blogspot.com/2005_06_01_archive.html (21 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
ELSE something
END IF;
Do these two pieces of pseudo-code look the same to you? In many languages,
yes. But not in PL/SQL. Why?
Consider the case that x is NULL. In the first case it will do "something else"
because it is not equal to 'A'. In the second case it will do "something" because it is
also not not equal to 'A'.
Tom knows I never believe anything without proof, so he provided me with one:
ops$tkyte@ORA9IR2> declare
2 x varchar2(1);
3 begin
4 if ( x = 'A' )
5 then
6 dbms_output.put_line( 'X=A' );
7 else
8 dbms_output.put_line( 'NOT X=A' );
9 end if;
10
11 if NOT( x = 'A' )
12 then
13 dbms_output.put_line( 'NOT X=A' );
14 else
15 dbms_output.put_line( 'X=A' );
16 end if;
17 end;
18 /
NOT X=A
X=A
PL/SQL procedure successfully completed.
Pretty bizarre, eh? Starting to understand the behaviour of NULL? Starting to see
why misunderstanding NULL can lead to application errors?
While we are discussing NULLs, here a few more useful things.
First, you can use the "set null" command to change how NULL will appear in
SQLPLUS. This will not change anything in the database, or equality, it will just
change the appearance.
create table atable (last_name varchar(12));
insert into atable (last_name) values ('Smith');
insert into atable (last_name) values (NULL);
select * from atable;
http://thinkoracle.blogspot.com/2005_06_01_archive.html (22 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
LAST_NAME
------------
Smith
2 rows selected.
set null [NULL];
select * from atable;
LAST_NAME
------------
Smith
[NULL]
2 rows selected.
Also, you can use these two functions, nvl and nvl2:
nvl(expr_1, expr_2)
Returns expr_2 if expr_1 is null and expr_1 otherwise.
nvl2(expr_1, expr_2, expr_3)
Returns expr_3 if expr_1 is null and expr_2 otherwise.
select nvl(last_name, '[NONE]') as last_name from atable;
LAST_NAME
------------
Smith
[NONE]
2 rows selected.
select nvl2(last_name, 'Y: ' || last_name,'N') as last_name from atable;
LAST_NAME
---------------
Y: Smith
N
2 rows selected.
Those of you who, like me, work on many different databases have hopefully seen
by now that NULLs are handled differently in Oracle. As a final demonstration,
consider how NULLs are handled in Sybase ASE.
In Sybase ASE (and MS SQL), there is a configuration parameter that tells the
database whether to treat NULLs as they are defined in the ANSI SQL standard, or
http://thinkoracle.blogspot.com/2005_06_01_archive.html (23 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
whether to "bend the rules" a bit, and allow "NULL = NULL" to be true.
Furthermore, the behaviour changes depending on whether you're in a join clause
or a search clause.
http://sybasease.blogspot.com/2005/05/nulls-in-sybase-ase.html
That is not the case in Oracle. As confusing as NULLs may be at first, they are
consistent. They will behave the same logically everywhere no matter how and
where you use them.
So what should we do?
1. Understand that "NULL = NULL" and "NULL <> NULL" are both false because
NULL means "I don't know"
2. Use "IS NULL" instead of "= NULL" if you are checking for NULLness.
3. Use "nvl" and "nvl2" if you want to eliminate the possibility of a NULL in a
statement by assigning it a (temporary) value.
For future reference, consult Dan Morgan's page on NULL:
http://www.psoug.org/reference/null.html
This is definitely not the last article you'll see on NULL given how often it is
misunderstood. I encourage you to include your own experiences of your favourite
NULLisms in the "comments" section. And, naturally, I appreciate corrections and
clarifications.
And as a final thanks to Tom, here are some pictures from when he visited us in
Calgary this past March.
http://www.coug.ab.ca/events/05-mar.htm
// posted by Robert Vollman @ Friday, June 10, 2005 9 comments
About Me
Name: Robert Vollman
Location: Calgary, Alberta, Canada
I was born and raised in Ottawa, and have lived in Calgary since 1991. I like
playing sports (hockey, soccer, ultimate, basketball, you name it) and military
board games. I also enjoy reading, walking, and playing with my 2 cats Lilly and Brutus. I'm a
database application specialist, whatever that is.
View my complete profile
http://thinkoracle.blogspot.com/2005_06_01_archive.html (24 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
Best Links
● Ask Tom Kyte
● Oracle Docs
● Dan Morgan and PSOUG
● Steven Feuerstein
● Jonathan Lewis
● FAQ
● Connor McDonald
● The Oak Table
● Cary Millsap and Hotsos
● Steve Adams and Ixora
● Anjo Kolk and OraPerf
● Dizwell Oracle Wiki
● My Personal Blog
Aggregators
Brian Duff's OraBlogs
❍
❍ Eddie Awad's OracleNA
❍ Pete Finnigan's Aggregator
❍ Oracle's Bloglist
❍ Oracle Base Aggregator
Top Blogs
❍ Oracle's Ask Tom Kyte
❍ Oracle Guru Jonathan Lewis
❍ Blogger of the Year Eddie Awad
❍ Data Warehouser David Aldridge
❍ Oracle Geek Lewis Cunningham
❍ Database Expert James Koopmann
❍ Dizwell's Howard Rogers
❍ Oracle Master Laurent Schneider
❍ Security Expert Pete Finnigan
❍ Oracle Award Winner Mark Rittman
❍ Doug Burns
❍ Oracle ACE of the Year Dr. Tim Hall
❍ UKOUG's Andrew (Arfur C.) Clarke
❍ Newbie DBA Lisa Dobson
❍ Coffee-Drinking DBA Jon Emmons
❍ Chris Foot
http://thinkoracle.blogspot.com/2005_06_01_archive.html (25 of 26)1/9/2008 2:49:31 AM
OracleBlog: June 2005
❍ The Pythian DBA Team Blog
❍ DBA Don Seiler
❍ DBA Coskan Gundogar
❍ Oracle WTF
ARCHIVES
❍ LIST ALL ARTICLES
❍ May 2005
❍ June 2005
❍ July 2005
❍ August 2005
❍ September 2005
❍ October 2005
❍ November 2005
❍ December 2005
❍ January 2006
❍ February 2006
❍ March 2006
❍ April 2006
❍ May 2006
❍ June 2006
❍ July 2006
❍ August 2006
❍ September 2006
❍ October 2006
❍ November 2006
❍ December 2006
❍ January 2007
❍ February 2007
❍ March 2007
❍ April 2007
❍ May 2007
❍ June 2007
❍ October 2007
❍ Current Posts
http://thinkoracle.blogspot.com/2005_06_01_archive.html (26 of 26)1/9/2008 2:49:31 AM
OracleBlog: July 2005
OracleBlog
I love data. Collecting, storing, manipulating, studying and using data. Oracle may be
complicated, but it is the most powerful tool when it comes to data. That's why when I
think data, I think Oracle. I also enjoy writing, so I use this format to organise my
thoughts. Please feel free to discuss any thoughts you may have on the same topics,
even old ones (I will see and respond to such comments). You may want to start with
"LIST ALL ARTICLES" under Archives.
Friday, July 29, 2005
Oracle By Example
I would like to elaborate on one point from my recent blog on Using
Views:
http://thinkoracle.blogspot.com/2005/07/use-views.html
The 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 simplicity.
CREATE TABLE department (
dept_name VARCHAR2(32) PRIMARY KEY,
dept_description VARCHAR2(64)
);
CREATE TABLE expenses (
dept_name REFERENCES department(dept_name),
expense_type VARCHAR2(32),
expense_description VARCHAR2(64),
expense_amount NUMBER(10,2)
);
CREATE TABLE revenues (
dept_name REFERENCES department(dept_name),
revenue_type VARCHAR2(32),
http://thinkoracle.blogspot.com/2005_07_01_archive.html (1 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
revenue_description VARCHAR2(64),
revenue_amount NUMBER(10,2)
);
Okay now I'd like to insert a little sample data so we can test some
queries
INSERT INTO department VALUES ('DEPT_A', 'Expenses Only');
INSERT INTO department VALUES ('DEPT_B', 'Revenues Only');
INSERT INTO department VALUES ('DEPT_C', 'Expenses and
Revenues');
INSERT INTO department VALUES ('DEPT_D', 'No Expenses Nor
Revenues');
INSERT INTO expenses VALUES ('DEPT_A', 'TYPE_1', 'Expense 1',
10.00);
INSERT INTO expenses VALUES ('DEPT_A', 'TYPE_2', 'Expense 2',
12.50);
INSERT INTO expenses VALUES ('DEPT_C', 'TYPE_1', 'Expense 3',
44.90);
INSERT INTO expenses VALUES ('DEPT_C', 'TYPE_3', 'Expense 4',
92.75);
INSERT INTO revenues VALUES ('DEPT_B', 'TYPE_1', 'Revenue 1',
14.60);
INSERT INTO revenues VALUES ('DEPT_B', 'TYPE_1', 'Revenue 2',
15.80);
INSERT INTO revenues VALUES ('DEPT_C', 'TYPE_4', 'Revenue 3',
47.75);
INSERT INTO revenues VALUES ('DEPT_C', 'TYPE_5', 'Revenue 4',
6.15);
We want a query that will show us every department, and its total
expenses and revenues. We want dept_name, sum(expenses), sum
(revenues), regardless of type.
Doing a single sum would be easy, we could just "GROUP BY" a
particular column. Even if we wanted to add dept_description, we
could just use Connor McDonald's trick:
http://thinkoracle.blogspot.com/2005/07/extra-columns-in-
group-by.html
http://thinkoracle.blogspot.com/2005_07_01_archive.html (2 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
For simplicity, by the way, we won't include dept_description - we
know we can using that technique.
Go ahead and write the query. I'm sure its possible! But not
everyone can figure it out, you might wind up with something really
complex.
So how will views help us? Well, we can create views that give us the
sum for expenses and values:
CREATE VIEW expensesview AS
SELECT dept_name, sum(expense_amount) expense_sum
FROM expenses
GROUP BY dept_name;
CREATE VIEW revenuesview AS
SELECT dept_name, sum(revenue_amount) revenue_sum
FROM revenues
GROUP BY dept_name;
Nothing could be easier. That is also some pretty handy views to
have. As you recall, a view can be thought of as a "virtual table" or
as a stored query. A stored query we are about to put to use.
SELECT d.dept_name, e.expense_sum, r.revenue_sum
FROM department d, expensesview e, revenuesview r
WHERE d.dept_name = e.dept_name
AND d.dept_name = r.dept_name;
Here are the results ... oops!
DEPT_NAME EXPENSE_SUM REVENUE_SUM
-------------------------------- ----------- -----------
DEPT_C 137.65 53.9
Why did we only get DEPT_C? Because, of course, either
expense_sum or revenue_sum was NULL in the other 3
departments. I'm only showing this mistake so I can show the
http://thinkoracle.blogspot.com/2005_07_01_archive.html (3 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
application of an outer join. An outer join will give you all possible
rows. Just add a (+) next to the column you're joining on.
Symbolically that means you want to add (+) rows where none exist.
SELECT d.dept_name, e.expense_sum, r.revenue_sum
FROM department d, expensesview e, revenuesview r
WHERE d.dept_name = e.dept_name(+)
AND d.dept_name = r.dept_name(+);
DEPT_NAME EXPENSE_SUM REVENUE_SUM
-------------------------------- ----------- -----------
DEPT_A 22.5
DEPT_B 30.4
DEPT_C 137.65 53.9
DEPT_D
Perfect.
We could have done this without views. Indeed, we could have
embedded the simple queries we used to make the views directly
into this final query. Maybe in an example as simple as this that
would be ok. But these kinds of things can get complex. Plus, now
other queries can take advantage of those views.
As a final note, how do you figure we can replace the NULLs up
there with zeros? The answer is something like DECODE or NVL.
http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html
SELECT d.dept_name,
NVL(e.expense_sum,0) tot_expense,
NVL(r.revenue_sum,0) tot_revenue
FROM department d, expensesview e, revenuesview r
WHERE d.dept_name = e.dept_name(+)
AND d.dept_name = r.dept_name(+);
DEPT_NAME TOT_EXPENSE TOT_REVENUE
-------------------------------- ----------- -----------
DEPT_A 22.5 0
DEPT_B 0 30.4
DEPT_C 137.65 53.9
DEPT_D 0 0
http://thinkoracle.blogspot.com/2005_07_01_archive.html (4 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
// posted by Robert Vollman @ Friday, July 29, 2005 0 comments
Tuesday, July 26, 2005
Use Constraints
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 primer from my own
experience, and a couple of treats.
There are many types of constraints, including primary key, unique,
referential (foreign key) and check constraints. I'll talk about check
constraints.
There are basically three ways to set up your table constraint. Check
a reference (like Dan Morgan's http://www.psoug.org/reference/
constraints.html) for more detail, but I will review them here.
1. On the Same Line
CREATE TABLE ConstraintTable (
MyNumber NUMBER(1) CHECK (MyNumber < 5)
);
Using this method (only), you can't reference other columns in the
table in your check constraint. Try it:
CREATE TABLE ConstraintTable (
MyNumber1 NUMBER(1),
MyNumber2 NUMBER(1) CHECK (MyNumber2 > MyNumber1)
);
ORA-02438: Column check constraint cannot reference other
columns
It is also not obvious what name the constraint takes so it's more
difficult to alter it later. But here is how:
http://thinkoracle.blogspot.com/2005_07_01_archive.html (5 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
SQL> SELECT constraint_name
2 FROM user_constraints
3 WHERE table_name = 'CONSTRAINTTABLE';
CONSTRAINT_NAME
------------------------------
SYS_C007536
2. During table creation
Example:
CREATE TABLE ConstraintTable (
MyNumber NUMBER(1)
CONSTRAINT c_my_number CHECK (MyNumber < 5)
);
Doing it this way allows you to reference other columns in the table:
SQL> CREATE TABLE ConstraintTable (
2 MyNumber1 NUMBER(1),
3 MyNumber2 NUMBER(1),
4 CONSTRAINT c_my_number CHECK (MyNumber2 > MyNumber1)
5 );
3. Alter table
You can create your table as normal, and then add your constraints
separately. I don't think there is any actual difference to Oracle
between method #2 and #3.
CREATE TABLE ConstraintTable (MyNumber Number(1));
ALTER TABLE ConstraintTable ADD CONSTRAINT c_my_number
check (MyNumber < 5);
There is actually a 4th way, kind of. See, CHECK constraints can not
include sub-queries, and you can't reference other tables in them.
You also can't use package-defined constants or variables. All you
can basically do is simple things, like <>=, and [not] between/in/
like/equals
I get around all of this by using triggers. I add a simple constraint to
the table (possibly a "NOT NULL", if applicable) and then write a
trigger which will do my check - referencing other tables, writing
http://thinkoracle.blogspot.com/2005_07_01_archive.html (6 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
subqueries and using package-defined constants as I please - and
then deliberately set off the simple constraint.
Of course, this may look silly to a user, who gets an error on
inserting a row with a NULL value when it clearly isn't NULL. So I
usually write something to a log, or name my simple constraint, and
I certainly document the source code. But that's a topic for another
day.
Here is an article where I describe the concept in more detail,
picking especially on using variables in constraints. But apply the
same logic for complex integrity constraints when you want to
reference other tables.
http://thinkoracle.blogspot.com/2005/06/variable-constraints.html
Ok, let me wrap it up by saying that one of the other advantages of
constraints is that you can disable them when you need to:
SQL> INSERT INTO ConstraintTable (MyNumber) VALUES (6);
ORA-02290: check constraint (SYS.SYS_C007536) violated
(By the way, that's the other way to find out the name of your
constraint: violate it!)
SQL> ALTER TABLE ConstraintTable DISABLE CONSTRAINT
SYS_C007536;
SQL> INSERT INTO ConstraintTable (MyNumber) VALUES (6);
And you're ok! Of course, you better make sure the data is ok by the
time you turn it back on, or you'll get this error:
SQL> ALTER TABLE ConstraintTable ENABLE CONSTRAINT
SYS_C007536;
ORA-02293: cannot validate (SYS.SYS_C007536) - check constraint
violated
Which is a great segue into my closing, which includes two of my
favourite discussions on disabling and deferring constraints. Check
them out:
Jeff Hunter
http://thinkoracle.blogspot.com/2005_07_01_archive.html (7 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
http://marist89.blogspot.com/2005/06/deferrable-constraints_29.
html
Doug Burns
http://doug.burns.tripod.com/oracle/index.blog?entry_id=1170846
// posted by Robert Vollman @ Tuesday, July 26, 2005 2 comments
Monday, July 25, 2005
Use Views
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 view takes the output of a query and presents it as a table."
- Oracle Concepts Guide, Section 10.16 (Views)
How do you create a View?
Nothing could be easier. Here:
CREATE OR REPLACE VIEW MyView AS
(Insert Query Here!)
For more, you can always reference Dan Morgan's library:
http://www.psoug.org/reference/views.html
How do I use Views?
Treat it just like a table. You can query them, as well as insert,
update and delete*. Bear in mind that these actions will update the
base table(s). Likewise, any changes to the base table(s) will
automatically update the View*.
http://thinkoracle.blogspot.com/2005_07_01_archive.html (8 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
*This is true in general. Some Views can't be updated, and you can
make a View READONLY.
Here is how to tell what Views exist:
SELECT view_name FROM user_views;
Here is how to tell what the View is:
SELECT text FROM user_views WHERE view_name = 'MyView';
Why are they useful?
I use them for many reasons, here are the most common situations
where I will advocate their use:
1. Denormalizing the database
Sometimes its nice to pull related data from several tables into a
single table. Using views allows you to satisfy both the Fabian
Pascalesque relational database purist, and the pragmatic user.
2. Making things look better
For example, you can use a view to substitute a generic ID number
with the name of the individual. You can leave out columns that are
rarely interesting. You can use a view to add information that is
derived from other data, for example figure out everyone's salary in
Canadian dollars in an international organisation.
3. Complex queries/query simplification
You might have several queries that have to perform similar logic.
You can just create a view for that logic, and make the queries far
simpler. Also, when I can't figure out how to write a really complex
query, sometimes I just create views as intermediate steps.
For example, say you have a table of expenses, with "department",
"type" and "total", a revenue table with the same columns, and a
department table. Now you have to put together a query that shows
each department, and their total expenses and revenues, regardless
of type.
Rather than write a really complex query, just write a view (or two)
that contains the sum of the expenses and revenues, by
http://thinkoracle.blogspot.com/2005_07_01_archive.html (9 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
department. Then you can create a query to put them all into a
single row by (outer) joining on those views. Simple!
4. Restrict access to a column or row
Say you want a user to have access to only part of a table (either by
row or column). Here's what you do: restrict his access to the table
completely, then create a view that contains the information the
user is allowed to access, and then grant that user access to the
view.
5. Rename a column
Create a view which is exactly like the table, but with the column
renamed. That is really easy, and it will save you from having to
possibly update lots of other tables or applications because you left
the base table alone. No, you can't write indexes on views, but
queries get optimized as normal, and it will use the base table's
indexes at that time.
6. Change schema, but still support an old version
Just like renaming a column. You can completely re-design your
database schema, but create views to support legacy applications
who still rely on the structure and naming of the original. That will
save a lot of hassle.
I will close with a really interesting aspect of Views.
SQL> CREATE TABLE BaseTable (MyNumber NUMBER(1));
Table created.
SQL> INSERT INTO BaseTable (MyNumber) VALUES (1);
1 row created.
SQL> CREATE VIEW MyView AS SELECT * FROM BaseTable;
View created.
SQL> ALTER TABLE BaseTable ADD (MyString VARCHAR2(32));
Table altered.
SQL> INSERT INTO BaseTable (MyNumber, MyString) VALUES (2,
http://thinkoracle.blogspot.com/2005_07_01_archive.html (10 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
'Hello');
1 row created.
SQL> SELECT * FROM MyView;
MYNUMBER
----------
1
2
SQL> SELECT * FROM BaseTable;
MYNUMBER MYSTRING
---------- --------------------------------
1
2 Hello
The View is NOT updated when the BaseTable is, even when you
SELECT *. When you write SELECT * the view chooses its columns
then and there.
// posted by Robert Vollman @ Monday, July 25, 2005 7 comments
Saturday, July 23, 2005
Oracle BOOLEAN
There is no BOOLEAN datatype in Oracle, as far as tables are
concerned.
CREATE TABLE BooleanTable (MyBool BOOLEAN);
ORA-00902: invalid datatype
But 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');
http://thinkoracle.blogspot.com/2005_07_01_archive.html (11 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END BoolProc;
Why is there no Boolean in Oracle for tables? What should we do
instead? This:
CREATE TABLE BoolTable (MyBool CHAR(1) CHECK (MyBool IN ( 'Y',
'N' )));
As for the first question, as usual, let's Ask Tom:
http://asktom.oracle.com/pls/ask/f?
p=4950:8:2109566621053828525::NO::F4950_P8_DISPLAYID,
F4950_P8_CRITERIA:6263249199595
// posted by Robert Vollman @ Saturday, July 23, 2005 7 comments
Thursday, July 21, 2005
Oracle Blogs
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, respected author of "Ask Tom." Covers a pretty wide
spectrum of Oracle-related topics, including new things and "best
practises." This is on practically everyone's favourite blog list, and
every post gets dozens of comments.
http://tkyte.blogspot.com/
Niall Litchfield has one of the first Oracle blogs I read. He has
interesting posts on a variety of topics including htmldb, and Oracle
http://thinkoracle.blogspot.com/2005_07_01_archive.html (12 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
10g.
http://www.niall.litchfield.dial.pipex.com/
Howard Rogers is the master of the Dizwell Forum. The discussions
therein are generally the inspiration for his posts. His passionate
Oracle rants give his blog a lot of flavour.
http://www.dizwell.com/blogindex.html
David Aldridge, is an all-purpose Oracle warehouse specialist. I love
his posts on warehouse design and maintenance. He also posts
regularly about Oracle 10g and its features.
http://oraclesponge.blogspot.com/
Mark Rittman is a database warehouse developer and covers a lot of
stuff, like modelling. He has a link to all the Oracle blogs that are
out there (good or bad), so you can go through there and pick your
favourites.
http://www.rittman.net/
Jeff Hunter is an Oracle DBA with a relatively new blog. He posts a
wide variety of topics, and he is on a lot of people's favourites list.
http://marist89.blogspot.com/
Tim Hall is an Oracle DBA, designer and developer. Posts on a
variety of topics and is not shy about sharing his opinions. Despite
the fact that he doesn't use IDEs, he's one of the few people who
mention them.
http://oracle-base.blogspot.com/
Doug Burns, another Oracle DBA, reads and writes papers and
books. I count on him to post about the latest news and papers/
books of interest.
http://doug.burns.tripod.com/oracle/
Pete Finnigan, an Oracle security expert, provides the latest news of
bugs and security flaws. Even though I'm not into security that
much, I still enjoy his posts.
http://www.petefinnigan.com/weblog/entries
Eddie Awad, an Oracle application developer who posts interesting
things about Oracle as he finds them. Also talks about ColdFusion
and Biztalk occasionally. Has me in his blogroll! :)
http://awads.net/wp/
http://thinkoracle.blogspot.com/2005_07_01_archive.html (13 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
Peter Scott is a manager (!) in charge of an Oracle data warehouse.
As a result his posts are generally about the situation of the day,
which is usually of significance to everyone.
http://pjs-random.blogspot.com/
Lisa Dobson, an Oracle DBA whose brand new blog focuses on the
Newbie perspective.
http://newbiedba.blogspot.com/
Amis Technology Corner has a number of Oracle professionals from
various backgrounds that post on a variety of topics, including
Oracle-related events and publications, and various designs and
features.
http://technology.amis.nl/blog/
Mike Ault, a Burleson consultant and published author, posts on a
variety of Oracle-related topics related to his interesting adventures
while consulting. No comments allowed, ostensibly for legal reasons
but people posted a lot of corrections when they were allowed.
http://mikerault.blogspot.com/
Robert Freeman, another Burleson consultant and a 15-year Oracle
DBA, posts on a variety of Oracle-related topics of interest to DBAs,
including the types of problems he sees and solves and things he
finds in the latest releases.
http://robertgfreeman.blogspot.com/
If you know of any other Oracle-related blogs that you enjoy, please
add a Comment so I can check it out. Please spread the word of
these great blogs!
// posted by Robert Vollman @ Thursday, July 21, 2005 4 comments
Monday, July 18, 2005
Which instance am I in?
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.
http://thinkoracle.blogspot.com/2005_07_01_archive.html (14 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
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 SYS_CONTEXT. That looked like it would do the trick. So I
looked it up in my favourite reference, Dan Morgan's:
http://www.psoug.org/reference/sys_context.html
And came up with this:
SELECT SYS_CONTEXT(‘USERENV’,’DB_NAME’) FROM DUAL;
You can also get the 'INSTANCE' using this, but that doesn't do you
any good if you can't query the V$INSTANCE table.
That solves the problem, but I'm sure that's just one of many ways
to do it:
1. Some other queries on some system tables, and/or
2. Some command-line command, and/or
3. Some environment file somewhere.
Also, I know some people who put this directly into their sqlplus
prompt.
// posted by Robert Vollman @ Monday, July 18, 2005 1 comments
Thursday, July 14, 2005
Oracle Docs
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.html
Recently Tom Kyte wrote an article on Reading the F'n Manual:
http://tkyte.blogspot.com/2005/07/rtfm.html
Which begs the F'n question. Where are the F'n manuals? Which
http://thinkoracle.blogspot.com/2005_07_01_archive.html (15 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
manuals should you F'n read?
The first question is easy, it's all here:
http://www.oracle.com/technology/documentation/index.html
Personally, I use Oracle9, so I have this page bookmarked:
http://www.oracle.com/technology/documentation/oracle9i.html
For the novice, you want to go to View Library and Getting Started
There you will find links for the Installer, the Administrator, and the
Developer. These links give you recommendations for which F'n
manuals to read.
Being an application developer the key F'n manual for me, without
dispute, is:
Oracle9i Application Developer's Guide - Fundamentals
http://download-west.oracle.com/docs/cd/B10501_01/
appdev.920/a96590.pdf
I won't include all the links, because a list of all F'n manuals for
Oracle9i can be found here:
http://www.oracle.com/pls/db92/db92.docindex?
remark=homepage
Here are the F'n manuals that are recommended for Application
Developers:
Oracle9i Database Concepts Guide
Oracle9i SQL Reference
Oracle9i Data Warehousing Guide
PL/SQL User's Guide and Reference
Oracle9i Supplied PL/SQL Packages and Types Reference
Oracle9i Database Performance Tuning Guide and Reference
Oracle9i Database Error Messages
Beyond that, there are also F'n manuals for more advanced
programming (Java, C++, XML, Object-Related).
Please feel free to leave me some F'n comments with other F'n good
manuals and books.
http://thinkoracle.blogspot.com/2005_07_01_archive.html (16 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
// posted by Robert Vollman @ Thursday, July 14, 2005 4 comments
Wednesday, July 13, 2005
Stored Procedure template
Since there was some interest in the "object" template,
http://thinkoracle.blogspot.com/2005/06/oop-in-plsql-yep.html
and since I've been dry on ideas lately because I haven't been
working on non-Oracle things the last week or two, I decided to
share another one of my templates. This time: stored procedures.
Enjoy!
SET SERVEROUTPUT ON;
-- 'NOCOPY' makes it a pointer (pass by reference) - faster.
-- 'DETERMINISTIC' means the output is the same for every input -
allows caching (for return type tables)
-- CREATE OR REPLACE FUNCTION MyFunc (p_something IN OUT
NOCOPY Transactions.number%TYPE)
-- RETURN BOOLEAN DETERMINISTIC
-- 'DEFAULT' is the same as ':=' for both here and DECLARE
CREATE OR REPLACE PROCEDURE MyProc (p_something IN
Transactions.number%TYPE DEFAULT 1)
-- The following says it is independent of anything calling it (eg:
rollbacks, etc)
-- IS PRAGMA AUTONOMOUS_TRANSACTION
AS
[[BlockName]]
-- If its an anonymous block:
-- DECLARE
v_datetime TIMESTAMP;
v_transaction Transactions.number%TYPE := 0;
v_the_date_is CONSTANT VARCHAR2(12) := 'The date is ';
-- Create an exception, the pragma is optional
v_my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(v_my_exception, 100);
-- subprograms (functions) MUST be declared last
PROCEDURE NothingReally IS BEGIN NULL; END NothingReally;
http://thinkoracle.blogspot.com/2005_07_01_archive.html (17 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
BEGIN
-- SET TRANSACTION READ ONLY; -- Use consistent snapshot of
the database
-- SET TRANSACTION READ WRITE; -- The default. Turns "off" the
Read Only
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- SET TRANSACTION USE ROLLBACK SEGMENT
-- This is a comment
SELECT systime INTO BlockName.v_datetime FROM dual;
DBMS_OUTPUT.PUT_LINE(v_the_date_is || v_datetime);
[[LoopName]]
IF 1=1
THEN NULL;
ELSIF 1=2
THEN RAISE NO_DATA_FOUND;
ELSE
THEN NothingReally;
END IF;
CASE
WHEN 1=1 THEN NULL;
-- Application Errors have to be -20000 to -20999 inclusive and
can't be caught specifically
WHEN 1=2 THEN RAISE_APPLICATION_ERROR(-20000, 'Error: '||
v_the_date_is||' BAH');
ELSE NULL;
END CASE;
LOOP
EXIT WHEN 1=1;
END LOOP;
FOR v_transaction IN 0 .. 10
LOOP
NULL;
END LOOP;
WHILE 1=2
LOOP
RAISE v_my_exception;
END LOOP;
http://thinkoracle.blogspot.com/2005_07_01_archive.html (18 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
COMMIT;
EXCEPTION
-- This only covers errors raised after BEGIN (but NOT in 'DECLARE'!)
-- You can "OR" exceptions.
-- An exception can't be in more than 1 block
WHEN v_my_exception
THEN NULL;
-- This is optional but good practice.
-- Unhandled exceptions fall through to the next block or statement
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END MyProc;
/
// posted by Robert Vollman @ Wednesday, July 13, 2005 0 comments
Monday, July 11, 2005
Specifying INSERT Columns
Here is a quick one that I touched on briefly in one of my first blogs:
http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html
It can be important to specify which columns you plan on providing
values for in an INSERT statement. Repeating Dan Morgan's quote:
"By not specifying column names you are signifying that you are
providing values for ALL columns. This is why it is a very bad
practice as doing an ALTER TABLE ADD immediately invalidates all
SQL statements."
Here is an example. I created a table, and then wrote a procedure
that will insert a row WITHOUT specifying the values. Works fine if
the table doesn't change:
SET SERVEROUTPUT ON;
CREATE TABLE MyTable (MyInt NUMBER);
CREATE OR REPLACE PROCEDURE InsertIntoMyTable (InValue IN
http://thinkoracle.blogspot.com/2005_07_01_archive.html (19 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
NUMBER)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserting ' || InValue || ' into MyTable');
INSERT INTO MyTable VALUES (InValue);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END InsertIntoMyTable;
/
EXEC InsertIntoMyTable(1);
Inserting 1 into MyTable
PL/SQL procedure successfully completed.
So let's try modifying the table, and try again.
ALTER TABLE MyTable ADD (MyString VARCHAR2(32));
EXEC InsertIntoMyTable(2);
BEGIN InsertIntoMyTable(2); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SCOTT.INSERTINTOMYTABLE is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
The procedure fails, and can't recompile. Perhaps you want that
behaviour, because you want to require all your procedures to be
checked every time a related table has changed. But, then again,
maybe you don't.
In that case, modify the stored procedure to specify which value you
are inserting, and then you're good to go. Let's repeat this test that
way:
CREATE OR REPLACE PROCEDURE InsertIntoMyTable (InValue IN
NUMBER)
AS
http://thinkoracle.blogspot.com/2005_07_01_archive.html (20 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserting ' || InValue || ' into MyTable');
INSERT INTO MyTable (MyInt) VALUES (InValue);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END InsertIntoMyTable;
/
SQL> EXEC InsertIntoMyTable(2);
Inserting 2 into MyTable
PL/SQL procedure successfully completed.
SQL> ALTER TABLE MyTable ADD (MyOtherInt NUMBER);
Table altered.
SQL> EXEC InsertIntoMyTable(3);
Inserting 3 into MyTable
PL/SQL procedure successfully completed.
// posted by Robert Vollman @ Monday, July 11, 2005 0 comments
Tuesday, July 05, 2005
Regular Expressions in Oracle
I was recently asked to do a blog about Regular Expressions in
Oracle 10, because they were cool.
They are cool.
Except to people that get spooked by a bunch of [:whatever:] in
their code, and feel their lunch come up when they see '[^:]+,\?.{3}
(tip: if that is you, don't ever read Perl code).
Anyway I can not do a blog on Regular Expressions in Oracle 10 for
two reasons:
1. I only blog about what I'm currently working on and studying, and
http://thinkoracle.blogspot.com/2005_07_01_archive.html (21 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
2. I use Oracle 9.
But no worries. There is a great article on Oracle Regular
Expressions:
http://www.oracle.com/technology/oramag/webcolumns/
2003/techarticles/rischert_regexp_pt1.html
Hmph, looks like I blogged about Oracle Regular Expressions after
all.
Oh yes, and here is your Regular Expression reference, courtesy of
(who else) Dan Morgan:
http://www.psoug.org/reference/regexp.html
He covers REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE,
REGEXP_SUBSTR.
// posted by Robert Vollman @ Tuesday, July 05, 2005 3 comments
Monday, July 04, 2005
SQLCODE and SQLERRM in INSERTs
Here is an interesting foible. Normally you can build strings on the
fly from functions and insert them into a table. Like so:
CREATE TABLE LogTable (logString VARCHAR2(128));
CREATE OR REPLACE FUNCTION MyFunc RETURN VARCHAR2
AS
BEGIN
RETURN 'Hello';
END;
CREATE OR REPLACE PROCEDURE MyProc
AS
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
INSERT INTO LogTable (logString) VALUES (MyFunc || ' ' || MyFunc);
http://thinkoracle.blogspot.com/2005_07_01_archive.html (22 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
END;
But you can't do this with SQLCODE and SQLERRM.
CREATE OR REPLACE PROCEDURE MyProc
AS
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
INSERT INTO LogTable (logString) VALUES (SQLCODE || ' ' ||
SQLERRM);
END;
PL/SQL: ORA-00984: column not allowed here
You can always put the values in a string for a workaround.
CREATE OR REPLACE PROCEDURE MyProc
AS
errString LogTable.logString%TYPE;
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
errString := SQLCODE || ' ' || SQLERRM;
INSERT INTO LogTable (logString) VALUES (errString);
END;
Procedure created.
For reference, look up SQLCODE and SQLERRM in the PL/SQL User's
Guide and Reference.
Bonus. I found the following blog while reviewing OraBlogs:
http://blog.niftypaint.nl/
Which pointed me to Eddie Awad's great blog:
http://awads.net/wp/
Enjoy!
http://thinkoracle.blogspot.com/2005_07_01_archive.html (23 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
// posted by Robert Vollman @ Monday, July 04, 2005 5 comments
Friday, July 01, 2005
Extra Columns in a GROUP BY
Happy Canada Day. I have a really good one today.
My problem:
I have a table 'ASSIGNMENTS' that keeps track of each assignment,
who holds which assignment, and as of which date they have held it.
It would be very handy to have a view that shows all assignments,
and who is currently on that assignment.
CREATE TABLE ASSIGNMENTS(ASSIGNMENT VARCHAR2(32),
EMPLOYEE VARCHAR2(32), EFFECTIVE DATE);
INSERT INTO ASSIGNMENTS(ASSIGNMENT, EMPLOYEE, EFFECTIVE)
VALUES ('Prime Minister', 'Jean Chretien', '04-Nov-93');
INSERT INTO ASSIGNMENTS(ASSIGNMENT, EMPLOYEE, EFFECTIVE)
VALUES ('Governor General', 'Adrienne Clarkson', '07-Oct-99');
INSERT INTO ASSIGNMENTS(ASSIGNMENT, EMPLOYEE, EFFECTIVE)
VALUES ('Prime Minister', 'Paul Martin', '12-Dec-03');
You must be familiar with GROUP BY.
SELECT ASSIGNMENT, MAX(EFFECTIVE) AS_OF FROM ASSIGNMENTS
GROUP BY ASSIGNMENT;
ASSIGNMENT AS_OF
-------------------------------- ---------
Governor General 07-OCT-99
Prime Minister 12-DEC-03
2 rows selected.
Ok, now we would like to add in the name of the person. But
whoops, doesn't work as you'd expect.
SELECT ASSIGNMENT, EMPLOYEE, MAX(EFFECTIVE) AS_OF FROM
ASSIGNMENTS GROUP BY ASSIGNMENT;
ORA-00979: not a GROUP BY expression
http://thinkoracle.blogspot.com/2005_07_01_archive.html (24 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
You see, we can't include any columns that aren't part of the GROUP
BY. And, we can't include it in the group by, because then we'd get a
result no different from assignments.
There is an answer. Connor McDonald has it:
http://www.oracledba.co.uk/tips/9i_first_last.htm
Alright, what is Connor talking about? Basically he is grouping the
"extra" column (empno), and making it a secondary grouping to the
main grouping (sal).
But, really, MIN(EMPNO)? Why are we including an aggregate
function on empno? Well, Connor is doing it because he wants to
break ties.
Even though we don't need to break ties, we still need to use an
aggregate function, otherwise it becomes part of the group by. Any
aggregate function will do since we shouldn't have duplicates.
DENSE_RANK , FIRST, ORDER BY
- ORDER BY will sort a set of rows by a particular column. In
Connor's case it is salary, in our case, it will be 'effective'.
- DENSE_RANK provides the positioning of a particular row within
an ordered list of rows.
- FIRST goes hand-in-hand with DENSE_RANK and will naturally
provide us with the first, ranked row in a ordered list of rows.
Note that the ORDER BY defaults to ascending order, and so in our
case we either want to choose LAST or put the ORDER BY in
descending order instead.
Essentially he is creating an ordered list of all salaries, and choosing
the empno that shows up first on that list. Sounds like what we
want!
Here is Dan Morgan's reference on numeric functions like these:
http://www.psoug.org/reference/number_func.html
KEEP is just a clever Oracle trick to keep the work we're doing in the
shared pool because we're using it twice in the same query.
So applying Connor's teachings to our situation, we get:
http://thinkoracle.blogspot.com/2005_07_01_archive.html (25 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
CREATE OR REPLACE VIEW CURRENT_ASSIGNMENTS AS
SELECT ASSIGNMENT,
MAX(EMPLOYEE) KEEP (DENSE_RANK LAST ORDER BY EFFECTIVE)
EMPLOYEE,
MAX(EFFECTIVE) AS_OF
FROM ASSIGNMENTS
GROUP BY ASSIGNMENT;
SELECT * FROM CURRENT_ASSIGNMENTS;
ASSIGNMENT EMPLOYEE AS_OF
--------------------------------
-------------------------------- ---------
Governor General Adrienne Clarkson 07-OCT-99
Prime Minister Paul Martin 12-DEC-03
2 rows selected.
Coming up in some future blog, Tom Kyte mentioned the awesome
"MEMBER OF" feature that is in Oracle 10g. It will tell you easily
whether a value is contained in a particular set/table. I'm looking
for a way to do this in Oracle 9.
http://thinkoracle.blogspot.com/2005/05/enum-in-oracle.html
// posted by Robert Vollman @ Friday, July 01, 2005 1 comments
About Me
Name: Robert Vollman
Location: Calgary, Alberta, Canada
I was born and raised in Ottawa, and have lived in Calgary since 1991. I
like playing sports (hockey, soccer, ultimate, basketball, you name it)
and military board games. I also enjoy reading, walking, and playing with my 2 cats
Lilly and Brutus. I'm a database application specialist, whatever that is.
http://thinkoracle.blogspot.com/2005_07_01_archive.html (26 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
View my complete profile
Best Links
● Ask Tom Kyte
● Oracle Docs
● Dan Morgan and PSOUG
● Steven Feuerstein
● Jonathan Lewis
● FAQ
● Connor McDonald
● The Oak Table
● Cary Millsap and Hotsos
● Steve Adams and Ixora
● Anjo Kolk and OraPerf
● Dizwell Oracle Wiki
● My Personal Blog
Aggregators
Brian Duff's OraBlogs
❍
❍ Eddie Awad's OracleNA
❍ Pete Finnigan's Aggregator
❍ Oracle's Bloglist
❍ Oracle Base Aggregator
Top Blogs
❍ Oracle's Ask Tom Kyte
❍ Oracle Guru Jonathan Lewis
❍ Blogger of the Year Eddie Awad
❍ Data Warehouser David Aldridge
❍ Oracle Geek Lewis Cunningham
❍ Database Expert James Koopmann
❍ Dizwell's Howard Rogers
❍ Oracle Master Laurent Schneider
❍ Security Expert Pete Finnigan
❍ Oracle Award Winner Mark Rittman
❍ Doug Burns
http://thinkoracle.blogspot.com/2005_07_01_archive.html (27 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
❍ Oracle ACE of the Year Dr. Tim Hall
❍ UKOUG's Andrew (Arfur C.) Clarke
❍ Newbie DBA Lisa Dobson
❍ Coffee-Drinking DBA Jon Emmons
❍ Chris Foot
❍ The Pythian DBA Team Blog
❍ DBA Don Seiler
❍ DBA Coskan Gundogar
❍ Oracle WTF
ARCHIVES
❍ LIST ALL ARTICLES
❍ May 2005
❍ June 2005
❍ July 2005
❍ August 2005
❍ September 2005
❍ October 2005
❍ November 2005
❍ December 2005
❍ January 2006
❍ February 2006
❍ March 2006
❍ April 2006
❍ May 2006
❍ June 2006
❍ July 2006
❍ August 2006
❍ September 2006
❍ October 2006
❍ November 2006
❍ December 2006
❍ January 2007
❍ February 2007
❍ March 2007
❍ April 2007
❍ May 2007
❍ June 2007
❍ October 2007
http://thinkoracle.blogspot.com/2005_07_01_archive.html (28 of 29)1/9/2008 2:49:36 AM
OracleBlog: July 2005
❍ Current Posts
http://thinkoracle.blogspot.com/2005_07_01_archive.html (29 of 29)1/9/2008 2:49:36 AM
OracleBlog: August 2005
OracleBlog
I love data. Collecting, storing, manipulating, studying and using data. Oracle may be complicated, but it
is the most powerful tool when it comes to data. That's why when I think data, I think Oracle. I also enjoy
writing, so I use this format to organise my thoughts. Please feel free to discuss any thoughts you may
have on the same topics, even old ones (I will see and respond to such comments). You may want to
start with "LIST ALL ARTICLES" under Archives.
Monday, August 29, 2005
About Me
New Blogs Name: Robert
Not long ago, I wrote a blog on all the Oracle Blogs out Vollman
there: Location: Calgary,
Alberta, Canada
http://thinkoracle.blogspot.com/2005/07/oracle-blogs.
html
I was born and raised in Ottawa,
and have lived in Calgary since
Not long after that, I included a list of links to all the blogs
1991. I like playing sports
I follow (see the side, below links).
(hockey, soccer, ultimate,
Well there have been a couple of changes and new basketball, you name it) and
additions. The most exciting of which is I found a blog by military board games. I also
Laurent Schneider. He is one of my favourite posters from enjoy reading, walking, and
the Oracle Forum: playing with my 2 cats Lilly and
Brutus. I'm a database
http://forums.oracle.com/forums/forum.jspa?forumID=75 application specialist, whatever
that is.
His posts usually involve how to use Oracle to solve
someone's problems. Over the months I've been reading View my complete profile
this forum, I've seen him cover a wide spectrum.
Here is a link to one of of his most recent articles:
Best Links
http://laurentschneider.blogspot.com/2005/08/pivot- ● Ask Tom Kyte
table.html ● Oracle Docs
● Dan Morgan and PSOUG
There are a few other changes: ● Steven Feuerstein
Jonathan Lewis
Doug Burns changed his blog's location:
●
FAQ
http://oracledoug.blogspot.com/
●
● Connor McDonald
Radoslav Rusinov has a new blog, which he has kicked off ● The Oak Table
with a discussion of the latest Burleson Boondoggle on PGA: ● Cary Millsap and Hotsos
http://dba-blog.blogspot.com/ ● Steve Adams and Ixora
● Anjo Kolk and OraPerf
http://thinkoracle.blogspot.com/2005_08_01_archive.html (1 of 15)1/9/2008 2:49:41 AM
OracleBlog: August 2005
Hopefully these blogs will make up for my lack of content ● Dizwell Oracle Wiki
recently! ● My Personal Blog
// posted by Robert Vollman @ Monday, August 29, 2005 2 comments
Aggregators
Brian Duff's OraBlogs
❍
Wednesday, August 24, 2005 ❍ Eddie Awad's OracleNA
Pete Finnigan's Aggregator
COMPUTE ❍
Oracle's Bloglist
Let's say you wanted to write a query that contained an
❍
Oracle Base Aggregator
aggregate (eg: sum, min, max, count). No problem, you can ❍
use 'GROUP BY'.
Top Blogs
But suppose you wanted to write a query that was a report ❍ Oracle's Ask Tom Kyte
containing both the aggregate and the detail. That makes it ❍ Oracle Guru Jonathan Lewis
trickier. ❍ Blogger of the Year Eddie Awad
Data Warehouser David Aldridge
You may even know how to do it in other databases: ❍
❍ Oracle Geek Lewis Cunningham
http://sybasease.blogspot.com/2005/08/compute.html ❍ Database Expert James Koopmann
❍ Dizwell's Howard Rogers
There is a simple way of accomplishing this in SQL*Plus. ❍ Oracle Master Laurent Schneider
SQL*Plus includes a function of the same name (COMPUTE) ❍ Security Expert Pete Finnigan
that will do the formatting for you. ❍ Oracle Award Winner Mark
Rittman
In order to use it, you also have to use 'BREAK'. Let's take a
Doug Burns
very brief look at it. BREAK, like COMPUTE, is an SQL*Plus
❍
command that you would issue at any time prior to your ❍ Oracle ACE of the Year Dr. Tim
query. For example, the following BREAK command will Hall
remove all values in DEPTNO if they are the same as the ❍ UKOUG's Andrew (Arfur C.) Clarke
preceding value, and skip 1 line after each grouping. ❍ Newbie DBA Lisa Dobson
❍ Coffee-Drinking DBA Jon Emmons
scott@Robert> BREAK ON deptno SKIP 1 NODUPLICATES ❍ Chris Foot
scott@Robert> SELECT ename, sal, deptno ❍ The Pythian DBA Team Blog
2 FROM emp ❍ DBA Don Seiler
3 ORDER BY deptno; ❍ DBA Coskan Gundogar
ENAME SAL DEPTNO ❍ Oracle WTF
ARCHIVES
---------- ---------- ----------
CLARK 2450 10
VOLLMAN 5000
❍ LIST ALL ARTICLES
MILLER 1300
SMITH 800 20 ❍ May 2005
ADAMS 1100 ❍ June 2005
FORD 3000 ❍ July 2005
SCOTT 3000 ❍ August 2005
JONES 2975 ❍ September 2005
ALLEN 1600 30 ❍ October 2005
BLAKE 2850 ❍ November 2005
MARTIN 1250 ❍ December 2005
JAMES 950 ❍ January 2006
http://thinkoracle.blogspot.com/2005_08_01_archive.html (2 of 15)1/9/2008 2:49:41 AM
OracleBlog: August 2005
TURNER 1500 ❍ February 2006
WARD 1250 ❍ March 2006
❍ April 2006
❍ May 2006
June 2006
Now let's add our COMPUTE command. This one will ❍
calculate the subtotal of salary, just like it would using ❍ July 2006
GROUP BY. The advantage here is that its in the same table. ❍ August 2006
❍ September 2006
scott@Robert> COMPUTE SUM LABEL subtotal OF sal ON ❍ October 2006
deptno ❍ November 2006
scott@Robert> SELECT ename, sal, deptno FROM emp ❍ December 2006
ORDER BY deptno; ❍ January 2007
❍ February 2007
ENAME SAL DEPTNO ❍ March 2007
---------- ---------- ----------
❍ April 2007
CLARK 2450 10
❍ May 2007
VOLLMAN 5000
❍ June 2007
MILLER 1300
---------- ********** ❍ October 2007
8750 subtotal
❍ Current Posts
SMITH 800 20
ADAMS 1100
FORD 3000
SCOTT 3000
JONES 2975
---------- **********
10875 subtotal
ALLEN 1600 30
BLAKE 2850
MARTIN 1250
JAMES 950
TURNER 1500
WARD 1250
---------- **********
9400 subtotal
Naturally, this also works with other aggregate functions
like SUM, MIN, MAX, AVG, STD, VARIANCE, COUNT,
NUMBER.
For reference, check out the SQL*Plus User's Guide:
http://download-west.oracle.com/docs/cd/B10501_01/
server.920/a90842.pdf
You want chapters 7 and 13:
7 Formatting SQL*Plus Reports
13 SQL*Plus Command Reference
http://thinkoracle.blogspot.com/2005_08_01_archive.html (3 of 15)1/9/2008 2:49:41 AM
OracleBlog: August 2005
UPDATE: Actually there's a simple way without SQL*Plus, by
using GROUPING, see Tom Kyte's Comment. Here is his
example of my Oracle9 environment.
scott@Robert> SELECT DECODE(GROUPING_ID(ename), 1,
'Subtotal' ) tag
2 ename, deptno, SUM(sal)
3 FROM emp
4 GROUP BY GROUPING SETS( (ename,deptno), (deptno) );
TAG ENAME DEPTNO SUM(SAL)
-------- ---------- ---------- ----------
CLARK 10 2450
MILLER 10 1300
VOLLMAN 10 5000
Subtotal 10 8750
FORD 20 3000
ADAMS 20 1100
JONES 20 2975
SCOTT 20 3000
SMITH 20 800
Subtotal 20 10875
WARD 30 1250
ALLEN 30 1600
BLAKE 30 2850
JAMES 30 950
MARTIN 30 1250
TURNER 30 1500
Subtotal 30 9400
Read about GROUPING, GROUPING_ID and GROUPING SETS
in SQL Reference:
http://download-west.oracle.com/docs/cd/B10501_01/
server.920/a96540.pdf
// posted by Robert Vollman @ Wednesday, August 24, 2005 6 comments
Wednesday, August 17, 2005
Keeping Tables Small
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.html
There is one really simple, effective but often overlooked
http://thinkoracle.blogspot.com/2005_08_01_archive.html (4 of 15)1/9/2008 2:49:41 AM
OracleBlog: August 2005
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 roughly calculated
like so:
Number of Rows TIMES Time to Process One Row
Many people focus on the second part of the question (the
time it takes to process a single row), when sometimes it is
far more simple to decrease the number of rows.
What that means is, look at the table and determine if some
of the data is used infrequently enough that it can be
moved aside to an archived version of this table.
You may need to modify your existing applications to
account for cases where you truly do need to look at all
that data. In that case, you might keep a view of all data.
That is basically my point, so you can stop reading there if
you wish. I have put together an example, where I elaborate
on this a little further. It is meant only as a demonstration
of what I mean, not any kind of compelling argument or
proof.
First off, here is an easy way to create a nice, big test table.
CREATE TABLE ReallyBigTable AS SELECT * FROM
ALL_OBJECTS;
SELECT COUNT (*) FROM ReallyBigTable;
40763
Now I'm going to create a query that ought to really suffer,
performance-wise, the larger the table is:
SELECT SUM(object_id) FROM ReallyBigTable
WHERE object_id * 2 NOT IN
(SELECT object_id FROM ReallyBigTable);
Time Elapsed: 00:11:42.09
Alright, now let's create an archive and active version of
this table. For argument's sake, let's say that anything with
an object_id below 40000 is used infrequently enough that
it is safe to push aside for our business-specific purposes:
CREATE TABLE ReallyBigTable_Archive AS SELECT * FROM
ReallyBigTable
WHERE object_id < 40000;
http://thinkoracle.blogspot.com/2005_08_01_archive.html (5 of 15)1/9/2008 2:49:41 AM
OracleBlog: August 2005
SELECT COUNT (*) FROM ReallyBigTable_Archive;
38000
CREATE TABLE ReallyBigTable_Active AS SELECT * FROM
ReallyBigTable
WHERE object_id >= 40000;
SELECT COUNT (*) FROM ReallyBigTable_Active;
2763
I'm in a single-user environment doing a test, so I really
don't have to worry right now about updates taking place.
Otherwise we'd have to more clever about the creation of
our archive and active tables. But I will say that creating
these tables can be lightning-fast!
Ok, now let's try our query on our little table:
SELECT SUM(object_id) FROM ReallyBigTable_Active
WHERE object_id * 2 NOT IN
(SELECT object_id FROM ReallyBigTable_Active);
Time Elapsed: 00:00:02.08
So it completed in 0.3% of the time. Awesome!
"OK," you say, "but you're cheating!" So what if I am?
Sometimes in the real world we CAN cheat. Sometimes we
don't have to go through all the data, every time, every
case. Don't get mad at Oracle or your DBA or your
application when it's not fast enough, just get creative. You
don't always have to apply painful Kolkian techniques or
Millsapian analysis to get results.
Moving on, there may still be some applications that want
to query on the whole dataset. No problem, that is still
possible using UNION. But what kind of price will we pay?
Let's see, in this one example:
DROP TABLE ReallyBigTable;
CREATE VIEW ReallyBigTable AS
SELECT * FROM ReallyBigTable_Archive
UNION ALL
SELECT * FROM ReallyBigTable_Active;
Remember, a view is just like a stored query, but we can
use it like a table. Which means our applications don't have
to change their queries.
http://thinkoracle.blogspot.com/2005_08_01_archive.html (6 of 15)1/9/2008 2:49:41 AM
OracleBlog: August 2005
http://thinkoracle.blogspot.com/2005/07/use-views.html
One limitation of this kind of view is that we won't be able
to insert from our legacy applications anymore:
INSERT INTO ReallyBigTable (object_id) VALUES (NULL);
ORA-01732: data manipulation operation not legal on this
view
Let's see how much of a performance penalty we have in
this case by using a view instead of the original base table:
SELECT SUM(object_id) FROM ReallyBigTable
WHERE object_id * 2 NOT IN
(SELECT object_id FROM ReallyBigTable);
Time Elapsed: 00:08:18.02
What??? The use of a view on two smaller sub-tables took
less time than the original table? It only took 70% as much
time. Probably something to do with having the data in
memory, or statistics, or something like that. I'll get back
to you in another blog. The main thing I wanted to
demonstrate was that the performance would be in the
same "order" as the original query. That is, you're not
taking a huge performance hit by using a view.
Out of curiousity, let's take a mixed sub-example, where
we are using our new, smaller table against that big view.
SELECT SUM (object_id) FROM ReallyBigTable_Active
WHERE object_id * 2 NOT IN
(SELECT object_id FROM ReallyBigTable);
Time Elapsed: 00:00:43.05
Nice.
One final point, if I may. Notice I used "UNION ALL" instead
of "UNION." I did this because UNION rearranges the order
of the table, and removes duplicates.
http://thinkoracle.blogspot.com/2005/08/union-all.html
I don't care about duplicates and order, and I didn't want
the performance hit, so I used UNION ALL. Of course, I am
curious to know how significant the performance hit is in
this case. Let's see.
CREATE OR REPLACE VIEW ReallyBigTable AS
SELECT * FROM ReallyBigTable_Archive
UNION
http://thinkoracle.blogspot.com/2005_08_01_archive.html (7 of 15)1/9/2008 2:49:41 AM
OracleBlog: August 2005
SELECT * FROM ReallyBigTable_Active;
SELECT SUM(object_id) FROM ReallyBigTable
WHERE object_id * 2 NOT IN
(SELECT object_id FROM ReallyBigTable);
Time Elapsed: 00:13:20.02
So it took about 60% longer than with UNION ALL.
SELECT SUM (object_id) FROM ReallyBigTable_Active
WHERE object_id * 2 NOT IN
(SELECT object_id FROM ReallyBigTable);
Time Elapsed: 00:00:54.05
And this one about 25% longer. So in this case it was
definitely noticeable.
Wrap-up:
1. David Aldridge has a great article on how to make your
table scans go faster.
2. In addition to his techniques, I recommend "being
creative" (aka "cheating") and trying to split your data.
3. Use Views to maintain applications that SELECT from the
big, original table so you don't have to change code and
can still query the whole set.
4. Using Views will not have a big performance hit. In fact,
in this demonstration it was faster (a topic for another
day's blog).
5. Use UNION ALL instead of UNION in this view to avoid
the noticeable performance hit.
// posted by Robert Vollman @ Wednesday, August 17, 2005 4 comments
Sunday, August 14, 2005
UTL_HTTP
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 scores
No 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 'Supplied PL/SQL
http://thinkoracle.blogspot.com/2005_08_01_archive.html (8 of 15)1/9/2008 2:49:41 AM
OracleBlog: August 2005
Packages and Types Reference'.
Tom Kyte also had a discussion in the Appendix of "Expert
One-on-One Oracle", and also on Ask Tom:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::
F4950_P8_DISPLAYID:285215954607
Let's look at a simple example, grabbing a market
quotation.
You can get the latest quotes from Yahoo Finance. Using
UTL_HTTP.REQUEST, you can get the content of that page,
and then search within for the data you want.
SELECT UTL_HTTP.REQUEST('http://finance.yahoo.com/q?
s=KMP') FROM DUAL;
If you're behind a firewall, include the IP or name of your
proxy server as the second parameter.
For simplicity, I'm not including the output here, but you
can see that we don't have our quote in there. That's
because we only got the first 2000 bytes of the web page.
If we want more, we need to use REQUEST_PIECES.
Down below I have included a working example. It's
roughly thrown together, but it does illustrate the point:
you can use UTL_HTTP to retrieve information from the
Internet that you can put in the database.
As an aside, talk to some lawyers to make sure the data
you are mining is not violating any copyrights.
After you write stored procedures to retrieve web pages,
and to extract information from within and insert them into
the database, you can automate the updates by calling the
stored procedures using DBMS_JOB (a topic for another
day!).
SET SERVEROUTPUT ON
DECLARE
l_pieces UTL_HTTP.HTML_PIECES;
-- We'll look at two 2000-byte pages at a time
l_two_pages VARCHAR2(4000);
l_start_read NUMBER;
l_end_read NUMBER;
l_quote VARCHAR2(12);
BEGIN
-- Grab up to a maxium of 32 2000-byte pages, and then
go through them,
-- looking at 2 pages at a time in case the data we are
http://thinkoracle.blogspot.com/2005_08_01_archive.html (9 of 15)1/9/2008 2:49:41 AM
OracleBlog: August 2005
looking for
-- overlaps a page boundary
l_pieces := UTL_HTTP.REQUEST_PIECES('http://finance.
yahoo.com/q?s=KMP', 32);
FOR i IN 1 .. l_pieces.COUNT LOOP
l_two_pages := l_two_pages || l_pieces(i);
-- Look for a string preceding the information we want
-- If we find it, add 52 (magic, Yahoo-specific number)
-- to find the point where the quote will begin
SELECT INSTR(l_two_pages, 'Last Trade', 1, 1) INTO
l_start_read FROM dual;
IF (l_start_read > 0) THEN
l_start_read := l_start_read + 52;
IF (l_start_read < 3950) THEN
SELECT INSTR(l_two_pages, '<', l_start_read, 1) INTO
l_end_read FROM dual;
IF (l_end_read > 0) THEN
IF ((l_end_read - l_start_read) < 12) THEN
SELECT SUBSTR(l_two_pages, l_start_read, l_end_read -
l_start_read) INTO l_quote FROM dual;
DBMS_OUTPUT.PUT_LINE(l_quote);
ELSE
DBMS_OUTPUT.PUT_LINE('Error (Quote more than 12
chars)');
END IF;
EXIT;
END IF;
END IF;
END IF;
l_two_pages := l_pieces(i);
END LOOP;
END;
// posted by Robert Vollman @ Sunday, August 14, 2005 4 comments
Wednesday, August 10, 2005
UNION ALL
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 purposes, however,
let's consider only their names and phone numbers.
http://thinkoracle.blogspot.com/2005_08_01_archive.html (10 of 15)1/9/2008 2:49:41 AM
OracleBlog: August 2005
CREATE TABLE Employee (e_name VARCHAR2(32),
e_phonenumber VARCHAR2(16));
CREATE TABLE Contractor (c_name VARCHAR2(32),
c_phonenumber VARCHAR2(16), c_startcontract DATE,
c_endcontract DATE);
CREATE TABLE Client (c_name VARCHAR2(32),
c_phonenumber VARCHAR2(16));
Let's get some sample data:
INSERT INTO Employee VALUES ('Joe Smith', '555-555-
1234');
INSERT INTO Contractor VALUES ('Adam Johnson', '555-
555-8888', '01-Jan-04', '01-Mar-04');
INSERT INTO Contractor VALUES ('Bob Jackson', '555-555-
1111', '01-Jan-04', NULL);
INSERT INTO Contractor VALUES ('Adam Johnson', '555-
555-8888', '01-Jan-05', '01-Mar-05');
INSERT INTO Client VALUES ('Bill Taylor', '555-555-6767');
INSERT INTO Client VALUES ('Adam Johnson', '555-555-
8888');
What you would like to do is create a view to contain all the
phone numbers. You can use the UNION operator, which is
very easy. Write your queries however you wish, just make
sure that they all have the same number of columns and
similar data types.
SELECT e_name, e_phonenumber FROM Employee
UNION
SELECT c_name, c_phonenumber FROM Contractor
UNION
SELECT c_name, c_phonenumber FROM Client;
E_NAME E_PHONENUMBER
-------------------------------- ----------------
Adam Johnson 555-555-8888
Bill Taylor 555-555-6767
Bob Jackson 555-555-1111
Joe Smith 555-555-1234
Excellent!
But observe two things:
1. The order of the results have been re-arranged
2. There are no duplicates.
Actually, 1 and 2 are tied closely together. Oracle re-
arranges the results in order to put identical rows next to
each other and remove duplicates. On large tables, you
http://thinkoracle.blogspot.com/2005_08_01_archive.html (11 of 15)1/9/2008 2:49:41 AM
OracleBlog: August 2005
may get a bit of a performance hit.
If you don't care about removing duplicates, or especially if
you want the duplicates, use UNION ALL instead:
SELECT e_name, e_phonenumber FROM Employee
UNION ALL
SELECT c_name, c_phonenumber FROM Contractor
UNION ALL
SELECT c_name, c_phonenumber FROM Client;
E_NAME E_PHONENUMBER
-------------------------------- ----------------
Joe Smith 555-555-1234
Adam Johnson 555-555-8888
Bob Jackson 555-555-1111
Adam Johnson 555-555-8888
Bill Taylor 555-555-6767
Adam Johnson 555-555-8888
If you want them sorted, but don't want duplicates
removed, you can include an ORDER BY clause. Use UNION
with ORDER BY if you want duplicates removed and you
want a guaranteed order.
SELECT e_name, e_phonenumber FROM Employee
UNION ALL
SELECT c_name, c_phonenumber FROM Contractor
UNION ALL
SELECT c_name, c_phonenumber FROM Client
ORDER BY 1;
E_NAME E_PHONENUMBER
-------------------------------- ----------------
Adam Johnson 555-555-8888
Adam Johnson 555-555-8888
Adam Johnson 555-555-8888
Bill Taylor 555-555-6767
Bob Jackson 555-555-1111
Joe Smith 555-555-1234
If you just want to remove duplicates within tables, but not
in the merged set, try the DISTINCT clause.
SELECT DISTINCT e_name, e_phonenumber FROM Employee
UNION ALL
SELECT DISTINCT c_name, c_phonenumber FROM
http://thinkoracle.blogspot.com/2005_08_01_archive.html (12 of 15)1/9/2008 2:49:41 AM
OracleBlog: August 2005
Contractor
UNION ALL
SELECT DISTINCT c_name, c_phonenumber FROM Client;
E_NAME E_PHONENUMBER
-------------------------------- ----------------
Joe Smith 555-555-1234
Adam Johnson 555-555-8888
Bob Jackson 555-555-1111
Adam Johnson 555-555-8888
Bill Taylor 555-555-6767
I guess it all depends what you want.
Here is Dan Morgan's reference on Built-In Operators:
http://www.psoug.org/reference/ora_operators.html
But you also want to check out the Oracle SQL Reference,
Chapter 8: SQL Queries and Subqueries.
For your reading pleasure, check out this article by
Jonathan Gennick on set operators, including UNION:
http://five.pairlist.net/pipermail/oracle-
article/2003/000003.html
Administrative note: I've added a new section to include the
links to all the Oracle blogs I regularly visit, to save you
from going here:
http://thinkoracle.blogspot.com/2005/07/oracle-blogs.
html
// posted by Robert Vollman @ Wednesday, August 10, 2005 3 comments
Tuesday, August 09, 2005
OraBlogs!
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://thinkoracle.blogspot.com/2005_08_01_archive.html (13 of 15)1/9/2008 2:49:41 AM
OracleBlog: August 2005
http://www.feedburner.com/
Just go to their web site, type in the URL to your feed, and
then accept all the defaults. Just be sure that you have
these three settings (courtesy of Doug):
1) Do NOT select Smartfeed
2) Select Convert Format Burner
3) Select RSS 2.0 from the list of options.
If all goes well, this should be my first post on OraBlogs. It
would be great to kick things off with a great post but,
alas, I have nothing to post about today. Instead, just like a
bad 80s sitcom, I will do a "flashback highlight episode.
Here are my 5 favourite posts from my 3 months of
existence:
May 17th: NULL is not nothing
http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.
html
June 10th: NULLs in Oracle (kind of a "Part 2")
http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.
html
July 1st (Canada Day): Extra Columns in a Group By
http://thinkoracle.blogspot.com/2005/07/extra-columns-
in-group-by.html
July 26th: Use Constraints!
http://thinkoracle.blogspot.com/2005/07/use-constraints.
html
July 29th: Using Views and other techniques to solve a
particular problem
http://thinkoracle.blogspot.com/2005/07/oracle-by-
example.html
// posted by Robert Vollman @ Tuesday, August 09, 2005 0 comments
Monday, August 01, 2005
Import Export
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)
http://thinkoracle.blogspot.com/2005_08_01_archive.html (14 of 15)1/9/2008 2:49:41 AM
OracleBlog: August 2005
tools. They are described in Chapter 1 (Export) and Chapter
2 (Import) of the Oracle Utilities Guide, available here:
http://www.oracle.com/technology/documentation/index.
html
Everything you should need to know is in there. For good
measure, I read Chapter 8 of Tom Kyte's "Expert One-on-
One Oracle" on Import and Export.
Word of caution: Think twice before using IMP/EXP as your
back-up strategy on large, complex databases.
Both utilities are located in $ORACLE_HOME/bin directory.
You need to run the $ORACLE_HOME/rdbms/admin/catexp.
sql file. You can see the commands you need by using the
HELP=Y option at your command prompt
C:\> EXP HELP=Y
So for me, the process was as easy as this:
1. Export the data
C:\> EXP USERID=scott/tiger OWNER=scott FILE=scott.dmp
2. Copy the DMP file to the target machine
3. Import the data
C:\> IMP USERID=scott/tiger FILE=scott.dmp FULL=Y
Done! All the tables, triggers, procedures, view and
constraints, as well as all the data. While I was at it, I just
created a BAT file out of this and added it to the Task
Manager to back-up my data regularly. (Unix: SH file and
CRON).
As a final note, here is a good FQ, by Frank Naude:
http://www.orafaq.com/faqiexp.htm
// posted by Robert Vollman @ Monday, August 01, 2005 2 comments
http://thinkoracle.blogspot.com/2005_08_01_archive.html (15 of 15)1/9/2008 2:49:41 AM
OracleBlog: September 2005
OracleBlog
I love data. Collecting, storing, manipulating, studying and using data. Oracle may be
complicated, but it is the most powerful tool when it comes to data. That's why when I
think data, I think Oracle. I also enjoy writing, so I use this format to organise my
thoughts. Please feel free to discuss any thoughts you may have on the same topics,
even old ones (I will see and respond to such comments). You may want to start with
"LIST ALL ARTICLES" under Archives.
Friday, September 30, 2005
PL/SQL Procedure Call Overhead
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 there is at least some overhead to calling PL/SQL procedures. I
mean, if the procedure is not in the cache, you'll obviously have to go
the disk to fetch it.
If it's already in memory, there could still be some overhead in the
passing of parameters. UNLESS you can use the "NOCOPY" hint, that is.
http://thinkoracle.blogspot.com/2005/05/nocopy-hint.html
But to be honest, I don't know how much overhead any particular
procedure call will have. Sorry to those that read the title and hoped that
the content would contain the definitive answer. I might have something
more conclusive after doing some research. In the meantime, here is
what I do every time I have a question: I test it.
Even if I showed you a quote in a book that shows you how to calculate
the overhead, I would STILL advise testing it. Documents can be out-of-
date, misunderstood and just plain wrong. You need to test it.
http://thinkoracle.blogspot.com/2005_09_01_archive.html (1 of 22)1/9/2008 2:49:46 AM
OracleBlog: September 2005
Here is how to test it.
1. Write (or identify) a stored procedure that reflects your business
requirements.
CREATE OR REPLACE PROCEDURE DoIt
IS
BEGIN
NULL;
-- Do some stuff in here!
END DoIt;
2. Now split that stored procedure into two (or more) parts, and a master
proc
CREATE OR REPLACE PROCEDURE DoItPartOne
IS
BEGIN
NULL;
-- Do part of the stuff here...
END DoItPartOne;
...etc!
CREATE OR REPLACE PROCEDURE DoItInParts
IS
BEGIN
DoItPartOne;
-- DoItPartTwo;
-- etc...
END DoItInParts;
3. With stats on, call that first stored procedure that does everything,
and then run TKPROF to analyse it.
ALTER SESSION SET SQL_TRACE = TRUE;
EXEC DoIt;
ALTER SESSION SET SQL_TRACE = FALSE;
TKPROF robert_ora_3028.trc robert_ora_3028.prf explain='sys/********
as sysdba'
More on gathering and analysing simple performance statistics:
http://thinkoracle.blogspot.com/2005/09/analyzing-query-
http://thinkoracle.blogspot.com/2005_09_01_archive.html (2 of 22)1/9/2008 2:49:46 AM
OracleBlog: September 2005
performance.html
4. With stats on, call that second stored procedure.
ALTER SESSION SET SQL_TRACE = TRUE;
EXEC DoItInParts;
ALTER SESSION SET SQL_TRACE = FALSE;
You may find, as I did, that it is very hard to set up a test that reveals any
kind of noticeable performance overhead. But if the procedures were
spread out over the disk and not in the cache, or if there were lots and
lots of parameters, I bet we could see some overhead. But if your
procedure is called often enough for it to be important to you, the
procedures would probably be in the cache at any given time.
But don't spend too much time in conjecture, and even when I do
produce some facts, set up your tests anyway.
// posted by Robert Vollman @ Friday, September 30, 2005 4 comments
Thursday, September 22, 2005
Column Name as a Variable
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 salary,
hourly wage, bonus, dividend, etc. You have made each one of these a
separate column in the table. (Note: all these columns are of the same
type).
You have a number of stored procedures that access these tables. They
all share some things in common, so you have decided to make some
common "helper" procedures for all the "master" procedures to use.
Your "helper" procedure would have to take the column name from the
"master" procedure, and then perform the common queries and data
manipulations for that given column.
1. Dynamic SQL
http://thinkoracle.blogspot.com/2005_09_01_archive.html (3 of 22)1/9/2008 2:49:46 AM
OracleBlog: September 2005
One way of doing that is with dynamic SQL. Observe:
CREATE OR REPLACE PROCEDURE HelperProc (
in_col_name IN VARCHAR2,
out_val OUT NUMBER)
IS
BEGIN
EXECUTE IMMEDIATE 'SELECT MAX(' || in_col_name || ') FROM EMP' INTO
out_val;
END;
SET SERVEROUTPUT ON;
DECLARE
out_val NUMBER;
BEGIN
HelperProc('EMPNO', out_val);
DBMS_OUTPUT.PUT_LINE(out_val);
END;
It works very well:
- It's a single line, no matter how many possible columns are used
- You don't need to know the column names in advance
- You don't need to change it after a DDL change
However, there are drawbacks to Dynamic SQL. Among others, there is
extra parsing and (most seriously) vulnerabilities to SQL injection. I won't
go into more detail on Dynamic SQL, but I promise to blog on it soon.
2. Static SQL
The obvious recourse is to use something like IF or CASE or (my
favourite) DECODE.
CREATE OR REPLACE PROCEDURE HelperProc (
in_col_name IN VARCHAR2,
out_val OUT NUMBER)
IS
BEGIN
SELECT MAX(DECODE(in_col_name, 'EMPNO', EMPNO, 'MGR', MGR, 'SAL',
SAL, 'COMM', COMM, 'DEPTNO', DEPTNO, NULL))
INTO out_val
FROM EMP;
END;
http://thinkoracle.blogspot.com/2005_09_01_archive.html (4 of 22)1/9/2008 2:49:46 AM
OracleBlog: September 2005
Essentially this is like looking at the column name, and doing something
different depending on what it is. That's practically all you can do with
static SQL, by definition. This almost defeats the purpose of having a
common "helper" procedure, but there are still two reasons it would still
make sense:
1. Modularity (and abstraction) is generally a good thing
2. Any extra work done on out_val will justify the "helper" procedure.
3. Revised Data Model
There is an even more important consideration. Whenever you are
struggling to do something clever, take a step back and consider your
data model. It could be ill-suited for your needs.
In this case, what could we do?
We could break this information into separate tables. For example:
EmpBaseSalary, EmpHoury, EmpBonus, etc. Then we could join them to
the Emp table by employee id. Of course, that just makes the table name
variable instead of the column, so that doesn't really help us, so instead:
We could elongate the employee table, making something like this:
ID;...;SALARY;HOURLY;BONUS;DIVIDEND
1;...;60;NULL;NULL;NULL
2;...;100;NULL;NULL;20
into a separate table mapped by ID:
ID;VALUE;TYPE
1;60;'SALARY'
2;100;'SALARY'
2;20;'DIVIDEND'
That would effectively move the "column name" into the WHERE clause.
That would certainly make the task easier. That is sort of a "reverse
pivot."
Also, that opens the door to add extra columns for effective start and
end dates. We could even do this with views if we wanted to leave the
data model alone.
http://thinkoracle.blogspot.com/2005/07/use-views.html
http://thinkoracle.blogspot.com/2005/09/pivot-and-crosstab-queries.
http://thinkoracle.blogspot.com/2005_09_01_archive.html (5 of 22)1/9/2008 2:49:46 AM
OracleBlog: September 2005
html
That is just one example, but it shows how you need to take a step back
and consider the real-world application.
Here is a link to the Dizwell discussion forum where we discussed this,
and where most of this came from:
http://www.phpbbserver.com/phpbb/viewtopic.php?
t=450&mforum=dizwellforum
// posted by Robert Vollman @ Thursday, September 22, 2005 3 comments
Tuesday, September 20, 2005
Wanted: Your Unwanted Oracle/DB Books
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!
// posted by Robert Vollman @ Tuesday, September 20, 2005 3 comments
Monday, September 19, 2005
PL/SQL Code Storage: Files vs In-DB Packages
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=dizwellforum
http://thinkoracle.blogspot.com/2005_09_01_archive.html (6 of 22)1/9/2008 2:49:46 AM
OracleBlog: September 2005
To sum up their positions:
Option 1: In organized, packaged files on your DB server
- Fewer security holes
- Handles failover situations better
- Easier to use version-control system
- Available for a greater number of nice PL/SQL editors
- Harder to inadvertantly overwrite source code, leads to greater
confidence
Option 2: In-the-db packages
- Greater efficiency (pre-loaded)
- Greater code integrity (shows invalidation)
- Search code in USER_SOURCE table
- Use some PL/SQL tools easier
(Note: there are IDEs that integrate with source control and compile
directly into the database)
It seems like the leading camp is Option 2. The advantages of having
your packages pre-loaded into the database are just so significant,
especially since you should be able to find an IDE that integrates directly
with source control and can compile directly into the database. Scott
Swank provided this suggestion:
http://www.oracle.com/technology/products/jdev/101/
howtos/extools/subversion.html
One general consensus, however, is this:
- Code should be contained in packages
- These packages should be wrapped.
// posted by Robert Vollman @ Monday, September 19, 2005 2 comments
Wednesday, September 14, 2005
Analyzing Query Performance
Alternate title: Keeping Tables Small, Revisited
In 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.html
http://thinkoracle.blogspot.com/2005_09_01_archive.html (7 of 22)1/9/2008 2:49:46 AM
OracleBlog: September 2005
During 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 that it wouldn't be much slower, I did
not expect for it to be faster. I didn't pursue it at the time, but
reproduced those results in 2 separate tests later on.
Incidentally, David Aldridge, who inspired my original article, has a
theory on this:
http://oraclesponge.blogspot.com/2005/08/more-on-partition-not-
quite-pruning.html
So the greater question was:
"How do you determine why a query is faster (or slower) than you
expected?"
The first step is to use SQL Trace and TKProf:
http://download-west.oracle.com/
docs/cd/B10501_01/server.920/a96533/sqltrace.htm#1018
Note: there are MANY sources of information on this. Apart from the
Oracle documentation, I also used articled by Tom Kyte, as well as his
book "Expert One-on-One Oracle."
Here was my test.
1. Set some variables:
ALTER SESSION SET TIMED_STATISTICS=true;
ALTER SYSTEM SET MAX_DUMP_FILE_SIZE=1000;
ALTER SYSTEM SET USER_DUMP_DEST="C:/temp/trace";
2. Create the ReallyBigTable
CREATE TABLE ReallyBigTable AS SELECT * FROM ALL_OBJECTS;
3. Turn on tracing
ALTER SESSION SET SQL_TRACE = TRUE;
4. Run the query
SELECT SUM(object_id) FROM ReallyBigTable
http://thinkoracle.blogspot.com/2005_09_01_archive.html (8 of 22)1/9/2008 2:49:46 AM
OracleBlog: September 2005
WHERE object_id * 2 NOT IN
(SELECT object_id FROM ReallyBigTable);
Note: 00:44:50.07
5. Turn off tracing
ALTER SESSION SET SQL_TRACE = FALSE;
6. Run TKPROF (separate window)
TKPROF robert_ora_3236.trc robert_ora_3236.prf explain='sys/********
as sysdba'
- Save that file somewhere (it will be overwritten later)
7. Create Archive and Active tables.
CREATE TABLE ReallyBigTable_Archive AS SELECT * FROM ReallyBigTable
WHERE object_id < 40000;
CREATE TABLE ReallyBigTable_Active AS SELECT * FROM ReallyBigTable
WHERE object_id >= 40000;
8. Drop ReallyBigTable
DROP TABLE ReallyBigTable;
9. Create the view
CREATE VIEW ReallyBigTable AS
SELECT * FROM ReallyBigTable_Archive
UNION ALL
SELECT * FROM ReallyBigTable_Active;
10. Turn on Tracing
ALTER SESSION SET SQL_TRACE = TRUE;
11. Run the query again
SELECT SUM(object_id) FROM ReallyBigTable
WHERE object_id * 2 NOT IN
(SELECT object_id FROM ReallyBigTable);
Elapsed: 00:45:21.04
http://thinkoracle.blogspot.com/2005_09_01_archive.html (9 of 22)1/9/2008 2:49:46 AM
OracleBlog: September 2005
12. Turn off tracing
ALTER SESSION SET SQL_TRACE = FALSE;
13. Run TKPROF (separate window)
TKPROF robert_ora_3236.trc robert_ora_3236.prf explain='sys/********
as sysdba'
Conclusion:
I repeated the test 3 times with tracing on, and each time I could not
reproduce the results. I saw virtually no difference in time elapsed
between querying a big table, and querying a big table
So I guess we're left in the dark as to why querying the view was so much
faster during my earlier tests. Perhaps we can apply Occam's Razor and
the safest conclusion was simply that I goofed.
Either way, it made for an interesting article of how to generate
performance data and query plans. I will leave you with an excerpt from
the TKPROF output:
SELECT SUM(object_id) FROM ReallyBigTable
WHERE object_id * 2 NOT IN
(SELECT object_id FROM ReallyBigTable)
call count cpu elapsed disk query
------- ------ -------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0
Execute 1 0.00 0.00 0 0
Fetch 2 612.03 2690.60 16168915 17030020
------- ------ -------- ---------- ---------- ----------
total 4 612.04 2690.60 16168915 17030020
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
20495 FILTER
40764 TABLE ACCESS FULL REALLYBIGTABLE
20269 TABLE ACCESS FULL REALLYBIGTABLE
Rows Execution Plan
------- ---------------------------------------------------
http://thinkoracle.blogspot.com/2005_09_01_archive.html (10 of 22)1/9/2008 2:49:46 AM
OracleBlog: September 2005
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (AGGREGATE)
20495 FILTER
40764 TABLE ACCESS (FULL) OF 'REALLYBIGTABLE'
20269 TABLE ACCESS (FULL) OF 'REALLYBIGTABLE'
// posted by Robert Vollman @ Wednesday, September 14, 2005 2 comments
Monday, September 12, 2005
20 Oracle Lessons
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 adventures.
I decided this was as good a time as any to pause and reflect on some of
the lessons I've learned in this past year.
Oracle:
1. Oracle is very complex.
I always thought "a database is a database" but Oracle is about 4 times
as complex as Sybase/MS-SQL.