Thread: [GENERAL] ora2pg - Java Message Service (JMS) Type

Hi All,

We plan to use ora2pg tool to migrate Oracle to PostgreSQL.  During the testing, while importing content of the output.sql into PostgrSQL database, it terminated with the following error “ERROR:  type "aq$_jms_text_message" does not exist” for “user_data” column with “AQ$_JMS_TEXT_MESSAGE” as type.  In this case, if Java Message Service (JMS) types are not supported by Postgres, ora2pg would not be the right tool to use for the migration but I am not certain if it is true.  Can someone please advise?

psql -d wsp -U staging -W < /database/postgres/outputSTAGING.sql

Password for user staging:

SET

CREATE TABLE

:

CREATE TABLE

ALTER TABLE

CREATE INDEX

ERROR:  type "aq$_jms_text_message" does not exist

LINE 29:  user_data AQ$_JMS_TEXT_MESSAGE,

Thanks,

Joanna

On Fri, Dec 11, 2016 12:43 PM, Julien Rouhaud wrote:

>>I forwarded your mail to the author, since he's not on this mailing-list.  This issue should be fixed with commit >>https://github.com/darold/ora2pg/commit/2c22d377e9f90fa6fa83cfe1fbead9eb3396a0f3.

>>If this bugfix is not enough or if you find other issues, could you report them directly on github (https://github.com/darold/ora2pg/issues)?

Hi Julien,

Thanks for looking into the issue.

I tried and updated “Ora2Pg.pm” with the change suggested in https://github.com/darold/ora2pg/commit/2c22d377e9f90fa6fa83cfe1fbead9eb3396a0f3 (add “|TYPE” in the following line) but encountered the same issue "ERROR:  type "aq$_jms_text_message" while import the content of the output.sql into PostgreSQL database. 

[kll0199:/u01/app/oracle/ora2pg-17.6/lib] egrep "has_limitation" Ora2Pg.pm | egrep -v "has_limitation ="

                if (!$self->{is_mysql} && !$has_limitation && ($arr_type[$i] =~ /TABLE|SEQUENCE|VIEW|TRIGGER|TYPE/)) {

At this point, I guess the issue occurred for that particular type “aq$_jms_text_message”.  So the key question would be : does Postgres database support “aq$_jms_text_message” type?  If postgres does not support this type, it will error out.  I would need confirmation on this please.

Thanks,

Joanna

On Fri, Dec 09, 2016 at 05:22:07PM +0000, Joanna Xu wrote:

> Hi All,

>

> We plan to use ora2pg tool to migrate Oracle to PostgreSQL.  During the testing, while importing content of the output.sql into PostgrSQL database, it terminated with the following error "ERROR:  type "aq$_jms_text_message" does not exist" for "user_data" column with "AQ$_JMS_TEXT_MESSAGE" as type.  In this case, if Java Message Service (JMS) types are not supported by Postgres, ora2pg would not be the right tool to use for the migration but I am not certain if it is true.  Can someone please advise?

>

> psql -d wsp -U staging -W < /database/postgres/outputSTAGING.sql

> Password for user staging:

> SET

> CREATE TABLE

> :

> CREATE TABLE

> ALTER TABLE

> CREATE INDEX

> ERROR:  type "aq$_jms_text_message" does not exist LINE 29:  user_data

> AQ$_JMS_TEXT_MESSAGE,

>