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'