how to find the default character set of a database schema

Posted on 2009-04-29
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

    select * from database_properties
    LVL 18

    Assisted Solution

    select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
    LVL 18

    Expert Comment

    if you want to know how to set up
    LVL 47

    Assisted Solution

    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.
    LVL 7

    Accepted Solution


    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

    LVL 7

    Author Closing Comment

    thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    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…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to take different types of Oracle backups using RMAN.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now