This content has been marked as final. Show 5 replies
R. Royal wrote:
I need to change my dbms_scheduler job to a PLSQL_BLOCK and use DBMS_SESSION.SET_NLS in there before I call my procedure (the program_name MY_PRG call my stored procedure).
I don't want drop e recreate 'MY_JOB' but modify NLS_NUMERIC_CHARACTERS into column NLS_ENV of DBA_SCHEDULER_JOBS.
I tried this:
name => 'MY_JOB',
attribute => 'job_action',
value => 'BEGIN DBMS_SESSION.SET_NLS(''NLS_NUMERIC_CHARACTERS''='',.''); MY_PRG; END;');
but I get this error:
ORA-27488: unable to set JOB_ACTION because PROGRAM_NAME was/were already set
ORA-06512: at "SYS.DBMS_ISCHED", line 2986
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1847
ORA-06512: at line 1
Have you any idea?
Thanks in advance!
27488, 00000, "unable to set %s because %s was/were already set" // *Cause: An attempt was made to set an object's attribute even though one // or more conflicting attributes of the same object had already been // set. // *Action: Set the other conflicting attributes to NULL and then reissue // the command.
but the problem is on attribute: NLS_ENV
exec dbms_scheduler.disable( 'MY_JOB' );
PL/SQL procedure successfully completed.
exec dbms_scheduler.set_attribute_null ('MY_JOB' ,'nls_env');
ORA-27469: NLS_ENV is not a valid job attribute
I'd like modify the NLS_ENV or NLS_NUMERIC_CHARACTERS on JOB
Is it possible?
You must drop job and create it again. Set session NLS settings as you want before create job. nls_env is get from session job was created.
Or you can use execute immediate 'alter session set ...' in PL/SQL code called in job_action or program.
Or you could try to use dbms_isched but it is undocumented (similar as dbms_ijob for all fasion job).