We Have Cover Art!

August 21, 2009

You know a book is getting close to hitting the shelves when Amazon let’s you buy it pre-release, and has the cover art.  This is how the new book looks.

Oracle SQL Recipes Cover Art

Oracle SQL Recipes Cover Art

The word is we might make the Oracle OpenWorld conference, but worst-case is we slip to November.  Happy reading!

Clearly, I haven’t learnt my lesson after writing the DB2 book. Apress has asked me back to write another book.  This time, it’s on Oracle, and my co-authors and I are busy in the “mad writing” phase.  I’m certainly benefiting from all the valuable lessons gained from being a first-time book author, though combating the deities of procrastination is as much a problem as ever.

Wrong … at least one, and that’ll be the big O, as in Oracle. It’s just announced their buying Sun, and therefore MySQL, Java, Glassfish, Solaris, the whole box and dice. The Java aspect makes perfect sense, given Oracle bet the house on Java a decade ago (in much the same way the IBM acquisition made sense around Java). More compelling for Oracle is that they’ll own their own OS, possibly allowing great things like dtrace and zfs to escape the Sun CPPL (or whatever their licence is called), and be released under a Linux-friendly licence like LGPL or Apache.

And then there’s MySQL. At last, MySQL and InnoDB are owned by the same company. I think Oracle will use their market power and considerable ruthlessness to keep MySQL pegged in the “little web database that can” category, and use it to bludgeon Microsoft at the departmental/low-end. Of course, now all Larry Ellison and co. have to do is execute the post-acquisition merging of the two … they’ve shown they have some capability to execute such large mergers (think PeopleSoft, Seibel, Retek), but this one strikes me as a cross between a hand grenade and the proverbial Curate’s Egg. Good in parts, if you ignore the festering bits, and likely to blow up without warning 🙂

[The first article to be moved from the old site … hooray for progress :-)]

While I won’t ever be as organised as Howard, with his pantechnicon of useful articles, I did get my act in gear to write down the in’s and out’s of linking SQL Server to Oracle, and vice versa. In this first half of the topic, let’s look at how to create a linked server from SQL Server to Oracle – essentially putting SQL Server in the driving seat for any heterogeneous work you might like to do.

Step 1. You will need to install the Oracle client on your SQL Server host. Just so you’re completely clear … YOU WILL NEED TO INSTALL THE ORACLE CLIENT. We’re clear on that now, right? :-). The reason you need this is the same reason you need MDAC on a machine in order to access SQL Server. The client is what provides the network tranport interfaces that lets you talk Oracle’s transport protocol, Tranparent Network Substrate, or TNS. These are bundled up into a package that was historically called SQL*Net, and now is just known as Oracle Net. This is bundled in with the Oracle client, along with useful utilities like a query tool (SQL*Plus), network testing tool (tnsping), configuration wizards (such as Oracle Net Configuration Assistant) and other goodies.

To be completely honest, you can try to create a linked server using Oracle’s instant client, and even try using one of the “clientless” wire protocol drivers offered by third-parties. Best of luck with that :-P.

Step 2. Use the Oracle Net Configuration Assistant to configure tnsnames.ora properly. This is the file used by the Oracle client to find Oracle servers … in much the same way your operating system uses the hosts file to find hosts. The wizard will walk you through a set of dialogs and let you test your config at the end. If you can’t see the icon in the Configuration Tools start folder, you can simply type “netca” at the command prompt to launch the wizard. If that doesn’t work, then you can edit the tnsnames.ora file manually. The format is, in its simplest form:

MyServiceAlias =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = your-host-name-or-ip)(PORT = listener-port-usually-1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle-service-name)
)
)

Fill in the appropriate values for you host, listener port, service name and chosen local alias. Note that an Oracle service name is a published name chosen for the instance … don’t confuse it with the name you see in the windows Services.msc MMC applet if your Oracle instance happens to run on Windows :-). Be very careful not to mess up the formatting of other entries … dangling opening/closing parentheses will put a spanner in the works. The wizard is usually the best bet, as it ensures the formatting is correct. The listening port is usually 1521, the default, in much the same way that 1433 tcp / 1434 udp are SQL Server’s default listening ports.

