[SPCONV-ERR(<NN>)]:BEGIN TRAN Statement Ignored

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.