Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Learning Oracle Expand / Collapse
Author
Message
Posted Sunday, September 5, 2010 9:06 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #980792
Posted Sunday, September 5, 2010 1:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 35,371, Visits: 31,915
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #980827
Posted Sunday, September 5, 2010 1:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 20,739, Visits: 32,524
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.




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)
Post #980831
Posted Thursday, September 16, 2010 7:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #987253
Posted Friday, September 17, 2010 5:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 233, 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
Post #988070
Posted Friday, September 17, 2010 9:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 1,708, Visits: 4,854
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;

Post #988292
Posted Sunday, September 19, 2010 7:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #988908
Posted Wednesday, October 13, 2010 4:44 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 13, 2010 4:35 AM
Points: 2, Visits: 0
thanks for above links i also solved my stored procedure code

Kitchen Cabinet Manufacturers
Post #1003468
Posted Wednesday, October 13, 2010 4:46 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 13, 2010 4:35 AM
Points: 2, Visits: 0
what are advantages of oracle over sql

Kitchen Cabinet Manufacturers
Post #1003469
Posted Wednesday, October 13, 2010 8:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 20,739, Visits: 32,524
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.



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)
Post #1003668
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse