将Oracle PL / SQL转换为Postgresql PL / pgSQL的工具

Is there a tool (preferably free) which will translate Oracle's PL/SQL stored procedure language into Postgresql's PL/pgSQL stored procedure language?

是否有一个工具(最好是免费的)将Oracle的PL / SQL存储过程语言转换为Postgresql的PL / pgSQL存储过程语言?

4 个解决方案

#1

There is a tool available at http://ora2pg.darold.net/ which can be used to transalate Oracle Schemas to Postgres schemas, but I'm not sure if it will also translate the stored procedures. But it might provide a place to start.

http://ora2pg.darold.net/上有一个工具可用于将Oracle Schema转换为Postgres模式,但我不确定它是否也会转换存储过程。但它可能提供一个开始的地方。

#2

Having been working on an Oracle to Postgres conversion for quite some time. The only way to do it is by hand. There are subtle differences between the two languages that can trip you up. We tried using an automated tool but it only made the problem worse and we ended up trashing the output.

一段时间以来一直致力于Oracle到Postgres的转换。唯一的方法是手工完成。两种语言之间存在微妙的差异,可能会让您失望。我们尝试使用自动化工具,但它只会使问题变得更糟,我们最终破坏了输出。

#3

There's also EnterpriseDB which has a quite a bit of Oracle compatibility to help migration from Oracle. The version with Oracle compatibility is not free but worth a look if you are doing more than just one procedure translation.

还有EnterpriseDB具有相当多的Oracle兼容性,可帮助从Oracle迁移。具有Oracle兼容性的版本不是免费的,但如果您所做的不仅仅是一个过程转换,那么值得一看。

#4

Use ora2pg to translate your schema.

使用ora2pg翻译您的架构。

For stored procedures:

对于存储过程:

Manually convert all DECODE() to CASE statements and all old-style Oracle WHERE (+) outer joins to explicit LEFT OUTER JOIN statements. I haven't found a tool to do this.

手动将所有DECODE()转换为CASE语句,将所有旧式Oracle WHERE(+)外连接手动转换为显式LEFT OUTER JOIN语句。我还没有找到一个工具来做到这一点。

Translate PL/SQL functions in PL/PGSQL (see below).

在PL / PGSQL中翻译PL / SQL函数(见下文)。

It would be very nice if someone started a sourceforge project to do this.
Hint hint...

如果有人启动了sourceforge项目,那将是非常好的。提示提示...

Here's what I mean for (2) above:

这就是我对上述(2)的意思:

CREATE OR REPLACE FUNCTION trunc(
  parmDate   DATE    ,
  parmFormat VARCHAR ) 
RETURNS date 
AS $$
DECLARE
  varPlSqlFormat VARCHAR;
  varPgSqlFormat VARCHAR;
BEGIN
  varPgSqlFormat := lower(parmFormat);

  IF varPgSqlFormat IN (
    'syyyy' ,
    'yyyy'  ,
    'year'  ,
    'syear' ,
    'yyy'   ,
    'yy'    ,
    'y'     ) THEN
    varPgSqlFormat := 'year';
  ELSEIF varPgSqlFormat IN (
    'month' ,
    'mon'   ,
    'mm'    ,
    'rm'    ) THEN 
    varPgSqlFormat := 'month';
  ELSEIF varPgSqlFormat IN (
    'ddd' ,
    'dd'  ,
    'j'   ) THEN 
    varPgSqlFormat := 'day';
  END IF;

  RETURN DATE_TRUNC(varPgSqlFormat,parmDate);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION trunc(
  parmDate   DATE) 
RETURNS date 
AS $$
DECLARE
BEGIN
  RETURN DATE_TRUNC('day',parmDate);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION last_day(in_date date) RETURNS date 
AS $$
DECLARE
BEGIN
  RETURN CAST(DATE_TRUNC('month', in_date) + '1 month'::INTERVAL AS DATE) - 1;
END;
$$ LANGUAGE plpgsql;

#1