Transaction control is performed differently in T-SQL and PL/SQL. PL/SQL suggests that commits, rollbacks etc. are done at a high level [GUI?] . Variables such as @@TRANSCOUNT variables are ignored . BEGIN TRANSACTION <STRING> becomes SAVEPOINT <STRING>. BEGIN TRANSACTION with no string gives this conversion error.
The following example illustrates how the T-SQL to PL/SQL parser in the Migration Workbench could produce this error:
CREATE PROC proc1
AS
declare @mycol1 varchar(30)
BEGIN TRANS
select @mycol1=col1
FROM table1, table2
WHERE table1.id = table2.id
BEGIN TRANS myname
insert into tab1
values (1,2,3)
The following PL/SQL code is produced by the parser:
CREATE OR REPLACE
PROCEDURE proc1
AS
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
mycol1 VARCHAR2(30);
BEGIN
/*[SPCONV-ERR(4)]:BEGIN
TRAN statement ignored*/
NULL;
BEGIN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := 0;
SELECT col1
INTO
proc1.mycol1 FROM table1, table2
WHERE
table1.id = table2.id;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN
TOO_MANY_ROWS THEN
StoO_rowcnt := 2;
WHEN
OTHERS THEN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
END;
SAVEPOINT myname;
BEGIN
StoO_error
:= 0;
StoO_rowcnt
:= 0;
INSERT INTO
tab1 VALUES (1, 2, 3);
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
END;
END proc1;
Transaction mechanisms must be reappraised.