Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    10

    Unanswered: ASCII null character (chr(0)) at the end of the VARCHAR2 string

    Hi all.

    I have a personalised session summary table called SUMMARY_TAB that retrieves machine information from v$session via trigger.
    i.e.
    SQL>> insert into summary_tab (host_name) values (select machine from v$session).

    The ASCII NULL character appears in the v$session table for some machines, which is originally where the information is retrieved from.

    SQL>> select * from v$session where instr(machine,(chr(0)),1,1) > 1
    --this returns a number of machines.

    Does anybody have any idea, why the machine name in v$session contains that character. I suspect it's something to do with the name that the database is passed through.

    Also, how could I alter the trigger that populates SUMMARY_TAB, to remove that character from the host_name field. As, ofcourse NULL character also exists in the SUMMARY_TAB.

    Any hints or solution ideas will be much appreciated.
    Many Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> select * from v$session where instr(machine,(chr(0)),1,1) > 1;
    
    no rows selected
    It appears it is something unique to your environment.
    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 2002
    Location
    Reading, UK
    Posts
    1,137
    This might do the trick

    insert into summary_tab (host_name) values (select replace(machine,chr(0),'') from v$session)

    NOTE '' is two single quotes

    Alan

  4. #4
    Join Date
    May 2008
    Posts
    10
    hi Alan,

    Thanks a lot. Your trick definitely works.
    I altered the trigger which populated the machine information into my SUMMARY_TAB table. And the replace function seems to be converting the ASCII null character chr(0), into NULL. Which solves my problem for the SUMMARY_TAB.
    So, thanks again.

    Any idea why this is occuring in the v$session???

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Any idea why this is occuring in the v$session???
    here is what we know - You are claiming a problem exists
    Here is what we don't know -
    Operating system name & version
    Oracle version to 4 decimal places.
    How many different Oracle instances are running at your site & how many manifest this purported problem?
    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.

Posting Permissions

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