Microsoft T-SQL to Oracle SQL Translation

Convert SQL Server / T-SQL query to Oracle PL/SQL

Important difference between MS Sql and Oracle is that in MS

SELECT @inputData = column_name FROM table;

runs OK even with no predicates i.e. with multirows result set, returning the value of column_name from an arbitrary row. In Oracle

SELECT column_name INTO input_data FROM my_table;

will fail if the result set contains more then one row.
You shouldn't just translate syntax thoughtlessly.

Migration tool from TSQL to PL/SQL?

1. SwisSQL - SQL Server to Oracle Migration Tool 3.5;
2. tsql to pl sql;
3. Convert SQL Server T-SQL to Oracle PL/SQL (syntax and equivalences);

However, there are some optimisation that may be required to perform after the conversion is done. So, you should consider revising the conversion after it is done.

EDIT #1

Another interesting tool would be:

Spectral Core's Full Convert Enterprise

Do you have experience with any of them?

Unfortunately not. I have just performed conversions to SQL Server using SSIS.

EDIT #2

There seems to be a more appropriate tool directly from Oracle that I would better trust.

Directly from Oracle: Migrating from Microsoft SQL Server to Oracle

And the core features:

SQL Developer Migration technology supports the following core features when migrating from Microsoft SQL Server:

• Automatically converts column data types to appropriate Oracle data types.
• Automatically resolves object name conflicts, such as conflicts with Oracle reserved words.
• Parses and transforms T-SQL stored procedures, functions, triggers, and views to Oracle PL/SQL.
• Provides advanced customization capabilities such as the ability to change data type mappings, delete and rename objects.
• Generates reports about the status of the migration.
• Generates the DDL scripts for the creation of the destination Oracle database.
• Generates scripts for data movement

• Displays informational, error, and warning messages about the migration in a progress window.

  1. So, I would suggest that you look out everything in details that is not above-mentionned;
  2. Take a good look to the information details provided by the tool;
  3. Test your stored procedures after they are converted.

If you take a look as what SQL Server doesn't offer in comparison to Oracle, these are points where I would look first. In order to know these differences, follow the 3. Convert SQL Server T-SQL to Oracle PL/SQL (syntax and equivalences) link above.

Converting a query from Oracle PL/SQL to Microsoft T-SQL

Ok, try this query:

SELECT f.type_id, 
       LTRIM(RTRIM(f.event_type_name)), 
       LTRIM(RTRIM(e.event_name)), 
       ISNULL(LTRIM(RTRIM(e.event_title)), ' '), 
       e.cur_event_state, 
       CONVERT(VARCHAR(8), d.ev_start_dt, 112), 
       CONVERT(VARCHAR(5), d.ev_start_dt, 114), 
       CONVERT(VARCHAR(8), d.ev_end_dt, 112), 
       CONVERT(VARCHAR(5), d.ev_end_dt, 114), 
       DATENAME(DAY, d.ev_start_dt) 
FROM   rooms c 
       INNER JOIN sp_reservations d 
         ON c.room_id = d.room_id 
       INNER JOIN [EVENTS] e 
         ON d.event_id = e.event_id 
       INNER JOIN event_types f 
         ON e.event_type_id = f.[type_id] 
WHERE  @Room = RTRIM(LTRIM(REPLACE(c.room_short, '-', '*'))) 
       AND f.[type_id] IN ( '22', '40', '70', '71', 
                            '72', '105', '121', '119' ) 
       AND ( d.ev_start_dt BETWEEN CONVERT(DATETIME, [WS-TERM-START-DATE], 112) 
                                   AND 
                                         CONVERT(DATETIME, [WS-TERM-END-DATE], 
                                         112) 
              OR d.ev_end_dt BETWEEN CONVERT(DATETIME, [WS-TERM-START-DATE], 112 
                                     ) AND 
                                         CONVERT(DATETIME, [WS-TERM-END-DATE], 
                                         112) ) 
       AND NOT e.cur_event_state = '59' 

---

Related Topics