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'
Designing a SQL Schema for a Combination of Many-To-Many Relationship (Variations of Products)
How to Select Multiple Columns from a Subquery (In SQL Server) That Should Have One Record (Select Top 1) for Each Record in the Main Query
How to Copy a Record in a SQL Table But Swap Out the Unique Id of the New Row
How to Select Only the First Rows for Each Unique Value of a Column
SQL Update Fields of One Table from Fields of Another One
Find Duplicate Rows with Postgresql
Update Query Using Subquery in SQL Server
Oracle 'Partition By' and 'Row_Number' Keyword
Using SQL Server as a Db Queue with Multiple Clients
Using a Database Table as a Queue
Grant Privileges for a Particular Database in Postgresql
Hierarchical/Tree Database for Directories Path in Filesystem
Difference Between Filtering Queries in Join and Where
Copy Data from One Column to Other Column (Which Is in a Different Table)
How to View Grants on Redshift
Get Everything After and Before Certain Character in SQL Server
How to Select Records Without Duplicate on Just One Field in SQL