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

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!

1 thought on “execute Sybase procedures from Oracle SQL Developer

  1. Mandar Bakshi

    We have connected to Sybase database through Oracle sql developer software. Now our requirement is to execute the sybase script through sql developer on nightly basis. i.e. we want to schedule that script. we want to invoke the sql developer application and then through that the sybase script should be executed.

    Thanks,
    Mandar Bakshi

Comments are closed.