Everything is a Bit Bucket By: Michael O'Neill
@oraclenude
oraclenude.crisatunity.com

In response to Chet's frustration over yet another encounter with a database agnostic, I wanted to contribute my first article to the oraclenerd franchise. My thoughts seemed too long for the comment stream.

I ascribe the kernel of thought behind "the database is a bit bucket" primarily to each and every database vendor that ever existed. Every database vendor, in an effort to persuade users of competitive products to adopt their product, has participated willingly in espousing some core aspect of how "same as the other guy" their product is in addition to whatever differentiation pitch they have.

Now, the generally weak-minded and lazy developer (yes, I think the majority of developers are in fact weak-minded and lazy) latches on to the vendor's selective "sameness" claims for professional and personal reasons. (full disclosure: I am both an Oracle DBA and .NET developer)

Professionally, because they are financially invested in writing third-party code not database code. To them, the less they spend learning and understanding the particulars of things like databases, operating systems, networks, human beings, etc. the better. Personally, because there is a dominate thread in the culture of developers to dismiss the database as interesting or meaningful. It is a form of heresy to show affection towards any platform in any specificity.

This is why Java's Big Lie of "write once, run any where" swoons so many. Java's Big Lie is analogous to "the database is a bit bucket" by declaring that even the language of software code should be as absolutely interchangeable as possible - even at the expense of being cost-effective or useful. There is an unquestioned faith that decoupling everything from everything is a good thing. This faith gives us code that is as far from the simplest thing that could work from the first moment writing the code is undertaken. It is a faith I reject. That's why I'm an ORACLENERD.

P.S. I know oraclenude and oraclenerd is confusing. It's supposed to be.

Labels: database, moneill, rant

  ¶ 3/10/2010 01:34:00 PM   3 Comments Links to this post

  The "Database is a Bucket" Mentality Front and center again...I just woke up from a nap, I'm grumpy, so I must write. Besides, I haven't had a good rant in quite some time.

Friend of mine asked me last week for some advice, specifically asking if there was a tool to convert Oracle SQL Syntax to the ANSI SQL syntax. (A quick search turned up this (it was the first result), if you're interested).

I had to ask why.

Client is switching to an open source database, i.e. "free." Oracle licensing is way too pricey.

I'm sure Oracle costs a lot of money, it's pretty darn good software. Quite possibly the best in the world especially in the database realm. I've written about the incredibly feature rich goodness that is the Oracle database here here...actually, just trust me. It's in my name.

Why is there even a comparison?

Could it be that everyone out there believes that the sole purpose of a database is to store data? That it can't do anything else? The storage and retrieval of data...that's all it does of course.

It's like saying the Democrats and Republicans are the same...at face value, perhaps, but the devil is in the details.

This, this "Bit Bucket" mentality is what is so incredibly frustrating.

I am no position to argue the differences between the various flavors of database, I lack the experience. But if I were using SQL Server, I would leverage the shit out of it's capabilities. If I were using MySQL, I would leverage the shit out of it's capabilities. If I were using Firebird, I would leverage the shit out of it's capabilities. Same goes for every single flavor out there. Get my point here?

The database is NOT a bit bucket!

Do I need to use more 4-letter words?

I know that Oracle is feature rich and that 99% percent of your code can live in the database...think APEX and PL/SQL. You could probably put ALL of your code inside the database if you wanted to put the javascript in BLOBs as well.

Please, please please quit telling me they are the same...they are not.

Follow up rant by Mr. O'Neill can be found on this following post Everything is a Bit Bucket

Labels: database, design, development, oracle, rant

  ¶ 3/09/2010 10:50:00 PM   10 Comments Links to this post

  Connect to HP Neoview using Heterogenous Services Being the lazy sort that I am and not wanting to pay for a SQL client, I decided to use my local Oracle instance to access a HP Neoview database. Please don't ask me any questions about it, because like much of the world, I don't know either.

Hat(s) off to Tak Tang. I've used his guide a number of times throughout the years. If he blogs, I can't find it, so if you know about it, please link him up. The first time I used it was back in aught (sp?) 5 to connect to a DB2 instance.

Setup
Database (source): Oracle 10gR2
OS: Windows Vista Ultimate running as a VirtualBox Guest on Ubuntu
Database (target): HP Neoview 2.4 (or something)
OS: Doesn't matter

Materials
HP Neoview driver which can be found here. (Thanks Christian)

Steps
Download the HP Neoview driver and install it. Since it is windows, just click away accepting all the defaults.

Next, configure an ODBC Datasource. I won't go into the gory details, but it's pretty easy. When you are done, you should see something like this:



Now comes the fun part.

In your <ORACLE_HOME>\hs\admin directory you should see the following:



Open up the inithsodbc.ora file and set the parameters as follows:

HS_FDS_CONNECT_INFO = NEOVIEW
HS_FDS_TRACE_LEVEL = 1

Save the file as initNEOVIEW.ora in the same directory.

Now traverse to <ORACLE_HOME>\network\admin and open up your listener.ora file. Add a SID_DESC so that your file now looks like this:

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=testing)
      (ORACLE_HOME=c:\oracle)
      (SID_NAME=testing))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=c:\oracle)
      (PROGRAM=extproc))
    (SID_DESC=
      (SID_NAME=NEOVIEW)
      (ORACLE_HOME=c:\oracle)
      (PROGRAM=hsodbc)))

Reload or stop and start your listener.

Now open up your tnsnames.ora file and add an entry for NEOVIEW. It should look like this:

NEOVIEW =
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=
        (PROTOCOL=TCP)
        (HOST=localhost)
        (PORT=1521)
      )
    )
    (CONNECT_DATA=(SID=NEOVIEW))
    (HS=OK)
  )

The last step is to create a database link.

CREATE DATABASE LINK neoview
  CONNECT TO "username"
  IDENTIFIED BY "password"
  USING 'NEOVIEW';

You should be all set now. You can test it out by issueing a simple

SELECT * FROM dual

If that works, you're done.

If you have problems, check out Tak's Troubleshooting section.

Labels: database, datawarehouse, oracle

  ¶ 2/23/2010 10:49:00 PM   2 Comments Links to this post

  Database Application Security - A Visualization Security, in regards to the database, is pretty broad. There are a multitude of ways to secure your database. From physical or logical access to SQL Injection.

This is a (not so) pretty picture of how I visualize security from an application point of view.



The base of any application are the tables. Access to those tables should be limited to database views and PL/SQL packages (API) that are owned by the application (schema).

SELECT privileges on those views should be given to only users who need it (users also includes other applications). Views present the data in the format that is required by the application. VPD (column or row) can be used to further restrict access to sensitive data.

EXECUTE privileges on the APIs (PL/SQL) should only be given to those applications that need it. Under no circumstances, should direct INSERT/UPDATE/DELETE privileges be given to another user.

Much of my thoughts on this matter are directly traceable to reading AskTom for so many years. I can't point to any one specific article, I think it's the accumulation/internalization of reading for so long.

It makes sense though.

Who better to decide the functions (INSERT/UPDATE/DELETE) that can be performed against a given set of tables than the person(s) who created them? If said person has left, make someone else responsible for them. I've fought hard over the years to implement this "pattern" and have been fairly unsuccessful. It is difficult to go against years of doing it one way (full access to the underlying tables) to forcing someone or something to use the API.

The idea to visualize it came to me recently and I need to put it down.

What do you think? Am I full of it? Is this reasonable? Anything I left out?

Labels: database, design, oltp, security

  ¶ 2/21/2010 06:17:00 PM   8 Comments Links to this post

  11gR2: Installfest I installed Oracle Enterprise Linux 5.4 a few weeks ago in a VirtualBox virtual machine...I just didn't have the inclination to install the database.

Way back in June, I installed VirtualBox, OEL (5.3), 11gR1 and ApEx. To do that, I followed this guide on OTN by John Smiley (thanks John!). This time, I was going to try to do it from the docs. I can't say it was easy to read, so I was back at John's article before to long.

First up was preparing OEL for the database install. This included setting up kernel parameters and loading some extra libraries. There were slight differences between OEL 5.3 and 5.4, but I had the docs handy...and the installer complained loudly if something was amiss.

Next up, mount a shared folder in the virtual machine so that I could keep it to a relatively small size, 15 GB to be exact. Plus, I wanted to use the shared folders functionality. So I created a directory in the /mnt directory called software (as root of course), then mounted the shared folder.

root@oracledb#mkdir software
root@oracledb#mount -t vboxsf software /mnt/software

"software" was the name I gave the shared folder through the VirtualBox interface, that mapped to /opt/software on my host machine (ubuntu 9.04).

