How to set NLS_LANG env.variable for sql script jobs initiated using dbms_scheduler and executed on the db.server (Oracle ver.>=12.1)

Question

From Oracle version 12.1 it's possible to execute sql scripts (based on the database server SQLPlus environment) using dbms_scheduler API calls. (It’s necessary to define a host credential (in my case oracle linux user), a database credential (for a specific schema) and to write a procedure with a few dbms_scheduler API calls to set up a new sql job executed on the database server using it's SQLPlus environment , with the script source taken - for example - from a client's laptop’s file system or from an another network location. The job is defined and started from a client machine.)

When I execute exactly the same script on my windows laptop (using client SQLPlus) I can set NLS_LANG, before starting sqlplus (for example to something like:

CZECH_CZECH REPUBLIC.AL32UTF8 or

CZECH_CZECH REPUBLIC EE8MSWIN1250) etc. )

Also it’s possible to start ( local ) sqlplus executable in a specific directory where the output (spool files) will be generated.

If I set up a job with dbms_scheduler executed in the SQLPlus environment on the database server (as mentioned before and with the script source taken from local/client filesystem ):

Question 1: is it possible to set before the start of the script on the database server SQL Plus environment the NLS_LANG environment variable in a similar way like it is done in a local ( client) SQLPlus environment ?

(obviously, set is a command in windows environment; I need to do something similar on my database server sqlplus environment i.e. on a linux machine)

Question 2 is it possible to change (before the initiation of the scheduled sqlplus job on the database server SQL Plus environment) the working directory ?

(now i get the script spool output in the directory where init.ora resides)

It could be very useful to be able to use dbms_scheduler jobs for example to initiate long lasting sql scripts (without any modification of the source code) in the database server SQL Plus environment (after working hours /in unattended mode etc.)

Thank You in advance for your answers. (I've already asked a similar question on an Oracle forum but without any answers in the last 2 weeks).

Kind Regards,

user_alex

Share on Twitter

Answer 1

Answering your questions

Is it possible to set before the start of the script on the database server SQL Plus environment the NLS_LANG environment variable in a similar way like it is done in a local ( client) SQLPlus environment ?

It depends.

You can set the SET_ATTRIBUTE procedure to set any NLS environment for a job using the NLS_ENV attribute. But it will only change values of NLS variables in the job when it is running. NLS_LANG only affects the client settings. So if you run a script that needs to set NLS_LANG with a specific characterset, such characterset belongs to the client, not to the database, thus you can't change it.

An option, nonetheless, would be to have the SQL script in the database server and run it using EXTERNAL_SCRIPT using shell script, then you can change it inside the shell script called.

Example

BEGIN
    dbms_scheduler.create_job( job_name => 'MY_TEST',
                               job_type => 'PLSQL_BLOCK', 
                               job_action => 'BEGIN NULL; END;', 
                               number_of_arguments => 0,
                               start_date=> systimestamp + 1 , 
                               repeat_interval=>'freq=hourly; byminute=0; bysecond=0;',
                               enabled =>FALSE, 
                               auto_drop=>TRUE,
                               comments=> 'Test' 
                             );
   dbms_scheduler.set_attribute('MY_TEST','NLS_ENV','NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' ');
   dbms_scheduler.enable('MY_TEST');
END;
/

Demo

SQL> BEGIN
  2     dbms_scheduler.create_job( job_name => 'MY_TEST',
  3                                                        job_type => 'PLSQL_BLOCK',
  4                                                        job_action => 'BEGIN NULL; END;',
  5                                                        number_of_arguments => 0,
  6                                                        start_date=> systimestamp + 1 ,
  7                                                        repeat_interval=>'freq=hourly; byminute=0; bysecond=0;',
  8                                                        enabled =>FALSE,
  9                                                        auto_drop=>TRUE,
 10                                                        comments=> 'Test'
 11                                                      );
 12     dbms_scheduler.set_attribute('MY_TEST','NLS_ENV','NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' ');
 13     dbms_scheduler.enable('MY_TEST');
 14* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.run_job('MY_TEST') ;

PL/SQL procedure successfully completed.

Question 2 is it possible to change (before the initiation of the scheduled sqlplus job on the database server SQL Plus environment) the working directory ?

Well, you can use arguments in the job. That could be an option, but it is not clear what you want here. Once the job is set, you can't change it. If you have a sqlplus script that runs with an input parameter as working directory, you can use arguments in dbms_scheduler to emulate such behaviour. But in this case you need to use programs instead.

dbms_scheduler.create_program(program_name        => 'YOUR_PROGRAM',
                              program_type        => 'SQL_SCRIPT',                                                          
                              program_action      => 'my_test_scr', 
                              number_of_arguments => 1,
                              enabled             => false,
                              credential_name     => 'YOUR_CREDENTIAL' ,
                              comments            => 'Comments you want');

dbms_scheduler.define_program_argument(program_name      => 'YOUR_PROGRAM',
                                       argument_name     => 'param1',
                                       argument_position => 1,
                                       argument_type     => 'VARCHAR2',
                                       default_value     => '');

dbms_scheduler.enable (name => 'YOUR_PROGRAM');

dbms_scheduler.create_job(job_name        => 'my_test_job',
                          program_name    => 'YOUR_PROGRAM',
                          start_date      => systimestamp,
                          end_date        => null,
                          ...

Then you can run the job as

declare
    v_var1 varchar2(50) := '/my_dir';
begin
    dbms_scheduler.set_job_argument_value(
        job_name          => 'my_test_job',
        argument_position => 1,
        argument_value    => v_var1);
    dbms_scheduler.run_job(
        job_name            => 'my_test',
        use_current_session => false);
end;
/