ora2pg - Java Message Service (JMS) Type

The R statistic software is using PostgreSQL. Oracle has a module to
transform PostgreSQL tables very easy both ways. Just install R and the
module. I can't remember the name of the module but try googling.

Hope it helps.

/Poul

2016-12-09 18:22 GMT+01:00 Joanna Xu <Joanna.Xu@amdocs.com>:

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
This message and the information contained herein is proprietary and
confidential and subject to the Amdocs policy statement, you may review at
http://www.amdocs.com/email_disclaimer.asp

--
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA

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,

Hello,

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)?

Thanks!

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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,

This message and the information contained herein is proprietary and confidential and subject to the Amdocs policy statement,
you may review at http://www.amdocs.com/email_disclaimer.asp

On Mon, Dec 12, 2016 at 04:15:59PM +0000, Joanna Xu wrote:

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.

Unfortunately I have no idea what's "aq$_jms_text_message" type, but I don't
see any reason why it couldn't be migrated to PostgreSQL. It really seems that
ora2pg miss to migrate this type for some reason, so you should open an issue
on https://github.com/darold/ora2pg/issues. The author will have a better
answer than me on your problem.

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general