Thursday, February 21, 2008

Hah, they didn't even mention "bind variable"

But, we all know that is the true cause of the problem... The lack of use of bind variables - making special characters in names do nasty things like: (this is a quote)

... apostrophe is often mistaken for a piece of computer code, corrupting the system. ...

Ah, yes, the nasty apostrophe, capable of corrupting entire systems.

It is sloppy programming (they got that right).  Lack of binds is a co-winner of first place "bad things to do" right along side "when others then null".

I still wish....

  • when others would be removed from plsql
  • triggers would be deprecated and removed
  • autonomous transactions would start just raising errors
  • literals in sql would raise an error

All of those bullets are hyperbole, but they make a point.  When I see "when others" in code, it is more often used wrong (in the order of 1000 to 1).  When I see a trigger developed to "enforce integrity" - it is almost always wrong.  The way people use autonomous transactions - almost always WRONG.  Literals in SQL - usually wrong (but not always, but here I would err on the side of caution - outlaw literals and let bind peeking put them back in later...)

I know, I've said this before.

You know, I'll say it again.

Because I see the same four mistakes every day.  Every. Single. Day.  Especially the "when others" and lack of binds.  Followed by triggers.  Fortunately, most programmers never read to the autonomous transaction chapter...

POST A COMMENT

62 Comments:

Blogger Nitin Aggarwal said....

Bind variables also suppress the use of histograms to generate the best execution plan.
So, for selecting only 1% of records, where optimizer should have chosen indexes, would choose full table scan because bind variable hide the literal value which we intend to pass.

For queries which are run not too often e.g in batch processing (ETL for e.g.), i think it is still better to avoid bind variables and use literals instead.

Thu Feb 21, 10:56:00 PM EST  

Blogger Thomas Kyte said....

@nitin

don't believe you actually read the post...

All of those bullets are hyperbole, but they make a point. ... Literals in SQL - usually wrong (but not always, but here I would err on the side of caution - outlaw literals and let bind peeking put them back in later...)


and in 11g - you would be just plain wrong.

Thu Feb 21, 11:00:00 PM EST  

Blogger Gary Myers said....

Can you add implicit date to character conversion (and vice versa) to the list.
I like the ability to log errors that are rollback proof in an autonomous transaction. Maybe just ban anything other than an INSERT VALUES.

Fri Feb 22, 12:06:00 AM EST  

Blogger Stew said....

@Gary Myers

