Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Todd.

Asked: May 29, 2008 - 12:44 pm UTC

Last updated: February 17, 2009 - 8:55 am UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

Tom,

This is a rather simple question. But is it possible to insert characters like ¢ into a varchar2.

Does it have anything to do with the way the database is formated? Using UTF8 or something?

I'm very new to oracle and I'm not sure how to escape these characters, or if they can even be used at all...

Everytime I try to insert a "cent symbol" (¢), once I commit it changes it to a "?".

Any ideas?

Thanks,

Todd

and Tom said...

http://docs.oracle.com/cd/B19306_01/server.102/b14225/toc.htm

It has to do with the character set the database was created in.

For example, my database:

ops$tkyte%ORA11GR1> select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8MSWIN1252


is WE8MSWIN1252, an 8 bit character set. If I set my client NLS_LANG (a registry setting on windows, environment variable on other platforms) to US7ASCII - then Oracle will perform character set conversion.


On the way into the database, Oracle knows my data is 7 bit ascii data - it will convert that data into the 8 bit representation. On the way out, Oracle knows my client can only support 7 bit data and will map anything that takes 8 bits into 7 bits (eg: some characters might disappear - become question marks - because there is no mapping, no way to represent that)


Each character set supports a different set of characters - the cent symbol is part of some character sets and not others (depends on who the character set is designed for).

It is actually a rather complex question in reality. Storing "globalized" data is fairly difficult.



You would have to see what characterset your database supports - that'll let you know if you can even store that character (if that character is not part of your databases character set, you cannot store it in a varchar2)

You would have to see what characterset your clients use as well - even if the DATABASE supports storing the cent symbol - if your clients say "I use this characterset, please convert to it as we retrieve data", your cent symbol will go away for them.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Special character for password

sriram, February 17, 2009 - 5:47 am UTC

Tom,

Was experimenting with the usage of special characters on passwords.

SQL> alter user scott identified by test!dm;
alter user scott identified by test!dm
                                    *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> alter user scott identified by 'test!dm';
alter user scott identified by 'test!dm'
                               *
ERROR at line 1:
ORA-00988: missing or invalid password(s)


SQL> alter user scott identified by  values 'test!dm';

User altered.

SQL> conn scott/test!dm;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn scott/'test!dm';

Where am i going wrong ? , can i not use "!" for passwords?

Thanks


Tom Kyte
February 17, 2009 - 8:55 am UTC

it would be a rather bad idea - there will be many tools not capable of dealing with them.

quoted identifiers:

ops$tkyte%ORA9IR2> alter user scott identified by "FOO!BAR"
  2  /

User altered.

ops$tkyte%ORA9IR2> connect scott/"FOO!BAR"
Connected.
ops$tkyte%ORA9IR2>

ops$tkyte%ORA9IR2> connect scott/foo!bar
Connected.



character set

A reader, February 17, 2009 - 12:15 pm UTC


Special character for password

Branka Bowman, October 08, 2009 - 10:18 am UTC

I tried to use password with @.  I got error.  What would be solution for it?

  1* alter user branka identified by "test@password"
SQL> /

User altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\>sqlplus branka/"test@password"

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 8 11:09:23 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:

A reader, September 27, 2010 - 12:15 am UTC


Double Quotes

Marcus Fuchs, June 20, 2018 - 9:19 am UTC

quoted identifiers:
ops$tkyte%ORA9IR2> alter user scott identified by "FOO!BAR"
  2  /

User altered.


That solved my issue. I wasn't even using special characters, but my password only contained an L which - apparently - indicated a value of datatype LONG to Oracle.

Best regards,
Marcus