Error banal:

ORA-27369: job of type EXECUTABLE failed with exit code: Operation not permitted STANDARD_ERROR="Error 6 initializing SQL*Plus SP2-0667: Message file sp1<lang>.msb not found SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory"

In externaljob.ora the user oracle is registered with the oinstall group.
In .bash_profile of the user oracle all variables are registered:

 [oracle@testdb ~]$ set | grep ORACLE ORACLE_BASE=/ora/app/oracle ORACLE_HOME=/ora/app/oracle/product/11.2.0/db_1 ORACLE_HOSTNAME=testdb ORACLE_SID=ORCL ORACLE_UNQNAME=ORCL

Made a primitive bash script with a redirect of the current system variables to a separate text file. If run from the console, all variables are in place. When running through an external job, the user remains oracle, but there are no variables.

How to solve a problem?

  • Are you sure that oracle starts bash? Something tells me that it immediately starts the specified executable file, without the participation of the shell program. Then perhaps it is worthwhile to explicitly perform bash with parameters that will launch an external script which in turn will already start sqlplus - Mike
  • @Mike I did just about this kind of workout. Replaced sqlplus with a softlink script that sets variables and runs the real sqlplus already. I don't want to leave such a gomosyatin, I want a beautiful solution. - tsq
  • Running an external sqlplus is already weird. why not execute the required sql queries directly. - Mike
  • Once every n-days you need to roll n-number sql files generated on the other side. - tsq
  • Why plan a task through oracle, and not use standard tools? cron. True, there will still be some problems with environment variables. The environment in this case is desirable to form globally, so that it would already be at the time of the start of the oracle itself or the krone (see /etc/env.d, although I’m not sure that it’s in your distributor) - Mike

1 answer 1

Oracle calls the executuble specified in the external job . directly, i.e. without shell and in empty surroundings. From Topicaster comment :

Replaced sqlplus with a softlink script that sets variables and runs the real sqlplus already. I don't want to leave such a gomosyatin, I want a beautiful solution.

This is actually the right decision.

Or you can create a universal task launching shell with reading login scripts, and then run the desired program in it (or even built-in, function, etc.):

 begin dbms_scheduler.create_job ( job_name => '"SH1"."shell_exec"', job_type => 'EXECUTABLE', job_action => '/bin/bash', number_of_arguments => 2, start_date => to_date('2099','yyyy'), repeat_interval => NULL, end_date => NULL, enabled => false, auto_drop => false, comments => 'execute some command in bash'); dbms_scheduler.set_job_argument_value( job_name => '"SH1"."shell_exec"', argument_position => 1, argument_value => '-lc'); dbms_scheduler.set_job_argument_value( job_name => '"SH1"."shell_exec"', argument_position => 2, argument_value => 'env > ~/env.out; echo "env ok" >/dev/stderr; exit 0'); dbms_scheduler.enable('"SH1"."shell_exec"'); end; / exec sys.dbms_scheduler.run_job('"SH1"."shell_exec"');

In ~ / env.out output of the full user environment in the mode of non-interactive login.
Another example of launching and viewing output to the standard error stream:

 begin dbms_scheduler.set_job_argument_value( job_name => '"SH1"."shell_exec"', argument_position => 2, argument_value => 'echo "try some command here" >/dev/stderr ; exit 0'); dbms_scheduler.run_job('"SH1"."shell_exec"'); end; / select additional_info from dba_scheduler_job_run_details where job_name = 'shell_exec'; ADDITIONAL_INFO STANDARD_ERROR="env ok" STANDARD_ERROR="try some command here"

Chapter 28.3.1.2.1 About External Jobs , among other things, describes how to set up the user and group with which the task will run.