1 Reply Latest reply on Mar 12, 2013 3:41 PM by Peter Gjelstrup

    ORA-00932 with MERGE INTO and clob columns

    ovasen
      Hi.
      We a an environment ported from Postgresql to Oracle. Server version is 11.2.0.3 64 bit on Linux x86-64.
      The two tables that should be joined in the SQL are:
      SQL> desc localizations
      Navn Null? Type
      ----------------------------------------- -------- ------------------
      ID NOT NULL NUMBER(38)
      CREATED_AT NOT NULL DATE
      UPDATED_AT NOT NULL DATE
      BLURB_ID NUMBER(38)
      DRAFT_CONTENT NOT NULL CLOB
      LOCALE_ID NUMBER(38)
      PUBLISHED_CONTENT NOT NULL CLOB
      PUBLISHED_VERSION_ID NUMBER(38)

      SQL> desc versions
      Navn Null? Type
      ----------------------------------------- -------- ------------------
      ID NOT NULL NUMBER(38)
      CREATED_AT NOT NULL DATE
      UPDATED_AT NOT NULL DATE
      CONTENT NOT NULL CLOB
      LOCALIZATION_ID NUMBER(38)
      NUMBER NOT NULL NUMBER(38)

      SQL>

      The original Postgresql syntax was:
      UPDATE localizations
      SET published_version_id = latest_version.id,
      published_content = latest_version.content,
      updated_at = '2013-03-07 16:35:53'
      FROM (
      SELECT DISTINCT ON (localization_id) localization_id, id, content
      FROM versions ORDER BY localization_id DESC, id DESC

      ) AS latest_version
      WHERE latest_version.localization_id = localizations.id
      AND localizations.id IN (11186);

      So I have tried to convert it to Oracle syntax with the following:

      SQL> MERGE
      2 INTO (
      3 SELECT published_version_id, published_content,id,updated_at
      4 FROM localizations
      5 WHERE ID = 11186
      6 ) l
      7 USING (
      8 SELECT DISTINCT (localization_id) localization_id, id, content
      9 FROM versions where ID=11186
      10 ORDER BY localization_id DESC, id DESC
      11 ) v
      12 ON (l.id=v.localization_id)
      13 WHEN MATCHED THEN
      14 UPDATE
      15 SET l.published_version_id=v.id,
      16 l.published_content=v.content,
      17 l.updated_at = to_Date('2013-03-07 16:35:53','yyyy-mm-dd hh24:mi:ss')
      18 /
      SELECT DISTINCT (localization_id) localization_id, id, content
      *
      FEIL på linje 8:
      ORA-00932: inkonsekvente datatyper: forventet -, fikk CLOB
      But I seem unable to get around this. Anyone seen this before?
      Thanks

      Kjell Ove