Package name is shorter when referenced in CREATE TYPE

[janopha]

We have two broken package names in our export.

When two of our packages gets exported, they are correctly created as schemas in our package.sql. But after the CREATE SCHEMA statement for each of the two packages, ora2pg inserted a CREATE TYPE statement (this should be normal).

The schema gets referenced wrong in the CREATE TYPE statements. The schema name is shorter. It contains only the first 15 characters, instead of all 29 characters (or 24 for the other schema).

Hint: The schema names are identical in the beginning. They are only different in the last characters.

All other functions that will be imported in this schema/package are not affected by this problem. Only the CREATE TYPE statement contains the shorter invalid schema name. And that will cause errors in our Import.

Example Code snippet from ora2pg export:

-- Oracle package 'THISISALONGPACKAGENAME' declaration, please edit to match PostgreSQL syntax.

DROP SCHEMA IF EXISTS thisisalongpackagename CASCADE;
CREATE SCHEMA IF NOT EXISTS thisisalongpackagename;
CREATE TYPE thisisalongpack.exampletype AS (column character[]); -- <- This line is the problem

-- some psql includes ... (Inside of them, the schema name is referenced correctly)

-- End of Oracle package 'THISISALONGPACKAGENAME' declaration

[darold]

Commit c93d719 should fix this issue.

[janopha]

Thanks for your commit. Unfortunately, that did not fix our problem. The error situation is unchanged and is still identical to the issue description.

[darold]

Can you please send me the Oracle package DDL so that I can try to reproduce your problem, you can use my private email if you want, gilles AT darold DOT net.

When I try to reproduce, everything works as expected, here an export with a very long package name:

-- Oracle package 'TEST_GET_SET_LONGSIZE1_LONGSIZE2_LONGSIZE3_LONGSIZE4_LONGSIZE5_LONGSIZE6_LONGSIZE7' declaration, please edit to match PostgreSQL syntax.

DROP SCHEMA IF EXISTS test_get_set_longsize1_longsize2_longsize3_longsize4_longsize5_longsize6_longsize7 CASCADE;
CREATE SCHEMA IF NOT EXISTS test_get_set_longsize1_longsize2_longsize3_longsize4_longsize5_longsize6_longsize7;
CREATE TYPE test_get_set_longsize1_longsize2_longsize3_longsize4_longsize5_longsize6_longsize7.num_array AS (num_array bigint[]);

\ir test_get_set_longsize1_longsize2_longsize3_longsize4_longsize5_longsize6_longsize7/set_record_package.sql
\ir test_get_set_longsize1_longsize2_longsize3_longsize4_longsize5_longsize6_longsize7/get_record_package.sql

of course the name of the schema must be edited to fit the 63 characters but the export is done correctly for what I see.

[janopha]

In the meantime I also tried to reproduce this error. And I didn't manage to get the same error again with a new package that I created for testing. Package/schema name was exported correctly in the CREATE TYPE statement of the ora2pg export. I only discovered this error with the existing package.

So I also think it's not the long package name what causes this Issue, because I also used long package names for testing.

The Oracle DDL of the existing package should also be correct.

create PACKAGE     ThisIsALongPackageName
AUTHID CURRENT_USER
AS
  TYPE Exampletype
    IS TABLE OF NUMBER(6);

  -- ... functions and procedures ...

END ThisIsALongPackageName;
/

Export result.

DROP SCHEMA IF EXISTS thisisalongpackagename CASCADE;
CREATE SCHEMA IF NOT EXISTS thisisalongpackagename;
CREATE TYPE thisisalongpack.exampletype AS (exampletype integer[]);

The Name of the Type is correct in the CREATE TYPE statement of the export. But the package/schema name in the statement is shorter. As I said, I only noticed this with two existing packages. But they are just named differently and also have the same single TYPE in the CREATE PACKAGE statement (that's why the DDL fits to both).

[darold]

Not reproducible event with the same DDL. Closing.