Then I downloaded the 11gR2 software, 2 files, linux_11gR2_database_1of2.zip and linux_11gR2_database_2of2.zip. I then used Archive Manager to extract the files. Strangely, it created 2 folders, "database" and "database(2)." That can't be right.

Back to OEL and I cd'd to the directory, /mnt/software/11gR2/database and ran the installer.



I did not provide my email address and I unchecked the box that would send me updates from Oracle Support.



Selected Yes to continue.



I'm just accepting the defaults here.



Not really sure what the difference is between a Desktop Class and Server Class install, so I again accepted the default (Desktop).



Now I have to fill in 4 fields:
* OSDBA Group (drop down)
* Global database name
* Administrative password
* Confirm password



* OSDBA Group = oracle (for me)
* Global database name = testing
* Administrative password = testing, whoops, strong password rules, not testing
* Confirm password = same as above



I chose "oracle" as the oraInventory Group Name.



Some prerequisite checks, to make sure I have the right libraries and the kernel parameters are OK.



This is when I realized the values were different between 11gR1 and 11gR2 and went back to the docs to get the correct ones. I realized afterwards though, that if you select the particular failure, it will tell you what the values should be. Nice.

After I went back and changed the values, I only had a failure on the memory check, but I checked the "Ignore All" checkbox and proceeded forward.



(I missed a screen shot the second go-around, so ignore the OS Kernel Parameters and Package errors.



Confirmation screen.



And were off!



I like to take a lot of pictures.



Creating the database...



Screen behind the screen.



Almost done.



Post-installation tasks.



Running /u01/app/oraInventory/orainstRoot.sh



Running /u01/app/oracle/product/11.2.0/db_home_1/root.sh



Done.



And done.



I can login from the virtual machine...



And I can login from the host machine.



Enterprise Manager from the host machine.



And my new account from the host machine.

Easy right?

Special thanks to Tim Hall as well, he's got a great primer for installing 11gR2, Oracle Database 11g Release 2 (11.2) Installation On Enterprise Linux 5.0.

Almost forgot, my problem with Archive Manager creating "database" and "database(2)"? Solved by twitter naturally:





So I unzipped the 2 files from a terminal window and it (apparently) put everything in the same folder. Win.

Labels: 11g, database, howto, install, oracle

  ¶ 11/13/2009 02:22:00 AM   2 Comments Links to this post

  Oracle Press Release on Sun Merger Original can be found here:

Redwood Shores, Calif. - November 9, 2009

Oracle's acquisition of Sun is essential for competition in the high end server market, for revitalizing Sparc and Solaris and for strengthening the Java development platform. The transaction does not threaten to reduce competition in the slightest, including in the database market. The Commission's Statement of Objections reveals a profound misunderstanding of both database competition and open source dynamics. It is well understood by those knowledgeable about open source software that because MySQL is open source, it cannot be controlled by anyone. That is the whole point of open source.

The database market is intensely competitive with at least eight strong players, including IBM, Microsoft, Sybase and three distinct open source vendors. Oracle and MySQL are very different database products. There is no basis in European law for objecting to a merger of two among eight firms selling differentiated products. Mergers like this occur regularly and have not been prohibited by United States or European regulators in decades.

The U.S. Department of Justice carefully reviewed the proposed acquisition during the normal Hart-Scott-Rodino review and considered it again when the European Commission initiated a second phase review. On both occasions the Justice Department came to the conclusion that there is nothing anticompetitive about the deal, including specifically Oracle’s acquisition of the MySQL database product. The U.S. Department of Justice approved the acquisition without conditions and terminated the waiting period under the Hart-Scott-Rodino Act on August 20, 2009.

Sun's customers universally support this merger and do not benefit from the continued uncertainty and delay. Oracle plans to vigorously oppose the Commission’s Statement of Objections as the evidence against the Commission’s position is overwhelming. Given the lack of any credible theory or evidence of competitive harm, we are confident we will ultimately obtain unconditional clearance of the transaction.

Apparently the European Commission, the European Union's executive arm, issued a statement of objections on Monday. Sun issued a statement as well:

On November 9, 2009, the European Commission issued a statement of objections relating to the acquisition of Sun by Oracle Corporation. The Statement of Objections sets out the Commission's preliminary assessment regarding, and is limited to, the combination of Sun's open source MySQL database product with Oracle's enterprise database products and its potential negative effects on competition in the market for database products. The issuing of a Statement of Objections allows addressees to present arguments in response to the Commission's preliminary assessment of the competitive effects of a notified transaction. A Statement of Objections is a preparatory document that does not prejudge the European Commission's final decision. Any final decision by the European Commission is subject to appeal to the European Court of First Instance.

I'm sorry, but the EU just doesn't get it. First, it's open source. Do they really lack the technical acumen to determine what this means?

Second, there is plenty of competition out there. DB2, SQL Server, Sybase, Intercache, BTree...not to mention Postgres and other open source alternatives. This is just embarassing.

Update
Looks like the Department of Justice got into the act as well:

WASHINGTON - Deputy Assistant Attorney General Molly Boast of the Department of Justice's Antitrust Division issued the following statement today after the European Commission (EC) issued a statement of objections regarding Oracle Corporation's proposed acquisition of Sun Microsystems Inc.:

"After conducting a careful investigation of the proposed transaction between Oracle and Sun, the Department's Antitrust Division concluded that the merger is unlikely to be anticompetitive. This conclusion was based on the particular facts of the transaction and the Division's prior investigations in the relevant industries. The investigation included gathering statements from a variety of industry participants and a review of the parties' internal business documents. At this point in its process, it appears that the EC holds a different view. We remain hopeful that the parties and the EC will reach a speedy resolution that benefits consumers in the Commission's jurisdiction.

"Several factors led the Division to conclude that the proposed transaction is unlikely to be anticompetitive. There are many open-source and proprietary database competitors. The Division concluded, based on the specific facts at issue in the transaction, that consumer harm is unlikely because customers would continue to have choices from a variety of well established and widely accepted database products. The Department also concluded that there is a large community of developers and users of Sun's open source database with significant expertise in maintaining and improving the software, and who could support a derivative version of it.

"The Department and the European Commission have a strong and positive relationship on competition policy matters. The two competition authorities have enjoyed close and cooperative relations. The Antitrust Division will continue to work constructively with the EC and competition authorities in other jurisdictions to preserve sound antitrust enforcement policies that benefit consumers around the world."

For the life of me I can't find the "official" statement of objection by the European Commission.

Labels: database, oracle

  ¶ 11/09/2009 06:28:00 PM   1 Comments Links to this post

  Method R + C.J. Date = Fun! I just got an email blast from Mr. Cary Millsap.

It reads as follows:

This brief note is to update you on some of the things we've been doing at Method R Corporation.

C. J. Date seminar - There's a new course on our schedule: we've invited Chris Date to teach "How to Write Accurate SQL and Know It: A Relational Approach to SQL" January 26-28, 2010 in the Dallas/Fort Worth area. Our make-or-break date for the course is December 4, 2009, so if you're interested please enroll soon. I'm personally excited about the opportunity to sit in a classroom for three days with one of the inventors of our whole industry. http://method-r.com/education/107-cj-date-course.

MR Tools - A few weeks ago, we released a new set of software tools that let you do some pretty unbelievable things with Oracle trace data. Take a look at our Guided Tour pages behind the individual tool links at http://method-r.com/software/mrtools.

Method R Profiler - Recently, we released Method R Profiler 5.2.0. The Profiler now runs on Mac and Linux, in addition to Windows. And new with 5.2 is a graphical user interface that's a genuine pleasure to use. I was always a command line guy when I used the Profiler, but with the new 5.2 GUI, I'm sold. http://method-r.com/software/profiler.

The tools are currently a bit out of my league, but the opportunity to listen to C.J. Date would be cool (cool as in geek chic or nerd chic of course). I have no idea what my schedule will be like at this point, but I would definitely like to attend. Hopefully I'll have some things tied up so I can plan for it.

Labels: database, oracle, profiling

  ¶ 11/05/2009 06:05:00 PM   1 Comments Links to this post

  OOW 09: Real World Performance Demo - Exadata

Labels: database, exadata, oracle

  ¶ 10/30/2009 01:15:00 AM   0 Comments Links to this post

  Free Oracle Developer/dba There is a serious lack of work in the Tampa market and desperate times call for desperate measures.

Now, I've always wanted to do this, but was never in a position to do so financially...I'm still not, but something is way better than nothing.

I'm going to offer my services for free.

Not forever mind you, somewhere between 2 and 4 weeks. It is negotiable.

If it works out, i.e. you like what I can bring to your organization, I prove that I pick new systems up quickly and I fit in well with your team; and I like working in your organization, then you pay me for that time worked and I sign up full-time either as a permanent employee or some sort of contract.

If it doesn't work out, we part ways and no one is worse for wear. You get free work from me and I get to experience a new environment (i.e. meet new people, expand my network, etc).

You can view my resume here (permanent link up top coming soon).

Some basic highlights:
PL/SQL: Expert (I don't like that term)
DBA: Junior to Mid-level (or DBA in lowercase, "dba")
APEX: Worked with up to version 3.2, admittedly rusty, but passionate about the product.
Design: Love modeling data. Model just about everything in my head (in normal life). Use Visio extensively for visual representations. Experience with SQL Developer Data Modeler, ERwin, etc.
Documentation: Give me a wiki or something similar and I'll document just about everything that I do.

That's it. Contact info is on my resume or up top on the "email" icon.

It's now posted on craigslist as well.

Labels: database, dba, development, jobs, plsql, work

  ¶ 10/27/2009 12:25:00 PM   3 Comments Links to this post

  Followup Question for Mr. Ellison In my first year in IT, I was fortunate enough to be sponsored by my employer to go to Oracle OpenWorld. This was way back in 2002. Hard to remember that far back isn't it?

Anyway, I attended Larry's keynote, and if I remember correctly, he wasn't there. He was in Australia (or somewhere) in the middle of a some sort of boat race. I do remember being dared, during the question and answer period, to ask him for a job. Amazingly I didn't do it.

One thing that stuck with me though...

He was talking about the internal operations of Oracle. He stated that there were something like 500 databases in use throughout the company. With those 500 databases there was the requisite staff to support those databases. His goal was to get all of Oracle into a single database.

So my question to Larry, or anyone else that might remember this particular speech; Did this ever happen?

I was reminded of this last week at the SOUG meeting. Charlie Garry gave a "where we have been" type presentation. The title of the first slide was Oracle Database: 2000-2009, which I thought was a misprint, until he got started. The gist of the talk was that way back in 1999 (before I was IT born), they set out a plan for the database for the next 10 years. I can't remember specifics because that's the time I started to come down with The Plague. In essence, this phase of Oracle database development has been completed with the release of 11gR2. Pretty interesting presentation (stupid Plague).

* I still have The Plague, so pardon the haphazard ramblings

Specifically what reminded me of Mr. Ellison's statement back in 2002 was Mr. Garry telling us that all of his files were in a database somewhere.

Seven years later, I'm asking the followup, Did Oracle ever get down to a single database?

Labels: database, oracle

  ¶ 10/26/2009 07:33:00 PM   6 Comments Links to this post

  Oracle + MySQL = Monopoly?

BRUSSELS -- The European Commission opened an antitrust investigation into Oracle Corp.'s $7.4 billion acquisition of Sun Microsystems Inc., dealing a blow to Oracle's efforts to keep Sun customers who are increasingly being wooed by rival technology companies.

That's from EU to Probe Oracle-Sun Deal on September 3rd.

Ostensibly, this is about MySQL.

In announcing the probe, the European Union's executive arm cited concern that the deal would stymie competition for database software...Sun makes a low-cost alternative [MySQL] that is increasing in popularity.

With all of the database offerings out there, how can this even be a consideration?

Let's say Oracle decides to squash MySQL (which I doubt), are you telling me that it can't or won't be forked? Are you also telling me that these businesses have no other choices in database software?

monopoly ((economics) a market in which there are many buyers but only one seller) "a monopoly on silver"; "when you have a monopoly you can ask any price you like"

The wikipedia entry is littered with words like "exclusive" and phrases like "sole control." Where is the monopoly?

This isn't about Oracle, specifically, either. I could never understand the charges against Microsoft. Shouldn't they be allowed to build their systems the way they want to?

Oracle Has Customers Over a Barrell

If Oracle buys Sun, it could cripple or kill the rival product. "The Commission has an obligation to ensure that customers would not face reduced choice or higher prices as a result of this takeover," Competition Commissioner Neelie Kroes said in a statement.

Rival? Really? Didn't Marten Mickos say that MySQL wasn't, or didn't want to be, a competitor of Oracle?

Where is the reduced choice?

Besides (please correct me if I am wrong), aren't these customers paying for support and not the product itself?

Anyway, there are plenty of database options out there. If MySQL does fork, it might just be better for everyone involved because they'll take it in a new or different direction.

Please help this confused soul and explain to me how this might be a monopoly.

Labels: database, mysql, oracle

  ¶ 9/14/2009 04:27:00 PM   3 Comments Links to this post

  Database Tutoring Last week a friend of mine sent over a craigslist posting, someone looking for a tutor. Here's the ad:

Looking for an experienced SQL Database Systems analyst to help with homework assignments for a graduate level database course. Would like to meet 2 times per week (for 2 hours each session) over the next four months. Evening, weekends or Wednesdays preferred. The candidate must be able to explain the technical to the non-technical. Please reply with resume and availablility.

Course Topics Are:
* Relational Model and Languages ( SQL)
* Database Analysis and Design
* Methodology (Conceptual and Logical Design)
* Social, Legal, etc. Issues
* Distributed DBMSs and Replication
* Object DBMSs
* The Web and DBMSs
* Business Intelligence

I replied immediately and heard back the next day. I sent my resume but I thought the blog would be more appropriate. Apparently it was enough.

We spoke on Saturday for about an hour and I received all the materials necessary to start doing research including a sample database (in Access).

I have to say I'm pretty excited about it. I thoroughly enjoy trying to explain database concepts so that others (non-techies) can understand. It's a Masters level class filled with students from Computer Science and from an Education Technology tract. Bet you can guess which side my "student" falls in.

Seems a little odd that the Educational Technology folks are in the class, but I think it's a good thing. When they need an application in the future, they'll have a much better grasp of what to ask for and hopefully they'll be more involved in the process.

I'll use this space both for reporting on progress and helping to explain things. Wish us luck!

Labels: database, design, sql

  ¶ 9/13/2009 09:15:00 PM   3 Comments Links to this post

  Announcing the World's First OLTP Database Machine with Sun FlashFire Technology Apparently Larry Ellison is announcing a new product on Tuesday, an Oracle OLTP database machine with Sun FlashFire technology. Sign-up for the web cast here.

I first saw it here, at Francisco Munoz Alvarez's Oracle NZ blog.

Mark Rittman has some more speculation here.

Greg Rahn at Structured Data has one as well.

Talk about shiny new objects...

Labels: database, oltp, oracle, sun

  ¶ 9/13/2009 02:18:00 PM   0 Comments Links to this post

  Data vs. Information Last week in The Case For Views on the very last line I said

Records in a table typically constitute data. Tables, joined together, in a view, tend to turn that data into information.

That elicited a very, very strong reaction from a good friend and mentor. In the comments he left this

Turn data into information? That doesn't make a whole lot of sense to me-- All data is information. Can you clarify that statement a little?

On the face of it, that's not a very strong reaction. He tends to be a lurker though, rarely leaving comments.

Then there was twitter, where he sent me a few more links on the subject.

I'm pretty sure he was fired up.

Once a week or so, we'll get together over beers and have excellent conversations. Occasionally, I'll try to hold my ground from the database perspective. Last week we had a discussion about whether the database should be making web service calls.

Security aside, I thought it was appropriate given the size and skills of the shop, but he and our other friend staunchly disagreed.

Point is, we have some great conversations. It has never come down to "You are stupid!" or anything like that, it's a conversation with each side presenting their arguments.

Since my friend has like 28 degrees in Engineering, I've learned to give him the benefit of the doubt, so I wanted to study up on it.

I asked the oracle-l mailing list on Friday.

My contention, or what I have heard and read, is that a database stores data, only through the use of SQL or some reporting tool, does that data get turned into information. I don't know where I heard or read that for the first time, but I've probably been saying it for years.

Through my friends response and others on the mailing list, I probably need to rethink that particular statement.

Here are some relevant links provided by my friend and others on the oracle-l mailing list:

Principles of Communication Enginnering, By John M. Wozencraft, Irwin Mark Jacobs

Information Theory and Reliable Communication, By Robert G. Gallager

Nuno Suto, aka Noons suggested Fabian Pascal, which can be read here. He also suggested reading up on Chris Date and Ted Codd as well as

Conceptual Schema and Relational Database Design: A Fact Oriented Approach, By G. M. Nijssen, T. A. Halpin

Have you ever used the phrase, "data into information" or some derivation there of? I'd like to track down where I first came across it if possible. Thoughts on Data vs. Information as separate entities?

Labels: database, design

  ¶ 9/07/2009 10:13:00 PM   5 Comments Links to this post

  The Bit Bucket

Labels: database, funny

  ¶ 9/03/2009 12:51:00 AM   0 Comments Links to this post

  Oracle Database: 11gR2 Here is a dedicated site (not on the oracle.com domain). Here is the home page on OTN.

Maybe it's just me, but I've read or heard virtually nothing about this release. That's pretty amazing. I remember reading about 10g prior to release...I can't remember 11gR1 though. I'd have to say that's a pretty impressive feat.

At the first site, there is an Eduquiz which, after you submit your answer, links to specific areas.

Datawarehousing/Exadata

Lower Costs with Oracle Database 11g

Grid Consolidation

Security and Compliance

High Availability

Manageability

Storage Management

Information Management

Application Development

Upgrade/Support Services

Documentation can be found here and New Features can be found here. I guess I'll be reading these for fun and enjoyment tonight.

Labels: database, oracle

  ¶ 9/01/2009 10:09:00 AM   0 Comments Links to this post

  Constraints: PK = UQ? Another little thing I learned today, you can use uniquely constrained columns in referential integrity. Looking it up in the docs, I found this little blurb:

Foreign keys can be defined as multiple columns. However, a composite foreign key must reference a composite primary or unique key with the same number of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns.

Not really hard to miss I guess.

The only reason I found this was because I made the changes to the codes table, putting a UNIQUE constraint on the former PK, and nothing broke when I rebuilt the database.

So here are my 2 tables:

CREATE TABLE my_codes
(
  mycode VARCHAR2(20)
    CONSTRAINT pk_mycode PRIMARY KEY
);

CREATE TABLE t
(
  tid NUMBER(10)
    CONSTRAINT pk_tid PRIMARY KEY,
  mycode
    CONSTRAINT fk_mycode_t REFERENCES my_codes( mycode )
    CONSTRAINT nn_mycode_t NOT NULL
);

I want to add a surrogate key to the MY_CODES table, per this discussion. I made a compromise, essentially anything that will or could be user entered will have a surrogate key (either SYS_GUID or sequence generated).

My first step was to remove the constraint on T, then drop the PK constraint on MY_CODES, add a new column that will hold the surrogate key and finally add the PK constraint to the new column.

ALTER TABLE t DROP CONSTRAINT fk_mycode_t;

ALTER TABLE my_codes DROP CONSTRAINT pk_mycode;

ALTER TABLE my_codes ADD ( mycodeid NUMBER(10) );

ALTER TABLE my_codes
  ADD CONSTRAINT pk_mycodeid
  PRIMARY KEY ( mycodeid );

Since I'm changing the meaning, I want to make sure that someone doesn't enter the same code twice, so I add a UNIQUE constraint on the table.

ALTER TABLE my_codes
  ADD CONSTRAINT uq_mycode
  UNIQUE ( mycode );

I rebuild my tables, without referencing the new PK.

CJUSTICE@TESTING>CREATE TABLE my_codes
  2  (
  3    mycodeid NUMBER(10)
  4      CONSTRAINT pk_mycodeid PRIMARY KEY,
  5    mycode VARCHAR2(20)
  6      CONSTRAINT uq_mycode UNIQUE
  7      CONSTRAINT nn_mycode NOT NULL
  8  );

Table created.

CJUSTICE@TESTING>CREATE TABLE t
  2  (
  3    tid NUMBER(10)
  4      CONSTRAINT pk_tid PRIMARY KEY,
  5    mycode
  6      CONSTRAINT fk_mycode_t REFERENCES my_codes( mycode )
  7      CONSTRAINT nn_mycode_t NOT NULL
  8  );

Table created.

Wait a minute. I didn't change the FK to point to the PK.

I then posted the finding on Twitter and received a timely response from @neilkod:



So what's the point? There isn't one. I just found it interesting.

Labels: database, oracle

  ¶ 8/03/2009 06:30:00 PM   0 Comments Links to this post

  INTEGER = NUMBER(38) I noticed something a little strange today.

I've recently been creating tables with as specific a data type as I could. In that regard, I've been using INTEGER.

CREATE TABLE t
(
  x INTEGER
);

Do a describe on that table:

CJUSTICE@TESTING>@DESC T
 Name                                   Null?    Type
 -------------------------------------- -------- ------------
 X                                               NUMBER(38)

Running a query on USER_TAB_COLUMNS results in this:

SELECT
   data_type,
   data_type_mod,
   data_length,
   data_precision,
   data_scale,
   default_length
 FROM user_tab_columns
 WHERE table_name = 'T'
   AND column_name = 'X';

DATA_TYPE  DAT DATA_LENGTH DATA_PRECISION DATA_SCALE DEFAULT_LENGTH
---------- --- ----------- -------------- ---------- --------------
NUMBER                  22                         0

The describe says NUMBER(38) yet USER_TAB_COLUMNS says NUMBER(22). Weird.

When I view the object through the SQL Developer schema browser and go to the SQL tab I get this:

CREATE TABLE "CJUSTICE"."T" 
( 
  "X" NUMBER(*,0)
);

So Oracle converts it somewhere along the way?

Further, going to the source of DBA_TAB_COLUMNS took me to DBA_TAB_COLS, I really thought it was the other way around, but I digress. I began to look at the source from DBA_TAB_COLS which had many staring at all of the SYS tables. OBJ$. COL$. HIST_HEAD$. USER$. COLTYPE$. I then started to try to unravel that...but I stopped.

It obviously won't stop me from doing my work, I just found it interesting. It's similar to A NULL Observation, III.

Labels: database, oracle

  ¶ 8/03/2009 05:21:00 PM   6 Comments Links to this post

  SQL Objects, JDeveloper, JPublisher and Java This is one for my application developer friends.

Say your application uses stored procedures. Not just any stored procedures, but ones that accept arrays or SQL Object types (User Defined Types - UDTs).

One thing you have to do is map a java object to that SQL Object before you can pass it back to the stored procedure. I can't remember the specifics, but I can show you how to "automatically" create the Java code that maps to the SQL Object.

First, create type UDT.

If will be a table of a record...is it called a multi-dimensional array? Whatever.

CREATE TYPE r_record IS OBJECT
(
  id NUMBER(10),
  col1 VARCHAR2(30),
  col2 VARCHAR2(30),
  col3 VARCHAR2(30)
)
/

CREATE TYPE t_table AS TABLE OF R_RECORD
/

I'll create a procedure that accepts T_TABLE as an input parameter. It won't do anything, but just to give you an idea.

CREATE OR REPLACE
PROCEDURE test( p_t_table IN T_TABLE )
IS
BEGIN
  NULL;
END test;
/

Now go into JDeveloper and through the Database Navigator, select the schema you placed these objects.



Right click on the object and select Generate Java (as shown above).

You'll then be presented with an options menu:



It's all foreign to me so I accept the defaults. I do notice however that it's pretty customizable, for instance, you can select how you want to Number Types to be generated, either objectjdbc, oracle, jdbc or bigdecimal. You'll know better about what this means than I will.



From that, you get 3 files, TTable.java which maps to the TABLE OF R_RECORD, RRecord.java which maps to the SQL Type R_RECORD, and finally RRecordRef.java...which I don't really know what it does. I'm sure you will though.

Here's the code generated, in order.

TTable.java

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.jpub.runtime.MutableArray;

public class TTable implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "CJUSTICE.T_TABLE";
  public static final int _SQL_TYPECODE = OracleTypes.ARRAY;

  MutableArray _array;

private static final TTable _TTableFactory = new TTable();

  public static ORADataFactory getORADataFactory()
  { return _TTableFactory; }
  /* constructors */
  public TTable()
  {
    this((RRecord[])null);
  }

  public TTable(RRecord[] a)
  {
    _array = new MutableArray(2002, a, RRecord.getORADataFactory());
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _array.toDatum(c, _SQL_NAME);
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    TTable a = new TTable();
    a._array = new MutableArray(2002, (ARRAY) d, RRecord.getORADataFactory());
    return a;
  }

  public int length() throws SQLException
  {
    return _array.length();
  }

  public int getBaseType() throws SQLException
  {
    return _array.getBaseType();
  }

  public String getBaseTypeName() throws SQLException
  {
    return _array.getBaseTypeName();
  }

  public ArrayDescriptor getDescriptor() throws SQLException
  {
    return _array.getDescriptor();
  }

  /* array accessor methods */
  public RRecord[] getArray() throws SQLException
  {
    return (RRecord[]) _array.getObjectArray(
      new RRecord[_array.length()]);
  }

  public RRecord[] getArray(long index, int count) throws SQLException
  {
    return (RRecord[]) _array.getObjectArray(index,
      new RRecord[_array.sliceLength(index, count)]);
  }

  public void setArray(RRecord[] a) throws SQLException
  {
    _array.setObjectArray(a);
  }

  public void setArray(RRecord[] a, long index) throws SQLException
  {
    _array.setObjectArray(a, index);
  }

  public RRecord getElement(long index) throws SQLException
  {
    return (RRecord) _array.getObjectElement(index);
  }

  public void setElement(RRecord a, long index) throws SQLException
  {
    _array.setObjectElement(a, index);
  }
}

RRecord.java

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

public class RRecord implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "CJUSTICE.R_RECORD";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  protected MutableStruct _struct;

  protected static int[] _sqlType =  { 2,12,12,12 };
  protected static ORADataFactory[] _factory = new ORADataFactory[4];
  protected static final RRecord _RRecordFactory = new RRecord();

  public static ORADataFactory getORADataFactory()
  { return _RRecordFactory; }
  /* constructors */
  protected void _init_struct(boolean init)
  { if (init) _struct = new MutableStruct(new Object[4], _sqlType, _factory); }
  public RRecord()
  { _init_struct(true); }
  public RRecord(java.math.BigDecimal id, String col1, String col2, String col3) throws SQLException
  { _init_struct(true);
    setId(id);
    setCol1(col1);
    setCol2(col2);
    setCol3(col3);
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _struct.toDatum(c, _SQL_NAME);
  }


  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  { return create(null, d, sqlType); }
  protected ORAData create(RRecord o, Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    if (o == null) o = new RRecord();
    o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
    return o;
  }
  /* accessor methods */
  public java.math.BigDecimal getId() throws SQLException
  { return (java.math.BigDecimal) _struct.getAttribute(0); }

  public void setId(java.math.BigDecimal id) throws SQLException
  { _struct.setAttribute(0, id); }


  public String getCol1() throws SQLException
  { return (String) _struct.getAttribute(1); }

  public void setCol1(String col1) throws SQLException
  { _struct.setAttribute(1, col1); }


  public String getCol2() throws SQLException
  { return (String) _struct.getAttribute(2); }

  public void setCol2(String col2) throws SQLException
  { _struct.setAttribute(2, col2); }


  public String getCol3() throws SQLException
  { return (String) _struct.getAttribute(3); }

  public void setCol3(String col3) throws SQLException
  { _struct.setAttribute(3, col3); }
}