I also use WHEN OTHERS to log errors in an autonomous transaction. Then someone else comes along and comments out my RAISE. True story :(

From 10g on, *_SCHEDULER_JOB_RUN_DETAILS logs the errors from batches. I may just leave it at that.

Fri Feb 22, 02:20:00 AM EST  

Blogger KAJ said....

Erm - wow. Triggers? All the other mistakes I'm well aware of, but what's wrong with using a trigger?

Fri Feb 22, 03:01:00 AM EST  

Blogger Chris said....

One case where I use "when others" is in mod_plsql applications. The default error page is not acceptable for end users, so we catch all errors and present a more suitable text.

Fri Feb 22, 03:18:00 AM EST  

Blogger DomBrooks said....

I see the same "WHEN OTHERS" issues week in, week out but I still wouldn't outlaw it.

It's no more harmful than exception handling code in other languages.

It's bad programmers that should be banned.

Fri Feb 22, 03:42:00 AM EST  

Anonymous Anonymous said....

Just curious, do you think that using a trigger in order to set the PK in a table from a sequence is OK or do you consider that as bad practice ?

Fri Feb 22, 03:59:00 AM EST  

Blogger Malcolm said....

The 'when others' conversation has been done to death.

The 'triggers considered dangerous' conversation is one which needs to happen.

They're the Oracle programmer's equivalent of a GOTO.

Ideally, I can't think of ANY situation where I would use a trigger. If I want to automatically set some columns of a row upon insert (e.g. primary key), I would just specify those columns IN the insert. If I want to update table A everytime I update table B, I would just use two updates in a row.

Triggers:
1. Make your code hard to understand and maintain. They make spaghetti code.
2. Are slow. Every time DML is performed on a row, you want to parse/execute a block of PL/SQL, are you crazy?

The only situation I can think where triggers are useful, is to provide 'hooks' into code you don't own, or can't easily change for some reason. And that's a case of putting best practices aside in the interests of expediency, so I still feel dirty if I have to do it.

Fri Feb 22, 06:39:00 AM EST  

Blogger Thomas Kyte said....

@Gary

Hear hear, implicit conversions. Just dealt with that yesterday and someone not understanding why:

where column = DATE_VARIABLE

was different from

where column = TO_DATE( date_variable)

@Chris

unfortunately, there are times when WHEN OTHERS would be appropriate - just like there are times triggers make sense, autonomous transactions make sense and so on.

Unfortunate in that those who know how and when to use them are in the smallest of minorities, but those who know how to use them inappropriately abound.

@Anonymous regarding setting pk via sequence.

I sure don't see the need for the overhead of the trigger, no. I have no problem whatsoever putting the S.NEXTVAL in the insert.

@Malcom

If the "when others" has been done to death, why do I see it every day - including weekends - at least once or twice and probably more.

It is like bind variables - people have said "ok, I get it", but unfortunately the universe of new programmers is expanding at an exponential rate - and they haven't heard it.

I honestly don't believe they teach error handling anywhere in school. Ever.

The only situation I can think where triggers are useful, is to provide 'hooks' into code you don't own, or can't easily change for some reason. And that's a case of putting best practices aside in the interests of expediency, so I still feel dirty if I have to do it.

I like that - I'm going to have to borrow some of that verbiage :)

@KAJ

triggers, I've written about that many times. Just so abused that I would rather live without them than have to live with what people do with them day to day.

Fri Feb 22, 07:01:00 AM EST  

Anonymous David Aldridge said....

Just the other day I was looking at a stored procedure that implementing a report by returning a ref cursor to the client. The exception handling was WHEN OTHERS THEN return-a-single-row-of-null-values.

Total ... error ... supression.

Sweet.

Now, where's that sarcasm emoticon?

Fri Feb 22, 07:11:00 AM EST  

Blogger karthick said....

I prefer to use WHEN OTHERS at the top level of my program. By which I can prevent sending my original error message to the user and just say “Hey you got an error… Contact the concern person”. By doing this I stop the end user from peeping into my data structure. Because sending original error to the end user is dangerous. The world has heard about SQL injection. So I prefer using WHEN OTHERS some thing like this.

MAIN PROGRAM
BEGIN
SUB PROGRAM 1
BEGIN

END;

SUB PROGRAM 2
BEGIN

END;

SUB PROGRAM 3
BEGIN

END;

EXCEPTION
WHEN OTHERS THEN
Log your error here…
Also we have lot of stuff now to back track the error. Use them all here. Help yourself. Because no one else is going to…

RAISE_APPLICATION_ERROR(-20001, ‘Hey you got an error… Contact the concern person’);
END;

But what would be horrible is that…

MAIN PROGRAM
BEGIN
SUB PROGRAM 1
BEGIN

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);
END;

SUB PROGRAM 2
BEGIN

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);

END;

SUB PROGRAM 3
BEGIN

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);

END;

EXCEPTION
WHEN OTHERS THEN
Log your error here…
RAISE_APPLICATION_ERROR(-20001, ‘Hey you got a error… Contact the concern person’);
END;

Believe me this is a horrible approach… I have done this and I have suffered… Believe me…

Fri Feb 22, 07:46:00 AM EST  

Blogger William Robertson said....

That is a depressing story. Especially the part where it says "Even if the computer system is sophisticated enough to welcome an O'Brien or Al-Kurd, the name must be stored in the database, where a hyphen or apostrophe is often mistaken for a piece of computer code, corrupting the system." What database on Earth is 'corrupted' by apostrophes?

Regarding Features We Wish Were Banned, you'd think the compiler could spot a WHEN OTHERS that is not followed by a RAISE or RAE - and in fact in 11g it can.

Any use of SQLERRM or SQLCODE within the message argument of RAISE_APPLICATION_ERROR is always wrong, and stupid.

Fri Feb 22, 08:57:00 AM EST  

Anonymous Brian Tkatch said....

If a PROCEDURE uses a CURSOR, is it a good idea to use WHEN OTHERS to CLOSE the CURSOR if it is OPEN?

Basically, in case the unexpected happens, make sure the CURSOR gets CLOSEd.

As for TRIGGERs, i like to use them for what i call DB maintenance (not part of the data model, per se). Like history TABLEs when DELETEing a record (though the idea of a history TABLE is not always a good idea) and to fill in SOx compliance COLUMNs, just as tossing in a SYSDATE for last updated when and the current user for last updated by.

