Some Oracle praise?
-
Mika Wendelius wrote:
One benefit with Oracle is to be able to run it on different platforms.
Yes, that's one I heard and a very plausible one at that! :D Good thing we run it on Windows servers :laugh:
Mika Wendelius wrote:
Any examples?
Table variables, simply declaring some variables and using them (in all manner of ways). For example, I simply wanted to something like the following:
DECLARE @Names TABLE (Name VARCHAR(100))
INSERT INTO @Names
SELECT Name
FROM Person.PersonSELECT @Names
SELECT Column
FROM Table
WHERE Name IN @Names -- Not sure if I need a sub select from the top of my headDECLARE @SomeText VARCHAR(100)
SET @SomeText = "Not doable in Oracle"
SELECT @SomeTextI think the first thing with the table variable just isn't possible. My coworker, who worked with Oracle for about 30 years now, looked at me in disbelief and asked why I wanted to do such a thing anyway :~ The second, declaring a variable, assigning it a value and simply selecting it (FROM DUAL???) is just so awkward. Having multiple result sets in one procedure/session isn't possible. For example, from my software I want to insert an order, its details and do some auditing/logging and check if everything is in the database as expected.
SELECT * FROM SalesOrder
SELECT * FROM SalesOrderDetail
SELECT * FROM AuditLogI have to execute the statements one by one and check the results, no way to do it all at once and get the results next to each other. Please, I beg of you, prove me wrong! It would make my life so much easier :D Maybe I'm in a "SQL Server state of mind" and Oracle just is another paradigm? Like OOP vs. Functional? And I'm simply missing it? :confused:
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
Hopefully people don't mind me posting a few technical stuff...
Sander Rossel wrote:
I think the first thing with the table variable just isn't possible.
Why? You can define a type and you can define a table for a type: PL/SQL Tables and User-Defined Records[^]
Sander Rossel wrote:
The second, declaring a variable, assigning it a value and simply selecting it (FROM DUAL???) is just so awkward
Why not use a simple assignment
X:=5;
Sander Rossel wrote:
Having multiple result sets in one procedure/session isn't possible.
Yes it is, and you can use REF cursors: Optimize Result Set Retrieval Using ODP.NET and Ref Cursors[^] If these cause you headache, we can take the discussion outside, for example to Q&A. Lounge is not appropriate for these :)
-
Sander Rossel wrote:
I've heard that a lot (it's what every Oracle user says), but why though?
One thing I find intriguing about Oracle PL/SQL Packages is that you can do row-level operations with loops (i.e. cursors) but still have set-based performance. There's this (well-deserved, imo) attitude in SQL Server land that cursors are the work of the devil (entirely plausible), and any code that uses cursors will murder any semblance of performance (seen it myself). So you go through a lot of mental gymnastics to convert row-level operations with a bunch of if/else conditions into a series of set-based queries. So the code now performs the way it should, but it doesn't really *read* the way it should. That seems to not be the case in Oracle. You can loop with a cursor and do all sorts of imperative coding, but the performance hit is negligible. Disclaimer: I'm not really a fan of Oracle, it's just that our company uses it to death. We're one of those "50% of the business logic is buried somewhere in the database" type companies. Me? I prefer a dumb datastore and keep my business logic in places that can be reused easily.
Vark111 wrote:
One thing I find intriguing about Oracle PL/SQL Packages is that you can do row-level operations with loops (i.e. cursors) but still have set-based performance.
That's really interesting actually. I never use cursors in SQL Server for that reason. I've come across them A LOT in Oracle (although Oracle has a few different cursor types I believe). Then again, databases shouldn't really be doing loops in the first place :D I'm going to check that out and add it to the "cool in Oracle" list though :thumbsup:
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
It''s interesting to read the comments in this thread. Having worked for many years on Oracle, but now having changed company to work with SQL server, I have quite different experience. SQL Server is a much less mature product. It really feels like going fifteen years back in time. Programming in T-Sql feels like using prehistoric VB, the code is totally littered with stupid prefixes like @ and #. And it lacks consistency. The worst part is probably lock handling, on Oracle I never needed to think about it, period. No flashback queries. On Oracle you can do a
SELECT AS OF TIMESTAMP. Even from dropped tables. Always on failover is a really poor substitute for RAC. Half the point with clustering is gone if you can only use one node at a time. Composite indexes are only having statistics on the first column, reducing the efficiency of the optimizer considerably. No pipelined functions, at all. I can keep on going for long. On the other hand, SSIS is just brilliant.Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
No pipelined functions, at all.
I've only seen it once so far. As I understand it a pipelined function returns a row to the caller as soon as it's available instead of waiting for the entire result set to be calculated. Sounds really good. Adding it to my "cool in Oracle list"! :D Flashback technology sounds awesome (just Googled it) :omg: Not really something a dev would need though ;)
Jörgen Andersson wrote:
Having worked for many years on Oracle, but now having changed company to work with SQL server, I have quite different experience.
As far as I can see you and @mika-wendelius are the only ones to like/prefer Oracle so far. And you two have one thing in common that the other people on this thread have not: you're actual DBA's rather than developers who need to use a database now and then. So perhaps Oracle has more going on than what it looks like, but that's not very interesting to the average developer? Unfortunately, due to awful tooling from Oracle, the average developer will never see it either.
Jörgen Andersson wrote:
Programming in T-Sql feels like using prehistoric VB, the code is totally littered with stupid prefixes like @ and #. And it lacks consistency.
AT LEAST ORACLE IS CONSISTENT IN ONE THING :D I'm not sure why you'd think T-SQL feels like prehistoric VB though :~
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
Mika Wendelius wrote:
One benefit with Oracle is to be able to run it on different platforms.
Yes, that's one I heard and a very plausible one at that! :D Good thing we run it on Windows servers :laugh:
Mika Wendelius wrote:
Any examples?
Table variables, simply declaring some variables and using them (in all manner of ways). For example, I simply wanted to something like the following:
DECLARE @Names TABLE (Name VARCHAR(100))
INSERT INTO @Names
SELECT Name
FROM Person.PersonSELECT @Names
SELECT Column
FROM Table
WHERE Name IN @Names -- Not sure if I need a sub select from the top of my headDECLARE @SomeText VARCHAR(100)
SET @SomeText = "Not doable in Oracle"
SELECT @SomeTextI think the first thing with the table variable just isn't possible. My coworker, who worked with Oracle for about 30 years now, looked at me in disbelief and asked why I wanted to do such a thing anyway :~ The second, declaring a variable, assigning it a value and simply selecting it (FROM DUAL???) is just so awkward. Having multiple result sets in one procedure/session isn't possible. For example, from my software I want to insert an order, its details and do some auditing/logging and check if everything is in the database as expected.
SELECT * FROM SalesOrder
SELECT * FROM SalesOrderDetail
SELECT * FROM AuditLogI have to execute the statements one by one and check the results, no way to do it all at once and get the results next to each other. Please, I beg of you, prove me wrong! It would make my life so much easier :D Maybe I'm in a "SQL Server state of mind" and Oracle just is another paradigm? Like OOP vs. Functional? And I'm simply missing it? :confused:
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
Yes I believe the state of mind is a powerful thing to overcome. As to your questions, they have already been addressed by Mika. Oracle is undoubtedly a bit quirkier than SQL Server, but it's more consistent in my opinion, it's a little bit like going from VB to C#.
Wrong is evil and must be defeated. - Jeff Ello
-
Don't particularly like South Park, but that's hilarious! :laugh: :laugh: :laugh:
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
Don't particularly like South Park, but that's hilarious! :laugh: :laugh: :laugh:
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
It seemed relevant lol.
CPallini wrote:
You cannot argue with agile people so just take the extreme approach and shoot him. :Smile:
-
Yes I believe the state of mind is a powerful thing to overcome. As to your questions, they have already been addressed by Mika. Oracle is undoubtedly a bit quirkier than SQL Server, but it's more consistent in my opinion, it's a little bit like going from VB to C#.
Wrong is evil and must be defeated. - Jeff Ello
-
Hopefully people don't mind me posting a few technical stuff...
Sander Rossel wrote:
I think the first thing with the table variable just isn't possible.
Why? You can define a type and you can define a table for a type: PL/SQL Tables and User-Defined Records[^]
Sander Rossel wrote:
The second, declaring a variable, assigning it a value and simply selecting it (FROM DUAL???) is just so awkward
Why not use a simple assignment
X:=5;
Sander Rossel wrote:
Having multiple result sets in one procedure/session isn't possible.
Yes it is, and you can use REF cursors: Optimize Result Set Retrieval Using ODP.NET and Ref Cursors[^] If these cause you headache, we can take the discussion outside, for example to Q&A. Lounge is not appropriate for these :)
Mika Wendelius wrote:
If these cause you headache
The headache is actually in doing any of that ad-hoc :laugh: Open up a window in SQL Developer and typing:
DECLARE x NUMBER;
x := 5;
SELECT x FROM DUAL;won't work at all :D
Mika Wendelius wrote:
That's actually a pretty good article, will read it :thumbsup:
Mika Wendelius wrote:
we can take the discussion outside, for example to Q&A
I could create a question so that you specifically could answer it :laugh: I don't have access to Oracle right now though, so I can't post anything at the moment :)
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
Yes I believe the state of mind is a powerful thing to overcome. As to your questions, they have already been addressed by Mika. Oracle is undoubtedly a bit quirkier than SQL Server, but it's more consistent in my opinion, it's a little bit like going from VB to C#.
Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
it's a little bit like going from VB to C#
I actually did just that (VB.NET mind you!) and all the glorious stories I heard, about C# being the better and prettier language, were proven not true. It seems idiots write in C# as well :sigh: And indeed, I went from a SQL Server team to an Oracle team and suddenly everyone uses
select ... from table1, table2 where table1.id = table2.table1_idto join tables... :sigh: Not what I was expecting from the grande Oracle database (that, and everything else in this topic) ;pRead my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
Jörgen Andersson wrote:
No pipelined functions, at all.
I've only seen it once so far. As I understand it a pipelined function returns a row to the caller as soon as it's available instead of waiting for the entire result set to be calculated. Sounds really good. Adding it to my "cool in Oracle list"! :D Flashback technology sounds awesome (just Googled it) :omg: Not really something a dev would need though ;)
Jörgen Andersson wrote:
Having worked for many years on Oracle, but now having changed company to work with SQL server, I have quite different experience.
As far as I can see you and @mika-wendelius are the only ones to like/prefer Oracle so far. And you two have one thing in common that the other people on this thread have not: you're actual DBA's rather than developers who need to use a database now and then. So perhaps Oracle has more going on than what it looks like, but that's not very interesting to the average developer? Unfortunately, due to awful tooling from Oracle, the average developer will never see it either.
Jörgen Andersson wrote:
Programming in T-Sql feels like using prehistoric VB, the code is totally littered with stupid prefixes like @ and #. And it lacks consistency.
AT LEAST ORACLE IS CONSISTENT IN ONE THING :D I'm not sure why you'd think T-SQL feels like prehistoric VB though :~
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
You can think of pipelined functions in the same way as
yield returnin C#. Flashback really is the best thing since sliced bread. It's a bit of a performance drain, but still faster than SQL Server though. Ever wondered why you get the query time in seconds in SSMS and in ms from Oracle? ;PSander Rossel wrote:
Unfortunately, due to awful tooling from Oracle
Well, I certainly give you that one, SSIS is simply awesome despite some quirks.
Sander Rossel wrote:
you're actual DBA's
I'm not, I'm both a backend developer and database developer. No admin in my title to be found.
Sander Rossel wrote:
but that's not very interesting to the average developer?
That's maybe where the problem is. People don't understand what's going on in the databases, and SQL-Server is much better integrated with Visual Studio so it's really quite hidden from people. I once saw someone here comment that the biggest problem with Access is that it makes it to easy for people that don't know what they are doing to create a database. Well that statement really holds a bit of truth for SQL Server as well. That's also why we're seeing these reactionist NOSQL databases which is really bringing us back to the sixties. BTW, one of the big differences between PL-SQL and T-SQL is that there is a lot of implicit stuff going on in T-SQL. In PL-SQL you have to define everything. And that's probably why you thought you can only have one return set from PL-SQL.
Wrong is evil and must be defeated. - Jeff Ello
-
Jörgen Andersson wrote:
it's a little bit like going from VB to C#.
Thank you so much! Now I have to wipe away the stains of tea from my monitor... :-D
Glad to be of service. :)
Wrong is evil and must be defeated. - Jeff Ello
-
Do people here actually like the Oracle database? Especially when compared to SQL Server. It seems everything I did so easily in SQL Server seems to be difficult or even impossible in Oracle. So far I like the for loops and the some_table.some_column%type type declarations. I'm not looking for Oracle hate or a religious war. I really just want to know what's so great about Oracle so I can enjoy it too (so far it's been mostly frustration).
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
It is maybe the order I learned things, but I do not find Oracle that difficult (my first was DB2 on mainframe), but it seems to be less advanced than MSSQL these days, so can't see any reason to bother with in the first place (it even seems to me that most free RDBMS offer more than Oracle for some problems)... So. No good words for Oracle...
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
-
You can think of pipelined functions in the same way as
yield returnin C#. Flashback really is the best thing since sliced bread. It's a bit of a performance drain, but still faster than SQL Server though. Ever wondered why you get the query time in seconds in SSMS and in ms from Oracle? ;PSander Rossel wrote:
Unfortunately, due to awful tooling from Oracle
Well, I certainly give you that one, SSIS is simply awesome despite some quirks.
Sander Rossel wrote:
you're actual DBA's
I'm not, I'm both a backend developer and database developer. No admin in my title to be found.
Sander Rossel wrote:
but that's not very interesting to the average developer?
That's maybe where the problem is. People don't understand what's going on in the databases, and SQL-Server is much better integrated with Visual Studio so it's really quite hidden from people. I once saw someone here comment that the biggest problem with Access is that it makes it to easy for people that don't know what they are doing to create a database. Well that statement really holds a bit of truth for SQL Server as well. That's also why we're seeing these reactionist NOSQL databases which is really bringing us back to the sixties. BTW, one of the big differences between PL-SQL and T-SQL is that there is a lot of implicit stuff going on in T-SQL. In PL-SQL you have to define everything. And that's probably why you thought you can only have one return set from PL-SQL.
Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
Ever wondered why you get the query time in seconds in SSMS and in ms from Oracle?
I never got a timeout in Oracle and plenty in SQL Server... :sigh: I SHOULD mention that I haven't worked with Oracle like I have with SQL Server (few months development in Oracle vs. years of development and production in SQL Server).
Jörgen Andersson wrote:
I'm not, I'm both a backend developer and database developer. No admin in my title to be found.
Still, your profile says "Database developer" rather than "Software developer" ;)
Jörgen Andersson wrote:
People don't understand what's going on in the databases
Very true, although my experience is that people don't know what's going on inside C# (or any technology for that matter) as well. Most people are missing theoretical knowledge because that's just not interesting, we're practical and need results :sigh: Just recently a coworker complained that a
SELECT TOP 10 * FROM SomeViewwas very slow so we should move the select statement from the view to an SP and pass in the TOP 10 as parameter to that SP. Yes, because that's certainly going to make it a lot faster... :~ (his idea was actually that the entire view was selected (and the view didn't have a top 10) and that the top 10 was applied after that).Jörgen Andersson wrote:
That's also why we're seeing these reactionist NOSQL databases which is really bringing us back to the sixties.
I have to disagree there! I'm no NoSQL expert, but I really think NoSQL databases solve actual problems and can be successfully leveraged to create highly performant systems where traditional relational databases would fail miserably. Of course you still have to know what you're actually doing.
Jörgen Andersson wrote:
that's probably why you thought you can only have one return set from PL-SQL
Nope, I actually want to see exactly this[^] in SQL Developer, but it's impossible :)
-
Schema is used for example to define an ownership and to separate logically different components into separate areas, in both systems. A separate database in SQL Server is very different from schema.
Exactly. Correct me if I am wrong, but it is usually just "one" database in Oracle with _n_Number of schemas.
-
Slacker007 wrote:
schemas (oracle)
SQL Server has schemas too[^]. They used to be tied to user accounts in SQL 2000, but that was fixed in SQL 2005[^].
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Thanks, Richard but schemas in SQL Server are a bit different than in Oracle, as Mika and Sander pointed out.
-
Mika Wendelius wrote:
If these cause you headache
The headache is actually in doing any of that ad-hoc :laugh: Open up a window in SQL Developer and typing:
DECLARE x NUMBER;
x := 5;
SELECT x FROM DUAL;won't work at all :D
Mika Wendelius wrote:
That's actually a pretty good article, will read it :thumbsup:
Mika Wendelius wrote:
we can take the discussion outside, for example to Q&A
I could create a question so that you specifically could answer it :laugh: I don't have access to Oracle right now though, so I can't post anything at the moment :)
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
This works ... :doh:
DECLARE
x NUMBER;
y NUMBER;
BEGIN
x := 5;
SELECT x into y FROM DUAL;
dbms_output.put_line(y);
END;:-D
The environment that nurtures creative programmers kills management and marketing types - and vice versa. - Orson Scott Card
Joe Simes wrote:
dbms_output.put_line(y);
I know, but having many fields and rows (or a few with variable length) requires a gigantic put_line which will become unreadable. I really want my result to be in a table :D
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
I actually have a theory that PL/SQL actually stands for Polish SQL (as in Poland, the country). First, it's like Polish, I don't understand a word of what they say. Second, Polish are to us what Mexicans are to Americans, cheap labor (stealing our jobs)*. Larry just hired some cheap Polish people and let them make a database, hence Oracle and PL/SQL was born :D *Not particularly my opinion, no offence to Polish and Mexicans :D
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
Why do the Poles always get a bad rap, no matter what country is speaking about them? I always found that interesting.
-
Jörgen Andersson wrote:
it's a little bit like going from VB to C#
I actually did just that (VB.NET mind you!) and all the glorious stories I heard, about C# being the better and prettier language, were proven not true. It seems idiots write in C# as well :sigh: And indeed, I went from a SQL Server team to an Oracle team and suddenly everyone uses
select ... from table1, table2 where table1.id = table2.table1_idto join tables... :sigh: Not what I was expecting from the grande Oracle database (that, and everything else in this topic) ;pRead my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
Teaching old dogs new tricks. Well I rarely use implicit joins, and do you know why, you can't decide the order of the joins (outer) if they're implicit. Better get used to a consistent way of doing things. One reason that the old people use implicit joins is that on Oracle the optimizer was for a long time better at handling implicit joins. But sometimes when I don't get the performance I expect I try implicit joins instead of ANSI joins. It's all about the order of execution. (I'm having an article about that on the way)
Wrong is evil and must be defeated. - Jeff Ello
-
Eddy Vluggen wrote:
You're over 15 years too late to 'enjoy' the Oracle database-server.
Thought as much :sigh:
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
