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'.
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 drop procedure p1 / drop procedure p2 / create procedure p1 as return 123 / create procedure p2 as declare @x int, @y varchar(255) exec @x = p1 set @y = convert(varchar(255), @x) print @y / 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 drop procedure p / CREATE PROCEDURE P LANGUAGE SQL P1: BEGIN DECLARE X int; END P1 / 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!