August 31, 2010 at 7:53 pm
Found some resources, SQL Reference Guide for Oracle 10g. Guess what, Oracle actually has CTE's, they just don't call it that in the reference manual.
Having access to the reference manual is showing me that I can actually use some of the things I have learned here on SSC.
Still looking forward to seeing if I can push for moving toward SQL Server, but we'll have to wait and see.
Now, if they would just get me access to an instance of Oracle.
August 31, 2010 at 8:05 pm
I've downloaded a free copy of Oracle before (kind of like SQL Express). This is the link to their downloads.
Edit: and this is the link to the 10g free version.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 4, 2010 at 5:51 pm
Lynn Pettis (8/31/2010)
Found some resources, SQL Reference Guide for Oracle 10g. Guess what, Oracle actually has CTE's, they just don't call it that in the reference manual.Having access to the reference manual is showing me that I can actually use some of the things I have learned here on SSC.
Still looking forward to seeing if I can push for moving toward SQL Server, but we'll have to wait and see.
Now, if they would just get me access to an instance of Oracle.
Way to go Lynn.
All Oracle documentation is free and public, if interested in Ora10g please check http://www.oracle.com/pls/db102/portal.all_books
In regards to licensing, Oracle is free for educational purposes so you can just go to Oracle and download a full version of it. If 10g is your target go for Ora10gR2
Having said that, I would personally go for Ora11g
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 4, 2010 at 6:44 pm
Lynn Pettis (8/31/2010)
Guess what, Oracle actually has CTE's, they just don't call it that in the reference manual.
True enough. And search for WITH won't do it either. They call it "Subquery ReFactoring".
Having access to the reference manual is showing me that I can actually use some of the things I have learned here on SSC.
There will be so much more that you won't be able to apply. Try overwriting a variable using a pseudo-cursor. Try writing a set based trigger. And wait until you discover the joys of the UPDATE statement in Oracle... you need to get really, really good at using correlated subqueries to do any joined UPDATEs in Oracle. If you have a late enough version, forget all about using UPDATE and just jump straight to MERGE for doing updates even if you don't need to do an "upsert".
Still looking forward to seeing if I can push for moving toward SQL Server, but we'll have to wait and see.
Heh... ask their Oracle DBA's about that. Of course, that's if you can get their attention on the subject. Most of the Oracle DBA's I've run across still think SQL Server is a toy to be ignored especially for "enterprise class applications". There are, of course, exceptions...
Now, if they would just get me access to an instance of Oracle.
If you want to get good at it, you have to do the same thing that we've all done with SQL Server... get a DEV copy. However, when you install Oracle on a desktop, I'd make it a dedicated desktop. Besure to save all the original build disks you get with the computer, as well. You'll eventually need them to do a rebuild of the system (according to my small bit of experience in that area).
I have to ask... with as good as you were in SQL Server, why on Earth did you take an Oracle-centric position?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2010 at 6:58 pm
PaulB-TheOneAndOnly (9/4/2010)
Lynn Pettis (8/31/2010)
Found some resources, SQL Reference Guide for Oracle 10g. Guess what, Oracle actually has CTE's, they just don't call it that in the reference manual.Having access to the reference manual is showing me that I can actually use some of the things I have learned here on SSC.
Still looking forward to seeing if I can push for moving toward SQL Server, but we'll have to wait and see.
Now, if they would just get me access to an instance of Oracle.
Way to go Lynn.
All Oracle documentation is free and public, if interested in Ora10g please check http://www.oracle.com/pls/db102/portal.all_books
In regards to licensing, Oracle is free for educational purposes so you can just go to Oracle and download a full version of it. If 10g is your target go for Ora10gR2
Having said that, I would personally go for Ora11g
I would, but I really should work with what we are using on the government contract. Depending on some of the other projects they may have work, I may try to bring in SQL Server. When working with moving data between databases (ETL), SSIS may come in handy. For one process there is currently over 60,000 lines of PL/SQL code. Unfortunately, I may not see that code for a while, as I believe it is proprietary to the subcontractor on that other project that is still in development.
September 4, 2010 at 8:05 pm
What version is the government contract using, Lynn?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2010 at 8:32 pm
We are using 8g and 10g. From what has been discussed, one of the databases will be moving from 8g to 10g. This is to eliminate duplicate data for the database. For development purposes on another project, a copy of the database was made in 10g. Since it was determined to would be easier to relink an Access front end from 8g to 10g rather than integrate the 10g databasse back to the 8g database, that is the direction we are going. At least then the two databases will be on the same server and integrated together in the new app that will replace the Access front end and a manual request process.
September 4, 2010 at 8:35 pm
I can say that I know they are using MS SQL Server locally, unless someone has learned how to run SharePoint with an Oracle backend.
September 5, 2010 at 9:04 am
Lynn Pettis (9/4/2010)
We are using 8g and 10g.
I assume you meant 8i. Oracle v8 is actually two very different animals - what we call 8 - a transitional version getting away from Oracle 7 basic concepts, then 8.1.7 which is the foundation of 9i.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 5, 2010 at 9:06 am
Jeff Moden (9/4/2010)
Heh... ask their Oracle DBA's about that. Of course, that's if you can get their attention on the subject. Most of the Oracle DBA's I've run across still think SQL Server is a toy to be ignored especially for "enterprise class applications". There are, of course, exceptions...
Please count me as one of the exceptions 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 5, 2010 at 1:09 pm
Lynn Pettis (9/4/2010)
We are using 8g and 10g. From what has been discussed, one of the databases will be moving from 8g to 10g. This is to eliminate duplicate data for the database. For development purposes on another project, a copy of the database was made in 10g. Since it was determined to would be easier to relink an Access front end from 8g to 10g rather than integrate the 10g databasse back to the 8g database, that is the direction we are going. At least then the two databases will be on the same server and integrated together in the new app that will replace the Access front end and a manual request process.
"8" doesn't have MERGE. If you have to write joined updates, you will need to use the correlated subquery method. And, you need to do it both in the update list and in the WHERE clause for performance... otherwise, your generally updating the whole table.
I've got a couple of tricks on disk somewhere about such things has how to build a "Tally Table" on the fly in Oracle... I'll see if I can still find them for you.
The other thing you need to be keenly aware of is that Oracle doesn't (unless something has changed) return result sets directly to a GUI. Instead, you need to build a "Reference Cursor" and those are best built in what they refer to as a "Package" using a "Global Reference Cursor". Packages are a bit of a pain to program but they do allow you to "group" stored procedures and functions into logical units. I'm still not sure I like that but some swear by it.
Think of the above as "reading suggestions".
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2010 at 1:35 pm
Jeff Moden (9/5/2010)
Lynn Pettis (9/4/2010)
We are using 8g and 10g. From what has been discussed, one of the databases will be moving from 8g to 10g. This is to eliminate duplicate data for the database. For development purposes on another project, a copy of the database was made in 10g. Since it was determined to would be easier to relink an Access front end from 8g to 10g rather than integrate the 10g databasse back to the 8g database, that is the direction we are going. At least then the two databases will be on the same server and integrated together in the new app that will replace the Access front end and a manual request process."8" doesn't have MERGE. If you have to write joined updates, you will need to use the correlated subquery method. And, you need to do it both in the update list and in the WHERE clause for performance... otherwise, your generally updating the whole table.
I've got a couple of tricks on disk somewhere about such things has how to build a "Tally Table" on the fly in Oracle... I'll see if I can still find them for you.
The other thing you need to be keenly aware of is that Oracle doesn't (unless something has changed) return result sets directly to a GUI. Instead, you need to build a "Reference Cursor" and those are best built in what they refer to as a "Package" using a "Global Reference Cursor". Packages are a bit of a pain to program but they do allow you to "group" stored procedures and functions into logical units. I'm still not sure I like that but some swear by it.
Think of the above as "reading suggestions".
Not using packages in the project I will becoming the database developer. The individual currently there is a SQL Server type, the more he works with PL/SQL the more he misses T-SQL. Unfortunately, he talks a lot about cursors and iterating through results sets. Looking at some code already written, I'm thinking I can make some improvements. Talked with my manager Friday, going to go to the Lead Engineer and just say it's time to get dirty and start working in the project itself instead of waiting for a sandbox to start learning.
September 16, 2010 at 7:44 am
Jeff Moden (9/5/2010)... you need to build a "Reference Cursor" and those are best built in what they refer to as a "Package" using a "Global Reference Cursor". Packages are a bit of a pain to program but they do allow you to "group" stored procedures and functions into logical units.
You are correct Jeff, nothing has changed in that department and reference-cursors are the way to expose the returned dataset to the application.
In regards to packages, the logic behind is to load into memory -at once- all the code you need to perform some activity. This can backfire if packages are not well designed, like putting in the same package the procedures you need for night batch processes as well as the procedures you need for daily-hours quering.
If in doubt, don't use packages and go for individual storedprocs and functions which are also supported.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 17, 2010 at 5:07 am
Jeff Moden (9/4/2010)
Lynn Pettis (8/31/2010)
Guess what, Oracle actually has CTE's, they just don't call it that in the reference manual.True enough. And search for WITH won't do it either. They call it "Subquery ReFactoring".
I believe they called it this because this was what it was commonly known as in the SQL:1999 standard... which Microsoft only ran with in SQL Server 2005...
Jeff Moden (9/4/2010)
Lynn Pettis (8/31/2010)
Having access to the reference manual is showing me that I can actually use some of the things I have learned here on SSC.
There will be so much more that you won't be able to apply. Try overwriting a variable using a pseudo-cursor. Try writing a set based trigger. And wait until you discover the joys of the UPDATE statement in Oracle... you need to get really, really good at using correlated subqueries to do any joined UPDATEs in Oracle. If you have a late enough version, forget all about using UPDATE and just jump straight to MERGE for doing updates even if you don't need to do an "upsert".
If I may interject... 🙂
A few things:
1. If by "set based trigger" you mean that you can interate over every row that is updated, then you can iuse the "for each row" clause and use the :new or :old row tables to do inserts. Not sure what the performance is like compared to SQL Server's do it in one shot solution, but if anything it's more flexible than SQL Server's approach.
2. If you mean by a "joined" update you are missing the FROM clause, have a look at the following link: http://geekswithblogs.net/WillSmith/archive/2008/06/18/oracle-update-with-join-again.aspx
In other words, you can do the same thing in Oracle, only with different syntax. In fact, you can do many, many more things in the UPDATE and DELETE statements along these lines - check the UPDATE documentation and the DELETE documentation.
Jeff Moden (9/4/2010)
Lynn Pettis (8/31/2010)
Still looking forward to seeing if I can push for moving toward SQL Server, but we'll have to wait and see.
Heh... ask their Oracle DBA's about that. Of course, that's if you can get their attention on the subject. Most of the Oracle DBA's I've run across still think SQL Server is a toy to be ignored especially for "enterprise class applications". There are, of course, exceptions...
That is indeed somewhat infuriating, but sometimes I get their point, because:
1. Oracle runs on more platforms,
2. Oracle has implemented more of the SQL standard that Microsoft. Case in point: try doing a lag, lead, or top analytic function in SQL Server... you can't! You can only use rank, denserank, and row_number. If you were able to use lag(column) over () then I'm almost certain we wouldn't need to be using non-supported clustered index approaches to running total aggregates... though we wouldn't have had one of the coolest articles around on this approach, of course 🙂
3. Oracle RAC - enough said
4. Temporary tablespaces can be assigned to users - try THAT in SQL Server (TempDB anyone?)
5. Oracle has a sophisticated CBO - sadly, hard to get your head around, unlike SQL Server.
But then I look at the way you need to get execution plans, and I always think to myself - "I'd rather do that in SQL Server!".
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply