Home » Blog Aggregator » Sources

Eddie Awad

Syndicate content awads.net/wp
News, views, tips and tricks on Oracle and other fun stuff
URL: http://awads.net/wp
Updated: 1 hour 48 min ago

Virtual Machines Are Your Friends

Sun, 2008-06-29 16:48

VirtualBox, Virtual PC, VMware Workstation and a few other software packages help you create and run multiple virtual machines on your desktop or laptop enabling you to run multiple operating systems simultaneously on a single computer.

My personal favorite is VirtualBox (recently acquired by Sun Microsystems), not only because it is freely available as Open Source Software and runs on Windows, Linux and Macintosh but also because it has great features and supports a large number of guest operating systems.

Last Saturday I used VirtualBox to create a new virtual machine (VM) running Windows XP Pro and Oracle Database 11gR1. First, I created a “base” VM with only Windows XP pro SP3 installed. I then detached the virtual disk file (VDI) from the VM. I ended up with a VDI file that I can clone as many times as I want eliminating the need to install a new operating system every time I create a new VM.

Cloning a virtual disk in VirtualBox is done using the command line. For example, to clone WindowsXP.vdi as a new virtual disk called WindowsXPProOraDB11gR1.vdi, you would issue this command:

VBoxManage clonevdi WindowsXP.vdi WindowsXPProOraDB11gR1.vdi

You would then assign WindowsXPProOraDB11gR1.vdi as the virtual hard drive of a new virtual machine.

The installation of Oracle Database 11gR1 on a new Windows XP VirtualBox VM on my laptop went very smoothly, but I had to troubleshoot a couple of things on the VM: A missing loopback adapter and a 100% CPU usage.

My laptop and the newly created VM do not have a static IP address. Dynamic Host Configuration Protocol (DHCP) is used to assign dynamic IP addresses on the network. According to the documentation, before installing Oracle Database onto a computer that uses the DHCP protocol, you need to install a loopback adapter to assign a local static IP address to that computer. I followed the instructions found in the Oracle Database Pre-installation Requirements document to install the loopback adapter. After that, the database installed without any issues.

Now that my WinXP VM and 11g Database are up and running, I noticed that the oracle.exe process was consuming 100% of the CPU. I waited a few minutes, but the CPU usage did not go down, it stayed at 100%. After a quick search, I found these two forum posts: post 1 and post 2. Since this is a test database, I did not hesitate to follow the instructions in post 2 and was able to bring the CPU usage down to a normal level.

Virtual machines are a great way to learn and try new software and applications without the need to buy new hardware or messing up your existing computer. You may even have fun embarking on some virtual adventures.

---
Related Articles at Eddie Awad's Blog:


5 Useful Links for 2008-06-13

Fri, 2008-06-13 04:29

More from my bookmarks on del.icio.us

---
Related Articles at Eddie Awad's Blog:


Cool Undocumented OVERLAPS Predicate

Thu, 2008-06-12 22:30

David Aldridge’s post about OVERLAPS caught my attention. I did not know that you could do something like this in Oracle:

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 12 22:06:24 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect eddie/awad
Connected.
SQL>  SELECT *
  2     FROM dual
  3    WHERE (add_months(sysdate, -6), DATE '2008-08-08')
  4          OVERLAPS
  5          (sysdate - 180, interval '2' YEAR);

D
-
X

Indeed, like David, I searched the Oracle docs and I could not find anything describing OVERLAPS. I extended my search to the OTN Forums and I got these hits:

So, what does OVERLAPS really do? according to Mimer SQL Reference Manual:

The OVERLAPS predicate tests whether two “events” cover a common point in time or not, and has the form:

(expression, expression) OVERLAPS (expression, expression)

Each of the two “events” specified on either side of the OVERLAPS keyword is a period of time between two specified points on the timeline. The two points can be specified as a pair of datetime values or as one datetime value and an INTERVAL offset.

Each “event” is defined by a two expressions constituting a row value expression having two columns.

The first column in each row value expression must be a DATE, TIME or TIMESTAMP and the value in the first column of the first “event” must be comparable.

