SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Learning Oracle


Learning Oracle

Author
Message
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5337 Visits: 4639
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85036 Visits: 41074
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39011 Visits: 38508
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5337 Visits: 4639
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.
ta.bu.shi.da.yu
ta.bu.shi.da.yu
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 494
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!".

Random Technical Stuff
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12076 Visits: 10632
Most Oracle developers love cursors. I mean, it's so obvious they're in love. If you read an Oracle PL/SQL developer guide, it will be full of sappy love poetry dedicated to the art of writing cursors.

Another thing you will see a lot of in Oracle is old school non-ANSI join syntax. When (not if) you do see something like this, then don't freak out; it's basically the same as a left outer join.

SELECT Table_A.letter, Table_B.letter
FROM Table_A, Table_B
WHERE Table_A.letter(+) = Table_B.letter;



The example below is what's called a NATURAL JOIN. Now this is a piece of work. Because these two tables share a common key column, Oracle will just make an assumption about how they should be joined, I guess because it saves the developer all the additional keystrokes required to explicitly code the join. On the downside, if your employer measures your productivity in terms of how many lines of code you can write in an hour, then this style of coding will work against you. However, I can think of even more practical reasons why natural joins are a bad idea.

SELECT region_id, r.region_name, c.country_id, c.country_name
FROM countries c
NATURAL JOIN regions r;




"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5337 Visits: 4639
Eric Russell 13013 (9/17/2010)
Most Oracle developers love cursors. I mean, it's so obvious they're in love. If you read an Oracle PL/SQL developer guide, it will be full of sappy love poetry dedicated to the art of writing cursors.


This is a tricky one.

It might be true that some developers love cursors - probably developers with strong mainframe procedural programming languages background. Cursors usually allow for a direct translation of poorly defined business rules.

On the other hand, cursors force row-based processing which usually performs horrible. Best practice is to design back-end code to take advantage of set-based data processing; avoiding this way the use of cursors.

Last but not least, cursors are a powerfull tool but it has to be used when needed rather than relying on cursors as the preferred development approach.

Just remember Rule #5 which states that DBA are responsible for Database performance then, your job as a DBA is to be sure no unnecesary use of cursors makes it to production environment. In most serious Oracle shops DBA still have the power to validate/improve/reject bad code.

Hope this clarifies a bit.

_____________________________________
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.
harrifolfenced
harrifolfenced
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 0
thanks for above links i also solved my stored procedure code

Kitchen Cabinet Manufacturers
harrifolfenced
harrifolfenced
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 0
what are advantages of oracle over sql

Kitchen Cabinet Manufacturers
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39011 Visits: 38508
harrifolfenced (10/13/2010)
what are advantages of oracle over sql


In my opinion, nothing. Both serve a purpose and can do the job.

In the short time I have had with Oracle, however, I definately prefer MS SQL Server. I could be biasedn howevern having worked with SQL Server for 12+ years and Oracle only a couple of weeks.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search