migrating-oracle-to-postgres-stored-procedures

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Migrating Stored Procedures from Oracle to PostgreSQL

将存储过程从Oracle迁移至PostgreSQL

Translate Oracle PL/SQL stored procedures and functions to PostgreSQL PL/pgSQL equivalents.

将Oracle PL/SQL存储过程和函数转换为PostgreSQL PL/pgSQL的等效实现。

Workflow

工作流程

Progress:
- [ ] Step 1: Read the Oracle source procedure
- [ ] Step 2: Translate to PostgreSQL PL/pgSQL
- [ ] Step 3: Write the migrated procedure to Postgres output directory

Step 1: Read the Oracle source procedure

Read the Oracle stored procedure from

.github/oracle-to-postgres-migration/DDL/Oracle/Procedures and Functions/

. Consult the Oracle table/view definitions at

.github/oracle-to-postgres-migration/DDL/Oracle/Tables and Views/

for type resolution.

Step 2: Translate to PostgreSQL PL/pgSQL

Apply these translation rules:

Translate all Oracle-specific syntax to PostgreSQL equivalents.
Preserve original functionality and control flow logic.
Keep type-anchored input parameters (e.g.,
```
PARAM_NAME IN table_name.column_name%TYPE
```
).
Use explicit types (
```
NUMERIC
```
,
```
VARCHAR
```
,
```
INTEGER
```
) for output parameters passed to other procedures — do not type-anchor these.
Do not alter method signatures.
Do not prefix object names with schema names unless already present in the Oracle source.
Leave exception handling and rollback logic unchanged.
Do not generate
```
COMMENT
```
or
```
GRANT
```
statements.
Use
```
COLLATE "C"
```
when ordering by text fields for Oracle-compatible sorting.
Leverage the
```
orafce
```
extension when it improves clarity or fidelity.

Consult the PostgreSQL table/view definitions at

.github/oracle-to-postgres-migration/DDL/Postgres/Tables and Views/

for target schema details.

Step 3: Write the migrated procedure to Postgres output directory

Place each migrated procedure in its own file under

.github/oracle-to-postgres-migration/DDL/Postgres/Procedures and Functions/{PACKAGE_NAME_IF_APPLICABLE}/

. One procedure per file.

进度：
- [ ] 步骤1：读取Oracle源存储过程
- [ ] 步骤2：转换为PostgreSQL PL/pgSQL
- [ ] 步骤3：将迁移后的存储过程写入Postgres输出目录

步骤1：读取Oracle源存储过程

从

.github/oracle-to-postgres-migration/DDL/Oracle/Procedures and Functions/

路径读取Oracle存储过程。如需解析类型，请参考

.github/oracle-to-postgres-migration/DDL/Oracle/Tables and Views/

下的Oracle表/视图定义。

步骤2：转换为PostgreSQL PL/pgSQL

应用以下转换规则：

将所有Oracle专属语法转换为PostgreSQL等效语法。
保留原有功能和控制流逻辑。
保留类型锚定的输入参数（例如
```
PARAM_NAME IN table_name.column_name%TYPE
```
）。
对于传递给其他存储过程的输出参数，使用显式类型（
```
NUMERIC
```
、
```
VARCHAR
```
、
```
INTEGER
```
）——请勿对这些参数进行类型锚定。
不要修改方法签名。
除非Oracle源中已包含，否则不要为对象名称添加模式名前缀。
保持异常处理和回滚逻辑不变。
不要生成
```
COMMENT
```
或
```
GRANT
```
语句。
对文本字段排序时使用
```
COLLATE "C"
```
，以实现与Oracle兼容的排序效果。
当orafce扩展能提升清晰度或保真度时，使用该扩展。

如需了解目标模式详情，请参考

.github/oracle-to-postgres-migration/DDL/Postgres/Tables and Views/

下的PostgreSQL表/视图定义。

步骤3：将迁移后的存储过程写入Postgres输出目录

将每个迁移后的存储过程单独保存到

.github/oracle-to-postgres-migration/DDL/Postgres/Procedures and Functions/{PACKAGE_NAME_IF_APPLICABLE}/

路径下的独立文件中。每个文件对应一个存储过程。