Re: New to Oracle

"Jon Waterhouse" <jonwaterhouse_at_mail.gov.nf.ca> wrote in message news:39206c8b.0204040941.4b631bd5_at_posting.google.com...
> damorgan <damorgan_at_exesolutions.com> wrote in message
news:<3CAB3224.B907552C_at_exesolutions.com>...
> > You have quite a few mistakes in what you wrote is causing you grief
which leads
> > me to believe that you haven't really studied Oracle much. For example
... the
> > inner join syntax is different in 8i but both are supported in 9i. CASE
in
> > PL/SQL can be used in any manner in 9i and in 8i it is supported in
PL/SQL
> > through native dynamic SQL. And there many are other examples I could
cite.
>
> I'm using 8i because that is all that is available on Novell Netware.

aaaaarggghhh don't you have at least an NT box? even one.

> So far as I understand, the INNER JOIN syntax is part of ANSI 92 and
> so I was surprised to find it unsupported in 2002 by Oracle. In 8i a
> SQL statement with CASE in it will not compile in a stored procedure.
> It's good to know that I can put the statement in as dynamic SQL, but
> I have to say that I regard it as an undesirable glitch of Oracle that
> selected components of standard SQL will not compile in stored
> procedures.

I agree that it is undesirable that the SQL and PL/SQL engines differ ihn the syntax they support - unfortunately they do. I disagree with your statements about standards cos - basically - there aren't any. CASE is not a part of the 'entry-level' sql92 spec which oracle fully supports but is a higher level which oracle doesn't. however as there is NO database that supports SQL92 in full the idea of using ansi compliant sql is just dead. use the sql syntax your db supports.

case in point

USE Northwind
Go
Select * from employees

is not ansi compliant. works in sqlserver though !

--
Niall Litchfield
Oracle DBA
Audit Commission UK













 Even if I did understand Oracle better, there would still


> be these unforseen inconsistencies that would bite you now and again.

> >

> > But what really troubles me is that you don't seem to have spent any

time


> > learning how to do things in Oracle ... it appears that you are just

trying to


> > do the same thing in another product which is, if not impossible, a

great way to


> > make an unscalable mess lacking in data integrity. Your comment about

temp


> > tables, for example. While temp tables are commonly used in TSQL there

is almost


> > never a rational reason to use them in Oracle. I would guess that 95% of

the


> > time I see them it is developers from a TSQL background who don't

understand


> > Oracle's multiversioning, transaction model, use of resources, locking,

etc.


> >

> You've already told me you don't like temp tables, and I guess I could

> look more closely at nested tables, index by tables etc. but for my

> purposes I don't think it makes a tap of difference which I use. In

> general I am trying to use set-based approaches to what I am doing

> rather than procedural approaches in cursor loops for neatness,

> portability and speed reasons.

>

> Basically I am setting up a data warehouse from old unnormalised data

> where data integrity rules were not properly enforced. Once set up

> there will be monthly additions of data in a batch. The temporary

> tables are used while I clean up the data before adding it to the real

> files.

>

> I'm currently the only user of the data (and one of two users of the

> database)so I don't have to worry about locking, concurrency issues

> etc. Within reason, speed of execution is not really an issue either.

>

> I'm quite open to suggestions of ways other than temp tables to do

> things, but at the moment it seems like a reasonable approach to

> continue using.

>

> > I strongly urge you to stop banging code and learn the underlying

architecture


> > before you continue down this road any further. In the end you will

blame Oracle


> > for what is clearly your own lack of learning the product.

> >

>

> Partly there's the whole driving a car if you're not able to fix it

> argument in here, and partly there is just the pragmatic need to get

> things done. So I will continue actually coding, and will just have to

> remain mystified why, for example, I can get the following to run and

> complete in a few minutes (there are 20 duplicates in a file of

> 225000):

>

> create table delids as (select c1.rowid delid from children

> c1,children c2 where c2.fil = c1.fil and c2.fname = c1.fname and

> c2.dob=c1.dob and c2.sex = c1.sex and c2.rowid > c1.rowid);

> delete from children where children.rowid in (select delid from

> delids);

> drop table delids;

>

> and the statement

>

> delete from children c1 where exists (select * from children c2 where

> c2.fil = c1.fil and c2.fname = c1.fname and c2.dob=c1.dob and c2.sex =

> c1.sex and c2.rowid > c1.rowid)

>

> is expected to take 48 hours to run.

>

> Jon