Wednesday, February 20

How to delete an object with a special character in Oracle

There are some things in Oracle that are possible but shouldn't be possible.
One thing I love to hate is the fact that you can create tables with almost any name, just as long as you double quote it.
I.e.:
SQL> create table "My Fruit Table" (id number);

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
My Fruit Table TABLE
Horrible! And what's even more horrible is that people actually do this.

Now to the problem, a user created a table with a special character in the name. Not even sure what character but I need a way to drop it. PL/SQL to the rescue.
Example:
SQL> set serveroutput on
-- Lets create a dummy table with a bogus character in the name
SQL> declare
a varchar2(500);
begin
a:='create table "abctest'||chr(150)||'" (id number)';
execute immediate a;
end;
/

PL/SQL procedure successfully completed.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
abctest? TABLE

SQL> desc "abctest?";
ERROR:
ORA-04043: object "abctest?" does not exist

-- And a bit of PL/SQL to drop it
-- change the abctest% string to something matching your single table.
SQL> declare
a varchar2(500);
tname varchar2(50);
begin
a:='select table_name from user_tables where table_name like ''abctest%''';
execute immediate a into tname;
dbms_output.put_line('Table name is: '||tname);
execute immediate 'drop table "'||tname||'"';
end;
/

Table name is: abctest?

PL/SQL procedure successfully completed.

SQL> select * from tab;

no rows selected
Handy.

Sunday, February 3

Buying marketshare

Ok, I'm way waay late on commenting on the two most recent IT company takeovers .

Sun buys MySQL
Tricky one, I have to honestly say I have a hard time seeing where MySQL fits in to Sun's portfolio.
Sun is one of Oracle biggest partners (the biggest after Dell?) and they are one of the biggest backers of PostgreSQL, then even employee a few of head lead Postgres developers.
Sun has got two options with MySQL, they can either invest a lot of RnD in MySQL and actually turn it in to a really good product. MySQL is without a doubt a product with incredible potential, but is today pretty much a over-sized way to store CSV index files (bit harsh perhaps but hey).
Or they can ride out the profits by selling LAMP-stack boxes and let MySQL sustain itself, I doubt they can make up for the billion dollars they payed for it though.
Oh, and what will Sun make of the (rather evil) closed source MySQL Enterprise Monitor administration product from MySQL. Sun having new closed source software? Errr!

Some advice for sun,
1) Add real constraint support. This is a show stopper for many, and the InnoDB approach isn't good enough. MySQL needs native solid from the ground up constraint support, independent of storage engine, version bla bla. If people don't want the "performance loss" of constraints, don't add the constraints!
2) Sort out proper I/O management, tablespaces, clean partitioning all that stuff. MySQL has improved a lot on this point over the last 12 months. But more work is needed, especially if they are planning on selling MySQL powered Thumpers.
3) 100% solid transactional support. MVCC would be fun. I've seen a few to many transactional problems in MySQL. Some sort of logging/undo/redo system is needed.

On top of that I wouldn't mind a clean hot-backup tool, requires proper transactional support (with something like SCN's etc), 3rd party scripting language support (PgPerl anyone?).

That's about 250.000 RnD hours, go get busy!

Oracle buys BEA
This is most likely very good news. I'm a big fan of the Oracle database (duh) and a fan of Weblogic Application Server.
What I'm not a big fan of is Oracle Application Server (oc4j).
I hope Oracle realize that Weblogic is the superior product and works with Weblogic as the front runner and port oc4j features to Weblogic and not the other way around. I doubt that will be the case though. If not, I hope they don't kill off Weblogic any time soon.

Hmm, that's all for now.