Tuesday, 20 May 2008

Parameters Of A Procedure Call

**Update: This is now fixed in SQL Developer 1.5.1**
A SQL Server and Sybase translation issue.
If you executed a procedure from another procedure, and you specified the executed procedure parameter names, you may have got a doubling up of param names.

T-SQL Example
create procedure procparam1 @param1 int, @param2 int
as
select @param1, @param2
go


create procedure procparam2 @param1 int
as
declare @var1 int
begin
select @var1 = 10
execute procparam1 @param1=@param1,@param2=@var1

execute procparam1 @param1,@var1

execute procparam1 @param1=1,@param2=2

execute procparam1 1,2
end
go


Generated PL/SQL
CREATE OR REPLACE PROCEDURE procparam2
(
v_param1 IN NUMBER DEFAULT NULL ,
cv_1 IN OUT SYS_REFCURSOR,
cv_2 IN OUT SYS_REFCURSOR,
cv_3 IN OUT SYS_REFCURSOR,
cv_4 IN OUT SYS_REFCURSOR
)
AS
v_var1 NUMBER(10,0);
BEGIN

BEGIN
v_var1 := 10;

procparam1(v_param1v_param1 => v_param1,
v_param2v_param2 => v_var1,
cv_1 => cv_1);

procparam1(v_param1,
v_var1,
cv_1 => cv_2);

procparam1(v_param1v_param1 => 1,
v_param2v_param2 => 2,
cv_1 => cv_3);

procparam1(1,
2,
cv_1 => cv_4);

END;
END;
Note the v_param1v_param1 .

A real fix will be included in SQL Dev 1.5.1, but if you cant wait heres a hack.

  • Close SQL Developer
  • Backup your \sqldeveloper\extensions\ oracle.sqldeveloper.migration.translation.sqlserver.jar
  • Extract \sqldeveloper\extensions\oracle.sqldeveloper.migration.translation.sqlserver.jar:templates\tsql.stg
  • Including its directory structure to C:\
  • Edit the "templates\tsql.stg" file
  • Use the "first" method around the paramName, so only 1 paramName is used
TSQL_executeProcedureParam(paramName, paramValue) ::=<<
<if(paramName)><first(paramName)> =\> <paramValue>
<else><paramValue>
<endif>
>>
  • Delete the exising templates\tsql.stg from the jar file, and add the modified one back in, including directory structure, so the jar file has a templates\tsql.stg
  • Restart SQL Developer and convert again.