CLOB/NCLOB help needed please.

[Rob Panosh]

Hello,

I am having trouble moving data from a CLOB to a NCLOB column.

My current table structure for table fmattachmentembed:
--------------------------------------------------------
fmattachmentid number(10,0) not null,
fmvalue clob null,
fmextension varchar2(4) null,
fmsize number(8,0) null

New structure desired:
----------------------
CREATE TABLE xxattachmentembed (
fmattachmentid number(10,0) not null,
fmvalue nclob null,
fmextension nvarchar2(4) null,
fmsize number(8,0) null )

Now when I execute the insert statement, below, to move the existing data
into the new table structure the
following error is encountered: "ORA-01482: unsupported character set".

Any ideas on how to fix this?

Insert Statement to move the data:
----------------------------------
INSERT /* APPEND */ INTO xxattachmentembed nologging
SELECT fmattachmentid
,Translate(fmvalue USING NVARCHAR_CS )
,fmextension
,fmsize

FROM fmattachmentembed
/

Drop the old table:
DROP TABLE fmattachmentembed
/

RENAME xxAttachmentEmbed TO fmAttachmentEmbed
/
Thanks,
Rob Panosh
Advanced Software Designs

[Rob Panosh]

Here are my database properties:

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DBTIMEZONE -07:00 DB time zone
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET AL32UTF8 Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone
format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
GLOBAL_DB_NAME PVOR9I.US.ORACLE.COM Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
NLS_RDBMS_VERSION 9.2.0.3.0 RDBMS version for NLS parameters
"Rob Panosh" <ro************************@asdsoftadfdware.com> wrote in
message news:3f********@news.splitrock.net...

Hello,

I am having trouble moving data from a CLOB to a NCLOB column.

My current table structure for table fmattachmentembed:
--------------------------------------------------------
fmattachmentid number(10,0) not null,
fmvalue clob null,
fmextension varchar2(4) null,
fmsize number(8,0) null

New structure desired:
----------------------
CREATE TABLE xxattachmentembed (
fmattachmentid number(10,0) not null,
fmvalue nclob null,
fmextension nvarchar2(4) null,
fmsize number(8,0) null )

Now when I execute the insert statement, below, to move the existing data
into the new table structure the
following error is encountered: "ORA-01482: unsupported character set".

Any ideas on how to fix this?

Insert Statement to move the data:
----------------------------------
INSERT /* APPEND */ INTO xxattachmentembed nologging
SELECT fmattachmentid
,Translate(fmvalue USING NVARCHAR_CS )
,fmextension
,fmsize

FROM fmattachmentembed
/

Drop the old table:
DROP TABLE fmattachmentembed
/

RENAME xxAttachmentEmbed TO fmAttachmentEmbed
/
Thanks,
Rob Panosh
Advanced Software Designs

[Rob Panosh]

To anybody who is interested I resolved my problem by adding the TO_NCLOB(
... ) function to my column.

INSERT /* APPEND */ INTO xxattachmentembed nologging
SELECT fmattachmentid
,TO_NCLOB(fmvalue)
,fmextension
,fmsize

Cheers,
Rob Panosh
"Rob Panosh" <ro************************@asdsoftadfdware.com> wrote in
message news:3f********@news.splitrock.net...

Hello,

I am having trouble moving data from a CLOB to a NCLOB column.

My current table structure for table fmattachmentembed:
--------------------------------------------------------
fmattachmentid number(10,0) not null,
fmvalue clob null,
fmextension varchar2(4) null,
fmsize number(8,0) null

New structure desired:
----------------------
CREATE TABLE xxattachmentembed (
fmattachmentid number(10,0) not null,
fmvalue nclob null,
fmextension nvarchar2(4) null,
fmsize number(8,0) null )

Now when I execute the insert statement, below, to move the existing data
into the new table structure the
following error is encountered: "ORA-01482: unsupported character set".

Any ideas on how to fix this?

Insert Statement to move the data:
----------------------------------
INSERT /* APPEND */ INTO xxattachmentembed nologging
SELECT fmattachmentid
,Translate(fmvalue USING NVARCHAR_CS )
,fmextension
,fmsize

FROM fmattachmentembed
/

Drop the old table:
DROP TABLE fmattachmentembed
/

RENAME xxAttachmentEmbed TO fmAttachmentEmbed
/
Thanks,
Rob Panosh
Advanced Software Designs