Syntax error when a cursor has a RETURN

[prouxel]

I have a syntax error in the mtk_bad_sql file:

2019-05-24 09:08:09 -- MTK-16004: Error Creating Package Body AXGRA
-- DB-42601: com.edb.util.PSQLException: ERROR: syntax error on or near "RETURN"
Position�: 375
CREATE PACKAGE BODY axgra AS

/* Curseur rafraichissement des textes */
/* ----------------------------------- */
CURSOR rfhcur ( vidf IN NUMBER) IS
SELECT idg, dxf_01 FROM ax_graph_view
WHERE idf = vidf AND (typg=0 OR typg=6)
ORDER BY idg;

/* Curseur lecture folio */
/* --------------------- */
CURSOR loafolcur ( vidf IN NUMBER)
RETURN axgra.grarec IS
SELECT idg, idl, idp, idf, typg, typl, dxf_01 , dxf_02, dxf_06, dxf_07,
dxf_08 , dxf_10x, dxf_10y, dxf_10z, dxf_11x, dxf_11y, dxf_11z,
dxf_40, dxf_41, dxf_42, dxf_43, dxf_50, dxf_51, dxf_62, dxf_66,
dxf_70, dxf_71, dxf_72, dxf_73, dxf_74, dxf_210x, dxf_210y, dxf_210z, tooltip
FROM ax_graph_view
WHERE idf = vidf
ORDER BY idg;

.

Is it normal?

Regards

[Rohit · Accepted solution]

Hi,

Yes, the mentioned syntax for cursor declaration does not match with the one in Oracle compatible mode.

and you will need to need to be rewritten after migrating to PL/pgSQL.

[prouxel]

I understand that this cursor declaration need to be rewrite if we migrate to plpgsql.

Do you confirm that this declaration are not cover by the Oracle compatible mode?

https://www.enterprisedb.com/docs/en/11.0/EPAS_Oracompat_Guide_v11/Database_Compatibility_for_Oracle...

[Rohit]

Hi,

The work of RETURN in Cursor declaration in PL/pgSQL is different compared to PL/SQL.

Please refer below URL for CURSOR in PL/pgSQL
https://www.postgresql.org/docs/11/plpgsql-cursors.html

Please refer below example with respect to your cursor :

PL/SQL :

CURSOR loafolcur ( vidf IN NUMBER)
RETURN axgra.grarec IS
SELECT idg, idl, idp, idf, typg, typl, dxf_01 , dxf_02, dxf_06, dxf_07, dxf_08 , dxf_10x, dxf_10y, dxf_10z, dxf_11x, dxf_11y, dxf_11z, dxf_40, dxf_41, dxf_42, dxf_43, dxf_50, dxf_51, dxf_62, dxf_66, dxf_70, dxf_71, dxf_72, dxf_73, dxf_74, dxf_210x, dxf_210y, dxf_210z, tooltip FROM ax_graph_view WHERE idf = vidf ORDER BY idg;

PL/pgSQL :

CREATE OR REPLACE FUNCTION loafolcur( vidf IN NUMBER ) RETURNS axgra.grarec AS '
BEGIN
OPEN $1 FOR SELECT idg, idl, idp, idf, typg, typl, dxf_01 , dxf_02, dxf_06, dxf_07, dxf_08 , dxf_10x, dxf_10y, dxf_10z, dxf_11x, dxf_11y, dxf_11z, dxf_40, dxf_41, dxf_42, dxf_43, dxf_50, dxf_51, dxf_62, dxf_66, dxf_70, dxf_71, dxf_72, dxf_73, dxf_74, dxf_210x, dxf_210y, dxf_210z, tooltip FROM ax_graph_view WHERE idf = vidf ORDER BY idg;
RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT loafolcur('funccursor');
FETCH ALL IN funccursor;
COMMIT;

[prouxel]

I use Oracle 11 with plsql code and I try to migrate to Postgres Advanced Server 11

using the Migration Toolkit (Build 52.0.3)

I have a syntax error when the declaration of the cursor use a RETURN.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/cursor_declaration.htm

It is valid with Oracle but I does not with Postgres.

[prouxel]

Running EnterpriseDB Migration Toolkit (Build 52.0.3)

"C:\Program Files (x86)\edb\mtk\bin\runMTK.bat" -allTables -constraints -indexes -allProcs -allFuncs -allPackages -triggers -allViews -allSequences -allSynonyms -logBadSQL SHUSR0000

[Rohit]

Hi, Could you please share the details of what are you to achieve by running MTK.? Source : PPAS/Oracle Source : Version Expected : PPAS Expected : Version

[sachinfate]

Hi,

Could you please provide the steps which you are following to do migration and the version details?