cancel
Showing results for 
Search instead for 
Did you mean: 

Syntax error when a cursor has a RETURN

Accepted Solution

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)

Rohit
EDB Team Member
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.

Answers (5)

Answers (5)

prouxel
Adventurer

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
EDB Team Member

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
Adventurer

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
Adventurer

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

sachinfate
EDB Team Member

Hi,

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

Ask a Question