Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Is there a way to Oracle expdp not inherit char type nls semantics on DDL?

Luis Claudio SantosSep 6 2017 — edited Sep 6 2017

expdp, by default, generated internal DDL to create table observing current NLS_LENGTH_SEMANTICS of each column.

If a column as BYTE (the default and most used) NLS_LENGTH_SEMANTICS, the internal DDL would be like:

CREATE TABLE BDATA.Artikel(

    Key                   VARCHAR2(3 BYTE)  NOT NULL,

    Name                  VARCHAR2(60 BYTE) NOT NULL,

    Abkuerzung            VARCHAR2(5 BYTE)  NOT NULL

);

But I want an option to force expdp to not get/inherit the CHAR_USED value from DBA_TAB_COLUMNS, resulting into the following DDL:

CREATE TABLE BDATA.Artikel(

    Key                   VARCHAR2(3)   NOT NULL,

    Name                  VARCHAR2(60)  NOT NULL,

    Abkuerzung            VARCHAR2(5)   NOT NULL

);

The reason is because the target database has a AL32UTF8 charset and NLS_LENGTH_SEMANTICS set as CHAR. So I want the tables be converted from BYTE to CHAR (accordingly to NLS_LENGTH_SEMANTICS parameter value).

Comments

John Thorton

Some, many, most Professional IT groups maintain application code, including DDL, in source code repository.

so just do the following

1) extract DDL into single text file.

2) use favorite text editor to do global Search & Replace of CHAR to BYTE

3) run changed text file against newly created Oracle database

4) save text file back into code repository

unknown-7404

expdp, by default, generated internal DDL to create table observing current NLS_LENGTH_SEMANTICS of each column.

. . .

But I want an option to force expdp to not get/inherit the CHAR_USED value from DBA_TAB_COLUMNS

Then use the right tool for the job instead of expdp.

Using Sql Developer you can set all of the options you want for the metadata of the objects you export.

pastedImage_3.png

See that 'Add BYTE keyword' checkbox at the upper right?

Just uncheck it and do your export.

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 4 2017
Added on Sep 6 2017
3 comments
1,288 views