The second column in each row value expression may be either a DATE, TIME or TIMESTAMP that is comparable with the value in the first column or an INTERVAL with a precision that allows it to be added to the value in the first column.

The value in the first column of each row value expression defines one of the points on the timeline for the event.

If the value in the second column of the row value expression is a datetime, it defines the other point on the timeline for the event.

If the value in the second column of the row value expression is an INTERVAL, the other point on the timeline for the event is defined by adding the values in the two column of the row value to expression together.

The NULL value is assumed to be a point that is infinitely late in time.

Either of the two points may be the earlier point in time.

If the value in the first column of the row value expression is the NULL value, then this is assumed to be the later point in time.

I wonder if or when a similar documentation will be added to the Oracle SQL Reference.

Warning: Undocumented features should not be used in production systems.

---
Related Articles at Eddie Awad's Blog:


ANY Types, Pipelined Table Functions, Dynamic SQL and Pivot

Thu, 2008-06-05 20:01

Amazing what you can do with ANYTYPE, ANYDATA, ANYDATASET, DBMS_TYPES, DBMS_SQL and pipelined table functions implemented using the interface approach. You can end up with a dynamic sql mashup like you’ve never seen before, and pivot like you’ve never pivoted before. Of course, if you’re on Oracle DB 10g or below, you can never pivot like this.

---
Related Articles at Eddie Awad's Blog:


The Lazy Developer’s Way to Populate a Surrogate Key

Tue, 2008-05-20 11:56

Consider this table:

CREATE TABLE t
  (
     id   NUMBER PRIMARY KEY,
     name VARCHAR2(100)
  );

You want the column id to be populated from this sequence:

CREATE SEQUENCE t_s;

Here are a couple of ways you can do this:

1- Use the sequence’s next value directly in the insert statement:

INSERT INTO t (id, name) VALUES (t_s.nextval,'Eddie');

2- Use a database trigger:

CREATE OR REPLACE TRIGGER t_trig
   BEFORE INSERT
   ON t
   FOR EACH ROW
BEGIN
   SELECT t_s.NEXTVAL
     INTO :NEW.ID
     FROM DUAL;
END;

You will then insert a new row like this:

INSERT INTO t (name) VALUES ('John');

And you do not have to worry about the id column, unless of course you need it in subsequent code. In this case, use the RETURNING clause:

DECLARE
   l_id t.id%TYPE;
BEGIN
   INSERT INTO t (NAME) VALUES ('John')
     RETURNING id
          INTO l_id;
   DBMS_OUTPUT.put_line ('id: ' || l_id);
END;

So, when populating a surrogate key, which is better: using the sequence.nextval directly in the insert statement or using a database trigger?

In general, I do not like database triggers, they hide your logic and make it difficult to debug. But, in this case, I believe that using a database trigger to populate a primary key with a sequence value - or any unique value for that matter - is the right thing to do. Why? Well, I was troubleshooting an ORA-00001: unique constraint violated on a table’s primary key. The PL/SQL API that inserts rows into this table populates the key from a sequence, using the sequence.nextval in the insert statement.

A developer decides to “manually” insert rows into this table, bypassing the API all together. So, he queries the last number from the table’s primary key, and then inserts rows with hardcoded numbers in the key. He adds 1 to the number each time he inserts a new row. He does not use the sequence.nextval. Guess what happens next!

The next time the API is called to create a new row, the sequence generates its next number, but oops! this is the same number that the developer has already used. Boom! ORA-00001: unique constraint violated.

If the primary key was populated from a trigger, bypassing the value given to it from the developer, this error would not have happened.

---
Related Articles at Eddie Awad's Blog:


A Quick Update

Thu, 2008-05-15 17:13

I owe you, my dear reader, a quick update. If you have been following me on Twitter, you already know that on April 23 I started a new job with a new employer. In the last three weeks, I have been adapting myself to the new environment. This is not easy to do after being in the same job and the same employer for 9 years.

My new position is still “Oracle Developer” and I’m still based in Portland, Oregon USA. I will be doing SQL and PL/SQL development, Fusion Middleware development like Portal, OID, Business Intelligence and SOA/BPEL, in addition to E-Business Suite. I will be exposed to a broader and different set of Oracle technologies compared to my previous job. As always, I will share my new experiences with you through my blog.

