Scribd
Upload
8 views4 pages

Procedures Documentaton

This document outlines the step-by-step process for migrating procedures and triggers from SQL Server to Oracle using SSMA for Oracle. It includes prerequisites, project creation, connection setup, code conversion, synchronization, deployment, and manual review. Key considerations include handling unsupported SQL Server features and validating the converted PL/SQL code.

Uploaded by

prasanth kumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
8 views4 pages

Procedures Documentaton

This document outlines the step-by-step process for migrating procedures and triggers from SQL Server to Oracle using SSMA for Oracle. It includes prerequisites, project creation, connection setup, code conversion, synchronization, deployment, and manual review. Key considerations include handling unsupported SQL Server features and validating the converted PL/SQL code.

Uploaded by

prasanth kumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

Step-by-Step: Migrating Procedures & Triggers with

SSMA
Pre-requisites
● SSMA for Oracle installed
Download from Microsoft

● SQL Server source database

● Oracle target database (with user credentials)

Step 1: Create SSMA Project


1. Open SSMA for Oracle

2. Click File → New Project

3. Choose:

○ Project Type: SQL Server to Oracle

○ Provide a Project Name

○ Set Oracle version target (e.g., 19c)

Step 2: Connect to SQL Server Source


1. Click Connect to SQL Server

2. Enter server name, authentication method, and select the source database

3. After connecting, it will show you:

○ Tables

○ Views

○ Stored Procedures

1
○ Triggers

Select stored procedures and triggers you want to migrate.

Step 3: Convert SQL Server Code to Oracle PL/SQL


1. In the left pane, select the objects (procedures, triggers)

2. Click "Convert Schema" on the toolbar

SSMA will try to convert T-SQL (SQL Server) to PL/SQL (Oracle):

● The converted PL/SQL is shown in the bottom pane (Migration Reports)

● You’ll see warnings or errors if something isn’t directly supported

Note: Some SQL Server features like TRY...CATCH, RAISERROR, or inserted/deleted


pseudo-tables in triggers may need manual rewriting.

Step 4: Connect to Oracle Target


1. Click Connect to Oracle

2. Enter:

○ Oracle Host, Port

○ Username/password

○ Service name or SID

3. Test the connection

Step 5: Synchronize & Load Converted Code to Oracle


1. Click Synchronize with Database to push converted structure to Oracle (optional)

2
2. Click Deploy or "Load into Oracle" to send the stored procedures and triggers

It will create them in the Oracle schema as PL/SQL blocks.

Step 6: Manual Review and Fixes (Important)


● After deployment, test each procedure and trigger:

○ Use Oracle SQL Developer or SQL*Plus

○ Validate input/output

○ Debug logic involving:

■ Temp tables

■ inserted/deleted logic

■ Cursor behavior

■ Error handling

Query to check the Procedure wit referenced Table:

Query:
=====

SELECT
DISTINCT
p.name AS ProcedureName,
t.name AS ReferencedTable
FROM
sys.procedures p
JOIN
sys.sql_expression_dependencies d ON p.object_id = d.referencing_id
JOIN
sys.objects t ON d.referenced_id = t.object_id
WHERE
t.type = 'U'; -- U = User-defined table

3
1. Check for Invalid Object Dependencies

You might also like