Oracle 7.1 Enhancements

Oracle 7.1 Enhancements

Post by John Denn » Thu, 09 Jun 1994 00:56:53



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

 
 
 

Oracle 7.1 Enhancements

Post by Doug Harr » Fri, 10 Jun 1994 08:01:04



Quote:>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 ##
--

 
 
 

Oracle 7.1 Enhancements

Post by John Denn » Sat, 11 Jun 1994 21:23:53


SUMMARY:

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

----------

Date: Wed, 8 Jun 94 06:40:29 EDT


Newsgroups: comp.databases.oracle
Subject: Re: Oracle 7.1 Enhancements

Original Question:

Quote:>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.

-=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}

-=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}>]:=--=:[<{()}

 
 
 

Oracle 7.1 Enhancements

Post by Michael Sallwass » Wed, 15 Jun 1994 06:35:08




>   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.
============================================================================

 
 
 

Oracle 7.1 Enhancements

Post by Doug Harr » Thu, 16 Jun 1994 06:34:36



Quote:>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.

Quote:>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)!

--

 
 
 

Oracle 7.1 Enhancements

Post by Wolfgang Roeckele » Fri, 17 Jun 1994 22:35:42





(Doug Harris) writes:


> >   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).

Quote:> 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

Burgplatz 2                     (NeXTmail ok)
D-56179 Vallendar
Germany
 
 
 

Oracle 7.1 Enhancements

Post by Alan Be » Sat, 18 Jun 1994 02:08:42


|>    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
L*Federal Systems
Owego, NY

 
 
 

Oracle 7.1 Enhancements

Post by Hal Kinn » Sat, 18 Jun 1994 02:07:41


Subject: << P/A Oracle SQL*Forms Reportwriter >> Agent SFSYS

          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     ~~~~~~~~~~~~~~~~~~~

110 Sutter St. Suite 701     |                           Voice: 415.982.3500
San Francisco, Ca. 94104     |          FAX: 415.982.6013 (high res. please)

 
 
 

Oracle 7.1 Enhancements

Post by Mark Mill » Sun, 19 Jun 1994 11:03:23


In my opinion the SQL treatment of NULLS has a few serious flaws.

   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.

 
 
 

Oracle 7.1 Enhancements

Post by Ray Ont » Sun, 19 Jun 1994 22:34:31




>Subject: Re: Oracle 7.1 Enhancements
>Date: 17 Jun 1994 20:03:23 -0600
>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.
-----------------------------------------------------------------------


 
 
 

Oracle 7.1 Enhancements

Post by Hans Forbri » Wed, 22 Jun 1994 01:29:29





> >Subject: Re: Oracle 7.1 Enhancements
> >Date: 17 Jun 1994 20:03:23 -0600
> >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.
> -----------------------------------------------------------------------



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

 
 
 

Oracle 7.1 Enhancements

Post by Michael Sallwass » Thu, 23 Jun 1994 07:23:17



>In my opinion the SQL treatment of NULLS has a few serious flaws.

>   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.

------------------------------------------------------

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'
--
============================================================================
Michael Sallwasser  | Down one path is utter dispair and hopelessness. Down
Northrop Grumman    | the other is total destruction. Let us choose wisely.
============================================================================

 
 
 

Oracle 7.1 Enhancements

Post by Mark Mill » Fri, 24 Jun 1994 09:46:37



>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.

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';

 
 
 

1. image enhancement and ORACLE 7.0

I have got a configuration that consists of a DG AViion RAID 5 box running
SVR4. This box has got ORACLE 7.0 that stores images (people faces) as BLOBS
, as well as textual data about people.  ORACLE FORMS 4.0 handles images.
does any one know of commercial software that one can incorporate into this
ORACLE application, so that image enhancement can be done.  This software would
have built-in utilities to enable a user (not a programmer) do enhancement.
(by enhancement, I guess I mean, making images sharper).  

If you know of such a software, (or for that matter, you know that there is
no such software) could you please send me an e-mail at the following address.


Any help in this regard is greately appreciated.

-hossein

2. Sounds

3. Integrated Switching Regulators

4. Request for Proposals: Oracle-based Toxic Air Emissions Inventory Enhancements

5. CSWS & PHP

6. ORACLE Applications MULTI-ORG enhancement

7. Controlling the 3.5 Drive hardware (IIGS only) (2 of 2)

8. How analagous is Personal Oracle 7.1 to the other Oracles

9. Oracle 7.1/7.2+NT3.51+Cheyenne DBagent for Oracle Server ????????????

10. Connection Oracle 7.1 with Oracle ODBC Driver SQO32_72.DDL

11. Oracle 7.0 -> Oracle 7.1

12. Call for Enhancements