Syntax error when a cursor has a RETURN
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
Accepted Solutions (1)
Accepted Solutions (1)
- Report Inappropriate Content
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.
Answers (5)
Answers (5)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Email to a Friend
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Email to a Friend
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Email to a Friend
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Email to a Friend
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Email to a Friend
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Email to a Friend
- Report Inappropriate Content
Hi,
Could you please provide the steps which you are following to do migration and the version details?