Re: Oracle #1? Then why are these still missing...

On Wed, 28 Jul 1999 14:28:26 GMT, tkyte_at_us.oracle.com (Thomas Kyte) wrote:

>I know you don't want "how to implement" but -- others might AND most of this
>list is in fact implemented today.
>
>>1. A "RANDOM" function.
>
>
>package dbms_random was added to the 8.0 database over a year ago. Or see
>
>http://www.deja.com/[ST_rn=ps]/getdoc.xp?AN=490388693&fmt=text
>
>for how to do it in 7.x or before.
>
>>2. A RANDOMIZE function to accompany "RANDOM" function.

>
>don't know what a 'randomize' function would do? do you mean:
I think he means to set the random seed. No function can generate truly random numbers, it relies on a seed for its computation. Randomize in other languages sets the seed to a new random(ish) value.

>
>>3. A native "DIV" function. Oracle has MOD an integer remainder
>>function but not an integer division function. DIV and MOD always
>>appear together, but not in Oracle.
>
>do you mean:
>
>trunc( x/y )

But why not DIV? Just add it to Oracle, why is it so difficult to understand that people associated MOD and DIV, not MOD and trunc(x/y)?

>
>
>>4. Bitwise boolean functions OR, XOR, AND, NOT (not the same as
>>boolean operators). Ever tried to write a decent encryption algorithm
>>in Oracle without these? Ever tried to mask an integer to see if a
>>bit is set? Just overload the existing boolean operators to allow
>>Bitwise boolean opeartions.
>
>-- with java stored procedures -- you not only get all of these BUT you also get
>the ability to *NOT HAVE TO* write your own encryption routine. Just grab one
>thats out there and put it in the database. I would say:
>
>>functionality. So let me suggest something, instead of poncing around
>>with new Oracle technologies like Java, why don't you sort out the
>
>is not the 100% correct approach maybe.... with java in the database -- alot of
>new opportunities have come up.

You work for Oracle, so you're bound to love Java. Not everyone will want to use Java, especially for licensing cost reasons, after all Oracle's Java Server is not free, Many live systems are written using PL/SQL server code. We simply want it in PL/SQL, no work around, or a Java Virtual Machine install just to use them.
>
>
>bitand() exists

