ORDER BY statements in subqueries are not allowed in Oracle. Therefore, a flag has been added to comment them out in some cases. If the ORDER BY clause causes a problem it can be removed.
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 numeric
declare @mycol2 numeric
select @mycol2=table2.col2
FROM table2 tab21
WHERE EXISTS (select
col2 from table2 t2 where col2 max(table1.col1)and
tab21.id=t2.id order
by col2)
The following code is the PL/SQL code 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 NUMERIC;
mycol2 NUMERIC;
BEGIN
BEGIN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := 0;
SELECT table2.col2
INTO
proc1.mycol2 FROM table2 tab21
WHERE EXISTS (
SELECT col2
FROM
table2 t2
WHERE col2MAX(table1.col1)
and
tab21.id = t2.id
/*[SPCONV-ERR(8)]:ORDER BY statement ignored*/
);
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;
No change is required in this case; the ORDER BY clause was redundant.