Conversion of Execution Procedures and User-Defined Functions from Microsoft SQL Server to Oracle

The Microsoft SQL Server EXEC statement is used for executing user-defined functions and stored procedures. In order to execute a user-defined functions or stored procedure in Oracle, you have to specify its name and parameters in the procedure body.

SQLWays converts the Microsoft SQL Server EXEC statement to the Oracle syntax for calling stored procedures and functions. .

TABLE 39. Conversion of Execution Procedures and User-Defined Functions from Microsoft SQL Server to Oracle
Microsoft SQL Server	Oracle
create procedure sql_sp_exec as DECLARE @a varchar(20) EXEC @a=func1 1	create or replace procedure sql_sp_exec as v_a varchar2(20); begin v_a:=func1(1); end;
create procedure sql_sp_exec2 as DECLARE @a varchar(20) EXECUTE proc1 @par1=@a,3	create or replace procedure sql_sp_exec2 as v_a varchar2(20); begin proc1 (v_a,3); end;
create procedure sql_sp_exec3 as EXECUTE proc3	create or replace procedure sql_sp_exec3 as begin proc3(); end;

TABLE 39. Conversion of Execution Procedures and User-Defined Functions from Microsoft SQL Server to Oracle

Microsoft SQL Server

Oracle

create procedure sql_sp_exec 
as
DECLARE @a varchar(20)
EXEC @a=func1 1

create or replace procedure sql_sp_exec
as 
v_a varchar2(20);
begin
v_a:=func1(1);
end;

create procedure sql_sp_exec2
as
DECLARE @a varchar(20)
EXECUTE proc1 @par1=@a,3

create or replace procedure sql_sp_exec2 
as 
v_a varchar2(20);
begin
proc1 (v_a,3);
end;

create procedure sql_sp_exec3
as 
EXECUTE proc3

create or replace procedure sql_sp_exec3 
as 
begin
proc3();
end;

Ispirer SQLWays Database Migration Software

Conversion of Execution Procedures and User-Defined Functions from Microsoft SQL Server to Oracle