execute Sybase procedures from Oracle SQL Developer

Oracle SQL Developer provides access to data and code from other database systems than Oracle

If you run Sybase procedures (or Microsoft SQL Server I suppose), you cannot use the BEGIN / DECLARE / EXEC in anonymous blocks


begin
print 'HELLO'
end



Error starting at line : 1 in command -
begin
print 'HELLO'
end
Error report -
Incorrect syntax near the keyword 'begin'.



exec p



Error starting at line : 1 in command -
exec p
Error report -
Incorrect syntax near the keyword 'BEGIN'.


Ô rage ô désespoir!

But actually there is a way to twist the syntax so that it does not look like an oracle exec !

But in fact you create a procedure with CREATE PROCEDURE and end the procedure with a slash. Within the procedure you can use all the keywords you want. To call a procedure, do not use the exec keyword


set echo on

p2



> drop procedure p1
procedure P1 dropped.
> drop procedure p2
procedure P2 dropped.
> create procedure p1 as
return 123
PROCEDURE P1 compiled
> create procedure p2 as
declare @x int, @y varchar(255)
exec @x = p1
set @y = convert(varchar(255), @x)
print @y
PROCEDURE P2 compiled
> p2
123


Okay, it looks a bit like a sqlplus mutant, but it does the trick sometimes when you need to only call a procedure and get it’s return code.

The sames applies for other rdbms, here db2


set echo on

call p



> drop procedure p
procedure P dropped.
> CREATE PROCEDURE P
LANGUAGE SQL
P1: BEGIN
DECLARE X int;
END P1
PROCEDURE P compiled
> call p
call p


UPDATE: check dermotoneill for additional tricks!