Fri Feb 22, 09:28:00 AM EST  

Blogger Scott Horowitz said....

Personally, I've learned to hate triggers. This is a small reason:

SQLWKS> SELECT MAX(LINE) FROM
2> (select count(line) LINE,NAME from user_source
3> where type = 'TRIGGER'
4> GROUP BY NAME)
5>
MAX(LINE)
----------
1487

I have had procedures run, then wonder why something else happens. There's an update trigger on the header table, which updates the detail table, then queries the header table... It just gets ugly. I'd take all the code out of the triggers, and just put in the procedures. It makes following code a virtual impossibility.

-Scott

Fri Feb 22, 09:35:00 AM EST  

Anonymous Robert said....

<sarc>wow people still code EXCEPTION like that ?</sarc>
haha I wish I could see in real time Toms reaction when he comes across irritants like these with the responsible developer(s) right there.
Hey Tom how about taping a reenactment and put it on YouTube ? please ? :)

Fri Feb 22, 10:05:00 AM EST  

Blogger Thomas Kyte said....

@Brian

If your cursors are local variables - you don't need to worry about it, eg: if you have:

is
cursor c is select ....
begin
open c;
......

C will close automagically when C goes out of scope. If you

for x in (select ... )

for x in (C)

the cursors close upon exiting the loop (regardless of how you exit the loop - failure, goto, exit, end of data...)


Only if you

a) open a cursor that was in a package scope (a global)

b) open a cursor using dbms_sql

c) open a ref cursor that'll be returned

eg: only if you use a "global variable that is a cursor" or the procedural dbms_sql API


@Scott

I could not agree more. Too many developers take the cute "look at this, my insert of 5 into this table actually does 5,000 things in the background - isn't that just so cool".

I hate it. It is not maintainable at all. For the very few times a trigger would be useful, I would give it up just to prevent others from trigger abuse.

Fri Feb 22, 10:59:00 AM EST  

Anonymous Brian Tkatch said....

@Tom

Thanx! I had read about CURSOR "snarfing" and it did not sink in that in a regular local PROCEDURE this was not the case. Thanx for explaining.

Fri Feb 22, 11:25:00 AM EST  

Blogger Alberto Dell'Era said....

Apostrophe ... my name on Metalink has been for years "Alberto Dell", which is not a bad thing :)

Fri Feb 22, 11:41:00 AM EST  

Blogger Rahul said....

@karthick,

Two things. DON"T WRITE THE RAISE_APPLICATION_ERROR in the inner sub programs. ON THE TOP MOST program, Log the error (using autonomous transactions) using FORMAT_ERROR_BACKTRACE program given by Oracle.

And then raise a obscure error message if you want ON THE TOP MOST program(s).

But, I digress. Anyway, I have see my boss calling a program not using bind variable(s), and he was from SQL Server side. Changed the call to binds and it worked perfect(no 50000 different rows for each call in shared pool).

And yes, I am also so surprised the that consultant mentioned "Michael Rais, director of software development at Permission Data" didn't mention the word 'bind variable' eventhough everyone in here knows that pretty much every database out there takes bind variables( I think) and this problem is BECAUSE the developer didn't use bind variables.

Rahul.

Fri Feb 22, 01:08:00 PM EST  

Anonymous Slavik Markovich said....

It's a pity that Oracle does not allow using bind variables to object names in PL/SQL. Many Oracle SQL Injection vulnerabilities in built-in code come from the fact that you have to concatenate a table name or index name to your query if it is passed in a parameter. This is the reason for dbms_assert but even Oracle has issues with implementing that correctly.

Fri Feb 22, 01:24:00 PM EST  

Blogger karthick said....

@Rahul

I think i was trying to say the same thing. I think you might have to read it again.

Fri Feb 22, 01:58:00 PM EST  

Blogger Thomas Kyte said....

@Slavik Markovich

Well, it is not plsql that doesn't allow IDENTIFIERS to be bound, it is SQL itself - down to the core.

The purpose of binding is to reuse SQL.

select * from :t;

that could not be "reused", it needs an entirely different plan, set of authorizations, everything.

Oracle doesn't have any problems using dbms_assert - not anymore than anyone else - it is a matter of education, and many times (well, all of the time) the people writing code here are the same as people writing code over there...

For you see, I got hired by Oracle. I was hired by someone before that. I did not become instantly 100% the best Oracle developer ever the day I switched jobs. In fact, I haven't become that yet.

