1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Equivalent oracle functions for sql server migration

Discussion in 'SQL PL/SQL' started by Vicky, Aug 2, 2016.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    I'm gonna start working in SQL Server, But many of our Oracle Functions are not Supported in MsSQL.

    For Example,
    1. No "For Loop" in SQL Server, because of which I've to add one column and increment it to finish the loop which is more tedious..
    2. No "Level" fucntion for achieving sample data generation and Process using loop in query itself,
    3. No "To_date", "To_Char" function, but seperate code for converting into seperate formats..

    So far, I've faced these functions which are incompatible in MsSQL.
    Could anyone who are Good at SQL server too, can help me by providing some tips how you handled these kind of conversions Intially..?

    I asked 1 of my Friend, who's a SQL server Guy, he told me to code in accordance with ANSI Standards.

    So, that means the above mentioned Oracle Functions doesn't comes under ANSI Standards?!
    If that so, could you help me with the materials having functions which are Universally Compatible(for All DataBases).

    Please Advise.
     
    Last edited: Aug 2, 2016
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Vicky likes this.
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Can You provide here a code which causes problems?

    MS SQL(with T-SQL ) good DBMS, in it are supported the modern technologies.
    But in case of transfer of a code from Oracle there are sometimes problems since there are no technical capabilities provided to Oracle.
     
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi Sergy,

    Please find the Oracle Plsql Code Below.

    Code (SQL):

    SET serveroutput ON;
    DECLARE
    active_dept departments.department_name%TYPE;
    BEGIN
    FOR rec IN (SELECT department_id FROM employees WHERE department_id IS NOT NULL) loop
    SELECT nvl(department_name,'UNK') INTO active_dept FROM departments
    WHERE department_id=rec.department_id;
    dbms_output.put_line('active_dept=='||active_dept);
    END loop;
    END;
     
    Could u help me to achieve the same in SQL Server.,
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    NVL is usually replaced by isnull. Consult the SQL Server documentation for the required syntax.
     
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    You're Correct David. But the real challenge here is achieving the for loop activity without using it.,
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO