将 Oracle PL/SQL 翻译成 Postgresql PL/pgSQL 的工具
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/158310/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Tool for translation of Oracle PL/SQL into Postgresql PL/pgSQL
提问by dacracot
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 存储过程语言？
采纳答案by Jimoc
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 模式转换为 Postgres 模式，但我不确定它是否也能转换存储过程。但它可能提供一个起点。
回答by chotchki
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 的转换已经有一段时间了。唯一的方法是手工。这两种语言之间存在细微的差异，可能会让您感到困惑。我们尝试使用自动化工具，但它只会使问题变得更糟，我们最终破坏了输出。
回答by Vinko Vrsalovic
There's also EnterpriseDBwhich 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 兼容性的版本不是免费的，但如果您不仅要进行一个过程翻译，则值得一看。
回答by Vinko Vrsalovic
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.
- Translate PL/SQL functions in PL/PGSQL (see below).
- 手动将所有 DECODE() 转换为 CASE 语句，并将所有旧式 Oracle WHERE (+) 外连接转换为显式 LEFT OUTER JOIN 语句。我还没有找到一个工具来做到这一点。
- 在 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;
