Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    16

    Unanswered: Varchar2 Problem

    I made a program that iterates over ASCII Characters [01,FF].
    For each character, the program try insert it in varchar2(10) column, and try read it back using a SELECT...WHERE CH=<the caracter>.
    The problem is that some characters are well inserted but not retrieved (SELECT return no rows). Some of those chars are (in hexa):

    [80,9F]
    A4
    A6
    A8
    B4
    B6
    B8
    BC
    BD
    BE

    Why this happen?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Why this happen?
    True ASCII is within the range of 1 - 127;
    while FF is 255.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2004
    Posts
    16
    Thanks anyway but that is not an answer! I explain why...

    When I tell ASCII I really meaning 8 bits (01 through FF). 00 is the NULL and for me is not valid because my program is written in C... and in C the NULL is the string terminator!

    But the problem is more complicated than that. My program, that reports the failure in some chars in my database, runs well in my customer ORACLE Database. That's why I put the question in the forum... i'm sure there is a ORACLE parameter that I forgot...

    I use AMERICAN_AMERICA.WEISO8855P15

    Could anybody help me? Any tip is appreciated!

  4. #4
    Join Date
    Jan 2004
    Posts
    370
    I use AMERICAN_AMERICA.WEISO8855P15
    That is your NLS_LANG setting on your client machine.

    What is the DATABASE characterset?

    select nls_characterset from nls_database_parameters;

  5. #5
    Join Date
    Mar 2004
    Posts
    16
    Thanks for the tip. My ORACLE parameters (database, instance and session) are the following:

    select * from nls_database_parameters;
    =======================================
    NLS_LANGUAGE AMERICAN
    NLS_TERRITORY AMERICA
    NLS_CURRENCY $
    NLS_ISO_CURRENCY AMERICA
    NLS_NUMERIC_CHARACTERS .,
    NLS_CHARACTERSET WE8ISO8859P1
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_DATE_LANGUAGE AMERICAN
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
    NLS_DUAL_CURRENCY $
    NLS_COMP BINARY
    NLS_NCHAR_CHARACTERSET WE8ISO8859P1
    NLS_RDBMS_VERSION 8.1.7.0.0

    select * from nls_instance_parameters;
    ======================================
    NLS_LANGUAGE AMERICAN
    NLS_TERRITORY AMERICA
    NLS_SORT
    NLS_DATE_LANGUAGE
    NLS_DATE_FORMAT
    NLS_CURRENCY
    NLS_NUMERIC_CHARACTERS
    NLS_ISO_CURRENCY
    NLS_CALENDAR
    NLS_TIME_FORMAT
    NLS_TIMESTAMP_FORMAT
    NLS_TIME_TZ_FORMAT
    NLS_TIMESTAMP_TZ_FORMAT
    NLS_DUAL_CURRENCY
    NLS_COMP

    select * from nls_session_parameters;
    =====================================
    NLS_LANGUAGE AMERICAN
    NLS_TERRITORY AMERICA
    NLS_CURRENCY $
    NLS_ISO_CURRENCY AMERICA
    NLS_NUMERIC_CHARACTERS .,
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_DATE_LANGUAGE AMERICAN
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
    NLS_DUAL_CURRENCY $
    NLS_COMP BINARY

    I hope the help you to help me.
    Many thanks in advance.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    With the code below I put 255 values into the table & get 255 back out.
    DECLARE
    ALPHA VARCHAR2(9);
    VALUE NUMBER;
    BEGIN
    DBMS_OUTPUT.ENABLE(100000);
    FOR II IN 1..255 LOOP
    INSERT INTO TEST_VARCHAR VALUES (TO_CHAR(II));
    END LOOP;
    COMMIT;
    SELECT COUNT(*) INTO VALUE FROM TEST_VARCHAR;
    DBMS_OUTPUT.PUT_LINE(VALUE);
    FOR II IN 1..255 LOOP
    SELECT STRING INTO ALPHA FROM TEST_VARCHAR where string = (TO_CHAR(II));
    DBMS_OUTPUT.PUT_LINE(ALPHA);
    END LOOP;
    END;
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Mar 2004
    Posts
    16
    Perhaps could you send me the environment variables of your ORACLE database, to help me understanding why I can't select the characters I told you?

    The nls_database_parameters, nls_instance_parameters and nls_session parameters I appreciated also.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What do you see when running MY code (& not your code)?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Mar 2004
    Posts
    16
    I copy&paste your code inside SQLPlus and ran it. I get no output although SQLPlus reports success. The table TESTE_VARCHAR has numbers from 1 to 255. And? What you want to prove?

  10. #10
    Join Date
    Mar 2004
    Posts
    16

    PROBLEM SOLVED

    The problem has solved.

    I change in HKEY_LOCAL_MACHINE/Software/ORACLE/home0 the NLS_LANG parameter for

    ENGLISH_UNITED KINGDOM.WEISO8855P1

    And all characters in [01,FF] are inserted and retrieved!

  11. #11
    Join Date
    Mar 2004
    Posts
    16

    PROBLEM SOLVED (2)

    My mistake:

    ENGLISH_UNITED KINGDOM.WE8ISO8859P1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •