0

Whenever i try to save any record in oracle with hyphen in the middle of sentence then it is saving as inverted question mark in oracle. This is happening only when i execute this insert query with other queries combined and execute all at a time using SQL plus application. But this is not happening when i execute insert query individually using SQL developer. Why is this happening and what is the solution for this.?

Aleksej
  • 22,443
  • 5
  • 33
  • 38
VasuDev
  • 31
  • 1
  • 8
  • What do you get in SQLPlus for a "select '-' result from dual;" command? There maybe some problem with your default escaping, hyphen should not be a special char by default, I think. – Galcoholic May 26 '17 at 07:58
  • When i try to get results with '-' in sql plus it is returning 0 records. – VasuDev May 26 '17 at 08:09
  • 2
    The hyphen on your keyboard is ASCII 45 and is supported by all character sets. You must be pasting an extended hyphen or something funny. – sandman May 26 '17 at 08:27
  • Entering the above select you should not get 0 records, DUAL do has one record. Type exactly that command, you must get one result row. – Galcoholic May 26 '17 at 08:32

2 Answers2

2

SQL Developer is Java based which does not use NLS_LANG settings but SQL*Plus does, so you have to set NLS_LANG properly.

You did not tell us which hyphen you use, see answer from MT0. In your question you took U+002D (CHR(45)) which is in ASCII - for sure this does make any problem.

SQL*Plus inherits character set from calling command line, you must set NLS_LANG accordingly, for example like this:

C:\>chcp 1252
Active code page: 1252

C:\>set NLS_LANG=.WE8MSWIN1252

C:\>sqlplus ...

SQL> SELECT UNISTR( '\002D' ) AS HyphenMinus,
  2         UNISTR( '\2010' ) AS Hyphen,
  3         UNISTR( '\2011' ) AS NonBreakingHyphen,
  4         UNISTR( '\2012' ) AS FigureDash,
  5         UNISTR( '\2013' ) AS EnDash,
  6         UNISTR( '\2014' ) AS EmDash,
  7         UNISTR( '\2015' ) AS HorizontalBar,
  8         UNISTR( '\2043' ) AS HyphenBullet
  9  FROM   DUAL;

H H N F E E H H
- - - - - - - -
- - - ¿ – — ¿ ¿

SQL>

You see FigureDash, HorizontalBar and HyphenBullet are not supported by CP1252, depending on your requirement you have to choose another character set, e.g. UTF-8

C:\>chcp 65001
Active code page: 65001

C:\>set NLS_LANG=.AL32UTF8
C:\>sqlplus ...

Have a look at this answer to get some more information about NLS_LANG settings.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
1

There are multiple different hyphen/dash/minus characters:

SELECT UNISTR( '\002D' ) AS HyphenMinus,
       UNISTR( '\2010' ) AS Hyphen,
       UNISTR( '\2011' ) AS NonBreakingHyphen,
       UNISTR( '\2012' ) AS FigureDash,
       UNISTR( '\2013' ) AS EnDash,
       UNISTR( '\2014' ) AS EmDash,
       UNISTR( '\2015' ) AS HorizontalBar,
       UNISTR( '\2043' ) AS HyphenBullet
FROM   DUAL

Only - (CHR(45)) is within the base ASCII character set. If your column only supports this then the characters within the extended character sets may not display properly.

Check what character set your database is using and you may need to either:

  • change the database's character set to something that supports the characters you are using; or
  • use a NVARCHAR2 data type rather than VARCHAR2
  • Only use the supported characters in your inputs (and reject any strings that contain chatacters in the extended character set).
MT0
  • 143,790
  • 11
  • 59
  • 117
  • 1
    Since SQL-Developer inserts characters properly 1st and 2nd proposal should not be valid. Apparently the database and used datatype supports desired characters properly. – Wernfried Domscheit May 26 '17 at 12:06