Conversion of Oracle function into SQL Server 2008

Question

Please help to convert this Oracle function into SQL Server function.

I am very new to SQL Server functions.

Thank you.

Oracle Function:

 

Answer 1

Try SSMA......

here is the link to download - > http://www.microsoft.com/en-us/download/details.aspx?id=28763

---

Regards Harsh

Answer 2

In addition, please visit this blog:

SQL Server Migration Assistant (SSMA) Team's Blog

---

Saeid Hasani, sqldevelop.wordpress.com

Download Books Online for SQL Server 2012

Answer 3

I am certainly not going to write the corresponding T-SQL code for you. I don't know PL/SQL, and I don't like reading code which is all in uppercase.

I will however, make a few remarks:

*  An SQL Server function must not change database state, why you cannot have an INSERT into a permanent table as you seem to have in this function. Presumably, you want a stored procedure instead.

*  I see that there are a couple of cursors. I know that cursors are fairly commonly used in Oracle. And you can certainly find a lot of examples in SQL Server code as well. However, beware that cursors do not perform well in SQL Server. Or more exactly: using loops over a set-based statement often comes with a high penalty in performance.

* The || operator in Oracle (and ANSI SQL) does not exist in SQL Server. Use + instead.

* I see that you use "FROM dual" in the function. In SQL Server you achieve the same thing by simply omitting the FROM clause.

Particularly with the second point in mind, I recommend that you make a fresh start and analyse what your function does. It is not unlikely that you can replace it with a single statement.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

In addition to Erland's reply:

* You can use the standard COALESCE() function to return the first non-NULL value within the provided list. You probably need it to replace NVL()

* I am not sure SQL Server support the SUBSTR abbreviation of the SUBSTRING function

* You can use the standard CASE expression to replace the DECODE function

* I guess you can use CEILING to replace the CEIL function

* In one of the cursors, you are using the non-deterministic outer join syntax "(+)".  I strongly advise you to rewrite all joins in that query to INNER JOIN / OUTER JOIN instead of listing all tables and then joining them in the WHERE clause.

* I guess you can use CHARINDEX to replace the INSTR function

* "!=" will work in SQL Server, but "<>" is the standard notation for "not equal to"

* In TSQL, there is no ELSIF. There is only IF and ELSE. So you would need to nest the IFs to achieve the same, or change to a CASE expression where applicable.

And I agree with Erland that you can probably rewrite this entire (hard to read) piece of code to two INSERT statements.

---

Gert-Jan