将Oracle过程移植到PostgreSQL(来自orafce的utl_file模块的异常代码) [英] Porting Oracle procedure to PostgreSQL (exception codes from orafce for utl_file module)

问题描述

我正在从Oracle迁移到PostgreSQL的数据库中.我们正在使用 ora2pg 来实现自动转换以及

I am in the middle of a database migration from Oracle to PostgreSQL. We are using ora2pg to make the conversion the plus automatic possible and the orafce plugin for PostgreSQL for functions compatibility.

我们才刚刚开始，还有很多工作要做.

We have just started and a lot of work is to be done.

现在，我使用存储过程(ora2pg脚本的输出)，并且在解决了不同的语法错误之后，我不知道如何解决最后一个.

Just now I am with stored procedures (output from ora2pg script) and after resolving the different syntax errors I does not know how to solve the last one.

尤其是有问题的代码如下:

In particular the problematic code is the following:

 select utl_file.put_line(vIdLog,'******************************** COMPTE RENDU PURGE_DOUBLONS_FS **************************');
 select utl_file.put_line(vIdLog,'*      - Debut du traitement le          : '||vDateDebut);
 select utl_file.put_line(vIdLog,'*');
 select utl_file.put_line(vIdLog,'*      - Nb de lun appairées à plusieurs ZV  : ' || vNbLunMultiZV);
 select utl_file.put_line(vIdLog,'*      - Nb FS appairée à plusieurs ZV       : ' || vNbUpdateFsMultiZV);
 select utl_file.put_line(vIdLog,'*      - Nb Liens Lun/FS en suppression logique    : ' || vNbUpdateLunMultiZV);
 select utl_file.put_line(vIdLog,'*      - Nb Liens FS en suppression logique    : ' || vNbUpdateFsMultiZV);
 select utl_file.put_line(vIdLog,'*      - Nb Liens FS(ZG mono ZV)en suppression logique    : ' || vNbUpdateLunFSZVseule);
 select utl_file.put_line(vIdLog,'*      - Nb Liens FS(ZG mono ZV)en suppression logique 2  : ' || vNbUpdateLunFSZVseule2);
 select utl_file.put_line(vIdLog,'*      - Nb Liens LUN/FS ZV obsolètes             : ' || vNbOldLunZV);
 select utl_file.put_line(vIdLog,'*      - Nb Liens LUN/FS ZG obsolètes             : ' || vNbOldLunZG);
 select utl_file.put_line(vIdLog,'*      - Temps de traitement de calcul   : ' || OUTILS.time_to_char(tTotal));
 select utl_file.put_line(vIdLog,'*');
 select utl_file.put_line(vIdLog,'*      - Fin du calcul HOST_LUN le       : ' || to_char(clock_timestamp(), 'DD/MM/YYYY HH24:MI:SS'));
 select utl_file.put_line(vIdLog,'************************** FIN COMPTE RENDU PURGE_DOUBLONS_FS ****************************');
 select utl_file.fclose(vIdLog);
EXCEPTION
 when UTL_FILE.INVALID_PATH then
   select Fin_traitement_fichier('Erreur E/S');
   RAISE EXCEPTION '%', 'File location or filename was invalid.';
 when UTL_FILE.INVALID_MODE then
   select Fin_traitement_fichier('Erreur E/S');
   RAISE EXCEPTION '%', 'The open_mode parameter in FOPEN was invalid.';
 when others then
   select Fin_traitement_fichier(SQLERRM);
   RAISE NOTICE 'ERR005 : ERREUR TRAITEMENT PURGE_DOUBLONS_FS : %', SQLERRM;
   IF cFs%ISOPEN THEN
     CLOSE cFs;
   END IF;

产生的错误如下

ERROR:  syntax error at or near "UTL_FILE"
LINE 341:    RAISE EXCEPTION '%', 'File location or filename was inval...
                     ^
********** Error **********

如果我仅在异常处理中使用当其他人时" 部分，它就可以正常工作，因此问题出在常量 UTL_FILE.INVALID_PATH 和 UTL_FILE.INVALID_MODE .

If I use only the "when others" part of the exception treatment it works ok, so the problem comes from the constants UTL_FILE.INVALID_PATH and UTL_FILE.INVALID_MODE that are not recognised by PostgreSQL.

是否知道如何处理utl_file模块的orafce中的异常代码?

Any idea if how to treat exception codes from orafce for utl_file module?

推荐答案

PLpgSQL不允许定义自己的异常-因此Orafce无法定义UTL_FILE.*异常.您应该查看orafce 源代码

PLpgSQL doesn't allow to define own exceptions - so Orafce cannot to define UTL_FILE.* exceptions. You should to look to orafce source code file.c to list of used exceptions:

代码使用宏CUSTOM_EXCEPTION

#define CUSTOM_EXCEPTION(msg, detail) \
    ereport(ERROR, \
        (errcode(ERRCODE_RAISE_EXCEPTION), \
         errmsg("%s", msg), \
         errdetail("%s", detail)))

在此列表中，您可以看到所有PostgreSQL异常.因此，使用的异常的名称为raise_exception，而异常的原因在SQLERRM变量中.因此，您的代码应类似于:

In this list you can see all PostgreSQL exceptions. So the name of used exception is raise_exception and the reason of exception is in SQLERRM variable. So your code should to look like:

WHEN RAISE_EXCEPTION THEN
  CASE SQLERRM
   WHEN 'UTL_FILE_INVALID_PATH' THEN
     PERFORM ...
   WHEN 'UTL_FILE_INVALID_MODE' THEN
     PERFORM ...
   ELSE 
     PERFORM ...
   END;

这篇关于将Oracle过程移植到PostgreSQL(来自orafce的utl_file模块的异常代码)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！