RRecordRef.java

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.REF;
import oracle.sql.STRUCT;

public class RRecordRef implements ORAData, ORADataFactory
{
  public static final String _SQL_BASETYPE = "CJUSTICE.R_RECORD";
  public static final int _SQL_TYPECODE = OracleTypes.REF;

  REF _ref;

private static final RRecordRef _RRecordRefFactory = new RRecordRef();

  public static ORADataFactory getORADataFactory()
  { return _RRecordRefFactory; }
  /* constructor */
  public RRecordRef()
  {
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _ref;
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    RRecordRef r = new RRecordRef();
    r._ref = (REF) d;
    return r;
  }

  public static RRecordRef cast(ORAData o) throws SQLException
  {
     if (o == null) return null;
     try { return (RRecordRef) getORADataFactory().create(o.toDatum(null), OracleTypes.REF); }
     catch (Exception exn)
     { throw new SQLException("Unable to convert "+o.getClass().getName()+" to RRecordRef: "+exn.toString()); }
  }

  public RRecord getValue() throws SQLException
  {
     return (RRecord) RRecord.getORADataFactory().create(
       _ref.getSTRUCT(), OracleTypes.REF);
  }

  public void setValue(RRecord c) throws SQLException
  {
    _ref.setValue((STRUCT) c.toDatum(_ref.getJavaSqlConnection()));
  }
}

