Very Computer

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

Quote:>Along the same vein, does anyone else know of *new* features of Oracle 7.1?

   - Doug Harris
     Database Administrator,
     System Development Division,
     Statistics Canada              ## Standard Disclaimer Applies ##
--

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?

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.

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

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

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

Quote:>Please explain. NULL and empty values are conceptually the same thing.

   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?

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

--

Quote:> Are you experiencing differences in behaviour between statements such
> as these two:

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

          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)

   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.

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

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

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