Oracle 7.1 Enhancements
John Dennis
I heard on the Net that Oracle 7.1 will support a form of "Dynamic SQL"
within PL/SQL. This means passing table names and where clauses to
stored procedures to me -- if this is true. Can any verify or elaborate
on this?
Along the same vein, does anyone else know of *new* features of Oracle 7.1?
Thanks,
John Dennis
Atlanta, GA
Doug Harris
In a previous article, jde...@netcom.com (John Dennis) says:
>Along the same vein, does anyone else know of *new* features of Oracle 7.1?
I would also like to know if 7.1 can distinguish between empty
CHAR/VARCHAR values (i.e. '') and NULL.
- Doug Harris
Database Administrator,
System Development Division,
Statistics Canada ## Standard Disclaimer Applies ##
--
John Dennis
The following reply summed up the responses I have gotten. I hope the
"replier" doesn't mind me posting his input.
John Dennis
Atlanta, GA
----------
From dnat...@us.oracle.com Fri Jun 10 05:16:47 1994
Date: Wed, 8 Jun 94 06:40:29 EDT
From: David Natelson <dnat...@us.oracle.com>
To: jde...@netcom.com
Newsgroups: comp.databases.oracle
Subject: Re: Oracle 7.1 Enhancements
Original Question:
>I heard on the Net that Oracle 7.1 will support a form of "Dynamic SQL"
>within PL/SQL. This means passing table names and where clauses to
>stored procedures to me -- if this is true. Can any verify or elaborate
>on this?
New Features
1. Dynamic SQL in PL/SQL allows any string to be declared as a cursor, parsed,
bind variables set, fetch, execute, open, close, just like levels 1-4
SQL in the Pro*C world.
2. Parallel load, index, query, recovery
3. Functions (stored procs) can be used in the select line -> user defined
additions to SQL functions, e.g. select my_average(sal) from emp
4. read-only tablespaces (so you can put them on CD or never back them up).
5. Replication of group of tables as transaction sets (so ref. intergrity is
maintained).
6. Server Manager (Motif or Windows based SQL*DBA)
7. Network Manager (Motif or Windows based SQL*Net multi-database config. tool)
8. Support for password encription between SQL*Net 2.1 client and server
9. Support for complete encryption of all data between client and server
uses a third party SW package (I believe RSA): this is extra cost.
10. Support for SQL*Net DCE (this is beta) Uses DCE RPCs between client
and server, plus uses DCE security for OPS$ users.
-=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}
David Natelson Oracle Corporation dnat...@oracle.com
-=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}
Michael Sallwasser
>
>
>In a previous article, jde...@netcom.com (John Dennis) says:
>>
> I would also like to know if 7.1 can distinguish between empty
>CHAR/VARCHAR values (i.e. '') and NULL.
>
Please explain. NULL and empty values are conceptually the same thing.
What exactly is the question here?
Are you experiencing differences in behaviour between statements such
as these two:
select a||null from b;
and
select a||'' from b;
I'm confused. ):-(
--
============================================================================
Michael Sallwasser | Down one path is utter dispair and hopelessness. Down
Northrop Grumman | the other is total destruction. Let us choose wisely.
============================================================================
Doug Harris
In a previous article, msal...@world.nad.northrop.com (Michael Sallwasser) says:
>Please explain. NULL and empty values are conceptually the same thing.
Survey sez....BZZZZZZT.
From the early days of RDBMSdom, NULL has been a special value used to
represent a MISSING value. Consider the following example...
First Name Middle Name Last Name
---------- ----------- -----------------
'John' '' 'Public'
'Scott' NULL 'Tiger'
In the first row, I represent the fact that John has no middle name, and
that I KNOW that John has no middle name.
In the second row I represent the fact that Scott's middle name is
MISSING or UNKNOWN. He may have a middle name, he may not. NULL
is used to represent 'I Don't Know'.
Imagine for example, the problems caused if Oracle considered
0 and NULL to be equivalent for NUMBER values.
>What exactly is the question here?
Many consider this to be a trivial point, but there are certain
types of data processing systems (Statistical Systems for one) in
which MISSING or UNKNOWN data must be specially handled.
Most of the other RDBMSs distinguish between '' and NULL, and this
is in fact a requirement of the ANSI SQL 92 standard. Since Oracle
dates back to well before this standard, they didn't think it was a big
deal originally to distinguish between the two and we've been stuck
with this quirk since.
In order to answer my original question by the way, I have been told
that indeed Oracle 7.1 will support the SQL92 standard regarding
empty strings and NULL.
Hope this clears up any confusion about what I was talking about.
Now if I can convince Oracle to up that 254 column/table limit
(another pain for statistical data processing)!
--
Wolfgang Roeckelein
> In article <Cr3p...@freenet.carleton.ca> ah...@FreeNet.Carleton.CA
(Doug Harris) writes:
> >
> >
> >In a previous article, jde...@netcom.com (John Dennis) says:
> >>
> > I would also like to know if 7.1 can distinguish between empty
> >CHAR/VARCHAR values (i.e. '') and NULL.
> >
>
> Please explain. NULL and empty values are conceptually the same thing.
No! Read Codd, who defined what key points a relational database should
have and one thing that he clearly states is that NULL has to be different
from anything else. NULL denotes a missing value (unknown), whereas a
empty string is a string. E.g. a telephone number: NULL means unknown (no
information), empty means the guy has none (information).
> Are you experiencing differences in behaviour between statements such
> as these two:
Of course!
--
Dipl.-Wirtsch.-Inf. Voice: +49 261 6509 173
Wolfgang Roeckelein Fax: +49 261 6509 179
WHU Koblenz E-Mail: roeck...@wi.whu-koblenz.de
Burgplatz 2 (NeXTmail ok)
D-56179 Vallendar
Germany
Alan Beal
|> In order to answer my original question by the way, I have been told
|> that indeed Oracle 7.1 will support the SQL92 standard regarding
|> empty strings and NULL.
Excellent writeup! Chris Date has even suggested that there should be more than
one type of NULL. The downside of NULLs is the added complexity of the
logic handling. Didn't someone write a paper that suggested that 3-valued
logic adds considerable complexity and that no one has implemented correctly
(or at least not in the same manner)?
We have been bitten by the '' (null string) and NULL problem. The major problem
is that some people think they are the same since Oracle implements it that
way.
--
Alan Beal
Loral Federal Systems
Owego, NY
(607) 751-2026 be...@lfs.loral.com
Hal Kinney
Skills : Forms 3.0 Reportwriter
Minimum Industry : 1 years
Experience (College Projects do "not" qualify)
Location : East Bay (San Francisco Area)
Start Date : ASAP
Pay Rate : Commensurate with experience
Length : Permanent
Student Visa ok ? : No No No No
H1 Visa ok ? : No
COMMENTS: Must be strong with SQL*FORMS and Reportwriter.
Exciting company with a significant edge on the market.
CASE a strong plus. Relocation assistance available to the right
candidate.
Client will "not" sponsor nonresidents.
Recent college grads need not apply.
Must have at least 1 year focused Oracle tools experience in
industry.
Send resume and/or lets talk.
--
~~~~~~~~~~ 20 years of Service and still growing ~~~~~~~~~~~~~~~~~~~
San Francisco Systems, Inc. | Contact: Hal Kinney hal...@netcom.com
110 Sutter St. Suite 701 | Voice: 415.982.3500
San Francisco, Ca. 94104 | FAX: 415.982.6013 (high res. please)
Mark Miller
NULL should be treated like an empty set in set algebra.
e.g. ( column <> B ) should be true if column is NULL.
Otherwise NOT ( column <> B ) does not equal ( column = B ).
NULL should behave like a value that is equal to nothing and
unequal to everything.
I welcome your comments.
Ray Ontko
>From: mem...@u.cc.utah.edu (Mark Miller)
>Subject: Re: Oracle 7.1 Enhancements
>In my opinion the SQL treatment of NULLS has a few serious flaws.
IMHO2. Several recent issues of Database Programming & Design have
interesting articles and letters on this topic. Chris Date has been
writing on this topic for years. I'm sure you'll find the discussion
interesting and helpful.
-----------------------------------------------------------------------
Ray Ontko ra...@ontko.com "Ask me about Database Illustrator(tm)"
Ray Ontko & Co in...@ontko.com ftp cscns.com:/pub/ontko
Hans Forbrich
One of the hardest things I faced when jumping from DB2 to Oracle
(back in the Oracle V4 days) was Oracle's mistreatment of NULLS.
Virtually every other area in Oracle was/is a pleasant improvement
over DB2 for my style of development, except NULLS.
Ray ... ditto your coments, esp. on Database Programming & Design
& Date's articles.
/Hans
Michael Sallwasser
------------------------------------------------------
I see your point; however, let's look at the example of employees receiving
a commission (This comes from Oracle SCOTT/TIGER examples)
Let say we have a table as such:
EMPLOYEES
employee_nb number(5)
week-ending date
salary_am number(8,2)
commission_am number(8,2)
(I will concede my table is not normalized, but I think the point is
still valid)
Let us assume that if you are not in sales you do not have a commission.
Your commission is not zero, but rather null.
Let us say we want a list of employees whose commission for the week
is over $400, and another list of employees whose commision is less
than or equal to $400. Since commission is an illogical or not applicable
concept for non-sales people, people who aren't sales people should logically
not appear in either list. And, as I understand it, by the rules of ANSI SQL,
they would not.
----------------------------
To address your original dilemma:
One could always use a SQL clause like
where not nvl(column,'A') <> 'B'
which would be that same as
where nvl(column,'A') = 'B'
Mark Miller
>
>Let us say we want a list of employees whose commission for the week
>is over $400, and another list of employees whose commision is less
>than or equal to $400. Since commission is an illogical or not applicable
>concept for non-sales people, people who aren't sales people should logically
>not appear in either list. And, as I understand it, by the rules of ANSI SQL,
>they would not.
>
time with.
Select * from employee where commission <> 400.00
should return all the employees that do not have a commission of $400.00,
NOT all employees who have a commission that is not 400.00 as defined
by ANSI SQL.
A better example: You want to query all the employees that may live out
of Nebraska.
You have to use
Select * from employee where nvl(state,'ZZ') <> 'NB';
instead of
Select * from employee where state <> 'NB';
Michael Nolan
: A better example: You want to query all the employees that may live out
: of Nebraska.
: You have to use
: Select * from employee where nvl(state,'ZZ') <> 'NB';
: instead of
: Select * from employee where state <> 'NB';
Unfortunately, this example won't find employees that don't live in
Nebraska, for which the approved abbreviation is 'NE', but it will find
employees that don't live in New Brunwick, Canada.
More to the point, while it may be argued that there is a major
difference from a mathematical perspective between a NULL and a zero-length
field, there are serious implementation issues surrounding how one
specifies the difference between the two in a product like Oracle Forms.
For example, you're entering the state information described above in
a new record. If you skip that field (by pressing ENTER) does that
create a NULL or a zero-length value. If I delete 'NB' from the state field,
do I get a NULL or a zero-length value?
---
Michael Nolan, Sysop for the DBMS RoundTable on GEnie
no...@notes.tssi.com, db...@genie.geis.com
Lincoln, Nebraska
Carl Gohringer
: I agree with the rules of ANSI SQL for > and <. It is <> that I have a hard
: time with.
: Select * from employee where commission <> 400.00
: should return all the employees that do not have a commission of $400.00,
: NOT all employees who have a commission that is not 400.00 as defined
: by ANSI SQL.
:
: A better example: You want to query all the employees that may live out
: of Nebraska.
:
: You have to use
: Select * from employee where nvl(state,'ZZ') <> 'NB';
: instead of
: Select * from employee where state <> 'NB';
If employees is a large table, and state is indexed, the
above query can give VERY POOR performance, as the nvl around state
will disable the index, and the above will perform a full tablescan.
Better, would proboubly be:
select * from employee where state <> 'NB' or state is NULL;
--
regards,
Carl
+-----------------------------------------------------------------------------+
Carl Gohringer,
Oracle UK, The Oracle Centre, The Ring, Bracknell, Berkshire, England, RG121BW
Internet : cgoh...@uk.oracle.com
+-----------------------------------------------------------------------------+
Heikki Rissanen
: No! Read Codd, who defined what key points a relational database should
: from anything else. NULL denotes a missing value (unknown), whereas a
: empty string is a string. E.g. a telephone number: NULL means unknown (no
: information), empty means the guy has none (information).
I feel that this null business is an example where relational mathematics
goes to far from the practical life. Since mathematics tell that there are
different meanings of NULL, and that nulls may or may not be eqal to each
other, our rdbms vendors take the easiest way and do nothing. And we pay
the bill.
Suggestion: a null is a null is a null - whatever mathematics say.
First thing to do would be to give us some way to define that, in OUR
installation, it is preferrable to considers NULL as something different
from anything else. A simple INIT.ORA parameter could be fine.
This would make it possible to include null values in index trees and
compare null with other values, for example. I don't care so much where
nulls are bigger or smaller that others.
Regards,
Hessu
--
---------------------------------------------------------------------
Heikki Rissanen, United Papermills, Rauma, Finland
Email: heikki....@ra.upm.yhtyneet.mailnet.fi
---------------------------------------------------------------------
Martin Farber
Scary what's out there designing systems - isn't it??? (Rhetorical ;-})
---
<<MFF>>
-------------------------------------
"Twenty-Five years ago it meant something to be Crazy!"
-- Charles Manson