Re: Oracle NULL vs '' revisited

Tony Rogerson wrote:
> Great, I'll send stuff over thru the next couple of weeks.
>
> Start with these two...
>
> "Translate character string using character set"
>
> We use CAST which I believe is ANSI standard as well; Oracle uses TREAT
> which I believe isn't ANSI standard.
>
> CAST( <expression> AS <type> )

What? <g>

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions016.htm#SQLRF00613 http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions198.htm#SQLRF06148

Please look at TREAT (second link) and explain what you intend. I am confused by what appears to be a misunderstanding of what TREAT is and does.

Now look at this:

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

CAST in Oracle and CAST in TransactSQL are different in what way? I am missing your point.

> "Table Level Triggers"
> "statement level"
>
> I don't believe Oracle has proper statement level triggers, by that I
> mean the new and old values for ALL rows modified by the statement are
> available.

You are correct and I would really like to see this dealt with in 11gR2.

But the point of my web page is not that each and every statement, left and right, has identical functionality. Were that the intent you couldn't equate anything between the products. The point of the page is to give students transitioning from one to the other an idea of what to look up. Anyone expecting identical functionality with anything more complex than SELECT * FROM t is going to be disappointed.

BTW for anyone not following this thread the relevant link is: http://www.psoug.org/reference/sqlserver.html

> Row level triggers, we just use a cursor against the "inserted" and/or
> "deleted" system materialised views.

Every product has workarounds. That isn't the point of my page. I teach at the University of Washington just two miles from the main Microsoft campus and many of my students, most actually, are experts on SQL Server or Sybase. The page is there to help them find rough equivalences. Nothing substitutes for reading the docs.

> INSTEAD OF is a before trigger in SQL Server.

Your point being? <g> If you look at my page I indicate that both products have instead-of triggers. I don't offer any information other than that. It is up to those interested to see in what ways they are similar and in what ways they differ.

Be thankful I haven't upgraded the page from 10.2 to 11.1. When I do that the disparities with SS2005 grows larger still.

Consider this for example. In a US medical environment with patient data HIPAA, federal law, requires that SELECT statements be audited. We have been able to do that in Oracle for years. In SQL Server it is still impossible. Something that won't be fixed until at least 2009. If I tried to put together a page of all differences I would need months just to catalog the differences.

> I'll send the rest via email once you have updated the current document
> to reflect the above.

I really do appreciate it. But please include links. I need to be able to read the docs to verify statements. The one place I know I am weak is on OLAP functionality so if you know any of that please include it. Thanks.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org