I've irritated more than one developer internally by taking the same attitude with them as I would with anyone regarding these issues (binding, when others then null, etc...)

Fri Feb 22, 02:30:00 PM EST  

Anonymous Garet said....

We've been using Apex for a good couple of years now (it keeps getting better and better!) but, talking about triggers, it seems to rely heavily on there being a trigger in the background to supply a technical key with every insert. I wonder if they (Apex team) are storing up trouble for themselves?

By the way, I sooo put out that I won't be present on your seminar on Monday, I didn't get the training approval by management... :-(((

Fri Feb 22, 03:21:00 PM EST  

Anonymous Gareth said....

Mind you, with my inability to hit the 'preview' button and type out my name correctly, I can see why management didn't approve the training!

Fri Feb 22, 03:27:00 PM EST  

Anonymous Long time fan! said....

Hi Tom - are you staying near Heathrow next week?
I'll buy you a drink if you are...

Fri Feb 22, 05:03:00 PM EST  

Anonymous Long time fan... said....

Gareth - I'm at SAP in London next week so can't take up the offer of a free Tom seminar I had. That's even worse...

Fri Feb 22, 06:54:00 PM EST  

Anonymous Rahul said....

@karthik,

Yes, you are right. Should have read it right the first time.

Rahul

Fri Feb 22, 11:26:00 PM EST  

Anonymous Skipjacker said....

Wait!

What am I missing?

You said:

"where column = DATE_VARIABLE

was different from

where column = TO_DATE( date_variable)"

How is To_Date an "implicit" conversion? Why would you do a To_date on a date variable?

I understand the difference between column = :date and column = :char_that_looks_like_date.

I understand the difference between

column = :date_variable

and

to_char(column) = :char_variable


I don't understand the difference in your example.

Mon Feb 25, 09:35:00 AM EST  

Blogger Thomas Kyte said....

@skipjacker:

where column = DATE_VARIABLE

that compares a date column to a date_variable - in plsql for example.


where column = TO_DATE( date_variable)

that calls to_date, on a date, but to_date takes a string, so it is really

to_date( to_char( date_variable ) )


the to_char is implicit

ops$tkyte%ORA10GR2> create table t (x date);

Table created.

ops$tkyte%ORA10GR2> insert into t values (sysdate);

1 row created.

ops$tkyte%ORA10GR2> insert into t select to_date(x) from t;

1 row created.

ops$tkyte%ORA10GR2> select to_char(x,'dd-mon-yyyy hh24:mi:ss') from t;

TO_CHAR(X,'DD-MON-YY
--------------------
25-feb-2008 12:54:38
25-feb-2008 00:00:00



insert into t select to_date(x) from t;

was really

insert into t select to_date( to_char(x,'dd-mon-rr'), 'dd-mon-rr' ) from t;


because my default date mask was dd-mon-rr.... lost the time there we did.

Mon Feb 25, 12:55:00 PM EST  

Anonymous Skipjacker said....

@TK,

Ah, ok I couldn't fathom why someone would To_date a date variable. I see that there would obviously have to be an implicit conversion to char because the to_date isn't overloaded to accept date.

The reason your example piqued my curiosity was because a few weeks prior a colleague made the following statement, "TO_DATE() in a WHERE clause confuses the CBO, or at least it used to." and I asked if he meant around a column or value and he said a value. He went on to clarify by saying the problem was with, "TO_DATE and other functions that obscured the actual value. Oracle has enhanced the CBO somewhere along the way so that it can look inside. The correction in a tuning guide was to use an INDEX hint."

So basically he was saying that

where column = DATE_VARIABLE

was different from

where column = TO_DATE( literal, format)

Did that used to be true?

Mon Feb 25, 02:12:00 PM EST  

Blogger Thomas Kyte said....

@skipjack

he might have been talking about something related to bind peeking - but not enough information to say for sure.

Mon Feb 25, 03:08:00 PM EST  

Blogger Kim Berg Hansen said....

Quote from skipjacker's question:

...the problem was with, "TO_DATE and other functions that obscured the actual value. ...

Could skipjacker's colleague mean that "where column = [literal]" allows CBO to use histograms, but "where column = function([literal])" would perhaps not (or perhaps not in older releases) allow the CBO to use histograms?
That would fit his response to the alleged problem: using index hint.

Just a thought: Would the CBO's ability to use histograms on "where column = function([literal])" depend on whether the function was deterministic or not?

Tue Feb 26, 08:48:00 AM EST  

Blogger Kim Berg Hansen said....

Addendum to previous post:

If the CBO can use histograms on "where column = function([literal])", would that mean that it executed the function at parse time?

(Funny how these questions can set a train of thoughts in motion :-)

Tue Feb 26, 08:51:00 AM EST  

Blogger Kim Berg Hansen said....

Addendum to the addendum:
(Sorry, but the thoughts just pop into my head just after posting a comment :-)

If CBO cannot use histograms on "where column = function([literal])", then what's best if you have query with a literal date-value?

You could do "where column = to_date('26-02-2008','DD-MM-YYYY')", which would be good for avoiding implicit conversions, but might be bad for using histograms.

Or you could do "where column = :date_bind_variable", even though the bind variable is a constant. That would allow bind variable peeking to use histograms.

Any comments would be highly appreciated, Tom, as I have a legacy system that defines 01-01-1900 as "empty date" and I therefore have some skewed indexes where I have queries of the form "where indexed_column = to_date('01-01-1900','DD-MM-YYYY'). Perhaps this might account for a couple of strange incidents I have seen :-)

Thanks for all your help to the Oracle community, Tom...

Tue Feb 26, 09:02:00 AM EST  

Anonymous Sokrates said....

what would be worse:

a.
create procedure mynull is
begin
null;
exception when others then null;
end mynull;

b.
create procedure mynull is
begin
mynull;
exception when others then mynull;
end mynull;

:-P

Wed Feb 27, 02:59:00 AM EST  

Anonymous Anonymous said....

Hi,Tom.Does AskTom is under problem? I has not been able to visit your web for several days. The page says "Error Unable to write activity.log " .I am in China. And sorry to report the problem here but I can not find another way.

Tomying tomying@21cn.com

Fri Feb 29, 03:32:00 AM EST  

Anonymous Ann O'Nymouse said....

anonymous:

file a bug to
Hú Jǐntāo
the rest of the world doesn't seem to have problems with asktom

Fri Feb 29, 06:15:00 AM EST  

Anonymous Anonymous said....

Pre 11g - non-oltp - if you want to give the CBO a clue about how a big a date range you're specifying - use literals. Just today, 10g I saw two different plans from what would have been the same query with bind variables. Both plans were good, one was appropriate for a day's worth of data, the other that included full table scans of a partition was appropriate for a couple of weeks out of a monthly partition. The CBO did a nice job with the literals. I could let Oracle peek the first parse, but of course then I would have only one plan, whichever plan was appropriate for the first-parsed runtime values. One-time bind-variable peeking, ok in some cases, just plain wrong in others.

Tue Mar 04, 11:50:00 PM EST  

Anonymous Anonymous said....

Wah Wah Wah...

These tools also serve the learner very well.

Wed Mar 05, 02:05:00 PM EST  

Anonymous Anonymous said....

wondering why can't Oracle allow to define date type variable in SQL* Plus prompt.

e.g

SQL> variable date1 date

Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
BINARY_FLOAT | BINARY_DOUBLE ] ]

Tue Mar 11, 04:58:00 PM EDT  

Anonymous Bauer Wolfgang said....

Hi Tom,

sometimes I think you become more and more extreme in your opinions. No Triggers, no literals, no WHEN OTHERS.
Well sounds a bit like mysql 4
I remember a discussion at asktom one or two years ago when someone asked why oracle doesn't index foreign key automatically because nearly everyone needs it.
You said, that oracle doesn't do it because of the one who doesn't need it. And I think that's the point: You can't blame the database because people do things wrong. Kick Bitmap Indexes, Merge Join Cartesian, IOTs because they are often used in a wrong way...
Just my 2 Cents.

Sun Mar 16, 04:15:00 PM EDT  

Blogger Thomas Kyte said....

@bauer

when or where have I ever said "no literals", I have said there are times and places for literals - but most of the time, no, you should be binding.

As for triggers, when others - I scarcely think you can compare that to an unindexed foreign key.

Let us see, why do I not like triggers and when others...

well that would be because people misuse them in a way that creates MASSIVE hard to find bugs - bugs that seem to "come and go", that people write off as "Oracle must be doing something wrong". They are things that can DO SERIOUS LONG TERM DAMAGE to data - because the people using them don't know what they are doing

Tell me when an unindexed foreign key, bitmap index, IOT or cartesian join caused serious long term damage to data please?

No comparison - find something you think seriously damages data because of "lack of proper use" and then we'll talk.

Sun Mar 16, 05:12:00 PM EDT  

Anonymous Wolfgang Bauer said....

Hi Tom,
when or where have I ever said "no literals"

In this blog you said:

I still wish....literals in sql would raise an error

Correct me if I'm wrong, but that sounds like 'no literals'

Don't understand me wrong, we agree that no program logic should ever be placed into a trigger, but triggers can also be very useful and "harmless" when you know what you are doing.

find something you think seriously damages data because of "lack of proper use"

DROP TABLE for example ;-)
I just want to say, that it is senseless (in my opinion) to try to "protect" the database by removing usefull features which migth be used wrong by some people.

We use autonomous transactions for logging in our pl/sql programs, triggers to track changes and historize the old record into other tables, when others clause so we don't have to write an error handler for every exception that might occur (of course we re-raise the exception using raise_application_error). Without these features (especially triggers) we presumably would have to use MSSQL oder DB/2.

Mon Mar 17, 02:57:00 PM EDT  

Blogger Thomas Kyte said....

@wolfgang bauer

did you see the sentence:

All of those bullets are hyperbole, but they make a point.

right after that list that employed hyperbole to make a point?

And I don't think drop table fits my description. You would sort of realize it sooner rather than later and recover.

Data integrity issues that are NOT enforced by the database - not discovered for months or longer, then blamed on Oracle, but the result is the same - bad data, unrecoverable - cannot be fixed by any 'normal means', it happened so long ago.


Autonomous transactions, triggers, not binding, when others null - things I run into so so often that I'd rather give them up anymore than have people have access to them.

simply because the universe of people that can apply them correctly is so small - and the universe of people that cannot use them correctly is so huge and so not aware of the fact that they don't know what they are doing.

Mon Mar 17, 04:54:00 PM EDT  

Anonymous Wolfgang Bauer said....

Hi Tom,

did you see the sentence:

You're right. Please forgive an old german speaker.

And I don't think drop table fits my description

No, I don't think that. That's why I made a ;-) after it.