Step 3. Before trying to configure the link in SQL Server, make certain you can talk to the listener and connect to the Oracle instance from the SQL Server box without any SQL Server involvement.

E.g:

c:> tnsping MyServiceAlias

[ boilerplate stuff … blah blah blah ]

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MyServiceAlias)))
OK (0 msec)

^^^ that’s the thing you want to see … “OK”

c:> sqlplus myusername/mypassword@MyServiceAlias

[ boilerplate stuff … blah blah blah ]

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> _

^^^ that’s the magic phrase and prompt … “Connected to:” and the SQL> prompt. type exit or quit to close.

Step 4. (Optional). You might like to create a dedicated Oracle user with which to use the soon-to-be-created link from SQL Server. Something simple will do for this test, but you can go to town if you like. Of course, an existing user could always be used.

SQL> create user oratest identified by oratest
2 default tablespace users
3 quota unlimited on users;

User created.

SQL> grant create session, resource to oratest;

Grant succeeded.

Step 5. Now you’re ready to create your link from SQL Server to Oracle. From within Query Analyzer, Isql, Osql or your favourite query tool, you can issue the following commands

EXEC sp_addlinkedserver @server = ‘TestOraLink’, @srvproduct = ‘Oracle’, @provider = ‘MSDAORA’, @datasrc = ‘MyServiceAlias’

EXEC sp_addlinkedsrvlogin ‘TestOraLink’, false, ‘sstest’, ‘oratest’, ‘oratest’

That creates a logical link called TestOraLink, mapped via the Microsoft OLEDB provider for Oracle to your Oracle service. We then associate a local SQL Server login – sstest – with an equivalent Oracle username and password, in this case oratest.

Step 6. Time to test your link.

SELECT * FROM OPENQUERY(TestOraLink, ‘select * from dual’)

D

X

Fantastic results! Well, OK, you’re just seeing the contents of the ‘dual’ view, but that’s come all the way from your Oracle instance, via your linked server definition.

So now you’re in business, you can go wild.

In the next installment, I’ll show off the three different ways to use your new link … the OPENQUERY, OPENROWSET and four-part-naming techniques. I’ll also highlight some of the flaws in SQL Server’s linked server logic, including the madness of “assumed” and “ignored” case-sensitivity in situations that should be just left in peace!

Happy linking.

IBM’s prolific Redbooks team have released the updated Oracle to DB2 conversion guide. While this is an excellent guide, it’s interesting to note that it still targets the previous version of DB2 for Linux, Unix and Windows … v8.1.

Oh well, maybe I can chip in an contribute to the re-write for version 9.1 🙂

What do government systems and databases have in common? The concept of the separation of powers. In a Westminster-style democracy, the three arms of power – the executive, the legislature, and the judiciary – each have separate well defined powers that are forbidden to the other arms of government. For example (and something the crowd at Microsoft should remember), the power to determine punishments is exclusively the right of the judiciary … not criminally convicted monopolists who engage in “self help” :-). But I digress.

The same idea holds in database land. Separate powers and privileges out into discrete realms, and grant those well-defined powers to appropriate users. Works well for DB2, Oracle, SQL Server … but crashes into the safety barrier of poor design when we get to MySQL.

MySQL gets it right in the more-recently added powers, such as creating and executing routines, but its flakey bedrock is shown in the way basic object creation privileges are managed. Creating and dropping tables and databases are controlled by the same two privileges. Read that again – the create privilege spans tables and databases, and the drop privilege is equally-poorly scoped. What it means in practice is that developers who should be given a restricted authority to create objects can now create entire databases, and worse, the power to drop a table comes bundled with the “oops, was that the dev or production database I just wiped” ease of use feature. These should be separate powers … so let’s see how many other people have requested this in the past on the MySQL forums/bug site, and if in the unlikely event that count is zero, I’ll submit it and we’ll see what happens. Stay tuned to watch the fun.