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?
- SwisSQL - SQL Server to Oracle Migration Tool 3.5;
- tsql to pl sql;
- 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.
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.
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.
- So, I would suggest that you look out everything in details that is not above-mentionned;
- Take a good look to the information details provided by the tool;
- 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:
ISNULL(LTRIM(RTRIM(e.event_title)), ' '),
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),
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)
OR d.ev_end_dt BETWEEN CONVERT(DATETIME, [WS-TERM-START-DATE], 112
AND NOT e.cur_event_state = '59'