This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Oct 10, 2012 4:02 AM by CInglez RSS

Generic Currency Symbol

CInglez Newbie
Currently Being Moderated
Please, I need to generate the generic currency symbol Alt+207 using SqlDeveloper, but I cannot make it work. It always changes to an inverted question mark. Already tried to change the encoding to 8859p1, but the result is the same.

Thanks
  • 1. Re: Generic Currency Symbol
    rp0428 Guru
    Currently Being Moderated
    >
    Please, I need to generate the generic currency symbol Alt+207 using SqlDeveloper, but I cannot make it work. It always changes to an inverted question mark. Already tried to change the encoding to 8859p1, but the result is the same.
    >
    There is no such thing as a 'generic currency symbol'. The currency symbol used by Oracle products depends on one or several NLS parameters.

    See the discussion of 'currency' in 3 Setting Up a Globalization Support Environment of the Globalization Support Guide
    >
    NLS_CURRENCY specifies the character string returned by the L number format mask, the local currency symbol. Setting NLS_CURRENCY overrides the setting defined implicitly by NLS_TERRITORY.
    . . .
    NLS_ISO_CURRENCY specifies the character string returned by the C number format mask, the ISO currency symbol. Setting NLS_ISO_CURRENCY overrides the value defined implicitly by NLS_TERRITORY.
    . . .
    Use NLS_DUAL_CURRENCY to override the default dual currency symbol defined implicitly by NLS_TERRITORY.

    NLS_DUAL_CURRENCY was introduced to support the euro currency symbol during the euro transition period. See Table A-8, "Character Sets that Support the Euro Symbol" for the character sets that support the euro symbol.
    >
    Each of those sections above shows examples of the output based on different parameter settings.

    So when you say 'I need to generate the generic currency symbol' what are you talking about? Where are you 'trying to generate' it? Are you trying to use it in a query predicate?

    Explain what you are trying to do.
  • 2. Re: Generic Currency Symbol
    CInglez Newbie
    Currently Being Moderated
    Well, so let me explain better what does not exist.

    In SqlPlus, using NLS_LANG=BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1, if I type select Chr(207) from dual, it gives me ¤. This is the generic currency symbol that you say does not exist. It is used on some printing machines. If on Windows, open notepad ant type Alt+207.

    The thing is, I can generate this character on sqlplus, but not on SqlDeveloper. I can generate it using Alt+207 on the query window, but not in the result window.

    It is an special character. Please don't tell me it does not exist, we have been using it for years with older tools (like Forms 6i), but we cannot reproduce the same results on Forms 11g, nor on SqlDeveloper.

    http://www.theasciicode.com.ar/extended-ascii-code/generic-currency-sign-ascii-code-207.html

    Thanks anyway.
  • 3. Re: Generic Currency Symbol
    rp0428 Guru
    Currently Being Moderated
    >
    It is an special character. Please don't tell me it does not exist, we have been using it for years with older tools (like Forms 6i), but we cannot reproduce the same results on Forms 11g, nor on SqlDeveloper.
    >
    You need to get a cup of coffee (decaf) and reread my reply. I didn't say that currency symbols don't exist or that Alt+207 (when used with the proper character set) doesn't exist or isn't a currency symbol. What I said was
    >
    There is no such thing as a 'generic currency symbol'.
    >
    What is, or isn't a currency symbol depends on the character set, language settings, territory settings or, in general, globalization settings.

    So if we are now on the same page do you want to take a shot at providing the information that was ask for?
    >
    So when you say 'I need to generate the generic currency symbol' what are you talking about? Where are you 'trying to generate' it? Are you trying to use it in a query predicate?

    Explain what you are trying to do.
    >
    You need to explain what it is you are trying to do, the steps you are taking to do it and the results you are getting.
    >
    In SqlPlus, using NLS_LANG=BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1, if I type select Chr(207) from dual, it gives me ¤.
    >
    So did you set the NLS properties at Tools => Preferences => Database => NLS to the values you want to use?
    There are properties there for Language, Territory, ISO Currency, Currency among others.

    That's why I'm asking you to explain what steps you are taking so we can try to reproduce what you are doing and see if we get the problem you are describing.
  • 4. Re: Generic Currency Symbol
    CInglez Newbie
    Currently Being Moderated
    Thanks for the reply.

    My final target is an Oracle Forms app, where we used to generate a text file using Text_IO. This character '¤' is already saved on a database table. This file is used on a check printing machine. The forms app read the column on the database and outputs it to the text file.

    It always worked with forms 6i (using the brazilian NLS settings I exposed). But it does not work on 11g. The same behaviour is seen on SqlDeveloper. Perhaps anything with JVM? I don't know. But I am trying to reproduce the character on SqlDeveloper to track the problem. But I cannot. Even if I type

    select '¤' text from dual;

    On the result window I get

    TEXT
    ----
    ¿

    So, I can type the character in the query window (using Alt+207), but I cannot make it to the result window.

    Database Characterset is WE8ISO8859P15.

    Thanks again, and sorry if I seemed rude.
  • 5. Re: Generic Currency Symbol
    jmsjr Newbie
    Currently Being Moderated
    CInglez wrote:
    Thanks for the reply.

    My final target is an Oracle Forms app, where we used to generate a text file using Text_IO. This character '¤' is already saved on a database table. This file is used on a check printing machine. The forms app read the column on the database and outputs it to the text file.

    It always worked with forms 6i (using the brazilian NLS settings I exposed). But it does not work on 11g. The same behaviour is seen on SqlDeveloper. Perhaps anything with JVM? I don't know. But I am trying to reproduce the character on SqlDeveloper to track the problem. But I cannot. Even if I type

    select '¤' text from dual;

    On the result window I get

    TEXT
    ----
    ¿

    So, I can type the character in the query window (using Alt+207), but I cannot make it to the result window.

    Database Characterset is WE8ISO8859P15.

    Thanks again, and sorry if I seemed rude.
    What output do you get for the following:

    select '¤' as currency, dump( '¤' , 1016 ) as hex from dual;
    select * from v$nls_parameters;
    select * from nls_session_parameters;
  • 6. Re: Generic Currency Symbol
    Irian Pro
    Currently Being Moderated
    Maybe the real problem is not in the character generation but in the font used.
    The special char you are using probably does not exist in the font currently used by sqldeveloper for the result grid, but it exists in the font used in the SQL worksheet.
    Try setting something like Arial Unicode or the GNU Unifont or whatever font supports the '¤' sign in:
    Tools -> Preferences -> Code Editor -> Fonts
    With this property set to Arial Unicode I was able to run
    select '¤' text from dual;
    without problems.

    I cannot say anything about Forms, but maybe there are similar problems and the Forms forum should be a better place to check for this.

    Forms
  • 7. Re: Generic Currency Symbol
    jmsjr Newbie
    Currently Being Moderated
    rp0428 wrote:

    In SqlPlus, using NLS_LANG=BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1, if I type select Chr(207) from dual, it gives me ¤.
    So did you set the NLS properties at Tools => Preferences => Database => NLS to the values you want to use?
    There are properties there for Language, Territory, ISO Currency, Currency among others.

    That's why I'm asking you to explain what steps you are taking so we can try to reproduce what you are doing and see if we get the problem you are describing.
    Unfortunately, you can't set the client / session character set via that menu option, only the language and territory, but you can set the character set via the NLS_LANG environment variable.

    I also noticed that the original poster here mentioned that he set the NLS_LANG to have a character set of WE8ISO8859P1, and then in a later post mentioned that the character set on the database is WE8ISO8859P15 ( Latin-1 vs Latin-9 ) ... and what's one of the differences between the two ? the 'generic currency symbol' is replaced by the euro currency symbol.

    Also, the reference to Alt+207 should really be changed to Alt+0164. The only reference I could find for Alt+207 being the Unicode character generic currency symbol is a website that says it is an ASCII extended character. Yes, both Alt+207 and Alt+0164 shows the same character in Notepad and SQLDeveloper. I'm just wondering if Alt+207 is interpreted differently on the original poster's PC instead of using Alt+0164.

    ( On a side note, in a linux console that supports UTF-8 and using a Unicode font, you don't have to add the leading zero, simply type Alt+164 )

    http://www.fileformat.info/info/unicode/char/a4/index.htm
    http://www.fileformat.info/info/unicode/char/a4/charset_support.htm

    The original poster should also indicate what font is being used. It maybe that the font used does not support that character.
  • 8. Re: Generic Currency Symbol
    CInglez Newbie
    Currently Being Moderated
    Hi.

    1) select '¤' as currency, dump( '¤' , 1016 ) as hex from dual;

    CURRENCY HEX
    -------- -------------------------------------------
    ¿ Typ=96 Len=1 CharacterSet=WE8ISO8859P15: bf

    2) select * from v$nls_parameters;

    ARAMETER VALUE
    ---------------------------------------------------------------- ----------------------------------------------------------------
    NLS_LANGUAGE ENGLISH
    NLS_TERRITORY BRAZIL
    NLS_CURRENCY R$
    NLS_ISO_CURRENCY BRAZIL
    NLS_NUMERIC_CHARACTERS ,.
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD/MM/YYYY
    NLS_DATE_LANGUAGE BRAZILIAN PORTUGUESE
    NLS_CHARACTERSET WE8ISO8859P15
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH24:MI:SSXFF
    NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF
    NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
    NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR
    NLS_DUAL_CURRENCY Cr$
    NLS_NCHAR_CHARACTERSET AL16UTF16
    NLS_COMP BINARY
    NLS_LENGTH_SEMANTICS CHAR
    NLS_NCHAR_CONV_EXCP FALSE

    3) select * from nls_session_parameters;

    PARAMETER VALUE
    ------------------------------ ----------------------------------------
    NLS_LANGUAGE ENGLISH
    NLS_TERRITORY BRAZIL
    NLS_CURRENCY R$
    NLS_ISO_CURRENCY BRAZIL
    NLS_NUMERIC_CHARACTERS ,.
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD/MM/YYYY
    NLS_DATE_LANGUAGE BRAZILIAN PORTUGUESE
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH24:MI:SSXFF
    NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF
    NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
    NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR
    NLS_DUAL_CURRENCY Cr$
    NLS_COMP BINARY
    NLS_LENGTH_SEMANTICS CHAR
    NLS_NCHAR_CONV_EXCP FALSE

    Thanks
  • 9. Re: Generic Currency Symbol
    CInglez Newbie
    Currently Being Moderated
    Hi.

    I don´t know what I am doing wrong, but even using Arial (I don´t see an Arial Unicode option), the results are the same.

    select '¤' from dual;

    '¿'
    ---


    Thanks
  • 10. Re: Generic Currency Symbol
    CInglez Newbie
    Currently Being Moderated
    Hi.

    I have tried setting NLS_LANG as environment variable, with no success. I tried with Courier New and Arial fonts. Aren't those Unicode?

    Thanks
  • 11. Re: Generic Currency Symbol
    jmsjr Newbie
    Currently Being Moderated
    CInglez wrote:
    Hi.

    1) select '¤' as currency, dump( '¤' , 1016 ) as hex from dual;

    CURRENCY HEX
    -------- -------------------------------------------
    ¿ Typ=96 Len=1 CharacterSet=WE8ISO8859P15: bf
    Well, according to that one alone, you literally sent the INVERTED QUESTION MARK character, as it is encoded as 0xBF in ISO-8859-1/9/15:

    http://www.fileformat.info/info/unicode/char/bf/charset_support.htm

    So it is not a font issue as what I had suspected earlier, but it seems like, in your case but not mine, your session actually sent the INVERTED QUESTION MARK character to the server, and that's why you get 0xBF in the dump.

    Next questions would be:

    1) Do you have NLS_LANG set from the client where you have SQLDeveloper running ? If so, what is it set to ?
    2) Have you tried the same ( dump ) via SQL*Plus ?
    3) Maybe the guys here can answer if SQL*Developer uses the NLS_LANG environment variable.
  • 12. Re: Generic Currency Symbol
    CInglez Newbie
    Currently Being Moderated
    1) Yes, I have tried both with and without NLS_LANG set as an environment variable. It was set as BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1

    2) select '¤' as currency, dump( '¤' , 1016 ) as hex from dual;
    C HEX
    - -------------------------------------------
    ¤ Typ=96 Len=1 CharacterSet=WE8ISO8859P15: cf

    3) I really don´t know...
  • 13. Re: Generic Currency Symbol
    Irian Pro
    Currently Being Moderated
    Normally these fonts are not unicode by default, Arial Unicode MS is quite common, because it comes with the MS Office suite.

    Since you do not have this font your best option could be to download and install the GNU Unifont, or look through the list and find a font with "unicode" mentioned in the description.
    http://unifoundry.com/unifont.html
    But since the dump from the database returns garbage it will probably not help (worth a try thou).
  • 14. Re: Generic Currency Symbol
    jmsjr Newbie
    Currently Being Moderated
    CInglez wrote:
    1) Yes, I have tried both with and without NLS_LANG set as an environment variable. It was set as BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1

    2) select '¤' as currency, dump( '¤' , 1016 ) as hex from dual;
    C HEX
    - -------------------------------------------
    ¤ Typ=96 Len=1 CharacterSet=WE8ISO8859P15: cf

    3) I really don´t know...
    What did you do differently here, because:

    1) You are now returning 0xCF instead of 0xBF from your previous post. 0xCF, however, is the encoded form of "LATIN CAPITAL LETTER I WITH DIAERESIS" in ISO-8859-1/9/15:

    http://www.fileformat.info/info/unicode/char/cf/charset_support.htm

    2) Your output ( or at least your post here ), now shows the currency symbol


    Also:

    3) What do you get if you set NLS_LANG so that it the charset is WE8ISO8859P15 instead of WE8ISO8859P1 ?

    4) Have you tried using Alt+0164 instead of Alt+207?

    5) Have you tried using the CHR() function as well .. specifying the code point of the currency symbol for YOUR character set ?

    http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions019.htm
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points