v$process, traceid: allowable special characters? 1

[SJSFoxPro]

I have been working on correcting (and understanding) a logon trigger that sets trace on...

In short, the trigger does the following:
1) I set a string that includes the users name to append to the trace file: s_trace_file_name := LOWER(SYS_CONTEXT('userenv', 'session_user'));
2) I alter the session to set the tracefile_identifier (v$process.traceid): EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER='''|| s_trace_file_name||'''';
3) Finally, I set trace on: EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 12''';

To accomodate errors resulting from user names including hyphens and single quotes, I have updated the trigger to replace those characters when setting the string: s_trace_file_name := replace(replace(LOWER(SYS_CONTEXT('userenv', 'session_user')),'-', '_'), '''' , '_');

I have reviewed the documentation for the TRACEFILE_IDENTIFIER parameter, and it states the properties as follows:
Parameter type is String
Syntax is TRACEFILE_IDENTIFIER = "traceid"
Default value is There is no default value.
Modifiable is ALTER SESSION
Range of Values is Any characters that can occur as part of a file name on the customer platform.

My platform is Unix and there is no problem with creating a file name that includes a hyphen.

The error is created with either of the following commands:
alter session set tracefile_identifier = "john.-.doe";
alter session set tracefile_identifier = 'john.-.doe';
ERROR:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02236: invalid file name

Works fine if the hyphen is replaced with underscore:
alter session set tracefile_identifier = "john._.doe";
alter session set tracefile_identifier = 'john._.doe';

That's the background of what has been done. My question is: Are there known characters that are INVALID in setting the TRACEFILE_IDENTIFIER? Clearly the documentation indicates that any characters are valid, but the errors indicate otherwise.

Thanks for any help!

Sorry for the post length, just trying to give all the details...

 

[karluk]

Interesting problem. You are absolutely right that this is an Oracle issue with the allowed characters in TRACEFILE_IDENTIFIER. It's possible to create a file on Unix (using "vi", for example) of the same name as the trace file containing the hyphen.

I found only one document on Metalink which addresses this issue - 306083.1. It says that the only allowed special characters are period and underscore:

The apps user has a special character in the user name.
fnd_trace calls 'alter sesion set tracefile_identifier ...' to set the tracefile identifier. The only special characters (other than alpanumeric) that tracefile_identifier accepts are "." and "_".

At a minimum this is a documentation bug. I suppose it's ok for Oracle to restrict the allowed special characters in trace file names, but the docs should reflect this, not claim that there are no restrictions.

 

[SJSFoxPro]

Thank you for pointing out the Metalink article. I like to have Oracle confirmation if I suspect a problem. Even though it addresses an issue with Oracle Applications, I'm calling the same command. So, it totally applies! There is a patch that filters out the special characters for Oracle Apps, so now I have confirmation that I need to filter for special characters in my logon trigger.

That article wasn't easy to find. I'm going to work on an update to point out the issue for anyone calling the command (not just an issue for Oracle Apps).

Thanks again!