I'm working on Oracle to Sybase ASE SPs conversion and trapped into the autonomous transaction usage in PL/SQL.
Could you please let me know if there is any equivalent for this feature in Sybase Adaptive Server Enterprise? May be there are some relevant posts with the description of the solution for this problem?
Thank you in advance.
Never mind. I've managed to find the solution myself.
To whom it may be interested: The idea of the solution has been taken from the SQL Server external SPs, that Microsoft uses as the solution for AUTONOMOUS TRANSACTIONS from Oracle.
In Sybase Adaptive Server Enterprise there is an opportunity to create a Remote Server on your current server using the following statement:
exec sp_addserver SRVName, RPCServer, @@servername
Then it is required to set the option on the current server:
set transactional_rpc on
From this point you're allowed to refer to your objects through the SRVName server, like:
create proc sp_test_autonom1
as
begin
begin transaction
insert into test_idx values(10,20)
declare @status int
exec @status = SRVName.itest..sp_autonom
rollback transaction
end
SRVName.itest..sp_autonom can be created as:
create proc sp_autonom
as
begin
begin transaction
insert into test_idx2 values(1,2)
commit transaction
end
Tables can be created as:
create table test_idx(col1 int, col2 int)
go
create table test_idx2(col1 int, col2 int)
Thank you.