Oracle for SQL Server guys?
-
Hello...
I thought this might be a good place to ask... I am working on a Project where I am forced to use Oracle as the DB. (As a PURE MS Guy) I am a SQL Server developer/DBA and I need a reference for P/L SQL thats listing the major differences between Oracle and Microsoft SQL.
Even simple querries frustrate me right now ;)
-
[quote user="rdrunner"]
Hello...
I thought this might be a good place to ask... I am working on a Project where I am forced to use Oracle as the DB. (As a PURE MS Guy) I am a SQL Server developer/DBA and I need a reference for P/L SQL thats listing the major differences between Oracle and Microsoft SQL.
Even simple querries frustrate me right now ;)
[/quote]
PL/SQL is a programming language (used for triggers, stored procedures etc.), comparable to TSQL; you probably won't need it. If you need it, you have to learn it. (Listing the differences between PL/SQL and TSQL is about as usefull as listing the differences between VB and Java)
-
There is a Book called "SQL Server for Oracle DBAs" ...
I was wondering if there is a "reverse version"...
And most likely i have to implement my owns SPs in this project... So a "quick overview" would have been nice (I know they are 2 different lanuages, and that they cant be interchanged with a snap... But I am looking for a resource that will skip all the most fundamental stuff since I am already a DBA and point out the mayor differences)
-
[quote user="rdrunner"]
There is a Book called "SQL Server for Oracle DBAs" ...
I was wondering if there is a "reverse version"...
And most likely i have to implement my owns SPs in this project... So a "quick overview" would have been nice (I know they are 2 different lanuages, and that they cant be interchanged with a snap... But I am looking for a resource that will skip all the most fundamental stuff since I am already a DBA and point out the mayor differences)
[/quote]
Oracle's PL/SQL is much more than just another little script language to write little triggers and stored procedures in. Of course you can do that, but you can also write complete applications in PL/SQL. Crazy people like me do that. PL/SQL and TSQL have different syntax and different concepts. If you plan to use it, better learn it thoroughly, or your work might be featured here. There are too many things that can be done wrong in PL/SQL, like in any other language.
-
Well that answer was totally useless.
Q. Excuse me sir, when is the bus coming?
A. If you want to ride the bus, you first must be throughly learn the bus routes and time tables. You can ride the bus to a lot of different places; I frequently ride it to places that people really shouldn't go. If you don't learn the about this stuff, you could get on the wrong bus and people will mock you.
-
I haven't gone wrong with O'Reilly books:
http://www.oreilly.com/pub/topic/oracle
There are several PL/SQL books (since in later posts you say that you will need to use PL/SQL). This one isn't ... "for MS SQL users", but seems promising:
http://www.oreilly.com/catalog/oraclep4/
One that I have perused at my local book megacenter is this one, which does highlight differences between MySQL, Oracle, and MS SQL:
http://www.oreilly.com/catalog/sqlnut2/
Also, if you want a great tool that thoroughly helps when investigating an Oracle database, go and get the freely available from Oracle tool SQL Developer:
http://www.oracle.com/technology/products/database/sql_developer/index.html
It's written in Java, and you can get Linux, Solaris, and Win32 distributions. You have to create an Oracle username and password, but, again, that's a free process.
-
[quote user="Grauenwolf"]
Well that answer was totally useless.
Q. Excuse me sir, when is the bus coming?
A. If you want to ride the bus, you first must be throughly learn the bus routes and time tables. You can ride the bus to a lot of different places; I frequently ride it to places that people really shouldn't go. If you don't learn the about this stuff, you could get on the wrong bus and people will mock you.
[/quote]
Execpt that using Oracle is not as easy as riding a bus.
Q: Excuse me sir, I want to fly this plane, where is the ignition key?
A: If you want to fly a plane, you must get a pilot license. Some experience in driving a car is not enough.
-
[quote user="ammoQ"][quote user="Grauenwolf"]
Well that answer was totally useless.
Q. Excuse me sir, when is the bus coming?
A. If you want to ride the bus, you first must be throughly learn the bus routes and time tables. You can ride the bus to a lot of different places; I frequently ride it to places that people really shouldn't go. If you don't learn the about this stuff, you could get on the wrong bus and people will mock you.
[/quote]
Execpt that using Oracle is not as easy as riding a bus.
Q: Excuse me sir, I want to fly this plane, where is the ignition key?
A: If you want to fly a plane, you must get a pilot license. Some experience in driving a car is not enough.
[/quote]
Using Oracle as a coder that's sending SQL and retrieving values isn't that difficult. I won't deny that installing (what were they thinking with that install procedure... linking during install? Gah) and properly adminning aren't easy, but Oracle does freely provide lots of documentation that leads you through it all. Our original poster already has db concepts down, but needs "How do I do XXX in Oracle". That's not such a simpleminded request, I don't think.
-
[quote user="sinistral"]
Using Oracle as a coder that's sending SQL and retrieving values isn't that difficult. I won't deny that installing (what were they thinking with that install procedure... linking during install? Gah) and properly adminning aren't easy, but Oracle does freely provide lots of documentation that leads you through it all. Our original poster already has db concepts down, but needs "How do I do XXX in Oracle". That's not such a simpleminded request, I don't think.
[/quote]
Sending SQL and retrieving values isn't that difficult, I aggree; in fact, a simple "select" statement should work exaclty the same way like it does with other database systems. Anyway, in the "How do I do XXX in Oracle" question, the value range for XXX is very large, ranging from "How do I create autoincrement colmnns in Oracle?" to "How do I access LDAP servers in Oracle?" A reference of all differences between SQLSever and Oracle is about the same like a list of all features in Oracle.
-
[quote user="ammoQ"][quote user="sinistral"]
Using Oracle as a coder that's sending SQL and retrieving values isn't that difficult. I won't deny that installing (what were they thinking with that install procedure... linking during install? Gah) and properly adminning aren't easy, but Oracle does freely provide lots of documentation that leads you through it all. Our original poster already has db concepts down, but needs "How do I do XXX in Oracle". That's not such a simpleminded request, I don't think.
[/quote]
Sending SQL and retrieving values isn't that difficult, I aggree; in fact, a simple "select" statement should work exaclty the same way like it does with other database systems. Anyway, in the "How do I do XXX in Oracle" question, the value range for XXX is very large, ranging from "How do I create autoincrement colmnns in Oracle?" to "How do I access LDAP servers in Oracle?" A reference of all differences between SQLSever and Oracle is about the same like a list of all features in Oracle.
[/quote]
Unless you want to limit the number of rows returned. That appears to be different in every database.
For example:
SQL Server's
SELECT TOP 10 column, column2 FROM table
is the following in Oracle:
SELECT column, column2 FROM table WHERE ROWNUM <= 10
-
Thanks for the Book list. I will have a closer look at them.
For the scope of my question... I think that I will have to create all the tables I will use, implement a few triggers, several SPROCs for some crud work, since I want to handle my ORM mapping inside those. Also the Authorisation, but that was straight foreward. Not sure if i also have to define the indexes yet, or if thats the job of the DBA of the target system...
Here is one question i have "left open" about SP'S (insert,update and delete are simple):
I have to tables A & B and I want to write one querry that will return all needed information from BOTH tables Basically in SQL Server i would "just" do
Select c1,c2,c3 from TableA where...
Select c4,c5,c6 from Table B where...
How can I write a SP that will return me 2 tables at once? (P.s.: No i DONT want to join the 2 tables since Table A is very wide and table B contains MANY rows and the amount of transfered data should stay low since we have a bandwith limitation)
-
[quote user="powerlord"][quote user="ammoQ"][quote user="sinistral"]
Using Oracle as a coder that's sending SQL and retrieving values isn't that difficult. I won't deny that installing (what were they thinking with that install procedure... linking during install? Gah) and properly adminning aren't easy, but Oracle does freely provide lots of documentation that leads you through it all. Our original poster already has db concepts down, but needs "How do I do XXX in Oracle". That's not such a simpleminded request, I don't think.
[/quote]
Sending SQL and retrieving values isn't that difficult, I aggree; in fact, a simple "select" statement should work exaclty the same way like it does with other database systems. Anyway, in the "How do I do XXX in Oracle" question, the value range for XXX is very large, ranging from "How do I create autoincrement colmnns in Oracle?" to "How do I access LDAP servers in Oracle?" A reference of all differences between SQLSever and Oracle is about the same like a list of all features in Oracle.
[/quote]
Unless you want to limit the number of rows returned. That appears to be different in every database.
For example:
SQL Server's
SELECT TOP 10 column, column2 FROM table
is the following in Oracle:
SELECT column, column2 FROM table WHERE ROWNUM <= 10
[/quote]Beware, it's not exactly the same. (AFAIK)
SQLServer's "SELECT TOP 10 foo FROM bar ORDER BY foo" will give you the 10 lowest values of foo (in ascending order).
Oracles "SELECT foo FROM bar WHERE ROWNUM<=10 ORDER BY foo" will give you 10 arbitrary values of foo (in ascending order).To get the 10 lowest values in Oracle, you have to write the more elaborate statement
SELECT foo FROM (SELECT foo FROM bar ORDER BY foo) WHERE ROWNUM<=10
-
[quote user="rdrunner"]
Thanks for the Book list. I will have a closer look at them.
For the scope of my question... I think that I will have to create all the tables I will use, implement a few triggers, several SPROCs for some crud work, since I want to handle my ORM mapping inside those. Also the Authorisation, but that was straight foreward. Not sure if i also have to define the indexes yet, or if thats the job of the DBA of the target system...
Here is one question i have "left open" about SP'S (insert,update and delete are simple):
I have to tables A & B and I want to write one querry that will return all needed information from BOTH tables Basically in SQL Server i would "just" do
Select c1,c2,c3 from TableA where...
Select c4,c5,c6 from Table B where...
How can I write a SP that will return me 2 tables at once? (P.s.: No i DONT want to join the 2 tables since Table A is very wide and table B contains MANY rows and the amount of transfered data should stay low since we have a bandwith limitation)
[/quote]
In Oracle, you use so-called ref cursors to return "result sets" (rather: iterators over result sets) from stored procedures.
More info here: http://www.oracle-base.com/articles/8i/UsingRefCursorsToReturnRecordsets.php
Another way to handle similar problems is the use of table functions and pipelined functions: these are functions that act as if they were tables, i.e. you can use a select statement to retreive the data.
More info here: http://www.oracle-base.com/articles/9i/PipelinedTableFunctions9i.php
-
Thanks...
At least someone who gives resonable answers ;)
So when I do a select SP i only need to pass one output parameter per table I want to return?
Also... Whats that bean bag girl in your sig?
-
[quote user="rdrunner"]
Thanks...
At least someone who gives resonable answers ;)
So when I do a select SP i only need to pass one output parameter per table I want to return?
[/quote]
Yes. You could also write a function that returns the ref cursor.
Also... Whats that bean bag girl in your sig?
-
[quote user="ammoQ"][quote user="powerlord"]
Unless you want to limit the number of rows returned. That appears to be different in every database.
For example:
SQL Server's
SELECT TOP 10 column, column2 FROM table
is the following in Oracle:
SELECT column, column2 FROM table WHERE ROWNUM <= 10
[/quote]Beware, it's not exactly the same. (AFAIK)
SQLServer's "SELECT TOP 10 foo FROM bar ORDER BY foo" will give you the 10 lowest values of foo (in ascending order).
Oracles "SELECT foo FROM bar WHERE ROWNUM<=10 ORDER BY foo" will give you 10 arbitrary values of foo (in ascending order).To get the 10 lowest values in Oracle, you have to write the more elaborate statement
SELECT foo FROM (SELECT foo FROM bar ORDER BY foo) WHERE ROWNUM<=10
[/quote]
I don't use Oracle a lot, can you tell? I always thought the whole ROWNUM thing seemed rather silly, now I actually have a valid reason to hate them. Thank you.
-
[quote user="powerlord"][quote user="ammoQ"][quote user="powerlord"]
Unless you want to limit the number of rows returned. That appears to be different in every database.
For example:
SQL Server's
SELECT TOP 10 column, column2 FROM table
is the following in Oracle:
SELECT column, column2 FROM table WHERE ROWNUM <= 10
[/quote]Beware, it's not exactly the same. (AFAIK)
SQLServer's "SELECT TOP 10 foo FROM bar ORDER BY foo" will give you the 10 lowest values of foo (in ascending order).
Oracles "SELECT foo FROM bar WHERE ROWNUM<=10 ORDER BY foo" will give you 10 arbitrary values of foo (in ascending order).To get the 10 lowest values in Oracle, you have to write the more elaborate statement
SELECT foo FROM (SELECT foo FROM bar ORDER BY foo) WHERE ROWNUM<=10
[/quote]
I don't use Oracle a lot, can you tell? I always thought the whole ROWNUM thing seemed rather silly, now I actually have a valid reason to hate them. Thank you.
[/quote]read up
http://www.adp-gmbh.ch/ora/sql/agg/index.html
http://www.oracle-base.com/articles/misc/RankDenseRankFirstLastAnalyticFunctions.php
-
[quote user="powerlord"]
I don't use Oracle a lot, can you tell? I always thought the whole ROWNUM thing seemed rather silly, now I actually have a valid reason to hate them. Thank you.
[/quote]And the reason for that would be? ROWNUM is just another method to limit a result set, that's all.
l.
-
[quote user="lofwyr"]
[quote user="powerlord"]
I don't use Oracle a lot, can you tell? I always thought the whole ROWNUM thing seemed rather silly, now I actually have a valid reason to hate them. Thank you.
[/quote]And the reason for that would be?
[/quote]
Because the combination of "ROWNUM" and "ORDER BY" works in a moronic way?
-
[quote user="ammoQ"]
Because the combination of "ROWNUM" and "ORDER BY" works in a moronic way?
[/quote]
Does it? Can't see anything of it, but then I do read documentation (and asktom).
l.
-
[quote user="lofwyr"][quote user="ammoQ"]
Because the combination of "ROWNUM" and "ORDER BY" works in a moronic way?
[/quote]
Does it? Can't see anything of it, but then I do read documentation (and asktom).
l.
[/quote]
The way it works makes no sense. I can see a lot of reasons why "select x from y where rownum<=10 order by x desc" should give the 10 largest values of x. Many people need queries like that. Can you imagine a situation when someone would be satisfied with 10 arbitrary values, nicely sorted? I can't.
-
[quote user="ammoQ"]
The way it works makes no sense. I can see a lot of reasons why "select x from y where rownum<=10 order by x desc" should give the 10 largest values of x. Many people need queries like that. Can you imagine a situation when someone would be satisfied with 10 arbitrary values, nicely sorted? I can't.
[/quote]
Again, it's all in the documentation. http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns009.htm#sthref832
And yes, according to some requests in OTN forum, some people seem to need just 10 arbitrary values. And what about other clauses, such as GROUP BY? If we wanted to preselect a result set before using any of the mentioned clauses, we'd have to use it the other way. Oracle just went for the this way, that's all. I say, the way ROWNUM works in combination with inline views offers the necessary flexibility. But I'll just point to this article http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html since I consider this a discussion about taste, not technology.
l.
-
I would bet that more people have a need to return the X largest / smallest numbers out of the entire set rather than the need to return X random values from the set sorted. So Oracle implemented the least valuable of the two cases by default which doesn't make sense.
Granted it's in the documentation, but that doesn't automatically make it clear or appropriate. That's like saying if I implement my own complex number class and overload the "-" operator to do addition and the "+" operator to do subtraction it is okay as long as I document it. That's just stupid.
-
[quote user="lpope187"]
I would bet that more people have a need to return the X largest / smallest numbers out of the entire set rather than the need to return X random values from the set sorted. So Oracle implemented the least valuable of the two cases by default which doesn't make sense.
[/quote]
This may or may not be the case - but a bet is hardly a technical argument. And for finding the largest/smallest numbers, there are other methods since 8i EE: analytic functions.
[quote user="lpope187"]
Granted it's in the documentation, but that doesn't automatically make it clear or appropriate. That's like saying if I implement my own complex number class and overload the "-" operator to do addition and the "+" operator to do subtraction it is okay as long as I document it. That's just stupid.
[/quote]
I don't know about you, but it isn't unclear or inapporpriate to me. Yes, Oracle does have it's irks (and so does any other software), but ROWNUM would be the least of my worries. ROWNUM = 2 / ROWNUM > 1 doesn't work either, but the only thing I'd call stupid is the people that refuse the documentation that explains why.
l.
-
[quote user="lofwyr"][quote user="lpope187"]
I would bet that more people have a need to return the X largest / smallest numbers out of the entire set rather than the need to return X random values from the set sorted. So Oracle implemented the least valuable of the two cases by default which doesn't make sense.
[/quote]
This may or may not be the case - but a bet is hardly a technical argument. And for finding the largest/smallest numbers, there are other methods since 8i EE: analytic functions.
[/quote]
"may or may not be the case"? You must be joking.
[quote user="Ask Tom"]
That is why a query in the following form is almost certainly an error:select *
from emp
where ROWNUM <= 5
order by sal desc;[/quote]
[quote user="lofwyr"]I don't know about you, but it isn't unclear or inapporpriate to me. Yes, Oracle does have it's irks (and so does any other software), but ROWNUM would be the least of my worries. ROWNUM = 2 / ROWNUM > 1 doesn't work either, but the only thing I'd call stupid is the people that refuse the documentation that explains why.
[/quote]
When 3 out of 4 people get it wrong, because the behaviour is both useless and unintuitive, I think it's fair to say that it is stupid. It might be documented stupidity, but that doesn't make it right. It's hard, if not impossible, to understand why we should expect such an useless and unintuitive behaviour from a database that is clever enough to decide whether or not to use indexes, full table scans, hash joins and what-not to execute a query in the most efficient way.
-
[quote user="ammoQ"][
"may or may not be the case"? You must be joking.
[/quote]
Why should I? Did anybody ever count how many "WHERE ROWNUM = 1" constructs have been used to avoid duplicates and ORDER/GROUP BY doesn't play a role?
When 3 out of 4 people get it wrong, because the behaviour is both useless and unintuitive, I think it's fair to say that it is stupid.
When 3 out of 4 people can't figure out in which order WHERE, ORDER and GROUP BY work, do we have to blame the product?
It might be documented stupidity, but that doesn't make it right. It's hard, if not impossible, to understand why we should expect such an useless and unintuitive behaviour from a database that is clever enough to decide whether or not to use indexes, full table scans, hash joins and what-not to execute a query in the most efficient way.
I'm afraid we're in the middle of a taste discussion and I don't have the time for that. Maybe Oracle should provide a LIMIT clause as found in other databases, but they went with the ROWNUM approach. Yes, it does have it's downsides, but it also offers, together with analytic functions a flexibility that surpasses LIMIT, in my opinion. Let's agree to disagree, if nothing else than stupidity or bets show up as arguments.
l.
-
I kinda agree with ammoQ...
Rownum works exactly like coded and it is documented exactly like it works...
But that does not make it "right"... If I need to remove duplicates etc. I have no real need for a rownum function... I cant think of a single querry that would meet the "basic" rownum implementation (Gimme X random and sort those nicely)... So far I was allways asked to find the...slowest, fastest, richest, sold most, etc X things
Can anyone here give me an example of how this can be turned into a "usefull" querry that will really allow me to forget the "9 out of 10" times I had to write extra, useless code to achieve the "expected" behavior? (This means it really has to be a "Nice...." think that I cant do with a "simple" inline querry)
-
[quote user="rdrunner"]
I kinda agree with ammoQ...
Rownum works exactly like coded and it is documented exactly like it works...
[/quote]
Pseudocolumn in a WHERE clause - what else were you expecting? Automagically bypassing the processing order of the different clauses?
But that does not make it "right"... If I need to remove duplicates etc. I have no real need for a rownum function...
Others do. ROWNUM = 1 is quite popular among subselects that have to return 0 or 1 records without any aggregate functions.
I cant think of a single querry that would meet the "basic" rownum implementation (Gimme X random and sort those nicely)... So far I was allways asked to find the...slowest, fastest, richest, sold most, etc X things
One example was questions for a test.
Can anyone here give me an example of how this can be turned into a "usefull" querry that will really allow me to forget the "9 out of 10" times I had to write extra, useless code to achieve the "expected" behavior? (This means it really has to be a "Nice...." think that I cant do with a "simple" inline querry)
There are analytic functions - they work as coded and documented, probably misunderstood by a lot of people and and you've to use inline views if you want to count rows. Just another stupid invention, I guess.
l.
-
[quote user="lofwyr"]
Pseudocolumn in a WHERE clause - what else were you expecting? Automagically bypassing the processing order of the different clauses?
[/quote]
Wouldn't it be nice if Oracle was clever enough to read
select x from y where rownum = 1 order by x;
as
select x from (select x from y order by x) where rownum=1;
One example was questions for a test.
Not random enough (probably returns the same 10 arbitrary choosen rows every time the query is executed), no need to sort.
There are analytic functions - they work as coded and documented, probably misunderstood by a lot of people and and you've to use inline views if you want to count rows. Just another stupid invention, I guess.
IMO analytic functions are an overkill if all I want is "select top 10 * from emp order by sales desc "
-
[quote user="ammoQ"]
Wouldn't it be nice if Oracle was clever enough to read
select x from y where rownum = 1 order by x;
as
select x from (select x from y order by x) where rownum=1;
[/quote]
Even if it would be nice, because of its past implementation, something like this could be dangerous. But maybe, one day, Oracle offers a LIMIT clause.
[quote user="ammoQ"]
Not random enough (probably returns the same 10 arbitrary choosen rows every time the query is executed), no need to sort.
[/quote]
Now we're from no example to not random enough - ORDER BY DBMS_RANDOM could be more appropriate in this case of course. There's still the WHERE ROWNUM = 1 fo 0 or 1 record in subselects.
[quote user="ammoQ"]
IMO analytic functions are an overkill if all I want is "select top 10 * from emp order by sales desc "
[/quote]
Depends on the definition of TOP 10. What if there are 11 employees with the same salary? Or if there are more people on the 10th position with the same salary? But now we're entering the field of analytic functions where Rank() and other functions play a role.
l.
-
[quote user="lofwyr"][quote user="ammoQ"]
Wouldn't it be nice if Oracle was clever enough to read
select x from y where rownum = 1 order by x;
as
select x from (select x from y order by x) where rownum=1;
[/quote]
Even if it would be nice, because of its past implementation, something like this could be dangerous.
[/quote]
Why? Because suddenly it does what the programmer probably expected? Are predictable results dangerous?
Now we're from no example to not random enough - ORDER BY DBMS_RANDOM could be more appropriate in this case of course. There's still the WHERE ROWNUM = 1 fo 0 or 1 record in subselects.
Sorry, but your example is nonsense. If you need randomized results, you cannot use a query that most likely returns the same 10 arbitrarily chosen records every time. "WHERE ROWNUM=1" is ok, but it would be even better if "WHERE ROWNUM=1 ORDER BY x DESC" returned the record where x=max(x).
Depends on the definition of TOP 10. What if there are 11 employees with the same salary? Or if there are more people on the 10th position with the same salary? But now we're entering the field of analytic functions where Rank() and other functions play a role.
In many cases, the sort order is unambigious. If it isn't, and it matters, analytic functions have their place.
-
[quote user="ammoQ"]
Why? Because suddenly it does what the programmer probably expected? Are predictable results dangerous?
[/quote]
Quoting you: That's nonsense - the results are already predictable.
[quote user="ammoQ"]
Now we're from no example to not random enough - ORDER BY DBMS_RANDOM could be more appropriate in this case of course. There's still the WHERE ROWNUM = 1 fo 0 or 1 record in subselects.
Sorry, but your example is nonsense. If you need randomized results, you cannot use a query that most likely returns the same 10 arbitrarily chosen records every time. "WHERE ROWNUM=1" is ok, but it would be even better if "WHERE ROWNUM=1 ORDER BY x DESC" returned the record where x=max(x).
[/quote]
How about sampling data, without the need of randomness and ordering, for example when testing an export function? Or is that nonsense too? And why ORDER BY x DESC when you could use the Max-Function?
[quote user="ammoQ"]
In many cases, the sort order is unambigious. If it isn't, and it matters, analytic functions have their place.
[/quote]
You provided the example with the salaries, right? So why take the risk of an incomplete list, when you can do it right?
l.
-
[quote user="lofwyr"][quote user="ammoQ"]
Why? Because suddenly it does what the programmer probably expected? Are predictable results dangerous?
[/quote]
Quoting you: That's nonsense - the results are already predictable.[/quote]
No they are not. They are repeatable; once you run the query, you know what you will get next time (most likely).
How about sampling data, without the need of randomness and ordering, for example when testing an export function? Or is that nonsense too? And why ORDER BY x DESC when you could use the Max-Function?
If you do not need ordering, the "rownum+order by" problem doesn't strike you. Why "order by x desc?" Because I need the whole row, not just the maximum value of one column.
You provided the example with the salaries, right?
Only because the EMP table is the most well-known example table in the Oracle world.
So why take the risk of an incomplete list, when you can do it right?
There are countless other examples where the sort order is unambigious. For example, imagine log records with a unique ID generated by a sequence; the task is "show me the 10 most recent log entries".
-
[quote user="ammoQ"]
No they are not. They are repeatable; once you run the query, you know what you will get next time (most likely).
[/quote]
No, they are. But we can continue this ad infinitum, I suppose. Don't have the time for this.
[quote user="ammoQ"]
If you do not need ordering, the "rownum+order by" problem doesn't strike you. Why "order by x desc?" Because I need the whole row, not just the maximum value of one column.
[/quote]
Then use an inline view - I was talking about subselects on row level, like SELECT fieldA, (SELECT fieldB) ... .
[quote user="ammoQ"]
Only because the EMP table is the most well-known example table in the Oracle world.
[/quote]
And?
[quote user="ammoQ"]
There are countless other examples where the sort order is unambigious. For example, imagine log records with a unique ID generated by a sequence; the task is "show me the 10 most recent log entries".
[/quote]
Aside from the fact, that using timestamps may also have the same problem as in your salary example (and a sequence doesn't guarantee the right order in parallel inserts), I'd say it's still a let's agree to disagree matter. EOD for me.
l.
-
[quote user="lofwyr"][quote user="ammoQ"]
No they are not. They are repeatable; once you run the query, you know what you will get next time (most likely).
[/quote]
No, they are. But we can continue this ad infinitum, I suppose. Don't have the time for this.
[/quote]
Seems you have an uncommon definition of "predictable". In my understanding, predictable means: Knowing the contents of the table, I can correctly predict the results of the query.
[quote user="ammoQ"]
If you do not need ordering, the "rownum+order by" problem doesn't strike you. Why "order by x desc?" Because I need the whole row, not just the maximum value of one column.
Then use an inline view - I was talking about subselects on row level, like SELECT fieldA, (SELECT fieldB) ... .
[/quote]
By now, everyone who had the patience to follow this thread should already know that there are workarounds around the problem. The topic of this discussion, as far as I can say, is: Why do one need workarounds when Oracle could do it right?
Aside from the fact, that using timestamps may also have the same problem as in your salary example (and a sequence doesn't guarantee the right order in parallel inserts), I'd say it's still a let's agree to disagree matter. EOD for me.
I haven't suggested timestamps. AFAIK, sequences with ORDER and NOCACHE are as good as you can get in Oracle. Anyway, I fail to see how a analytic function could do better when sequences do not guarantee the right order.
-
Ok... Sorry for warming this thread up again...
I am now writing all the SP for our application and I came across a very complex problem... I need to return an address in the Format [street] + [space] + [Number]
So far I am only able to do it with 2 Concats in a row..
Ok this is no "complex" string, but I think this is a bit odd... Is there a way to create Strings by just adding them together?
-
[quote user="rdrunner"]
Ok... Sorry for warming this thread up again...
I am now writing all the SP for our application and I came across a very complex problem... I need to return an address in the Format [street] + [space] + [Number]
So far I am only able to do it with 2 Concats in a row..
Ok this is no "complex" string, but I think this is a bit odd... Is there a way to create Strings by just adding them together?
[/quote]
what's wrong with
select street||' '||number from address?
? I guess you haven't found the || operator (string concatenation) yet...
-
THANKS!
I tried +,&, "nothing" and a few other variants... After looking in some Oracle Documentation about Concatinating, I found the function called
Concat(String1,String2)
And that was ugly :)
