Wednesday, September 13, 2006

Something To Do Right Now

If you're still using any of the default Oracle username/passwords on any of your databases, go change it now.

I could blog about this for 1-2 pages, but for two reasons:
1. I think it is an obvious (but oft-neglected) thing to do, and
2. You can use the time you would have spent reading my persuasive arguments to actually go change your passwords.

So go do it now.

Tuesday, September 12, 2006

Using Numerical Fields

What is a number? The definition is one of the longest I've seen, but generally it refers to a quantity (or sum, total, count) of units. Quantities can be subjected to all sorts of calculations, such as addition, subtraction, multiplication and division.

Sometimes numbers are used for ordering, as well. So it makes sense to compare one number to another, not only as being greater or lesser than another, but also by what number of units it is greater.

Which brings me to my point. I've been asked on more than one occasion why I sometimes favour the use of VARCHAR2 in situations where others are using NUMBER. Take, for example, my earlier post on creating an appropriate Boolean type.

Some developers think I should have used NUMBER(1) as the type, denote True as 1, False as 0, and have a check to make sure the value is either 0 or 1.

That solution also works. But there is a reason I don't use it.

It is true that this method has its advantages, not the least of which is comfort to programmers who are used to that (most programming languages make that conversion implicitly). They also claim that you can use multiplication for AND operations, and addition for OR operations.

Or can you? True * False = False, so ok, multiplication seems to work fine. But addition? True + True = 2. 2 is not a valid value. And what about subtraction and division? "Oh those don't apply" Then it's not an appropriate use of NUMBER, in my mind. What about square root? What is True to the exponent False? And so on.

I don't mean to pick on this one example. I've seen cases where NUMBER is used for all sorts of things: dates, credit card numbers, social insurance/security numbers, street address house numbers, zip codes, and so on. In some of these cases (though not necessarily all), it would have been more appropriate to create a type: a VARCHAR2 restricted to numerical characters, and then use that one type for all such instances.

No, this is not a critical point. NUMBER will work just fine, and I have no stories about how people got into trouble by using NUMBER for something that wasn't. But consider the importance of having precision and accuracy in your data. Shouldn't we be equally precise in your description of the data?

That is why I generally prefer using NUMBER only in cases where the field actually is by definition a number, a quantity of units, and eligible for all numerical operations. I'd love to hear your thoughts on this matter too.

Saturday, September 02, 2006

Protecting PL/SQL Code


Reason #1 Protection

Reason #2 Malicious Reasons



In fact, the example I'm using is from Oracle's documentation.


SQL> WHERE name = 'WRAPTEST' order by line;


all_emps emp_tab;
FOR i IN 1..10 LOOP

How can we avoid this?

PL/SQL Code Storage: Files vs In-DB Packages






wrap iname=wraptest.sql oname=wraptest.plb


SQL> @wraptest.plb

Procedure created.



(garbage removed)





Oracle Packages - And why/how you should use them.

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