| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle #1? Then why are these still missing...
>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.
>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;
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;
>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)
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;
Function created.
SQL> select myreplace( 'How Now Brown Cow, How Now Brown Cow', 'Brown', 'Blue' )
2 from dual
3 /
X
>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
*
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;
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
LPAD(TO_BI
>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
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jul 28 1999 - 09:28:26 CDT
 |
 |