How do you integrate all that? I'm not really sure, but Marc recently wrote up a piece on using this method to tie into SQLUnit, pretty nice one too.

Labels: database, development, java, oracle, sqlunit

  ¶ 7/23/2009 05:34:00 PM   0 Comments Links to this post

  Constraints to the Max! I ran across this question today on the Oracle-l list:

Hi List,

I have read the following but I am looking for a way to create a
case-insensitive database. Is it even possible?

http://askanantha.blogspot.com/2007/07/making-oracle-case-insensitive.html

Thanks,

Roger Xu

Later in the thread, Niall Litchfield, replied with the following:

Assuming that you mean you want to make all string data case insensitive and that the requirement has come from developers who don't want to check their inputs for case errors (though I bet they want to say business logic is an application function) then a check constraint on each column that the inserted/updated value is equal to its uppercase representation is a start. Then they'll have to either check for the constraint failing or start discussions with you about database input validation aka constraints.

That gave me an idea.

CREATE TABLE t
(
  x VARCHAR2(30)
    CONSTRAINT pk_x PRIMARY KEY
    CONSTRAINT ck_upper_x_t CHECK ( x = UPPER( x ) )
);

What does that do? It insures that the value put in X is always uppercase.

PARTY@TESTING>INSERT INTO t ( x ) VALUES ( 'home' );
INSERT INTO t ( x ) VALUES ( 'home' )
*
ERROR at line 1:
ORA-02290: check constraint (PARTY.CK_UPPER_X_T) violated

PARTY@TESTING>INSERT INTO t ( x ) VALUES ( 'HOME' );

1 row created.

Cool. Now the Application Developers have to deal with it. No wiggle room there.

How far can you go?
Let's see.

DROP TABLE t PURGE;

CREATE TABLE t
(
  x VARCHAR2(30)
    CONSTRAINT pk_x PRIMARY KEY
    CONSTRAINT ck_upper_x_t CHECK ( x = UPPER( x ) )
    CONSTRAINT ck_nospaces_x_t CHECK ( INSTR( x, ' ' ) = 0 )
    CONSTRAINT ck_charonly_x_t CHECK ( REGEXP_INSTR( x, '[[:digit:]]' ) = 0 )
);

I'm sure I could go on and on...but it's kind of fun.

ARTY@TESTING>INSERT INTO t ( x ) VALUES ( 'HOME SCHOOL' );
INSERT INTO t ( x ) VALUES ( 'HOME SCHOOL' )
*
ERROR at line 1:
ORA-02290: check constraint (PARTY.CK_NOSPACES_X_T) violated

No spaces allowed.

PARTY@TESTING>INSERT INTO t ( x ) VALUES ( '12345' );
INSERT INTO t ( x ) VALUES ( '12345' )
*
ERROR at line 1:
ORA-02290: check constraint (PARTY.CK_CHARONLY_X_T) violated

No digits allowed!

Why?
Constraints are awesome. The very definition of constraints is awesome. Some of the best ideas come to you when you are constrained by something, usually time. OK, maybe not time in software development. Time in writing I've heard is pretty cool. Limit the colors an artist has available and see what they come up with. Less choices sometimes means better.

Typically I'll constrain the crap out of a data model. With development and testing, some of those will be relaxed. Some will be added.

The point is, don't be afraid of them. Constraints are a very good thing indeed.

Labels: constraints, database, development, oracle

  ¶ 7/21/2009 12:53:00 AM   2 Comments Links to this post

  Oracle Concepts: The Data Dictionary I have come to rely pretty heavily on the Data Dictionary as I don't typically use a GUI. This stems from the fact that in my first professional IT job (reporting off of Oracle), I was given a tnsnames.ora file and something called SQL*Plus. I came from Microsoft Access...I wanted pretty pictures of my database objects. My first year was spent asking how to find the tables in a schema (schema? what's that?), how to view the SQL that made up a view, and how to view the source code.

Find Tables

SELECT * FROM user_tables;

View Text

SET LONG 100000
SELECT text FROM user_views WHERE view_name = :your_view;

View Source

SELECT line, text 
FROM user_source 
WHERE name = :package_name
  AND type = :package_spec_or_package_body;

Needless to say it wasn't fun.

Without pain there is no progress.

What is the Data Dictionary?

...data dictionary, which is a read-only set of tables that provides information about the database. A data dictionary contains:

* The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
* How much space has been allocated for, and is currently used by, the schema objects
* Default values for columns
* Integrity constraint information
* The names of Oracle Database users
* Privileges and roles each user has been granted
* Auditing information, such as who has accessed or updated various schema objects
* Other general database information

Not completely read-only as I found out once. What's the point? None really. I just like being able to copy and paste the documentation. OK, that's only partially true.

