February 13, 2008 at 10:31 pm
Although a lot of this information is several years old, the "basics" still apply.
No, they really don't.
In SQL Server, the UPDATE statement has a FROM clause where you can do joins. In Oracle, you do not. Instead, you must do correlated subqueries in both the Select List and the Where Clause... unless you're lucky enough to have a version of Oracle that has the "new" MERGE command... that makes life a whole lot easier even if you just need to do updates.
First time a developer says he needs a result set from a stored procedure, you're gonna flip. In SQL Server, stored procedures can return result set's directly to a GUI. In Oracle, you need to write a package that has both an interface "spec" and a body and it has to use a "Global Cursor" data type.
First time you try to return something directly to the screen to troubleshoot, you're gonna have a big disappointment... if it's between a BEGIN and END;, forget about returning anything directly to the screen. You need to use a "DBMS_OUTPUT" command of some sort.
Ever try to check a formula in SQL server by doing something like a simple SELECT GETDATE()? In Oracle, it doesn't work... EVERY select requires a FROM clause... So, to do the same thing in Oracle, you have to do a SELECT SYSDATE FROM DUAL;
... yeah... you're gonna get to love the DUAL table...
Wanna write a set based trigger in Oracle... forget it... There's no INSERTED or DELETED tables. There are INSERTED and DELETED variables for each column... you have to write RBAR on steroids and then tell the trigger FOR EACH ROW. Oh yeah, almost forgot... if you need to write any big complicated triggers to drive audit tables, make sure you keep it below 32K because that how big a trigger you can make... period!
You're gonna love that fact that object names can only be 30 characters long and that any script that generates the CREATE statement for an object, converts all object and column names to upper case!
Varchar is called VARCHAR2 and it only goes up to 4000. NVARCHAR2 only goes up to 2000. If you need more than that, get ready to be greased by CLOBs which aren't much better than the TEXT datatype in SQL Server.
Oh, you'll love the error messages... I think there might be 5 that actually show up... most of them are about errors have to do with the bloddy semi colons required but only in certain spots. Doesn't really tell you where the error is though.
Learn how to use SELECT INTO to populate variables. Forget about local temp tables, they don't exist. Only global temp tables exist.
Practice typing ALTER SESSION SET CURRENT_SCHEMA = xxx; Only way to keep from killing yourself with a 2 part naming convention that doesn't mean much, especially in TOAD. You'll create stored procs and packages that work just fine only to find that you can't find them... TOAD lies about which schema it's in.
The default for sting literals (things in single quotes) is to be case sensitive.
The default for transactions is that you must to a commit even though you never declared and explit transaction. The exception to the rule is if you screw up and do some DDL in the middle of your DML, the DDL automatically performs a commit... even if you didn't want it to... and it doesn't tell you it did it.
Wait until you try to find equivalents for things in Oracle... naming convention is TOTALLY different. For example, what would you look for to find the equivalent for CTE's... oh, I'm sure you just spit "Sub-Query Refactoring" right out. Or, maybe you are smart enough to lookup WITH only to find 10,000 pages of info that have nothing to do with what you're looking for. Heh... for that matter, lookup UPDATE and see if you can find a single example that does the equivelent of a join between two tables for the UPDATE. You will not find a single example. No examples of using the correlated sub-queries to do it with, either. You might find something under correlated sub-queries, but only as a Select... nothing for the all important UPDATE.
Just wait until you try to build an "autonumbering column" like an IDENTITY column... they're called "sequences' in Oracle... yeah... you'll learn to pretty much hate those damned things.
Heh... Wait until you try to put a documentation header on a view... you'll love that!
There's about 10,000 other "basic" things that are totally different in Oracle than they are in SQL Server. SQL is NOT SQL... period.
I'm not trying to discourage you, but if you think for a minute that it's going to be easy for you and your guys to make the switch, then you're in for a very rude awakening. It's a major paradigm shift... contrary to what some have said, if you were a good set based programmer in SQL Server, you're going to be dazed and confused by Oracle because they nothing alike when it comes to good solid set based code.
And, as previously mentioned, the GUI's for Oracle suck. You'll also find out that Toad sucks pretty much, as well. And, if you write code and test it in Toad, and you promote it to production using SQL-Plus, there's a pretty good chance it won't work. You MUST make all code work with SQL-Plus or scheduled procs just might not work because of it.
It's gonna take some serious study on your part if you want to be good at it. Sure, you can quickly learn enough to be a hack... and watch your databases die just as quickly. If your company values your Oracle data... they need to send you to DBA school, right NOW, or rent an true experienced Oracle DBA for 6 weeks to get you and your team up to speed quickly!
And anyone who says "Well, the basics are the same", is probably right... they both live on a computer, they both store data, and that's about it.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2008 at 2:00 am
Hey Jeff!
Sounds like you're a good candidate to write "ORACLE for SQL Server Developers." 🙂
Seriously, would you consider writing an article for SQLServerCentral.com about some of the stuff you mentioned?? - for example, the need to create a cursor to output a strored procedure resultset to a GUI - things that would show the paradigm changes between SQL Server & Oracle.
"...need to send you to DBA school..." - I always thought of that as "Oracle Monastery" 😉
Best regards,
SteveR
February 14, 2008 at 5:56 am
Steve Rosenbach (2/14/2008)
Hey Jeff!Sounds like you're a good candidate to write "ORACLE for SQL Server Developers." 🙂
Seriously, would you consider writing an article for SQLServerCentral.com about some of the stuff you mentioned?? - for example, the need to create a cursor to output a strored procedure resultset to a GUI - things that would show the paradigm changes between SQL Server & Oracle.
"...need to send you to DBA school..." - I always thought of that as "Oracle Monastery" 😉
Best regards,
SteveR
Heh... thanks Steve... I'll consider it. It would be very difficult, though, because I break out in a bad case of Tourette's syndrome every time I try to do something in Oracle that would only take a minute to do in T-SQL 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2008 at 7:34 am
Heh... thanks Steve... I'll consider it. It would be very difficult, though, because I break out in a bad case of Tourette's syndrome every time I try to do something in Oracle that would only take a minute to do in T-SQL
Having been both an Oracle database expert (since 1986) covering V5 through 10g, as well as a SQL Server developer (V6.5 through 2005), in addition to IBM's DB2, I can make the exact same statement about SQL Server and T-SQL.
I'm continually frustrated by the lack of core functionality within the SQL Server engine that has existed in Oracle for years!
February 14, 2008 at 7:51 am
JohnG (2/14/2008)
Heh... thanks Steve... I'll consider it. It would be very difficult, though, because I break out in a bad case of Tourette's syndrome every time I try to do something in Oracle that would only take a minute to do in T-SQL
Having been both an Oracle database expert (since 1986) covering V5 through 10g, as well as a SQL Server developer (V6.5 through 2005), in addition to IBM's DB2, I can make the exact same statement about SQL Server and T-SQL.
I'm continually frustrated by the lack of core functionality within the SQL Server engine that has existed in Oracle for years!
The problem is that people somehow do not understand the fundamental fact that DBMSes ARE DIFFERENT.
If one gets a new car by another vendor he understands that the core functionality is the same i.e. the car can be driven but switches, detectors, the general panel whatever else are different. If one blindly tries to drive the car with automatic gear-box in the same way as he did with manual gera-box the car will be broken. As well as otherwise. If one blindly seeks light switch in the very same place where it was in the previous car he will simply drive in the ditch at night. Usually such people are considered [put in your own favourite epithet here] 🙂
Unfortunately somehow about DBMSes people think that they should be the same. Although produced by different vendors. Although designed by different people. It always amazes me how it comes?
Gints Plivna
Gints Plivna
http://www.gplivna.eu
February 14, 2008 at 1:39 pm
Jeff,
I did not mean to cause a Tourette's like reaction!
My statement about "the basics" should have been more clear. My assumption is that a company that has made a decision to migrate to Oracle will be doing so with either Oracle 10g or 11g as the RDBMS version. Most of my references refer to the Oracle 8i or 9i releases. Syntax for object creation such as triggers, tables, etc. will fundamentally be the same. As RDBMS versions are released the datatypes available (both deprecated and new) will need to be evaluated by the development team to see if any changes are needed before an upgrade is performed.
SQL Server and Oracle are both database engines. I have to think of them in their own terms, taking each with their respective pros and cons. I can even make them share data, and I have been known to get them to replicate on a really good day.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
February 14, 2008 at 6:18 pm
JohnG (2/14/2008)
Heh... thanks Steve... I'll consider it. It would be very difficult, though, because I break out in a bad case of Tourette's syndrome every time I try to do something in Oracle that would only take a minute to do in T-SQL
Having been both an Oracle database expert (since 1986) covering V5 through 10g, as well as a SQL Server developer (V6.5 through 2005), in addition to IBM's DB2, I can make the exact same statement about SQL Server and T-SQL.
I'm continually frustrated by the lack of core functionality within the SQL Server engine that has existed in Oracle for years!
Which "core functionality" would that be, please?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2008 at 6:24 pm
gints.plivna (2/14/2008)
If one gets a new car by another vendor he understands that the core functionality is the same i.e. the car can be driven but switches, detectors, the general panel whatever else are different.
Heh... it can actually be much worse than that... you drive all your life in the United States and, suddenly, you and your car are transported to the U.K.... and no one told you about the left side of the road thingy and you suddenly see this huge truck in front of you... 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2008 at 9:46 pm
I'm continually frustrated by the lack of core functionality within the SQL Server engine that has existed in Oracle for years!
I'm curious, too. As a developer who uses SQL Server extensively, I had a brief encounter with an Oracle project a few years back that frightened me so badly that I'm not anxious to have another experience like that again.
I have to smile, because a few months back, I was working with some mainframe DB2 guys on a data conversion project, who complained that SQL Server would not let them create a primary key on more than 16 columns. When I suggested that they perhaps change their design, they blamed SQL Server for being a "weak" dbms and unfit for production if it couldn't handle such a construct. I was thinking (but didn't say) that if a car LETS you drive off a cliff doesn't necessarily mean its a good idea...
February 14, 2008 at 9:51 pm
A primary key of more than 16 columns... Lordy, I sure hope someone doesn't think of that as "core" functionality... :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2008 at 4:58 pm
Jeff Moden (2/14/2008)and no one told you about the left side of the road thingy and you suddenly see this huge truck in front of you... 😛
Yea such people has their own contest. It is called Darvin award 😉
OK now let's talk a bit more serious and look at your stements. All examples are from Oracle 9i, which about the same time when SQL Server 2000 was released.
In SQL Server, the UPDATE statement has a FROM clause where you can do joins. In Oracle, you do not. Instead, you must do correlated subqueries in both the Select List and the Where Clause...
Actually this is a bit dangerous syntax anyway. Look at SQL Server docs here specifically example C. Using the UPDATE statement with information from another table. First statement using joins is dangerous and second simply doesn't use them. Speaking about Oracle you aren't correct BTW, it doesn't allow uncertainities but it allows cases when the result is 100% defined:
SQL> update (select * from emp inner join dept on emp_id = dept_emp_id)
2 set emp_name = dept_name
3 /
set emp_name = dept_name
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> ed
Wrote file afiedt.buf
1 update (
2 select *
3 from emp inner join dept on emp_id = dept_emp_id
4 )
5* set dept_name = emp_name
SQL> /
2 rows updated.
unless you're lucky enough to have a version of Oracle that has the "new" MERGE command... that makes life a whole lot easier even if you just need to do updates.
OK looking at facts it is new only for not yet existant SQL Server 2008 😛 Merge exists in Oracle since 9i and year 2001.
First time a developer says he needs a result set from a stored procedure, you're gonna flip. In SQL Server, stored procedures can return result set's directly to a GUI.
Khe, khe I remember one of so called Oracle bigots writing something like that "procedures were designed not to return any values, only functions were, untill Bill G that messed up 🙂 So it all depends from what viewpoint one looks at that.
In Oracle, you need to write a package that has both an interface "spec" and a body and it has to use a "Global Cursor" data type.
Hmm. OK I assume that with global cursor you understood the term REF CURSOR or reference cursors. Everything other in this statement is just plain wrong. Packages are a wonderful concept in Oracle and to be frank the forst time when I looked at SQL Server and has to find the procedure to be corrected among a few hundred other procs I was highly dissapointed. In Oracle we'd logically group them together in packages using such nice concepts like implementation hiding,, overloading etc. OK back to initial problem. There exists also standalone procedures which usually are a favourite (and wrong!!!) approach of coding done by people coming from SQL Server world 🙂
If one doesn't like to define it's own ref cursor type indicating what it shoul return one can use predefined sys_refcursor type. So the procedure would be something like that:
SQL> create or replace procedure p (curs in out sys_refcursor) is
2 begin
3 open curs for select * from emp;
4 end;
5 /
Procedure created.
SQL> variable c refcursor
SQL> exec p(:c)
PL/SQL procedure successfully completed.
SQL> print c
EMP_ID EMP_NAME
---------- ----------
1 aaa
2 bbb
First time you try to return something directly to the screen to troubleshoot, you're gonna have a big disappointment... if it's between a BEGIN and END;, forget about returning anything directly to the screen. You need to use a "DBMS_OUTPUT" command of some sort.
You can use DBMS_OUTPUT, the scenario written just in the code above which is specific for the SQL*Plus tool, all other GUI tools may have or have not that, built in package dbms_debug, built in package dbms_application_info to set your own operation progress, use autonomous transactions to create your own system of recording some info etc.
Ever try to check a formula in SQL server by doing something like a simple SELECT GETDATE()? In Oracle, it doesn't work... EVERY select requires a FROM clause... So, to do the same thing in Oracle, you have to do a SELECT SYSDATE FROM DUAL;
If we are speaking about pure SQL then yea, if we are speaking about PL/SQL then selecting from dual is just BAD BAD coding style.
SQL> select sysdate from dual;
SYSDATE
----------
2008.02.16
SQL> var a varchar2(100)
SQL> begin
2 :a := sysdate;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> print a
A
-----------------------------------------
2008.02.16
... yeah... you're gonna get to love the DUAL table...
Nope. You gonna love it only if you have bad coding habits and style. Otherwise yea sometimes you need it, but not often.
Wanna write a set based trigger in Oracle... forget it... There's no INSERTED or DELETED tables. There are INSERTED and DELETED variables for each column... you have to write RBAR on steroids and then tell the trigger FOR EACH ROW.
Oh yea. I wonder how many of these existing triggers will work correctly when someone will try to install the same code in SQL Server 2008 and run merge command. Should be nice to distinguish which rows are inserted, which updated and which deleted. Otherwise one simply writes FOR EACH ROW trigger in Oracle and remembers it not to use for very big loads (it of course affects performance) and write separate procs for big loads updating derived info. It is much the same just like not creating too many row locks in SQL Server 😛
Oh yeah, almost forgot... if you need to write any big complicated triggers to drive audit tables, make sure you keep it below 32K because that how big a trigger you can make... period!
Ummm. Audit... There is built in audot in Oracle. Everyone in every DB should look in built in features at first. OK if you are in desperate need for your own journaling tables then I'll ask a question in which version your knowledge stpped to advance? Get at least 9i and try it out yourself. And I'm not speaking about the fact that one simply can call a stored procedure from triggers.
Speaking about journaling - there is a nice feature in already existing 11g called total recall when journaling records are built completely automatically by background process from undo blocks and stored in a different tablespace not accessible even by admins. One can query these historical data using flashback query (AS OF clause). more here Keeping in mind that SQL server only a few years ago added snapshot isolation and row versioning probably after 10 years we will see that also in SQL Server 🙂
You're gonna love that fact that object names can only be 30 characters long and that any script that generates the CREATE statement for an object, converts all object and column names to upper case!
Yeahhh 30 symbol legth limit on identifier name applies. That's true. But if you want to mess around you are free to do something like that:
SQL> create table "my absolute killer table name" (
2 "!@#%^&*())_+" number);
Table created.
SQL> insert into "my absolute killer table name" values (1);
1 row created.
SQL> select * from "my absolute killer table name";
!@#%^&*())_+
------------
1
Varchar is called VARCHAR2
you can use varchar f you'd like.
SQL> create table q (a varchar(10));
Table created.
and it only goes up to 4000. NVARCHAR2 only goes up to 2000.
In fact they both goes up to 4000 bytes. NVARCHAR2 just have to be unicode which as you know might have more than one byte per character. And these limits apply only to SQL. In pl/sql one con go up to ~ 32K.
If you need more than that, get ready to be greased by CLOBs which aren't much better than the TEXT datatype in SQL Server.
OK I don't know what one can do with TEXT datatype in SQL Server, but just in case the functionality is very limited I suggest you to update your knowldge again. Functionality of CLOBs are pretty much the same as with VARCHARs, just the storing mechanism is different.
OK about other things I'll write tomorrow because it is 2 past midnight here and I really need some sleep.
Gints Plivna
Gints Plivna
http://www.gplivna.eu
February 15, 2008 at 7:04 pm
You've exemplified exactly what I was talking about, Gints... doesn't really matter what people think of one or the other, they are very different and anyone who thinks that the SQL in Oracle is going to be a cake walk just because someone is good at SQL Server, or vice versa, is in for a rude awaking.
Good feedback, though... seriously, thanks for jumping into the thread.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2008 at 8:17 pm
Heh... I did look at the "Example C" code you referenced... guess that proves that even people who write the documentation can be card carrying members of the Darwin Award... Unless you're one of those folks that believe in the myth of code portability between different database engines, I'm not sure why anyone would write a double nested correlated subquery to do such a thing.
Just so everyone doesn't have to go back and see what I'm talking about... here's the code that Gint's is talking about... it's actually in Books Online under UPDATE as Example "C"... I've included code to make a copy of the target table so we're not actually updating anything important in the Adventure Works database... I follow that code with some code I wrote if I were going to performance tune the code...
SET NOCOUNT ON
USE AdventureWorks;
----------------------------------------------------------------------------
SELECT * INTO Sales.SPTest FROM Sales.SalesPerson
GRANT UPDATE ON Sales.SPTest TO PUBLIC
----------------------------------------------------------------------------
SET NOCOUNT OFF
GO
PRINT REPLICATE('=',70)
PRINT 'The correlated subquery method...'
SET STATISTICS TIME ON
--==========================================================================
-- This is the code from Example C. Looks simple enough but has a double
-- nested correlated sub-query which is going to suck the life out of this
-- simple code performance wise.
--==========================================================================
UPDATE Sales.SPTest
SET SalesYTD = SalesYTD +
(SELECT SUM(so.SubTotal)
FROM Sales.SalesOrderHeader AS so
WHERE so.OrderDate = (SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader AS so2
WHERE so2.SalesPersonID = so.SalesPersonID)
AND Sales.SPTest.SalesPersonID = so.SalesPersonID
GROUP BY so.SalesPersonID)
SET STATISTICS TIME OFF
GO
--==========================================================================
-- This is the code I would write to be the performance enabled equivelant
-- to the code above. Notice that it uses derived tables instead of
-- correleated subqueries... we're using joins instead of RBAR here...
-- Guess what that's going to do to performance? ;-)
-- Also, the derived tables allow me to more easily troubleshoot each
-- section of the code. I can select smaller sections of the code and
-- execute them individually to ensure they are working as expected. With
-- the correlated subqueries above, there's NO change of doing that without
-- actually making a modification to the code to test it.
--==========================================================================
PRINT REPLICATE('=',70)
PRINT 'My performance enabled rewrite...'
SET STATISTICS TIME ON
UPDATE Sales.SPTest
SET SalesYTD = spt.SalesYTD + total.DayTotal
FROM Sales.SPTest spt
INNER JOIN
(--==== Derived table "total" finds total for max day by sales person
SELECT so.SalesPersonID,SUM(so.SubTotal) AS DayTotal
FROM Sales.SalesOrderHeader so
INNER JOIN
(--==== Find Max OrderDate for each SalesPerson
SELECT SalesPersonID,
MAX(OrderDate) AS MaxOrderDate
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
) maxorddate
ON so.SalesPersonID = maxorddate.SalesPersonID
AND so.OrderDate = maxorddate.MaxOrderDate
GROUP BY so.SalesPersonID
) total
ON spt.SalesPersonID = total.SalesPersonID
SET STATISTICS TIME OFF
PRINT REPLICATE('=',70)
--===== Remove the extra table we made...
DROP TABLE Sales.SPTest
And, here's the resulting stats from the Message tab...
[font="Courier New"]======================================================================
The correlated subquery method...
SQL Server Execution Times:
CPU time = 296 ms, elapsed time = 352 ms.
(17 row(s) affected)
======================================================================
My performance enabled rewrite...
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 48 ms.
(17 row(s) affected)
======================================================================
[/font]
The performance enabled code only runs 17.5 times faster in SQL Server than the correlated subquery does .. (Heh... sorry... I know I usually do better, but didnt want to take the time to make any indexes on the update test table :P)
Again, the purpose of this is just to demo a couple of things... If you write Oracle code, like the original version of "Example C", in SQL Server, performance will suffer greatly because SQL Server is a mostly "Set based" engine and doesn't do well with the RBAR of correlated subqueries.
On the other hand, if you try to use the performance enabled update query I wrote in Oracle, you'll get nowhere because the Oracle Update statement can't take a FROM clause. You must use some form of correlated subquery in Oracle to do this update (unless you use MERGE).
The kicker in Oracle is that both the seemingly set based MERGE command and the Update with the correlated subquery take roughly the same time to execute. That's because Oracle has been tuned to handle the RBAR that typical ISAM programmers throw at a database.
Again, the point is, don't even think that the "basics" are even close to each other between these two database engines... there's a whole different paradigm in the SQL, the PL-SQL and T-SQL, the supporting GUI's, and the very database engine itself.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2008 at 8:23 pm
Heh... and I damned sure wouldn't have actually written the performance enabled code the way I did... I did that just to make cuff's match collar in the code performance comparison.
In both code snippets, there's absolutely nothing that checks to see if the target table has already been updated meaning that if you run the code more than once on the same day... the data in the table is toast. 😉 Like I said, even the folks that write the documentation can be dead wrong or, at least, provide an unreasonable bit of code.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2008 at 5:39 am
Jeff Moden (2/15/2008)
You've exemplified exactly what I was talking about, Gints... doesn't really matter what people think of one or the other, they are very different and anyone who thinks that the SQL in Oracle is going to be a cake walk just because someone is good at SQL Server, or vice versa, is in for a rude awaking.
Yes, I agree with you. Just the small note different is not the same as worse 😉
So OK let's continue. Back to lengths. I'm not so concerned about limit of 30 symbols per identifier however, I'm quite amazed by following math in SQL Server:
select len(a), len(b), len(c), len(d) from a;
select len(a+b+c+d) from a;
3048304830483048
8000
3048+3048+3048+3048 = 8000??? 🙂
OK I assume that one somehow can switch on that such silent trimmings won't happen unnoticed.
In Oracle we'd have:
SQL> select length(a), length(b) from a;
LENGTH(A) LENGTH(B)
---------- ----------
4000 4000
SQL> select length(a||b) from a;
select length(a||b) from a
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
And of course this error message is one of these five mentioned below and absolutely cryptic 🙂
Oh, you'll love the error messages... I think there might be 5 that actually show up... most of them are about errors have to do with the bloddy semi colons required but only in certain spots. Doesn't really tell you where the error is though.
Hmmm what to say on that? Probably just start to understand how Oracle docs are organized. Browse one of the docs index here[/url], pick necessary Oracle version and look at Error messages document. One can try to count how many error messages are there, I don't know 🙂
Learn how to use SELECT INTO to populate variables. Forget about local temp tables, they don't exist. Only global temp tables exist.
Speaking about temporary tables - I sometimes wonder why people are so keen to use them? Oracle doesn't need them much. Syntax is powerful enough to write most of the queries without temp tables - joins, subqueries, subquery factoring clause(woth clause, common table expression in SQL Server), analytic functions, MODEL clause - all this is enough for almost all cases to live with just pure SQL.
OK if you really need temporary table then Oracle has such concept like global temporary tables, but data in these are session specific. Just table definition is global. In PL/SQL there are at least 3 types of arrays (varrays, nested tables and assciative arrays) which all can be used to cache some data in memory. EVEN MORE. In pl/sql one can define arrays in package spec and initialize as soon as session starts and reuse them all the session's lifetime. Not sure how to do something like that in SQL server.
Practice typing ALTER SESSION SET CURRENT_SCHEMA = xxx; Only way to keep from killing yourself with a 2 part naming convention that doesn't mean much, especially in TOAD. You'll create stored procs and packages that work just fine only to find that you can't find them... TOAD lies about which schema it's in.
TOAD is just one of many GUI tools for Oracle DB. If it is buggy, don't use it, choose such third party tools like PL/SQL Devloper, SQL Navigator or Oracle's own SQL developer, Enterprise Manger or I'm sure many other tools. No need to stick with a buggy one.
The default for transactions is that you must to a commit even though you never declared and explit transaction. The exception to the rule is if you screw up and do some DDL in the middle of your DML, the DDL automatically performs a commit... even if you didn't want it to... and it doesn't tell you it did it.
Yeahhh to be frank I was almost shocked when the first time issued some insert into blahh in SQL Server then issued Rollback and the server said me:
Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Blahhh autocommit is one of the most dangerous things I've encountered. I've always changed options in my tools not to do autocommit, even on normal logout. I want to commit data MYSELF. I want control my changes and be sure about them.
Actually I think this autocommit thingie has roots in SQL Server inablity to store row versions for a long time. SQL Server had mantra - keep your transactions short. Oracle never had such mantra. Oracle has mantra - keep your transactions as long as business needs them and commit only when necessary.
Speaking about DDLs mixed with DMLs. I consider it simply bad practice. Especially in pl/sql. It means dynamic sql. It means you cannot a priori compile and test your code on exiting objects and be sure that your code will run. And yes one have to remember that each DDL in actually Oracle means:
COMMIT;
DDL or DCL;
COMMIT;
/
So even if your DDL fails, but it was syntactically correct your data changes are commited. But as SQL server has autocommit why do you care? 🙂
Wait until you try to find equivalents for things in Oracle... naming convention is TOTALLY different. For example, what would you look for to find the equivalent for CTE's... oh, I'm sure you just spit "Sub-Query Refactoring" right out. Or, maybe you are smart enough to lookup WITH only to find 10,000 pages of info that have nothing to do with what you're looking for. Heh...
For CTE you simply browse SELECT syntax in SQL reference document as I did for SQL Server and find out that it is called subquery factoring clause.
for that matter, lookup UPDATE and see if you can find a single example that does the equivelent of a join between two tables for the UPDATE. You will not find a single example. No examples of using the correlated sub-queries to do it with, either. You might find something under correlated sub-queries, but only as a Select... nothing for the all important UPDATE.
As you've found out in your previous posts not every documentation is perfect. Google is your best friend also here and I'm sure you'd get hundreds of examples.
Just wait until you try to build an "autonumbering column" like an IDENTITY column... they're called "sequences' in Oracle... yeah... you'll learn to pretty much hate those damned things.
Of course if one doesn't like flexibility and many options, if one misuse sequences to treat them as gapless (see my article the curse of gapless sequences), then they strikes back. Just like truck in front of one on the roads of UK 🙂
Heh... Wait until you try to put a documentation header on a view... you'll love that!
Not sure I understood that. ButI assume that has nothing to do with DB itself, more with a tool showing/not showing headers.
There's about 10,000 other "basic" things that are totally different in Oracle than they are in SQL Server. SQL is NOT SQL... period.
Yeahh that's paryially true. But some basics still applies:
1. You can do everything in the fastest way if you don't do that at all. Many times business requirements can be fullfilled just throwing out half of the crap code.
2. If you can do that in pure SQL, then do that.
3. If you need more than SQL, then use pl/sql or t-sql.
4. If you need more than pl/sql then use Java. Not sure what in SQL server though. Probably .NET. And believe me - there are very few things one cannot do in pl/sql.
you're going to be dazed and confused by Oracle because they nothing alike when it comes to good solid set based code.
Umm if you are using much temporary tables in SQL server then probably yes. If you know the basics, that pure SQL usually is better than t-sql, then you just have to learn some technical things like recursive queries are not done using CTE but Oracle specific syntax start with/connect by (which existed looooong before CTE in SQL Server) and so on.
And, as previously mentioned, the GUI's for Oracle suck. You'll also find out that Toad sucks pretty much, as well. And, if you write code and test it in Toad, and you promote it to production using SQL-Plus, there's a pretty good chance it won't work. You MUST make all code work with SQL-Plus or scheduled procs just might not work because of it.
As I've already pointed out Toad is just one of the tens of available tools. Pick better one.
If your company values your Oracle data... they need to send you to DBA school, right NOW, or rent an true experienced Oracle DBA for 6 weeks to get you and your team up to speed quickly!
That's of course good advice. And another advice - be open to change your mind. Be open to do things like they are done in Oracle, not like they are done in SQL Server.
Gints Plivna
Gints Plivna
http://www.gplivna.eu
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply