[SPCONV-ERR(<NN>)]:(set) Manual Conversion Might be Required

All set options are currently ignored.

The following T-SQL example shows how this error may typically be produced:

CREATE PROC proc1
AS
set CURSOR_CLOSE_ON_COMMIT
declare @mycol1 varchar(30)
select @mycol1=col1
FROM table1, table2
WHERE table1.id = table2.id

The following is the PL/SQL code produced by the T-SQL to PL/SQL parser in the Migration Workbench:

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
 NULL; /* set  CURSOR_CLOSE_ON_COMMIT */
 /*[SPCONV-ERR(3)]:(set) MANUAL CONVERSION MIGHT BE REQUIRED*/

 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;
END proc1;

This section suggests how the PL/SQL code may be rewritten by hand. Set options are varied so that PL/SQL equivalents cannot be generalised. Some details about common set options are outlined below.

 (set NOCOUNT) Manual conversion required

  all set .. statements are ignored
  in this case, sqlserver7
  set nocount ..
  limits number of rows returned
  sqlserver specific:
  set rowcount 5
  select title, sales from titles order by sales
  set rowcount 0

  oracle specific (rowcount pseudo column)
  select title, sales from titles order by sales where rowcount < 5;

  ansi:(both)
  select title, sales from titles a where
  (
  select count (*) from titles b where b.sales a.sales
  ) < 5
  order by sales

(set DATEFORMAT) Manual conversion required

  all sets are ignored. Oracle equivalent of set dateformat is
  alter session set NLS_DATE_FORMAT = 'fmt'