All the information that you retrieve via your nice GUI interface comes from these tables. SQL Developer, JDeveloper, Toad, etc. I believe some even have an option to view the SQL being submitted to the database. If you haven't used (knowingly) the Data Dictionary before, check it out. You can easily view all 651 views (10gR2) by issuing:

SELECT * FROM dictionary;

Happy hunting.

Labels: database, documentation, oracle

  ¶ 7/08/2009 05:33:00 PM   2 Comments Links to this post

  Oracle: Getting Started A friend of mine recently expressed interest in learning Oracle.

Here's what I sent him:

Start here:

Oracle Concepts Guide - http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/toc.htm

You don’t have to read everything, but it’s probably the best place to start. I’d be more than happy to answer some of the more “inane” questions (How do I do this? How do I connect? Etc).

Oracle Documentation (List of Books) - http://www.oracle.com/pls/db111/portal.all_books

Don’t be overwhelmed by that. It’s ridiculous how much stuff there is out there. I list some books out by discipline:

DBA
--  2 Day DBA - http://download.oracle.com/docs/cd/B28359_01/server.111/b28301/toc.htm
----  Easiest place to start (after the Concepts Guide)
--  Oracle Database Administrator’s Guide - http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/toc.htm
----  Definitely getting into the nitty gritty stuff here.
Developer
--  PL/SQL Language Reference - http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/toc.htm
----  That should help you get started with PL/SQL if you so choose

For either role, the bible is probably the SQL Language Reference manual: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/toc.htm

Usually what I do, is just download the software and try to get it running. It’s super easy on Windows, a bit more difficult on *nix environments (I just recently installed Oracle on Oracle Enterprise Linux, my first go at a non-Windows environment).

The documentation above is for 11g, the latest version. You can get the software here: http://www.oracle.com/technology/software/products/database/index.html I’d suggest installing EE, might as well go full boat. Plus, it doesn’t really make a difference resource wise. If you have 1.5 to 2GB RAM, you should have plenty. You can run an instance at about 512MB.

It requires an account (free) and they do not spam.

Also, try Oracle Database XE, a free (as in beer) standalone edition built off of the 10.2 kernel. Application Express is used as the front end (a pretty sweet GUI tool).

http://www.oracle.com/technology/software/products/database/xe/index.html

Hope that doesn’t scare you off too much. ;)

Any other pointers? I know there are tons of books so link them up if you feel they are worthy.

Labels: database, dba, oracle

  ¶ 7/08/2009 12:56:00 AM   8 Comments Links to this post

  Classic: Application Developers vs. Database Developers One of my favorite "articles" of all time. I love these types of conversations, in the DB or the Middle Tier? For the vast majority of us, the Database will do just fine. As I've learned more about the data grids and the like, there are trade-offs, which aren't often discussed. One way or another you lose data (say you decide to only UPDATE one time instead of 60). Originally posted on February 20, 2008. Enjoy.

It started innocently enough with this article. I sent it out to about 20 colleagues.

The best line from the article:
"Jerry: "Yeah, databases cause lots of headaches. They crash all the time, corrupt data, etc. Using text files is better."

One of my more recently arrived colleagues (I'll call him Mr. M) replied to everyone with this statement:

"Kind of funny actually, databases are less and less important at the large investment banks, where they basically load everything up into a data grid across a several hundred node cluster. Writing to the db is way too slow."

This started a day long exchange of emails. What follows is the entire thread (up until my last post tonight).

Me:
"I would just argue that they don’t necessarily know how to write to databases. I would however love to see benchmarking done on both methods. Would be an interesting test..."

Mr. M:
"Well, my understanding is they just can’t scale out the db enough. Even something like Oracle RAC won’t work. And outside of the military, these are probably the top 1% of programmers in the world building this stuff."

Me:
"A benchmark would be the only way I would believe it.

If you said the top 1% of database developers tried it and failed, I would be more likely to agree.

My experience is that application developers != database developers. Different type of thinking involved."

Mr. M:
"'A benchmark would be the only way I would believe it.'

Do you need a benchmark before you would believe in-memory retrieval is faster than disk retrieval? Essentially, this is what we’re talking about.

'If you said the top 1% of database developers tried it and failed, I would be more likely to agree. My experience is that application developers != database developers. Different type of thinking involved.'

Why? It’s an issue to do with application performance not simply database performance. Database concerns are a subset of application concerns, essentially a specialization, requiring less encompassing knowledge. ;)

From the article you linked to (http://www.watersonline.com/public/showPage.html?page=432587)

"Better data management is the answer, says Lewis Foti, manager of high-performance computing and grid at The Royal Bank of Scotland (RBS) global banking and markets. "For very large compute arrays, the key issue is data starvation and saturation. This problem requires data grids with high bandwidth and scalable, parallel access,
...
Banks are learning that data management in a distributed grid environment is very different from online transaction processing. "With so many data sources, distribution channels, demands for aggregation and analytics, surges in data volumes and complex dynamics between the flows, we need to manage 'data in motion' and give up the notion that data is somehow stored. It's dynamic, not static," says Michael Di Stefano, vice president and architect for financial services at GemStone Systems
...
There is even some debate over how small a unit of work can be put on today's grids. Di Stefano at GemStone, for example, says, "One client has gone from 200 trades per second in a program trading application to more than 6,000 trades per second. This shows what the technology can do."

Yep, the writing is on the wall. Oracle knows it too.

http://www.google.com/search?hl=en&q=oracle+buys+tangosol&btnG=Google+Search"

Me:
"Good points. If it is in-memory it would be faster. I have not had the pleasure to work on such a system.

I do disagree with the database concerns being a subset of application concerns. The data drives the app. We’re probably getting religious at this point (or am I)."

Mr. M:
"‘The data drives the app.”

Exactly, but who’s to say where the data comes from or in what format? My application data may reside completely in xml files, or maybe I get it from some third party web services a la the en vogue “mashup.” Heck, I may not even need to worry about a database anymore…. http://www.amazon.com/gp/browse.html?node=16427261 The database is only one particular concern of the overall application. And it’s the application that matters. Data is useless if it just sits on a disk somewhere. It’s the ways in which the application lets the users view and manipulate the data that adds value to the business.

Yep, definitely a different type of thinking between application developers and database developers."

Me:
"Definitely religious now.

Applications come and go, data stays the same. Think Green Screens, EJBs, Ruby…what’s next?"

Mr. M:
"'Applications come and go'

Exactly. Businesses are not static, nor are the markets they compete in. Changing applications are a function of changing business processes and changing markets.

'data stays the same.'

Nonsense. Otherwise UPDATE would not be an SQL reserved word. If you mean database technology stays the same, well, I’m more inclined to agree with that.

'Think Green Screens, EJBs, Ruby...what’s next?'

Whatever comes along to let the business more effectively respond to current market realities. Application platforms have evolved much faster than database platforms have. They’ve had to, their sphere of operation is much broader than that of databases, this is only natural, they deal with much broader concerns than do databases. Databases in the internet era function in essentially the same role they did in the era of dumb terminals. Clearly application platforms have evolved orders of magnitude more. Hence the statement, database concerns are a subset of application concerns.

Here’s a simple test….if I take some business application and I’m forced to throw away one or the other, either the database or the appl- wait a second, it doesn’t even make sense to finish it, does it? The business can live without the database. I could do all kinds of things with the data, I could stick it anywhere. The business can’t live without the application though. Another way to look at is, what do the business users look at, test, approve, and use? The database? Of course not, they look at the application. They could care less whether the data sits on disk in an RDBMS, xml, or flat files."

Me:
"We obviously violently disagree.

Without the database (and I use database and data interchangebly), the business could no longer function. The app is meaningless. How would you contact your customer? You couldn’t find it.

'Exactly. Businesses are not static, nor are the markets they compete in. Changing applications are a function of changing business processes and changing markets.'

Poorly designed applications…that is all."

A Feisty Colleague:
"Using data and database interchangeably is incorrect. A database is a mechanism for data storage. XML data sets and flat files are mechanisms for data storage, too. So is a file cabinet, because, the data doesn’t have to be electronic, it could be … gasp! … on paper, and the application to use that data would be hands for holding the paper and a pencil to update and add data to the page."

Me:
"No it isn’t. I take into account xml files, flat files, web services (but not paper, unless it’s scanned) and all that. It would be consumed by the database and then accessed by the application via SQL.

(that’s for Mr. M and the feisty one)"

At which point someone forwarded the home page for Oracle's TimesTen In-Memory Database.

Me:
"A database on/in the mid-tier...Perfect!"

Mr. M:
"Implicit acknowledgment that disk IO operations that come with traditional database access simply can’t match the performance of in-memory data access (a point which you previously were unconvinced of but now seem perfectly accepting of the idea once you see it’s got Oracle’s imprimatur on it).

Of course, why any application developer would want to program against an SQL interface if they weren’t forced to is beyond me. It is orthogonal to the programming model of most application platform languages.

Surely Oracle recognize this fact too or they wouldn’t be buying Tangosol and other data grid technologies. Of course, most of those products are far more technically advanced than TimesTen or anything Oracle has in that space.

Incidentally, it’s illustrative to note that Coherence and other products like it were for the most part designed and built by application programmers. The development of all these products is pretty much driven by the needs of the large investment banks on Wall Street. These trading applications simply had too many concurrent transactions to use an RDBMS (a problem quite a number of public domains now share, most famously google.com, nope, no RDBMS there, yet miraculously there is still data). The database just simply would not scale to such a degree. So the application developers, by necessity, came up with an alternate solution that did work, a fully transactional cache of data replicated across a cluster with node numbers in the thousands, and no relational model whatsoever to speak of. A perfect example of how database concerns are only one, sometimes small, concern amongst many that application developers must be aware of and ready to solve."

Me:
"Like you said initially, the top 1%.

Many of us will never touch a system like this.

I will certainly concede that it is faster (still would love to see benchmarking though), but that still leaves 99% of the applications out there that do not require that kind of performance."

Me (again):
"And don’t forget, I use data and database interchangeably. Applications are nothing without the data right?

As to the object/relational impedance mismatch...well, more people that don’t know how to work in sets. Looping is what they understand. I understand the application side more than you seem to give me credit for.

I’m not saying applications aren’t important, they are. Data (databases) and applications go hand in hand. If the application went away though, they could still access their data via SELECT statements (yes, via an application client tool), however painful that may be. Applications make retrieving data that much easier for our users.

If anyone wants to unsubscribe from this mailing list, just let us know. This is fun for me (I’m guessing Mr. M too)."

Needless to say it was a fun day. It didn't get [too] personal. More than anything I'm happy to have an equally passionate colleague.

Besides, he claims he was just fracking around with me. ;)

