[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


how to find the default character set of a database schema

Posted on 2009-04-29
Medium Priority
Last Modified: 2013-12-18

I would like to know the default character set of my oracle database schema. There are some spanish content within my schema tables which are displaying good in my local environment but when we promote those table to the client environment using stored procedures some wierd reverse '?' kind of data is dispaying for some spanish characters. I would like to set up same character set that is there in the local machine over there at client place also before running the stored procedures. Any links, resources, ideas, sample code highly appreciated. Thanks in advance.
Question by:gudii9
LVL 18

Expert Comment

ID: 24262582
select * from database_properties
LVL 18

Assisted Solution

sventhan earned 800 total points
ID: 24262629
select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
LVL 18

Expert Comment

ID: 24262705
if you want to know how to set up
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 48

Assisted Solution

schwertner earned 200 total points
ID: 24263267
The client machine should have Spanish Keyboard activated and localized for Spanish. The application should be able to display these letters.

I do not believe you use SQL*Plus, but if it so set NLS_LANG to Spanish.
Alternativelly make experiment with iSQLPlus.

Accepted Solution

Piloute earned 1000 total points
ID: 24270624

First of all, characterset is a database parameter, not a schema one. You set it when you create your database.

So if you have charset issues, it's about your whole database, not only a schema. SQLNet converts charsets while tranferring form a machine to the other, depending on each machines setup.

What are your charactersets in source and target environments ?
            select NLS_CHARACTERSET from database_properties;

How do you do your "transfer" ? What is the "stored procedure" you use ? Custom or some standard code ?


How do you query the target database ? Is it with the same client (your computer) as the source one ? If it is your client who sais they only see '?' characters, they might just have a wrong setup on their client machines, thus not showing them the real answer ....

To make sure you have the same text stored in a field in both databases, do a select with the function dump() :
              select dump(your_col_here,16) from your_table_here where whatever_you_want;

This will allow you to get the hexadecimal values of whatever you have stored in your field in each database. Then compare those two answers. If the answer is the same, it means you don't have any problem and it's all about some strange conversion when getting the data from the database. If the values are different, it means
- either you are using in the source db a charset that can not be converted to the target db charset
- or you forced a conversion that is not correct


Author Closing Comment

ID: 31576024
thank you

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month19 days, 21 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question