WARNING: no Oracle character set for database encoding "SQL_ASCII" - Resolved

During migration from Oracle to PostgreSQL, the problem arises in the situation below.

     CREATE EXTENSION oracle_fdw;
     CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//<oracle database IP address>:<port>/<oracle database name>');
      GRANT USAGE ON FOREIGN SERVER oradb TO postgres;
      CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user '<oracle user>', password '<oracle password>')");
      CREATE FOREIGN TABLE f_table (. . . . . .) SERVER oradb OPTIONS (schema '<oracle schema name>', table '<oracle table name>');
      SELECT * FROM f_table;

Returns:

WARNING:  no Oracle character set for database encoding "SQL_ASCII"
DETAIL:  All but ASCII characters will be lost.
HINT:  You can set the option "nls_lang" on the foreign data wrapper to force an Oracle character set.

To fix this problem: set the nls_lang option for the foreign data wrapper.

      CREATE EXTENSION oracle_fdw;
      CREATE FOREIGN DATA WRAPPER oracle_fdw2 HANDLER oracle_fdw_handler VALIDATOR oracle_fdw_validator OPTIONS (nls_lang 'AMERICAN_AMERICA.WE8MSWIN1252');

(The value of the nls_lang must be in constant with the charset of the oracle database. Use the following SQL statement to find out the charset of your oracle database: SELECT * FROM V$NLS_PARAMETERS; A list of the nls_lang values can be found at https://docs.oracle.com/cd/B19306_01/install.102/b14317/gblsupp.htm#NTDBX382.)

      CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw2 OPTIONS (dbserver '//<oracle database IP address>:<port>/<oracle database name>');
      GRANT USAGE ON FOREIGN SERVER oradb TO postgres;
      CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user '<oracle user>', password '<oracle password>')");
      CREATE FOREIGN TABLE f_table (. . . . . .) SERVER oradb OPTIONS (schema '<oracle schema name>', table '<oracle table name>');
      SELECT * FROM f_table;

Returns the data from the table.

References:

1. How to set nls_lang?

---------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.