Labels: coherence, database, design, oracle

  ¶ 7/06/2009 09:17:00 PM   16 Comments Links to this post

  SQL: SYS_CONTEXT In my experience so far, I've seen very few places where SYS_CONTEXT is used. It is typically used in conjunction with CONTEXTs and also Virtual Private Database (VPD).

Oracle has a built in namespace called USERENV and their is a wealth of information you can retrieve from there. I wrote up a quick view to demonstrate the use of SYS_CONTEXT:

SELECT
  SYS_CONTEXT( 'USERENV', 'ACTION' ) action,
  SYS_CONTEXT( 'USERENV', 'AUTHENTICATION_METHOD' ) authentication_method,
  SYS_CONTEXT( 'USERENV', 'BG_JOB_ID' ) bg_job_id,
  SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ) client_identifier,
  SYS_CONTEXT( 'USERENV', 'CLIENT_INFO' ) client_info,
  SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMAID' ) current_schemaid,
  SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA' ) current_schema,
  SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) current_user,
  SYS_CONTEXT( 'USERENV', 'DB_DOMAIN' ) db_domain,
  SYS_CONTEXT( 'USERENV', 'DB_NAME' ) db_name,
  SYS_CONTEXT( 'USERENV', 'DB_UNIQUE_NAME' ) db_unique_name,
  SYS_CONTEXT( 'USERENV', 'HOST' ) host,
  SYS_CONTEXT( 'USERENV', 'INSTANCE' ) instance,
  SYS_CONTEXT( 'USERENV', 'INSTANCE_NAME' ) instance_name,
  SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) ip_address,
  SYS_CONTEXT( 'USERENV', 'ISDBA' ) isdba,
  SYS_CONTEXT( 'USERENV', 'LANG' ) lang,
  SYS_CONTEXT( 'USERENV', 'LANGUAGE' ) language,
  SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) network_protocol,
  SYS_CONTEXT( 'USERENV', 'NLS_CALENDAR' ) nls_calendar,
  SYS_CONTEXT( 'USERENV', 'NLS_CURRENCY' ) nls_currency,
  SYS_CONTEXT( 'USERENV', 'OS_USER' ) os_user,
  SYS_CONTEXT( 'USERENV', 'SERVER_HOST' ) server_host,
  SYS_CONTEXT( 'USERENV', 'TERMINAL' ) terminal
FROM dual

Which yields the following:

ACTION:  NULL
AUTHENTICATION_METHOD:  PASSWORD
BG_JOB_ID:  NULL
CLIENT_IDENTIFIER:  NULL
CLIENT_INFO:  NULL
CURRENT_SCHEMAID:  88
CURRENT_SCHEMA:  CJUSTICE
CURRENT_USER:  CJUSTICE
DB_DOMAIN:  NULL
DB_NAME:  testing
DB_UNIQUE_NAME:  testing
HOST:  cdj-laptop
INSTANCE:  1
INSTANCE_NAME:  TESTING
IP_ADDRESS:  192.168.1.4
ISDBA:  FALSE
LANG:  US
LANGUAGE:  AMERICAN_AMERICA.WE8MSWIN1252
NETWORK_PROTOCOL:  tcp
NLS_CALENDAR:  GREGORIAN
NLS_CURRENCY:  $
OS_USER:  chet
SERVER_HOST:  oracledb
TERMINAL:  UNKNOWN

Basically, these are global variables that Oracle stores on the current session/user.

You can create a context for just about anything you want. I've used them in the past along with table based security in a stateless environment. In essence, I create a record in a table with a SESSION_ID, I then store that in the context (global variable) and I use that to reconnect to the database each time.

I promise to have an example of using it in the near future.

Labels: database, oracle, plsql, security, sql

  ¶ 7/05/2009 11:02:00 PM   0 Comments Links to this post

  Oracle Concepts: Data Integrity Rules I'm reading through the Concepts manual again as mentioned on last week.

I'm going to make a small effort to post some of the key concepts here over the next couple of weeks. If you've read through the Concepts Guide before, this can serve as a brief refresher. If not, good, you're exposed to something new.

Data Integrity Rules

This section describes the rules that can be applied to table columns to enforce different types of data integrity.

Null rule: A null rule is a rule defined on a single column that allows or disallows inserts or updates of rows containing a null (the absence of a value) in that column.

Unique column values: A unique value rule defined on a column (or set of columns) allows the insert or update of a row only if it contains a unique value in that column (or set of columns).

Primary key values: A primary key value rule defined on a key (a column or set of columns) specifies that each row in the table can be uniquely identified by the values in the key.

Referential integrity rules: A referential integrity rule is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).

Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. The rules associated with referential integrity are:

* Restrict: Disallows the update or deletion of referenced data.
* Set to null: When referenced data is updated or deleted, all associated dependent data is set to NULL.
* Set to default: When referenced data is updated or deleted, all associated dependent data is set to a default value.
* Cascade: When referenced data is updated, all associated dependent data is correspondingly updated. When a referenced row is deleted, all associated dependent rows are deleted.
* No action: Disallows the update or deletion of referenced data. This differs from RESTRICT in that it is checked at the end of the statement, or at the end of the transaction if the constraint is deferred. (Oracle Database uses No Action as its default action.)

Complex integrity checking: A user-defined rule for a column (or set of columns) that allows or disallows inserts, updates, or deletes of a row based on the value it contains for the column (or set of columns).

Reading on past the brief section to the Constraint States I found this nugget:

ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. This allows existing rows to violate the constraint, while ensuring that all new or modified rows are valid.

This is a great tool for legacy systems. You have data in the column(s) that you can't really do anything with, but you want to insure that all future data that goes in that particular column(s) matches the parent key.

Of course the ideal is to somehow clean the data up, but you don't always have that option. This is a good first step towards to overall cleanup of your legacy system.

Labels: database, documentation, oracle

  ¶ 6/29/2009 01:17:00 AM   0 Comments Links to this post

  A NULL Observation, III Part I here.

Part II here.

OK, we have a winner. Coskan Gundogar suggested in the comments, that using the MODIFY clause of the ALTER TABLE statement should work. Let's see:

desc t_null

Name                    Null?    Type
----------------------- -------- ----------------
COL1                             VARCHAR2(30)

CJUSTICE@TESTING>ALTER TABLE T_NULL DROP CONSTRAINT nn_col1_tn;

Table altered.

CJUSTICE@TESTING>ALTER TABLE t_null MODIFY ( col1 CONSTRAINT nn_col1_tn NOT NULL );

Table altered.

CJUSTICE@TESTING>@DESC T_NULL
Name                    Null?    Type
----------------------- -------- ----------------
COL1                    NOT NULL VARCHAR2(30)

Voila!

Much better. The question still remains as to what's going on in the background. Until now, I had never thought that white space mattered in anything Oracle. Very strange.

Maybe I can get Miladin to dig through the internals and see what's really going on.

Labels: database, null, oracle

  ¶ 6/25/2009 11:03:00 PM   1 Comments Links to this post

  A NULL Observation, II Shoot me, I couldn't let this go. Plus, I needed a reason to test drive my new sandbox.

The part about the space had me a little perplexed:

CJUSTICE@TESTING>SELECT
  2    table_name,
  3    constraint_name,
  4    constraint_type,
  5    search_condition,
  6    status
  7  FROM user_constraints
  8  ORDER BY table_name;

TABLE_NAME           CONSTRAINT_NAME      C SEARCH_CONDITION     STATUS
-------------------- -------------------- - -------------------- --------
T_NOT_NULL           NN_COL1_TNN          C "COL1" IS NOT NULL   ENABLED
T_NOT_NULL_CHECK     NN_COL1_TNNC         C  "COL1" IS NOT NULL  ENABLED
T_NULL               NN_COL1_TN           C  col1 IS NOT NULL    ENABLED

Here's the DDL that created that constraint:

ALTER TABLE t_not_null_check DROP CONSTRAINT nn_col1_tnnc;

ALTER TABLE t_not_null_check
  ADD CONSTRAINT nn_col1_tnnc
  CHECK ( "COL1" IS NOT NULL );

Note the white space there. I like putting a space after a parenthesis as I believe it makes it slightly easier to read.

With that in mind, watch this:

ALTER TABLE t_not_null_check DROP CONSTRAINT nn_col1_tnnc;

ALTER TABLE t_not_null_check
  ADD CONSTRAINT nn_col1_tnnc
  CHECK ("COL1" IS NOT NULL);

Note that I removed the space between the "(" and the """. Here's what it looks like:

SELECT 
  table_name, 
  constraint_name, 
  constraint_type, 
  search_condition
FROM user_constraints
ORDER BY table_name;

TABLE_NAME       CONSTRAINT_N C SEARCH_CONDITION
---------------- ------------ - ------------------------------
T_NOT_NULL       NN_COL1_TNN  C "COL1" IS NOT NULL
T_NOT_NULL_CHECK NN_COL1_TNNC C "COL1" IS NOT NULL
T_NULL           NN_COL1_TN   C "COL1" IS NOT NULL

CJUSTICE@TESTING>desc t_not_null_check
 Name    Null?   Type
 ----------------------- -------- ----------------
 COL1      VARCHAR2(30)

The leading space is gone now. Null? still shows up as NULL. I would assume that most GUI apps get their data from USER/ALL/DBA_TAB_COLUMNS, so let's take a look:

SELECT 
  table_name, 
  column_name, 
  nullable
FROM user_tab_columns
ORDER BY table_name;

TABLE_NAME       COLUMN_NAME                    N
---------------- ------------------------------ -
T_NOT_NULL       COL1                           N
T_NOT_NULL_CHECK COL1                           Y
T_NULL           COL1                           Y

How about USER_TAB_COLS?

SELECT 
  table_name, 
  column_name, 
  nullable
FROM user_tab_cols
ORDER BY table_name;

TABLE_NAME       COLUMN_NAME                    N
---------------- ------------------------------ -
T_NOT_NULL       COL1                           N
T_NOT_NULL_CHECK COL1                           Y
T_NULL           COL1                           Y

Nothing to see there.

So, there is a difference between defining a NOT NULL constraint using either the NOT NULL or CHECK syntax. In USER_CONSTRAINTS, there are 4 distinct constraint types:
1.  P = PRIMARY
2.  U = UNIQUE
3.  R = REFERENCE or FOREIGN KEY
4.  C = CHECK

What's the lesson here? Well, if doing analysis, you can't just depend on using the DESCRIBE command from SQL*PLus to see what is required and what is not. Of course you can't depend on that for everything. Interesting "problem" none-the-less.

Update
Part III (the final solution) is here.

Labels: database, null, oracle

  ¶ 6/25/2009 12:06:00 AM   0 Comments Links to this post

  A NULL Observation As I've been doing a lot of analysis lately, I've found a slightly annoying "problem."

I typically use JDev or SQLDev to see details on a table, more than just a DESCRIBE from SQL*Plus can give me anyway.

This "problem" relates to how NULL columns are displayed, both via DESCRIBE and the previously mentioned tools.

First up, my favorite, the table definition with inline constraints.

CREATE TABLE t_not_null
(
  col1 VARCHAR2(30)
    CONSTRAINT nn_col1_tnn NOT NULL
);

Let's see how it looks in a SQL Worksheet (Columns):
Constraints:
SQL*Plus (DESCRIBE):

CJUSTICE@TESTING>desc t_not_null
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                    NOT NULL VARCHAR2(30)

Now I'll create a different table, this time instead of using NOT NULL, I'll use the CHECK syntax.

CREATE TABLE t_not_null_check
(
  col1 VARCHAR2(30)
    CONSTRAINT nn_col1_tnnc CHECK ( col1 IS NOT NULL )
);

Columns:
Nullable = Yes? Hmmm...

Constraints:

DESCRIBE

CJUSTICE@TESTING>desc t_not_null_check
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                             VARCHAR2(30)

Interesting, it doesn't show up in the "Null?" column like it did with the NOT NULL syntax used above.

Now I'll create a table with no inline constraint defined.

CREATE TABLE t_null
(
  col1 VARCHAR2(30)
);

I know (famous last words) I can't use the NOT NULL syntax in an out-of-line constraint:

ALTER TABLE t_null
  ADD CONSTRAINT nn_col1_tn NOT NULL;
  ADD CONSTRAINT nn_col1_tn NOT NULL
                            *
ERROR at line 2:
ORA-00904: : invalid identifier

So I use the CHECK syntax:

ALTER TABLE t_null
  ADD CONSTRAINT nn_col1_tn
  CHECK ( col1 IS NOT NULL );

Table altered.

Columns:
Constraints:
DESCRIBE

CJUSTICE@TESTING>desc t_null
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                             VARCHAR2(30)

Very odd.

Let's look at the dictionary:

SELECT
  table_name,
  constraint_name,
  constraint_type,
  search_condition,
  status
FROM user_constraints
ORDER BY table_name;

TABLE_NAME           CONSTRAINT_NAME      C SEARCH_CONDITION     STATUS
-------------------- -------------------- - -------------------- --------
T_NOT_NULL           NN_COL1_TNN          C "COL1" IS NOT NULL   ENABLED
T_NOT_NULL_CHECK     NN_COL1_TNNC         C  col1 IS NOT NULL    ENABLED
T_NULL               NN_COL1_TN           C  col1 IS NOT NULL    ENABLED

Interesting, I wonder if the fact that it's not UPPERcased and in quotes?

CJUSTICE@TESTING>DROP TABLE t_not_null_check;

Table dropped.

Elapsed: 00:00:00.04
CJUSTICE@TESTING>CREATE TABLE t_not_null_check
  2  (
  3    col1 VARCHAR2(30)
  4      CONSTRAINT nn_col1_tnnc CHECK ( "COL1" IS NOT NULL )
  5  );

Table created.

DESCRIBE

CJUSTICE@TESTING>DESC T_NOT_NULL_CHECK
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                             VARCHAR2(30)

OK, Null? is still...NULL.

CJUSTICE@TESTING>SELECT
  2    table_name,
  3    constraint_name,
  4    constraint_type,
  5    search_condition,
  6    status
  7  FROM user_constraints
  8  ORDER BY table_name;

TABLE_NAME           CONSTRAINT_NAME      C SEARCH_CONDITION     STATUS
-------------------- -------------------- - -------------------- --------
T_NOT_NULL           NN_COL1_TNN          C "COL1" IS NOT NULL   ENABLED
T_NOT_NULL_CHECK     NN_COL1_TNNC         C  "COL1" IS NOT NULL  ENABLED
T_NULL               NN_COL1_TN           C  col1 IS NOT NULL    ENABLED

Why is there that extra space in front of "COL1" IS NOT NULL?

I ended my investigation there as it seems to be a waste of time. I just found it interesting that there was a difference in how you defined a NOT NULL constraint and whether or not it would show up in the DESCRIBE command. Anyone out there notice this before?

Read Part II here.

Part III (the final solution) is here.

Labels: database, null, oracle

  ¶ 6/24/2009 05:24:00 PM   5 Comments Links to this post