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

From: Paul <aspscott_at_tcp.co.uk>
Date: 1999/07/28
Message-ID: <37a22186.5774353_at_news.cix.co.uk>


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

Thanks for your feedback. I have written many solutions already (which turned out to be similar to your suggestions), but thanks anyway. Still think a lot of them should be in Oracle anyway, don't you?

Paul

>>When are Oracle going to listen to their users and start to implement
>>fundamental requirements which already exist in other RDBMSs. I am
>>sick and tired with Oracle Support saying, "it's a bug, and there are
>>no known fixes", "it might be in the next version, but then again it
>>might not", "you want me to put in an enhancement request..might take
>>3-5 years though", "it has been asked for before, but we have no plans
>>to do it", "you'll have to write a work around", "speak to your
>>account manager, he might be able to get it put in, if you are a
>>really big corporate customer"
>>
>>Listen Mr Ellison and your crew, Oracle is still missing basic
>>functionality. So let me suggest something, instead of poncing around
>>with new Oracle technologies like Java, why don't you sort out the
>>basics first.
>>
>>Oh and before you read this list and reply: "But this is easy to
>>implement" or "I've written something to do this anyway" let me
>>explain....so have I had to write / find workarounds for all these.
>>But at the end of the day it shouldn't need to happen, it should have
>>been in Oracle years ago. They're fundamental.
>>
>
>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:
>
>update T set x = some_random_function
>
>?
>
>>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 )
>
>
>>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.
>
>
>bitand() exists
>
>
>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
>
>
>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.
>
>>7. A variant of REPLACE that only replaces the first instance of
>>search_string found. Come on Oracle simply overload it and add another
>>parameter Max_Replaces (Max_Replaces > 0).
>
>I've never had a case where i wanted to do this, however, it is easy enough to
>code ourselves.
>
>
>SQL> create or replace function myreplace( p_string in varchar2,
> 2 p_search in varchar2,
> 3 p_replace in varchar2 ) return varchar2
> 4 as
> 5 l_n number default nvl(instr( p_string, p_search ),0);
> 6 begin
> 7 if ( l_n > 0 )
> 8 then
> 9 return substr(p_string,1,l_n-1) || p_replace ||
> 10 substr(p_string,l_n+length(p_search) );
> 11 else
> 12 return p_string;
> 13 end if;
> 14 end;
> 15 /
>
>Function created.
>SQL> select myreplace( 'How Now Brown Cow, How Now Brown Cow', 'Brown', 'Blue' )
>2 from dual
> 3 /
>
>X
>--------------------------------------------------
>How Now Blue Cow, How Now Brown Cow
>
>
>
>>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.
>
>>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?
>
>One feature that is added in Oracle8i, release 8.1 is and ON DELETE SET NULL.
>
>For update cascade (for 90% of the cases) see http://govt.us.oracle.com/~tkyte/.
>It shows how to do this (and does it for you).
>
>
>Alot of people would argue that update cascade is *bad* (i would for example).
>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.
>
>>10. Constraint fk_MyForeignKey References MyOtherTable (MyPKField) ON
>>DELETE SET NULL. Another Oracle gotcha. It is basic referential
>>principles. Every single DB design case tool has this but oh no not
>>Oracle, it is far too obvious for them.
>
>Oracle8i, release 8.1
>
>>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.)
 

>>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.
>
>The GUIs (OEM, sqlworksheet and so on) of course do not suffer from this.
>
>>13 Polymorphism in Objects. What other object modeling language
>>doesn't have Polymorphism? derrrrr
 

>>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.
>
>SQL>
>SQL> set linesize 10
>SQL> select lpad( to_bin( 124 ), 10, '0' ),
> 2 lpad( to_bin( 246 ), 10, '0' ),
> 3 lpad( to_bin( bitxor(124,246) ), 10, '0' )
> 4 from dual
> 5 /
>
>LPAD(TO_BI
>----------
>LPAD(TO_BI
>----------
>LPAD(TO_BI
>----------
>0001111100
>0011110110
>0010001010
>
>
>>15. Give SQL Loader the ability to skip columns in CSV format. Not
>>all fields in a CSV file may be needed Oracle, so come on.
>
>Oracle8i, release 8.1 does this. For before that see
>
>http://www.deja.com/[ST_rn=ps]/getdoc.xp?AN=483755757&fmt=text
>
>for how to do it.
>
>>16 Allow Oracle Debugger Probe to watch of implicit loop variables.
>>17. Allow "OR REPLACE" syntax on all create object commands.
>>etc. etc. etc.
>>
>>I could continue, but I'm sure others will add to this.
>>
>
>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.
>
>
>>Kind regards
>>Paul Scott
>>aspscott_at_tcp.co.uk
>>^^ remove 'as' anti-spam prefix to E-mail
Received on Wed Jul 28 1999 - 00:00:00 CEST

Original text of this message