Expand my Community achievements bar.

SOLVED

nvarchar to varchar2 (Oracle)

Avatar

Level 2

Hi All.

I have one question about oracle datatype.

When I  try to insert data by FDA, 'ORA-12704' error has occured.

        Detail information,

     1) Adobe Campaign Database : Oracle, nvarchar data type

     2) ODS(External Database) : Oracle, varchar2 data type

1524491_pastedImage_0.png

Please let me know how can I solve this problem.

Thanks,

Best Regards

Daeho

1 Accepted Solution

Avatar

Correct answer by
Employee

HI Daeho,

You can also check character set in both databases.

Database

As mentioned in the link shared by Ananya , this issue could be

  • The string operands (other than an NLSPARAMS argument) to an operator or built-in function do not have the same character set.
  • An NLSPARAMS operand is not in the database character set.
  • String data with character set other than the database character set is passed to a built-in function not expecting it.

Also you can enable to sql log and see what actual query is being generated when you perform update and how it is different from the query which is there in Java script.

View solution in original post

3 Replies

Avatar

Employee

Hello daehob42316144

It seems the error appears because of mismatch of column types. Could you ask your DB team to change fields from varchar2 to nvarchar ( On the DB )  and see if that helps

Here is an link for the same error discussed in one of the Oracle forums

ORA-12704 character set mismatch

Regards,

Ananya Kuthiala

Avatar

Level 2

Hello Ananya Kuthiala

Thanks for your response.

But I can not change DB fields.

Because it is not our database.

In adobe campaign,

case 1 : By javascript code (using SQL Query)

var cnx = application.getConnection();

var table = vars.tableName;

var iSql = "MERGE INTO UOCMS02.APP_CAR_BUY_CNT APP ";

    iSql = iSql + " USING ";

    iSql = iSql + "        (SELECT IFAMILY_REPURCHASE_CNT ";

    iSql = iSql + "        , ISELF_REPURCHASE_CNT ";

    iSql = iSql + "        , SI_DI ";

    iSql = iSql + "        , SI_RESNO ";

    iSql = iSql + "        FROM " + table +" ";

    iSql = iSql + "        WHERE 1=1 ";

    iSql = iSql + "    ) SO ";

    iSql = iSql + " ON (APP.I_RESNO_DI = SO.SI_DI) ";

    iSql = iSql + " WHEN MATCHED THEN ";

    iSql = iSql + "    UPDATE SET ";

    iSql = iSql + "        APP.RE_BUY_CNT = SO.ISELF_REPURCHASE_CNT ";

    iSql = iSql + "        , APP.FAMILY_RE_BUY_CNT = SO.IFAMILY_REPURCHASE_CNT ";

    iSql = iSql + "        , REG_DT = SYSDATE ";

    iSql = iSql + " WHEN NOT MATCHED THEN ";

    iSql = iSql + "    INSERT (I_RESNO_DI, RE_BUY_CNT, FAMILY_RE_BUY_CNT, REG_DT) ";

    iSql = iSql + "    VALUES(SO.SI_DI, SO.ISELF_REPURCHASE_CNT , SO.IFAMILY_REPURCHASE_CNT , SYSDATE) ";

sqlExec(iSql);

It is ok. there is no error. But,

case 2 : By update activity (using FDA external database)

1524878_pastedImage_1.png

@I_RESNO_DI : VARCHAR2

@I_DI : NVARCHAR2

In this case, 'ORA-12704' error has occured.

I think if we can insert (or update) by query without no convert datatype, adobe campaign can insert (or update) too.

Please let me know any other tip or advice.

Thanks

Best regards,

Daeho

Avatar

Correct answer by
Employee

HI Daeho,

You can also check character set in both databases.

Database

As mentioned in the link shared by Ananya , this issue could be

  • The string operands (other than an NLSPARAMS argument) to an operator or built-in function do not have the same character set.
  • An NLSPARAMS operand is not in the database character set.
  • String data with character set other than the database character set is passed to a built-in function not expecting it.

Also you can enable to sql log and see what actual query is being generated when you perform update and how it is different from the query which is there in Java script.