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

Why do you run a script in SQLPus invoked by `DBMS_SCHEDULER`? I would prefer to run it by cron job (or Windows Scheduler Job) or create a PL/SQL procedure and execute this procedure by DBMS_SCHEDULER. — Wernfried Domscheit, Oct 18 '21 at 09:31
I would prefer to use `dbms_scheduler` to run jobs rather than using `cron`. You have a lot of more control over it ( you can create chains, dependencies between jobs, incompatibilities, etc ). In the case of using `sql_script`, I would prefer to use `external_script` instead, you will add the flexibility of shell scripting to your sql script. But that is just a preference. — Roberto Hernandez, Oct 18 '21 at 09:46

Roberto Hernandez · Answer 1 · 2021-10-18T09:37:40.210

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;
/

`NLS_LANG` parameter exist only on the client, so I doubt `SET_ATTRIBUTE` has desired effect, i.e. it affects only `NLS_LANGUAGE` and `NLS_TERRITORY` but not the character set - and I guess, that's the main concern. — Wernfried Domscheit, Oct 18 '21 at 09:28
of course `NLS_LANG` exists only in the client, either is the laptop or the database itself connecting from the Linux console. I was trying to provide some NLS settings you can alter using `dbms_scheduler`. I edit the answer. Thanks @WernfriedDomscheit — Roberto Hernandez, Oct 18 '21 at 09:36
Thank You for the examples, when I want to run SQL scripts using the scheduler in my 12.1 environment I can - call create_job (whith job_type => 'SQL_SCRIPT') or - call create_program (where program_type => 'SQL_SCRIPT' and number_of_arguments = 0). Otherwise, when program_type => 'SQL_SCRIPT' and number_of_arguments = 1 the block including dbms_scheduler.create_program returns ora-27458 - a program of type plsql_block cannot have any arguments. Some SQL PLUS scripts , can take a long time - i 'd prefer to test them using the scheduler capability (avoiding conn. issues etc). — user_alex, Dec 08 '21 at 16:02

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)

1 Answers1