Oracle Procs to SQL SPs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Oracle Procs to SQL SPs

Author

Topic

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-07-02 : 08:55:26

Hi all,

I have to write SPs in SQL 2005 based on procs in Oracle DB.How do I go abt this? Any help will be welcome.

Thank you

Necessity is the mother of all inventions!

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-07-02 : 09:56:25

I am having a big problem using the
EXPECTION
WHEN OTHERS THEN "......"

Any replacement for this in SQL? Or alternative method?

Necessity is the mother of all inventions!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-02 : 09:56:50

one by one?

you can search the google for a tool that does that, but i doubt that any tool
works 100% ok for this kind of thing.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-07-02 : 09:58:13

Spirit I dont mind doing it manually. But I am not able to find replacements for some of the functions used in Oracle in SQL. Like the one I posted above.

Necessity is the mother of all inventions!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-02 : 10:00:58

what does EXPECTION
WHEN OTHERS THEN "......"
do exactly?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-07-02 : 10:02:38

after the if conditions... if it doesnt fall underany of them and there is a error it captures it under OTHERS. There are other Expections like WHEN NO_DATA_FOUND THEN

Necessity is the mother of all inventions!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-02 : 10:08:12

you can use try ... catch block for this.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-02 : 10:08:54

If you are talking about Exception handling in ORACLE, You can use TRY..CATCH block in SQL 2005.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-07-02 : 12:11:43

http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-07-03 : 05:27:02

Hey thank you guys. I will try to work with this info.

Necessity is the mother of all inventions!

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-07-04 : 04:00:46

And guys what is Varchar2 in Oracle to SQL?

Necessity is the mother of all inventions!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-04 : 04:16:18

it's varchar in sql server

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-07-04 : 04:21:07

Thank you spirit.

Necessity is the mother of all inventions!

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-07-04 : 06:20:40

The next problem is SQL server is not 2005 and I dont have Try Catch...

Necessity is the mother of all inventions!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-04 : 06:27:49

Then no straight-forward translation.

Back to old T-SQL way of error handling:

After each SQL Statement,

If @@Error <> 0
Begin
  -- Error handling stuff here
End

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-04 : 10:45:29

http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

Madhivanan

Failing to plan is Planning to fail

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-07-05 : 07:11:42

Those are really good links thank you madhivan. Unfortunately for me .. it more same kind of work...now checking and improving Mysql Procs..I wish they had only one Database engine.

Necessity is the mother of all inventions!

Kristen
Test

22859 Posts

Posted - 2007-07-05 : 07:16:22

"I wish they had only one Database engine"

Amen to that!

We used to support Oracle, Sybase Anywhere and MS SQL Server.

Our skills were in MS ...

... eventually the Oracle-Only shops decided that too many suppliers were proficient in MS SQL and decided to allow that database too. So we stopped having to support Oracle.

And then Mini-SQL-Server came out, so we dropped Sybase Anywhere too ...

... and our application has improved immeasurably as we have taken advantage of methods in SQL Server which would have been expensive for us to maintain cross-platform, a nd our skills have become more polished by only using one platform.

Kristen

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-07-05 : 07:19:39

Lucky you Kristen...hope things will be the same here too.

Necessity is the mother of all inventions!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-07-05 : 11:05:42

In my experience, the Oracle IF OTHERS syntax is used to check for when a subquery that is expected to return a single record actually returns more than one record. If you write your code correctly, this won't ever happen and there is no need for IF OTHERS.
I recently rewrote a massive ORACLE procedure (9000+ lines of code) and dumped all the IF OTHERS statements.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources