CLOB/NCLOB help needed please.

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Rob Panosh
    #1

    CLOB/NCLOB help needed please.

    Hello,

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

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

    New structure desired:
    ----------------------
    CREATE TABLE xxattachmentemb ed (
    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 xxattachmentemb ed nologging
    SELECT fmattachmentid
    ,Translate(fmva lue USING NVARCHAR_CS )
    ,fmextension
    ,fmsize

    FROM fmattachmentemb ed
    /

    Drop the old table:
    DROP TABLE fmattachmentemb ed
    /

    RENAME xxAttachmentEmb ed TO fmAttachmentEmb ed
    /


    Thanks,
    Rob Panosh
    Advanced Software Designs


    Tags: None
    • Rob Panosh
      #2
      Re: CLOB/NCLOB help needed please.

      Here are my database properties:

      PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
      DICT.BASE 2 dictionary base tables version #
      DEFAULT_TEMP_TA BLESPACE 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_CURRENC Y AMERICA ISO currency
      NLS_NUMERIC_CHA RACTERS ., Numeric characters
      NLS_CHARACTERSE T AL32UTF8 Character set
      NLS_CALENDAR GREGORIAN Calendar system
      NLS_DATE_FORMAT DD-MON-RR Date format
      NLS_DATE_LANGUA GE AMERICAN Date language
      NLS_SORT BINARY Linguistic definition
      NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
      NLS_TIMESTAMP_F ORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
      NLS_TIME_TZ_FOR MAT HH.MI.SSXFF AM TZR Time with timezone format
      NLS_TIMESTAMP_T Z_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone
      format
      NLS_DUAL_CURREN CY $ Dual currency symbol
      NLS_COMP BINARY NLS comparison
      NLS_LENGTH_SEMA NTICS BYTE NLS length semantics
      NLS_NCHAR_CONV_ EXCP FALSE NLS conversion exception
      NLS_NCHAR_CHARA CTERSET AL16UTF16 NCHAR Character set
      GLOBAL_DB_NAME PVOR9I.US.ORACL E.COM Global database name
      EXPORT_VIEWS_VE RSION 8 Export views revision #
      NLS_RDBMS_VERSI ON 9.2.0.3.0 RDBMS version for NLS parameters


      "Rob Panosh" <rob_!!!NO!!!SP AM!!!_panosh@as dsoftadfdware.c omwrote in
      message news:3fbe4a4b$1 @news.splitrock .net...
      Hello,
      >
      I am having trouble moving data from a CLOB to a NCLOB column.
      >
      My current table structure for table fmattachmentemb ed:
      --------------------------------------------------------
      fmattachmentid number(10,0) not null,
      fmvalue clob null,
      fmextension varchar2(4) null,
      fmsize number(8,0) null
      >
      New structure desired:
      ----------------------
      CREATE TABLE xxattachmentemb ed (
      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 xxattachmentemb ed nologging
      SELECT fmattachmentid
      ,Translate(fmva lue USING NVARCHAR_CS )
      ,fmextension
      ,fmsize
      >
      FROM fmattachmentemb ed
      /
      >
      Drop the old table:
      DROP TABLE fmattachmentemb ed
      /
      >
      RENAME xxAttachmentEmb ed TO fmAttachmentEmb ed
      /
      >
      >
      Thanks,
      Rob Panosh
      Advanced Software Designs
      >
      >

        Comment

        • Rob Panosh
          #3
          Re: CLOB/NCLOB help needed please.

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

          INSERT /* APPEND */ INTO xxattachmentemb ed nologging
          SELECT fmattachmentid
          ,TO_NCLOB(fmval ue)
          ,fmextension
          ,fmsize

          Cheers,
          Rob Panosh


          "Rob Panosh" <rob_!!!NO!!!SP AM!!!_panosh@as dsoftadfdware.c omwrote in
          message news:3fbe4a4b$1 @news.splitrock .net...
          Hello,
          >
          I am having trouble moving data from a CLOB to a NCLOB column.
          >
          My current table structure for table fmattachmentemb ed:
          --------------------------------------------------------
          fmattachmentid number(10,0) not null,
          fmvalue clob null,
          fmextension varchar2(4) null,
          fmsize number(8,0) null
          >
          New structure desired:
          ----------------------
          CREATE TABLE xxattachmentemb ed (
          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 xxattachmentemb ed nologging
          SELECT fmattachmentid
          ,Translate(fmva lue USING NVARCHAR_CS )
          ,fmextension
          ,fmsize
          >
          FROM fmattachmentemb ed
          /
          >
          Drop the old table:
          DROP TABLE fmattachmentemb ed
          /
          >
          RENAME xxAttachmentEmb ed TO fmAttachmentEmb ed
          /
          >
          >
          Thanks,
          Rob Panosh
          Advanced Software Designs
          >
          >

            Comment

            Previous template Next
            Working...