Data integrity issues that are NOT enforced by the database

And do you really think these people will say: Hey we don't have triggers. Hmm well perhaps we should use PK/FK Constraints.
No. These people think a PK/FK Constraint makes the database slow (additional indexes you know...), makes the application not "database independent" or simply have never heard anything about constraints.
So these people would code these things into there applications with the same (wrong)result.
Well not exactely the same result: These people would poste java/php/vb code on asktom and not the pl/sql code out of there "integrity" triggers.

So removing triggers and other nice features would not change much but penalize (hope that's the right word for it) the developers who knows how to use it correctly.

Reading Kyte, Lewis & Co would make the (oracle) database world more save then kicking WHEN OTHERS.

Mon Mar 17, 06:05:00 PM EDT  

Blogger AR said....

Hi Tom,
Can you please advice me on the usage of bitmasks?
Where it will be advisable to use them?
My developers are suggesting using bitmasks for computation in the application layer and storing the bitmasks as "Number" data type in database.
Please share your thoughts on this subject.
Regds,
AR

Fri Mar 21, 06:52:00 AM EDT  

Blogger AR said....

Hi Tom,
Can you please advice me on the usage of bitmasks?
Where it will be advisable to use them?
My developers are suggesting using bitmasks for computation in the application layer and storing the bitmasks as "Number" data type in database.
Please share your thoughts on this subject.
Regds,
AR

Fri Mar 21, 07:14:00 AM EDT  

Blogger Thomas Kyte said....

@AR

99.999999999999999999999% of the time, this would be the wrongest thing on the planet to do.

How do your end users query this? How do people *know* into the future that this has been done - and what each bit means (I can see in the future rules like "if x < 10 then bit 13 represents A, else it represents B".

It would almost certainly be wrong - you use attributes to store attributes. Hiding more than one attribute in a single column is penny wise - pound foolish as they say.

Fri Mar 21, 08:14:00 AM EDT  

Blogger AR said....

Million Thanks for your response Tom!


SCENARIO A
--------------

Just to elaborate on this bitmask question
Let us imagine there are two entities

Person and Hobby

In the Hobby table there are 4 hobbies writing, singing ,dancing,playin golf
Each row will have a bitmask value which is a unique power of 2 (A=2^0, B=2^1, C=2^3, D=2^4).

Where BM_bin=Bimask binary
BM_dec=Bitmask decimal

Code Hobby BM_bin BM_dec
---------------------------------
h1 Writing 0001 1
h2 Singing 0010 2
h3 Dancing 0100 4
h4 Playing 1000 8
Golf
-----------------------------------
The PERSON table has a HOBBY_MASK value.
This is a logical OR of all of the Hobbies which apply for that person.

For example, if h1, h2 and h3 apply,
then the resultant binary value of the hobby mask is 0111 or 7 in decimal.

To check quickly if a specific hobby applies,
then this mask should be logically ANDed with the bitmask value from the HOBBY table,
a non zero result means it applies.

For example, if the mask is 7, then 7 & 8 = 0 which means that hobby h4(Playing Golf)
is not applicable for the person.

But if 7 & 1 = 1 which means hobby h1 (Writing) applies


SECNARIO B
------------

If we have to do this without bitmasks, then we would be typically going in for a Person_Hobby table
which will list the person and hobbies relationship


Can you please confirm which is better and if there is a better way of implementing this requirement.
Personally I do not want to complicate matters unless there is a solid reason!


Once again thanks for your feedback, it is much valued and appreciated.

Kind Regards,
AR

Fri Mar 21, 09:33:00 AM EDT  

Blogger Thomas Kyte said....

@AR

Just to elaborate on this bitmask question
Let us imagine there are two entities

Person and Hobby

In the Hobby table there are 4 hobbies writing, singing ,dancing,playin golf


I only needed to read that to confirm that in your case - bitmaps would be the worst design decision *ever*

Use a normal relational model, they *actually work* in real life.

Fri Mar 21, 10:55:00 AM EDT  

Blogger AR said....

Thanks a Million Tom! :)
Your advice and guidance in this matter is much appreciated.

Thank You.
Regds,
AR

Fri Mar 21, 01:40:00 PM EDT  

Anonymous Torsten said....

@Tom and AR:

Very funny this bitmap topic - a similar thing showed up a few days ago on DailyWTF...

Pretty Simple on Daily WTF

If you have some time take a look at it and spend some extra minutes to see some of the many comments!

This weird kind of design still seems to be sort of a popular "solution" for many developers... :-(

Cheers,
Torsten.

Sat Mar 22, 05:42:00 AM EDT  

Blogger lura said....

Hi,

I'm getting an error when I go to asktom.oracle.com


ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Error Unable to write activity log.
OK

Please let me know how to fix this.
Thank you

Tue Mar 17, 03:30:00 PM EDT  

Blogger Thomas Kyte said....

@lura

please tell me how to reproduce this?

Tue Mar 17, 04:26:00 PM EDT  

Blogger nh said....

I'm using windows xp and Internet explorer 7

The only think I see that page with the error .

Is it a Internet explorer issue or what?

Let me know if you need more details

Tue Mar 17, 07:29:00 PM EDT  

Blogger Thomas Kyte said....

@nh

just did that myself, no problem.

clear your cache maybe?

Tue Mar 17, 07:36:00 PM EDT  

Blogger nh said....

I've allready done that

Wed Mar 18, 04:27:00 PM EDT  

Blogger Thomas Kyte said....

out of curiosity - can you clear the cookies for asktom.oracle.com - and does this reproduce in other browsers for you?

Wed Mar 18, 04:30:00 PM EDT  

Blogger Dramus said....

"out of curiosity - can you clear the cookies for asktom.oracle.com - and does this reproduce in other browsers for you?"

I'm having the same problem.
Win Xp
IE 7 and 8.

I have removed the asktom.oracle.com cookie and I still get an ORA-06520 error whenever I try and access an asktom.oracle.com webpage.

Thu Jun 11, 11:09:00 AM EDT  

Blogger Dramus said....

"and does this reproduce in other browsers for you?"

Works with XP and Chrome.

Tried it with IE8 and Vista on a different PC and it works.

Thu Jun 11, 11:29:00 AM EDT  

Anonymous Alan Rosenthal said....

Hi,

I am having the same problem. I am running Win XP and IE 7.0. I am up-to-date on all my Microsoft patching. I have cleared the cache and the cookies.

Whenever I go to asktom.oracle.com I get the error:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Error Unable to write activity log.

I can run from different browsers on this workstation on on different computers in my office.

Any thoughts ?

Thanks,

Alan

Tue Jul 07, 08:16:00 PM EDT  

POST A COMMENT

<< Home