|
|
This works ... 
DECLARE
x NUMBER;
y NUMBER;
BEGIN
x := 5;
SELECT x into y FROM DUAL;
dbms_output.put_line(y);
END;
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
|
|
|
|
|
DECLARE
CURSOR c is SELECT * FROM DUAL;
r dual%ROWTYPE;
d dual.dummy%TYPE;
BEGIN
open c;
fetch c into r;
d := r.dummy;
dbms_output.put_line(d);
END;
The environment that nurtures creative programmers kills management and marketing types - and vice versa. - Orson Scott Card
|
|
|
|
|
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#.
|
|
|
|
|
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...
|
|
|
|
|
Glad to be of service. 
|
|
|
|
|
|
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)
|
|
|
|
|
Jörgen Andersson wrote: 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.
I actually read that today 
The reason I don't like it is because it really obscures your where clause and invites bugs because you forgot to join two tables in the where clause (actually happened today, that's why I read about it).
It shouldn't matter for your performance though, so I was pretty surprised it DOES matter for performance in Oracle (and not in the way that you want either).
|
|
|
|
|
The best part of Oracle is that I rarely have to use it anymore.
I even hate the Gigs of pollution you had to install just to talk damned thing. 
So now when I have to talk to it from VS I just add one file to my project (Oracle.ManagedDataAccess.dll) and then have all the access I need.
The bare minimum
|
|
|
|
|
RossMW wrote: The bare minimum
Still too much
|
|
|
|
|
Yep. 
|
|
|
|
|
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.
|
|
|
|
|
|
You can think of pipelined functions in the same way as yield return in 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?
Sander 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.
|
|
|
|
|
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... 
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
Just recently a coworker complained that a SELECT TOP 10 * FROM SomeView was 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
|
|
|
|
|
Sander Rossel wrote: I never got a timeout in Oracle and plenty in SQL Server.
Probably a setting though.
Sander Rossel wrote: 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)
He might be right you know, check the plan.
That's also why you should avoid views with joins as the plague! I never use views for anything but restricting access or prefiltering tables.
modified 18-Oct-16 17:54pm.
|
|
|
|
|
Jörgen Andersson wrote: He might be right you know, check the plan.
HE MIGHT be right that an SP would be faster, but not because the TOP 10 is applied after selecting everything from a view 
I did check the plan and it was really pretty hopeless.
Basically the view was this:
SELECT SomeFields
FROM BigTable
WHERE ...
UNION
SELECT SomeOtherFields
FROM BigTable
WHERE ...
Now to get that to work we probably DO need an SP because to get the top 10 the selects on both sides of the union needed to be completely evaluated, then sorted, and then top 10'd.
I wouldn't really know how to optimize that in a view. It's probably faster to take both top 10's, union and sort those and take another top 10
|
|
|
|
|
Sander Rossel wrote: , but not because the TOP 10 is applied after selecting everything from a view
Sander Rossel wrote: because to get the top 10 the selects on both sides of the union needed to be completely evaluated, then sorted, and then top 10'd.
That's a bit contradicting you know.
Sander Rossel wrote: It's probably faster to take both top 10's, union and sort those and take another top 10
That's it.
And that's exactly why I avoid views. You're forgetting what's happening underneath.
|
|
|
|
|
Jörgen Andersson wrote: That's a bit contradicting you know.
You can't say "a TOP X is applied after selecting everything from a view" for views in general.
It's true for this particular view, but you only know that after looking at the view. Besides, the WHERE clause was still applied even before "selecting everything"
|
|
|
|
|
It doesn't have anything to do with the view, it's because the TOP is the last thing to happen in the logical order of execution.
|
|
|
|
|
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.
|
|
|
|
|
Kornfeld Eliyahu Peter wrote: So. No good words for Oracle...
It's pretty hard to get some praise for Oracle it seems...
|
|
|
|
|
I do see a number of benefits to Oracle.
It is really good at crunching really huge volumes.
It commands extraordinary rates for consultants
It takes a DBA to maintain it
I think it requires a larger hardware footprint.
It is an absolute f***ing pain in the arse to develop against.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
Not really something a dev would need though 