Convert Oracle 12c Tables from BYTES to CHAR

Bally Jassal
Bally Jassal used Ask the Experts™
on
Convert Oracle 12c Tables from BYTES to CHAR
I want the tables be converted from BYTE to CHAR on 12.1.0.
I am doing a export dump from a non-Unicode database and the Varchar2 data types is in BYTES (Which is the default).
The import I am doing on is a UNICODE database but import dump is Keeping the same data type as in the source.
Is there a way to change the NLS_LENGTH_SEMANTICS set as CHAR and do the export?
or ; I have to change the NLS_LENGTH_SEMANTICS to CHAR on the target as well?
I am not sure if this will work?

Source:=
SQL> desc PS_GVT_POI_TBL
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- ------------------
SETID                                                                               NOT NULL VARCHAR2(5)
GVT_POI                                                                             NOT NULL VARCHAR2(4)

The result I am looking should look like this (below):=
SQL> desc PS_GVT_POI_TBL
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- ------------------
 SETID                                                                               NOT NULL VARCHAR2(5 CHAR)
 GVT_POI                                                                             NOT NULL VARCHAR2(4 CHAR)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HuaMin ChenProblem resolver

Commented:
Try

select TO_SINGLE_BYTE('Tech on the net')
from dual;

Open in new window


The syntax for the TO_SINGLE_BYTE function in Oracle/PLSQL is:

TO_SINGLE_BYTE( char )
Parameters or Arguments
char
A char, varchar2, nchar, or nvarchar2 value that will be converted to single-byte characters. This function will return its result in the same character set as char.
Most Valuable Expert 2011
Top Expert 2012
Commented:
you can alter the table directly, even while populated.

ALTER TABLE ps_gvt_poi_tbl
    MODIFY(setid VARCHAR2(5 CHAR), gvt_poi VARCHAR2(4 CHAR));

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial