How to import trigger, procedure, and sequence from Oracle to PostgreSQL using Ora2pg

Hi guys, first of all, i’m really new to Oracle and PostgreSQL. I am using CentOS 6 32-Bit. At the moment i’m learning how to migrate the database from Oracle to PostgreSQL using Ora2pg. I made the database i wanted to migrate as simple as i could. So far, i’m only able to migrate the tables, but i can’t migrate the trigger, procedure, and sequence along with it. So i’ve been wondering, is there any way to migrate the trigger, procedure, and sequence to PostgreSQL using Ora2pg?

Here’s the step i took :

  • i created the database, with simple tables, trigger, procedure, and sequence. I made sure to test the trigger, procedure, and sequence to be working.

  • to double check, i exported the schema which all the tables. trigger, procedure, and sequence were under with expdp. After that i drop the schema, and then imported it back with impdp. Everything was still the same and working just as fine.

-And then i tried to migrate the oracle database with ora2pg. Seemed to be worked just fine, it said the trigger, procedure, and sequence were imported as well as far as i can tell

[root@localhost ora2pg]# ora2pg
[========================>] 4/4 tables (100.0%) end of scanning.
[>] 0/4 tables (0.0%) end of scanning.
[========================>] 4/4 tables (100.0%) end of table export.
[========================>] 0/0 packages (100.0%) end of output.
[========================>] 5/2 rows (250.0%) Table DOSEN (5 recs/sec)
[====================>] 5/6 total rows (83.3%) - (0 sec., avg: 5 recs/sec).
[========>] 2/6 rows (33.3%) on total estimated data (1 sec., av[========================>] 2/1 rows (200.0%) Table KELAS (2 recs/sec)
[========================>] 7/6 total rows (116.7%) - (0 sec., avg: 7 recs/sec).
[============>] 3/6 rows (50.0%) on total estimated data (1 sec., av[========================>] 2/2 rows (100.0%) Table MAHASISWA (2 recs/sec)
[========================>] 9/6 total rows (150.0%) - (0 sec., avg: 9 recs/sec).
[====================>] 5/6 rows (83.3%) on total estimated data (1 sec., av[========================>] 1/1 rows (100.0%) Table RECORD (1 recs/sec)
[========================>] 10/6 total rows (166.7%) - (0 sec., avg: 10 recs/sec).
[========================>] 6/6 rows (100.0%) on total estimated data (1 sec., avg: 6 recs/sec)
[========================>] 0/0 views (100.0%) end of output.
[========================>] 1/1 sequences (100.0%) end of output.
[========================>] 1/1 triggers (100.0%) end of output.
[========================>] 0/0 functions (100.0%) end of functions export.
[========================>] 1/1 procedures (100.0%) end of procedures export.
[========================>] 0/0 types (100.0%) end of output.
[========================>] 0/0 partitions (100.0%) end of output.

-After i created the .sql , i imported it into my PostgreSQL 10  via psql

test=# \i /etc/ora2pg/xyz_schemawith_sequence_trigger_proc_usergrant0.sql
SET
CREATE SCHEMA
ALTER SCHEMA
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
SET
SET
SET
SET
SET
BEGIN
ALTER TABLE
SET
COPY 5
SET
COPY 2
SET
COPY 2
SET
COPY 1
ALTER TABLE
psql.bin:/etc/ora2pg/xyz_schemawith_sequence_trigger_proc_usergrant0.sql:93: NOTICE: relation "urutan" does not exist, skipping
ALTER SEQUENCE
COMMIT
test=# \dn
  List of schemas
  Name | Owner
--------+----------
 public | postgres
 xyz | xyz
(2 rows)

test=# \dt
           List of relations
 Schema | Name | Type | Owner
--------+-----------+-------+----------
 xyz | dosen | table | postgres
 xyz | kelas | table | postgres
 xyz | mahasiswa | table | postgres
 xyz | record | table | postgres
(4 rows)

test=# \dy
              List of event triggers
 Name | Event | Owner | Enabled | Procedure | Tags
------+-------+-------+---------+-----------+------
(0 rows)

test=# \ds
Did not find any relations.
test=#

-I checked the result on the PostgreSQL, it has all the tables and rows, but no triggers, procedures, and sequences to be found. About the trigger, i previously made the trigger on Oracle to insert a note  to table ‘record’ whenever someone inserted new value into table ‘dosen’. When i tried inserted new value into table ‘dosen’ on PostgreSQL, the trigger didn’t work

Sorry if there are some bad english, and if this is some rookie mistake or something that should be obvious. As i said earlier i’m completely new to this. And thanks in advance.

This post was moved to a different board that fits your topic of discussion a bit better. This means you’ll get better engagement on your post, and it keeps our community organized so users can more easily find information.

 

As you’ll notice, your topic is now here in the Project Development Help and Advice board. No action is needed on your part; you can continue the conversation as normal here.