I have an MVIEW on the DEV server as
CREATE MATERIALIZED VIEW MY_MVIEW
SELECT col1 AS "N° INVOICE" from TABLE
The MVIEW has been installed on PROD server and when i check for its query i have the characther
° not recognized
SELECT query from user_mviews where mview_name = 'MY_MVIEW' ;
SELECT col1 AS "N? INVOICE" from TABLE
Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - 64bit Production
for both environments .
Any idea why this happened and how to correct it? or I need to rename my alias?
Whenever you see a question mark instead of a special character it means that the client's character set does not support that character and a conversion was not available.
The conversion happens with the database character set as the source and the client character set as the target. The environment variable NLS_LANG lets you control the client character set. NLS_LANG must be set before the connection is opened and it cannot be changed during the session.
For more information about NLS_LANG refer to the Oracle® Database Globalization Support Guide
BTW, NLS_LANGUAGE has nothing to do with the character set! It only controls language used in server messages and locale settings.
I would definitely recommend renaming your alias to something simple like NO_INVOICE that doesn't contain any unusual characters and doesn't need embedding in double quotes to reference. It is not appropriate to treat column names as if they are formatted headings for reports - do that in the report. With non-standard characters like this you are going to get problems with different environmental settings like NLS_LANG.