One thing I will not continue doing is ColdFusion. Even though I have not blogged about it for a while, now I know for sure that I will not be using nor blogging about ColdFusion at all. As a result, I have changed the title of this blog to: News, views, tips and tricks on Oracle and other fun stuff. No more ColdFusion.

Because of this transition, my blogging activities have dropped recently. There is a lot of useful stuff I want to blog about. I promise I’ll find the time to write and hit the publish button. However, somehow I always find the time to Twitter. Hey, how much time does it take to write a 140 character blog post?! You can always follow me there.

Thank you for being a loyal reader.

---
Related Articles at Eddie Awad's Blog:


Oracle Street Talk (Video)

Sat, 2008-05-03 16:48

Seems like most Australians do not know about Oracle, at least as of the date this video was shot. Of course, it depends on whom you ask the question: Have you heard of Oracle Corporation before?

---
Related Articles at Eddie Awad's Blog:


AskTom Search Engine Plugin Revived

Mon, 2008-04-28 12:49

I went to AskTom to add the site’s search engine to my Firefox’s Search Bar but too bad the search engine link was broken:

So, I went ahead and created a new AskTom search engine plugin.

If you are browsing this page in Firefox 2 or above or IE7 or above, click here to install the AskTom search plugin.

Check out this page for more Oracle related search engine plugins.

---
Related Articles at Eddie Awad's Blog:


35+ Oracle Subdomains You May Not Know About

Sun, 2008-04-20 21:30

Here is a list of public Oracle.com subdomains that I know about. In no specific order:

Do you know an Oracle.com subdomain that is not listed above? Feel free to share it with us.

Update: Thanks to Colin for letting us know about this query which gives us more than 100 subdomains.

---
Related Articles at Eddie Awad's Blog:

  • No related posts


Give Me The Current Date Please

Thu, 2008-04-17 16:24

Did you know that in addition to SYSDATE, there is also a SQL function called CURRENT_DATE? Basically, they are the same except one important difference.

SYSDATE returns the current date and time set for the operating system on which the database resides whereas CURRENT_DATE returns the current date in the session time zone.

Here is a quick example to illustrate that difference:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH:MI:SS'
  2  /

Session altered.

SQL> SELECT SYSDATE, CURRENT_DATE
  2    FROM DUAL
  3  /

SYSDATE             CURRENT_DATE
------------------- -------------------
04/17/2008 04:09:56 04/17/2008 04:09:57

SQL> ALTER SESSION SET TIME_ZONE = 'EST'
  2  /

Session altered.

SQL> SELECT SYSDATE, CURRENT_DATE
  2    FROM DUAL
  3  /

SYSDATE             CURRENT_DATE
------------------- -------------------
04/17/2008 04:09:56 04/17/2008 07:09:57

Of course, I’m not telling you anything new here, it’s all in the documentation :)

---
Related Articles at Eddie Awad's Blog:


Check These SQL Beauties Out

Thu, 2008-04-03 00:17

Participation in the Obfuscated SQL Code Contest expired on April 1st. The SQL code that was submitted was not only obfuscated but also beautiful and artful.

When the contest started, I thought that we could just vote on the entries and select the most popular as the winner. But after seeing the entries, I believe that all the participants should be declared winners. Great Work!

Without further ado, here is the list of authors and their scripts:

  • Rob van Wijk: The script requires version 11.1.0.6 because of the pivot operator.

  • Shoblock: The script makes use of subquery factoring and generates rows from dual via “connect by level <”. Tested on 10.1.0.2.0

  • Laurent Schneider: The script requires 10.1.0.5 and cannot be formatted.

  • Frank Zhou: The script solves 4 different puzzles in a single SQL. Requires version 10.2.0.2.0.

  • Volder: The script draws an island and a turtle underneath. Requires any 10.2 version.

And here is a bonus script from Laurent (10.2.0.2).

Oh and by the way, Paul was kind enough to put Rob’s script on a t-shirt :)