So why the hell doesn't its complimentary functions? We want them put in natively, not functions that we have to write or code we have to compile onto every server schema.
>
>
>SQL> select bitand(55,1)+0 from dual;
>
>BITAND(55,1)+0
>--------------
> 1
>
>SQL> select bitand(55,2)+0 from dual;
>
>BITAND(55,2)+0
>--------------
> 2
>
>
>and you can always write your own bitor and other functions. for example:
>
>CREATE OR replace FUNCTION bitor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER
> AS
> l_x PLS_INTEGER DEFAULT x;
> l_y PLS_INTEGER DEFAULT y;
> l_r PLS_INTEGER DEFAULT 0;
> l_tmp PLS_INTEGER := 1;
>BEGIN
> FOR i IN 1 .. 32 LOOP
> IF ( bitand(l_x,l_tmp) = l_tmp OR bitand(l_y,l_tmp) = l_tmp )
> THEN
> l_r := l_r + l_tmp;
> END IF;
> l_tmp := l_tmp * 2;
> exit when ( l_tmp >= l_x AND l_tmp >= l_y );
> END LOOP;
>
> RETURN l_r;
>END;
>/
>
>
>
>>5. TO_HEX function. Are they stupid or what?
>
>Oracle8i, release 8.1:
>
>
>SQL> select to_char(1254,'xxxxxxxx' ) from dual;
>
>TO_CHAR(1
>---------
> 4e6

About time too.

>
>Before that, see http://govt.us.oracle.com/~tkyte/ for plsql that lets you:
>
>SQL> select to_hex( 1254 ) from dual;
>
>TO_HEX(1254)
>-------------
>4E6
>
>in any release of oracle 7.1 or later.
>
>
>>6. TO_BASE function which can handle bases up to 64.
>
>see http://govt.us.oracle.com/~tkyte/ for plsql that lets you do this in 7.1 and
>up. it is very easy.
>

>>8. Alter Table RENAME COLUMN .... How bloody obvious is this? How
>>many times do you need to be asked? OK you have constraints to
>>consider but they know this. We don't like using the data-dictionary
>>hack.
>
>don't do the dd hack. use a view. renaming a column has many ramifications.
>there are constraints on the column -- there are constraints referencing the
>column. its in the index. its part of a cluster. its the hash key. and so on
>and so on.
>
>views are designed just for this.

But views are an extra layer, especially if it's a fix for typo in a live system. An extra object to maintain. We simply want to change a name. I realise the complexities, I know there are many scripts to do it, so then why not add it natively?
>
>>9. Constraint fk_MyForeignKey References MyOtherTable (MyPKField) ON
>>UPDATE CASCASE. Is Oracle the only RDBMS that doesn't have a native
>>on update cascade?
>
>besides sybase, informix, db2 i guess?
Well I've wanted this.
Example : Imagine a user interface that is grid based, Oracle is the Server end. User sets up X and Y parameters. All X values are stored in their own table likewise with Y with PKs of their values. A child table of both X and Y - compound PK of both X&Y- are data at each X, Y grid coordinate. After much data entry, a user then realises the've goofed up on one of their Y parameters, but the grid data is correct and there is an awful lot of data. If user deletes that Y value they may lose hundreds of data items. So update cascade on that Y parent value would update the Y part of in child table. It is a needed requirement.

>Alot of people would argue that update cascade is *bad* (i would for example).
I would agree in most cases, but there is definitely a need as my example shows.
>I've supplied a packge to do update cascade but only because people upsized from
>access and due to a really bad design -- had to update cascade (to some 50 odd
>tables believe it or not) or recode everything. I offered to recode it instead
>-- it would have been a better system.

>>11. The ability to find out who is the locker when using pessimistic
>>locking (e.g. when "select 1 from MYTable where MyPrimaryKey = MyValue
>>for update nowait" fails because someone else has already
>>pessimistically locked it.)

Yes, when will Oracle have this?
>
>>12 The ability to move the cursor back to correct typos in SQL plus.
>>Why is it still not possible? Sometimes we haven't gone into another
>>editor and we accidentally make a typo at the end of a 200 character
>>line, so Oracle makes us retype the whole lot again. SQL Plus is and
>>always has been an extremely poor interface.
>
>It has built in editing commands -- just use some of them like this:
>
>SQL> select * from a big typo here
> 2 ;
>select * from a big typo here
> *
>ERROR at line 1:
>ORA-00933: SQL command not properly ended
>
>
>SQL> 1
> 1* select * from a big typo here
>SQL> c/a big typo here/dual
> 1* select * from dual
>SQL> /
>
>D
>-
>X
>
>SQL>
>
>
>I can change (correct) errors with or without an editor.
Come on dude, what a ridiculous set of operations just to be able to change something by simply moving a cursor with a cursor key. As far as tools go, SQL*Plus' lack of cursor movement is beyond poor to say the least. Your workaround is a demonstration of Oracle's mentality.

>The GUIs (OEM, sqlworksheet and so on) of course do not suffer from this.
Yes I use SQL Worksheet precisely for this reason. But it only comes with the Enterprise edition, right?

>
>>14. Binary operator XOR.
>
>as easy as bitor
>
>SQL> CREATE OR replace FUNCTION bitxor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER
> 2 AS
> 3 l_x PLS_INTEGER DEFAULT x;
> 4 l_y PLS_INTEGER DEFAULT y;
> 5 l_r PLS_INTEGER DEFAULT 0;
> 6 l_tmp PLS_INTEGER := 1;
> 7 BEGIN
> 8 FOR i IN 1 .. 32 LOOP
> 9 IF ( bitand(l_x,l_tmp) <> bitand(l_y,l_tmp) )
> 10 THEN
> 11 l_r := l_r + l_tmp;
> 12 END IF;
> 13 l_tmp := l_tmp * 2;
> 14 exit when ( l_tmp >= l_x AND l_tmp >= l_y );
> 15 END LOOP;
> 16
> 16 RETURN l_r;
> 17 END;
> 18 /
>
>Function created.

A work around again. If it is so easy, why not just add it and be done. I really can't understand you Oracle guys.

>I actually find it pretty good that given the product from 7.1 on -- we can
>implement fully 11 out of 17 of your suggestions. and if you consider Oracle8i
>-- most all of your suggestions are there.
But you have found out ways of solving problems that shouldn't be there. However much I like Oracle, they are so much like Microsoft in their attitude. Many work arounds for much needed basic internal functionality.

Tom Kingsley Received on Wed Jul 28 1999 - 19:38:13 CDT