January 27, 2009 at 12:11 pm
Loner (1/27/2009)
Oracle uses cursor extensively and SQL Server is a 'no, no'. The SQL Server DBA threw back my first SQL Server stored procedure to me and yelled at me because I used cursor.
Use of cursors for row by row processing is not generally good practice in Oracle either. Tom Kyte has covered this a few times:
"If you can do it in a single SQL statement, by all means do it in a single SQL statement. Do not waste time, energy, and CPU cycles writing procedural code that will run slower than regular SQL."
http://www.oracle.com/technology/oramag/oracle/07-mar/o27asktom.html
However, some Oracle old hands seem habitually to use cursors even where set-based solutions would do better. Partly I suppose this is because PL/SQL is a general purpose language in a way that T-SQL isn't, so PL/SQL gets used for a lot more procedural stuff that you would never even attempt in T-SQL.
January 27, 2009 at 6:29 pm
Heh... there are some places in Oracle that require RBAR... try writing a set based trigger in Oracle sometime... 😛
--Jeff Moden
January 28, 2009 at 9:18 am
I guess we know why Jeff doesn't like Oracle.
RBAR, RBAR, RBAR, RBAR, RBAR, lunch, RBAR, RBAR, RBAR, RBAR, ....
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 28, 2009 at 11:45 am
Steve Jones - Editor (1/28/2009)
I guess we know why Jeff doesn't like Oracle.RBAR, RBAR, RBAR, RBAR, RBAR, lunch, RBAR, RBAR, RBAR, RBAR, ....
You missed one on the right... gotta keep a balanced load. 😛
So far as my thoughts on Oracle (please, not directed at anyone in particular...)
[font="Arial Black"]O[/font]nly
[font="Arial Black"]R[/font]BAR
[font="Arial Black"]A[/font]nd
[font="Arial Black"]C[/font]ursors
[font="Arial Black"]L[/font]ive
[font="Arial Black"]E[/font]asily
😛
--Jeff Moden
January 28, 2009 at 11:52 am
Jeff Moden (1/28/2009)
Steve Jones - Editor (1/28/2009)
I guess we know why Jeff doesn't like Oracle.RBAR, RBAR, RBAR, RBAR, RBAR, lunch, RBAR, RBAR, RBAR, RBAR, ....
You missed one on the right... gotta keep a balanced load. 😛
So far as my thoughts on Oracle (please, not directed at anyone in particular...)
[font="Arial Black"]O[/font]nly
[font="Arial Black"]R[/font]BAR
[font="Arial Black"]A[/font]nd
[font="Arial Black"]C[/font]ursors
[font="Arial Black"]L[/font]ive
[font="Arial Black"]E[/font]asily
😛
Okay, that's a good one! :w00t:
January 28, 2009 at 11:58 am
[font="Verdana"]I've used both extensively. I prefer some of the options available with Oracle for high-end datawarehousing, but SQL Server is rapidly playing catch up there. Generally speaking, I prefer SQL Server. It's cheaper (both up-front costs and ongoing license and support costs), easier to run and easier to use.
If you want something to put an Oracle DBA in their place, just ask them "okay, how do I return a result set from a stored procedure?" The answer is interesting. Something we take for granted in SQL Server. 😀 Here's another: how do I create a file of data from Oracle? (In SQL Server, you can use SSIS or bcp, but in Oracle?)
On the other hand, there's stacks of stuff that I miss from Oracle, particularly when programming: Being able to declare a variable as a type based on the definition of a table, better transaction and error handling all spring to mind.
For most organisations, I would say which they use is a matter of choice. The only times I would specifically recommend Oracle is when the organisation has a lot of existing experience with Oracle or when they need Java inside the database (yes, I could also recommend Sybase for that, but I much prefer Oracle to Sybase.) For high end data warehousing, Oracle may or may not have a lead (it's a complex evaluation), and there are also systems such as Teradata.
Frankly, the reasons I have seen most organisations here in New Zealand choose Oracle is because they want to run an application that needs Oracle, or because they have someone on the IT management team who hates Microsoft. Yes, politics is a big factor in technology decisions. I personally prefer Microsoft to Oracle as an organisation (anyone else tried reading the Oracle documentation or dealing with Oracle support or handling the mess of patching and upgrades?) But it's not uncommon here to get people who dislike Microsoft for some bizarre reason.
"Row by agonising row" is just as large an issue in Oracle as it is in SQL Server. Sorry, neither system wins there. SQL Server (back in the Sybase days) actually implemented server-side cursors well before Oracle, from memory. Oracle PL/SQL used to be a client-side only technology (Oracle Forms), and didn't get implemented within the database until several years after Sybase made server-side stored procedures (and cursors) popular.
I once recoded an Oracle package designed to move data between two Oracle systems that was taking over 18 hours to run. It was written as a RBAR over a database link. I rewrote it to pull the remote data across as sets in one go, and then merge the data into the local database as set-based operations, and reduced the time to under 2 minutes. Sound familiar? Anyone who tells you that Oracle is "optimised for cursors" is just someone who hasn't been chased down and shot at yet by the poor DBAs supporting their code.
You can implement set-based triggers in Oracle. Most people choose not to. I'm really not sure why. What scares me is the number of triggers I have seen in SQL Server that don't realise they are set-based and think they are only dealing with a single row. So no clear winner there.
I'm a little puzzled over the claim that Microsoft SQL Server isn't relational. Is this a reference to ANSI SQL compliance? Oracle is just as non-compliant as SQL Server, although in different areas. Technically, being ANSI SQL compliant does not "relational" make: you can be relational without being SQL, and vice-versa (anyone remember the SQL front end they put into dBase?).
What was interesting was when MS Access was one of the most ANSI SQL compliant databases on the planet. 🙂 I learned ANSI SQL syntax joins on Access.
Anyway, to sum up what I have seen here... the choice of Oracle vs SQL Server is largely driven by what it's needed for (i.e. application that only runs on Oracle, for example) or for political reasons. Technologically, both Oracle and SQL Server have their unique strengths, and it's not really possible to compare the two.
Of course, the final say: http://www.tpc.org. Oracle and DB2 have the top 10 performance (not price/performance) spots for TPC-C, whereas Microsoft SQL Server has all of the TPC-E performance results. TPC-H is a mix. Which just goes to prove that there's no real answer to "which is better" even if you are purely looking at performance.
Regards,
Bruce W Cassidy
[/font]
January 28, 2009 at 12:23 pm
Well said. Thanks for taking the time.
Yeah... I use that one, alot...
"okay, how do I return a result set from a stored procedure?" I just love building packages with global reference cursors... then, build a reader to "iterate" through the cursor using something like DBMS_PrintLine (or something like that). Not real fun for troubleshooting. If I'm not mistaken, isn't there also a way to dump the output to DBMS_Output (almost) in bulk?
I suppose I've been spoiled, but that's one of the main reasons I like SQL Server so much. I also found out why they call Toad "Toad"... it "croaks" a lot. 😛 As you can tell, I'm not a big fan of that, either.
--Jeff Moden
January 28, 2009 at 12:31 pm
Jeff Moden (1/28/2009)If I'm not mistaken, isn't there also a way to dump the output to DBMS_Output (almost) in bulk?
[font="Verdana"]Yes. Up until you run into the size limitation of DBMS_Output, and your Oracle database crashes. 😀 Well, it doesn't crash any more, but relying on DBMS_Output is dodgy. You can do it now through file operations from the Java stuff, and you can also use DBMS_File from memory. I.e. to output data to a file, it's "programmatic". There still wasn't a handy, convenient tool for it last time I looked at Oracle (er, 10g.)[/font]
Jeff Moden (1/28/2009)I also found out why they call Toad "Toad"... it "croaks" a lot. 😛 As you can tell, I'm not a big fan of that, either.
[font="Verdana"]I've always disliked Toad. Which is why it scares me that people actually buy the "Toad for SQL Server" product! If you need a good Oracle query tool, use SQL Tools (freeware, from sqltools.net I think, but you may have to google it.)[/font]
January 28, 2009 at 3:08 pm
bruce.cassidy (1/28/2009)
[font="Verdana"]You can implement set-based triggers in Oracle.[/font]
Have any links / examples? This is perfect timing for something I'm working on.
January 28, 2009 at 3:21 pm
[font="Verdana"]The Oracle create trigger syntax is as follows:[/font]
[font="Courier New"]
CREATE [OR REPLACE] TRIGGER
{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON
[REFERENCING [NEW AS ]]
[FOR EACH ROW [WHEN ( )]]
[/font]
[font="Verdana"]If you leave off the "for each row" bit, it's a set-based trigger.
Other than that, hit the Oracle forums. :)[/font]
January 28, 2009 at 3:22 pm
Some comments for various posts:
anyone else tried reading the Oracle documentation
I'm reading it almost every day I produce any code. tahiti.oracle.com then is my starting point and I've personally found Oracle's docs very good, informative and full of examples. They are a lot, that's true, but as soon as someone understands the main docs and their structure everything is cool 🙂
Oracle PL/SQL used to be a client-side only technology (Oracle Forms), and didn't get implemented within the database until several years after Sybase made server-side stored procedures (and cursors) popular.
Actually one can compare quite comprehensive SQL Server history with outlined Oracle's history including feature by feature. Actually this is quite interesting - I've tried this evening find history articles for both databases outlining features for each version. For SQL server all of the articles were "story based" without clear feature list, but just the second one for Oracle was complete feature list. Probably it explains some hidden mentality behind each DBMS? 😉
I just love building packages with global reference cursors... then, build a reader to "iterate" through the cursor using something like DBMS_PrintLine (or something like that). Not real fun for troubleshooting.
a simple code snippet (using SQL*Plus) probably is the best answer:
SQL> variable c refcursor
SQL> begin
2 open :c for select 'output from refcursor' col, 1 num from dual;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> print c
COL NUM
--------------------- ----------
output from refcursor 1
and here it is with procedure parameter:
SQL> create or replace procedure p (c in out sys_refcursor) is
2 begin
3 open c for select 'output from refcursor' col, 1 num from dual;
4 end;
5 /
Procedure created.
SQL> exec p(:c)
PL/SQL procedure successfully completed.
SQL> print c
COL NUM
--------------------- ----------
output from refcursor 1
OK here are a few features I'm using quite extensively as a developer with Oracle which I really miss in SQL Server (probably haven't searched well enough?)
- analytic (ranking) functions. Yes I know some of them are in SQL Server as well but lag, lead, first_value, last_value, windowing clause these I really miss here.
- packages and package (global) variables and procedure overloading . Only procedures gives me shudder each time I have to do something in SQL Server and each time I need to remember something in session I miss package variables. Procedure overloading probably is the least problem of all three here, but also a nice feature sometimes.
- object types. As time goes by I'm more and more using object types in procedures to pass a bunch of related parameters (object type), just process a set of a bunch of related parameters (table of object type) in my procedure code, if I must use cursors (i.e. it is not possible reasonably do things in sets - yes there are such situations sometimes) I use bulk collect and forall syntax to avoid row by row pl/sql and sql engine switch.
Any suggestion how to achieve this or similar functionality in SQL Server? 😉
Gints Plivna
http://www.gplivna.eu
January 28, 2009 at 3:34 pm
gints.plivna (1/28/2009)
Some comments for various posts:anyone else tried reading the Oracle documentation
I'm reading it almost every day I produce any code. tahiti.oracle.com then is my starting point and I've personally found Oracle's docs very good, informative and full of examples. They are a lot, that's true, but as soon as someone understands the main docs and their structure everything is cool 🙂
[font="Verdana"]Compared to the documentation available for the Microsoft products, the Oracle documentation was abysmal. If it's improved (I haven't looked since 10g was released) then great![/font]
gints.plivna (1/28/2009)
OK here are a few features I'm using quite extensively as a developer with Oracle which I really miss in SQL Server (probably haven't searched well enough?)- analytic (ranking) functions. Yes I know some of them are in SQL Server as well but lag, lead, first_value, last_value, windowing clause these I really miss here.
Yep. You can do a lot with the analytics that were released as part of SQL 2005, and the new hierarchy stuff from SQL 2008 looks interesting. But this is still one area where SQL Server is lagging.
gints.plivna (1/28/2009)
- packages and package (global) variables and procedure overloading . Only procedures gives me shudder each time I have to do something in SQL Server and each time I need to remember something in session I miss package variables. Procedure overloading probably is the least problem of all three here, but also a nice feature sometimes.
[font="Verdana"]I miss those too![/font]
gints.plivna (1/28/2009)
- object types. As time goes by I'm more and more using object types in procedures to pass a bunch of related parameters (object type), just process a set of a bunch of related parameters (table of object type) in my procedure code, if I must use cursors (i.e. it is not possible reasonably do things in sets - yes there are such situations sometimes) I use bulk collect and forall syntax to avoid row by row pl/sql and sql engine switch.Any suggestion how to achieve this or similar functionality in SQL Server? 😉
[font="Verdana"]You could use SqlVariant I guess. But basically you have to use the CLR functionality and define complex types (including object types) within .net -- great if you are comfortable switching between languages and deploying assemblies to your database, but frankly I just don't feel it's worth the pain.[/font]
January 28, 2009 at 3:52 pm
Bruce: thanks for the quick reply.
It's not exactly what I expected, in that by using FOR EACH ROW it's no longer a set-based trigger ie one that can process against every row inserted/deleted at once. The only way I see to do that is load another table in a row-based before trigger, then use it in an instead-of or after trigger. Of course that is long-winded and probably offsets any benefits of processing all updates as a set. If FOR EACH ROW is omitted, I don't have access to the :new and :old variables - so how can I do a set-based operation on the modified rows?
Am I missing something?
January 28, 2009 at 4:07 pm
matt stockham (1/28/2009)
Bruce: thanks for the quick reply.It's not exactly what I expected, in that by using FOR EACH ROW it's no longer a set-based trigger ie one that can process against every row inserted/deleted at once. The only way I see to do that is load another table in a row-based before trigger, then use it in an instead-of or after trigger. Of course that is long-winded and probably offsets any benefits of processing all updates as a set.
Am I missing something?
[font="Verdana"]I'm not sure whether I can answer your question, as I don't actually have an instance of Oracle running that I can write some example code for you. Sorry about that. All I can do is try and remember (sorry, relying on my memory isn't the best.)
Without the "for each row", Oracle fires the trigger once per transaction. In this way it becomes a "set based trigger", as Oracle passes the entire set of changes to the trigger once.
With the "for each row", Oracle is firing the trigger once per row that is changed. You can imagine if you have a change to many rows, that means your trigger code gets called a lot! So Oracle is dropping down to row-by-(agonising)-row here. Only use this option if you absolutely cannot process your changes in a set-based fashion.
The before/after trigger syntax is simple: Oracle will fire the "before" trigger before it commits the changes. That gives a trigger the option of rolling back a change. This is the syntax that is (mostly) equivalent to what SQL Server does for triggers.
The "after" trigger will fire once the commit is complete. This is quite powerful in that you can have additional processes firing off after a data change (such as audit logs) that don't add to the length of the data change process (so Oracle isn't waiting around for the trigger to complete before it finishes the original transaction.)
Hope that helps. You're best to track down the Oracle forums and get more support from them, as they will be able to give you working examples.[/font]
January 28, 2009 at 4:16 pm
Ha, as you can see I edited my post as you were typing! Of course the line I missed originally changed the complexion of the question ...
Anyway to clarify :
I understand your comment about being set-based if it fires once per transaction. Where SQL differs (in my understanding) is that it can additionally perform set-based queries within the trigger using the modified data - I don't see how to do that in Oracle as :new and :old only apply to row-based triggers. And if that is correct, it's only the trigger call that is set-based in Oracle and not the trigger processing per se.
Viewing 15 posts - 61 through 75 (of 250 total)
You must be logged in to reply to this topic. Login to reply