---
Related Articles at Eddie Awad's Blog:


PL/Scope in Oracle Database 11g - Revisited

Tue, 2008-03-25 14:03

Oracle Database 11g introduced a new feature called PL/Scope. A while back, I wrote about Dan Morgan’s experience when he compiled the package STANDARD for PL/Scope. I also wrote about Oracle’s answer, in which they said “…the reason to compile STANDARD would be to make its identifiers available in the new DBA_Identifiers view family (see PL/Scope). Our script to do this sadly missed the release. We’re about to post it on OTN.”

In fact, the PL/Scope documentation mentions a utlirplscope.sql script:

A database that has been upgraded from a release of Oracle Database that did not yet support PL/Scope will have no PL/Scope metadata—nor will a new Oracle Database 11g environment that has been ordinarily created. The DBA can rectify this by running the utlirplscope.sql script.

In an email, Bryn Llewellyn, Oracle’s PL/SQL Product Manager, was kind enough to point me to this page on OTN:

On the Subject of the utlirplscope.sql script

It turns out that a script is not needed after all:

Instead of simply providing a script to recompile STANDARD and DBMS_STANDARD for PL/Scope, we would first like to clear up some misconceptions about the usefulness of such a script and make sure you really do need to perform such an operation.

I suggest you read this OTN piece if you want to know the answers to the following questions:

  • What was utlirplscope.sql?
  • What is STANDARD and DBMS_STANDARD identifier data?
  • Do I need STANDARD and DBMS_STANDARD identifier data?
  • Do I already have STANDARD and DBMS_STANDARD identifier data in my database?
  • Ok, I have determined that I do not already have STANDARD and DBMS_STANDARD PL/Scope identifier data and need it. What now?

Important notes to take from this:

  • Not everyone needs STANDARD and DBMS_STANDARD identifier data.
  • When needed, it’s going to be mostly in development environments.
  • Compiling STANDARD and DBMS_STANDARD using utlirp.sql should be done while the database is in UPGRADE mode.

In conclusion, I agree with Bryn that PL/Scope is a fine feature. Just get your DBA to read the OTN reference above if your use case would benefit from having STANDARD and DBMS_STANDARD identifier data.

---
Related Articles at Eddie Awad's Blog:


Yet Another Oracle Social Network in the Works

Wed, 2008-03-19 15:47

In addition to Oracle Mix, Oracle Wiki and Oracle Community, OAUG will be launching yet another Oracle related social network called the Knowledge Factory.

The Knowledge Factory will provide a platform for users to exchange ideas, experiences, and expertise within the Oracle Applications member community. It will feature user profiles with pictures and biographies, blogs, forums and wiki. Sounds familiar?

oaug_knowledge_factory1.jpg

oaug_knowledge_factory2.jpg

oaug_knowledge_factory3.jpg

oaug_knowledge_factory4.jpg

oaug_knowledge_factory5.jpg

Sources:

---
Related Articles at Eddie Awad's Blog:


Oracle SQL and PL/SQL Bad Practices Document

Sun, 2008-03-09 22:38

The document below contains patterns of bad SQL and PL/SQL code that Gojko Adzic has repeatedly found in various applications and databases. Some of the bad practices include:

  • Use of WHEN OTHERS in exception handling.
  • Embedding complex SQL inside PL/SQL code.
  • Poor PL/SQL error handling.
  • Hardcoding the size of PL/SQL variables.
  • Not using bind variables.
  • Storing ROWIDs for later reference.
  • Storing an empty LOB instead of NULL.
  • Use of COMMIT or ROLLBACK inside stored procedures or functions.
  • Use of magic numbers and strings instead of NULL.

Finally, Gojko makes the case against “wrapping everything into stored procedures”. He argues that instead of encapsulating all read/write access to data inside stored procedures, a better approach is to use views and instead-of triggers. Now that may make Steven nod his head in disagreement.

Have you used views instead of stored procedures to encapsulate data access, including inserts and updates? What do you think about this approach?


Oracle SQL and PLSQL Bad Practice - Get more free documents

More Resources:

---
Related Articles at Eddie Awad's Blog: