RDBMS ORACLE 23ai - Utilizzo di DBMS_SCHEDULER per esecuzione script sql su filesystem

 Partiamo dal seguente riepilogo e poi approfondiamo l'ultima riga:

Se vogliamo eseguire il file   

                            /opt/sched_cdd/wrapper_0.0/sql/TESTCRT_JOB.sql 

dal filesystem NON possiamo usare PLSQL_BLOCK o SQL_SCRIPT - occorre utilizzare il tipo EXECUTABLE.

• PASSO 1: Preparazione del file TESTCRT_2_parametri.sql sul filesystem

[oracle@localhost ~]$ more /opt/sched_cdd/wrapper_0.0/sql/TESTCRT_2_parametri.sql

DECLARE 

BEGIN

EXECUTE IMMEDIATE 'create table appo_wrapper_&p1  as select * from cat ';

EXECUTE IMMEDIATE 'create table appo_wrapper_&p2 as select * from cat ';

END;

/

• PASSO 2: Grant necessarie per creazione/esecuzione job (eseguire come SYSDBA se necessario)

Prima di creare il job per l'esecuzione del file dobbiamo settare l'ambiente (DB) affinchè esegua il job senza problemi.

•  Come SYSDBA, verificare le grant che mancano

SELECT 'GRANT ' || privilege || ' TO metadata_gest;' AS comando_grant

FROM (

  SELECT 'CREATE JOB' AS privilege FROM dual

  UNION ALL

  SELECT 'CREATE EXTERNAL JOB' FROM dual

  UNION ALL

  SELECT 'CREATE CREDENTIAL' FROM dual

) required

WHERE privilege NOT IN (

  SELECT privilege 

  FROM dba_sys_privs 

  WHERE grantee = 'METADATA_GEST'

);

    eseguire come SYSDBA se necessario

GRANT CREATE EXTERNAL JOB TO METADATA_GEST;

GRANT EXECUTE, DEBUG on "SYS"."DBMS_SCHEDULER" to "METADATA_GEST" ;

GRANT EXECUTE, DEBUG on "SYS"."DBMS_ISCHED" to "METADATA_GEST" ;

GRANT CREATE JOB TO metadata_gest;

GRANT CREATE CREDENTIAL TO metadata_gest;

• PASSO 3: Creare una CREDENTIAL per l'esecuzione del job appartenent all'utente metadata_gest
• -- username --> Utente OS con cui eseguire il comando
• -- password --> Password Utente SO

BEGIN

  DBMS_SCHEDULER.DROP_CREDENTIAL('OS_CREDENTIAL');

END;

BEGIN

  DBMS_SCHEDULER.CREATE_CREDENTIAL(

    credential_name => 'OS_CREDENTIAL',

    username        => 'oracle',  

    password        => 'oracle'   

  );

END;

=========================

-- TEST CREDENTIAL -

=========================

• PASSO 1: Verifica che la credential esista

SELECT  *

FROM user_scheduler_credentials

WHERE credential_name = 'OS_CREDENTIAL';

• PASSO 2: Creazione di un job di test minimo

SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24:MI:SS') FROM DUAL;

-- occorre per determinare l'orario di inizio test--

BEGIN

 DBMS_SCHEDULER.CREATE_JOB (

    job_name            => 'TEST_CREDENTIAL',

    job_type            => 'EXECUTABLE',

    job_action          => '/bin/echo',

    number_of_arguments => 1,

    credential_name     => 'OS_CREDENTIAL',

    enabled             => FALSE

  );

  

  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(

    job_name          => 'TEST_CREDENTIAL',

    argument_position => 1,

    argument_value    => 'Test credential OK'

  );

  

  DBMS_SCHEDULER.ENABLE('TEST_CREDENTIAL');

  

  -- Esegue il job appena creato come utente METADATA_GEST

  DBMS_SCHEDULER.RUN_JOB('TEST_CREDENTIAL', FALSE);

END;

• PASSO 3: Verifica il risultato (dopo 5-10 secondi)

SELECT 

    owner,job_name,status,log_date,additional_info, error#

FROM user_scheduler_job_run_details

WHERE job_name = 'TEST_CREDENTIAL'

ORDER BY log_date DESC

FETCH FIRST 1 ROWS ONLY;

• PASSO 4: Interpreta il risultato della query relativa al passo 3

Se STATUS = 'SUCCEEDED' → Credential corretta

Se STATUS = 'FAILED' e ERROR# = 27369 → Password errata o utente non esiste

Se STATUS = 'FAILED' e ERROR# = 27486 → Privilegi insufficienti

• PASSO 5: Cleanup

BEGIN

  DBMS_SCHEDULER.DROP_JOB('TEST_CREDENTIAL', force => TRUE);

END;

/

=========================

-- TEST JOB_TEST_CRT_SQL

=========================

• PASSO 1: Drop e Creazione del job External che esegue lo script sql
• in input i parametri P1 e P2
• utente proprietario e che esegue il JOB METADATA_GEST

BEGIN

    DBMS_SCHEDULER.DROP_JOB(job_name =>

                                '"METADATA_GEST"."JOB_TEST_CRT_SQL"',

                                defer => false,

                                force => true);

END;

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name            => 'JOB_TEST_CRT_SQL',

    job_type            => 'EXECUTABLE',

    job_action          => '/bin/bash',

    number_of_arguments => 2,

    credential_name     => 'OS_CREDENTIAL',

    enabled             => FALSE,

    auto_drop           => FALSE,

    comments            => 'Esegue test.sql con parametri P1 e P2'

  );

END;

• PASSO 2: Impostazione degli argomenti del job
• Il flag -c indica a bash di eseguire il comando che segue come stringa.
• Spiegazione:
• bash/bin/bash -c "comando da eseguire"
• c = "command" - dice a bash di leggere ed eseguire i comandi dalla stringa che segue. Senza -c, bash si aspetterebbe il nome di un file script da eseguire
• --Nel nostro caso:
• Argomento 1: il programma da eseguire  job_action => '/bin/bash'
• Argomento 2: flag che dice "esegui il comando che segue" argument 1 => '-c'
• Argomento 3: il comando vero e proprio
• argument 2 => 
• 'sqlplus -S username/password@TNS @/dire/test/test.sql "P1" "P2"'
• Risultato finale è come avere la seguente stringa da eseguire da filesystem
• /bin/bash -c 'sqlplus -S username/password@TNS @/dire/test/test.sql "P1" "P2"'

BEGIN

-- Argomento 1: flag -c per bash

  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(

    job_name          => 'JOB_TEST_CRT_SQL',

    argument_position => 1,

    argument_value    => '-c'

  );

• Argomento 2: comando completo con sqlplus e parametri
• Modificare i parametri P1 e P2 senza ricreare il job
• Per vedere l'output di sqlplus su filesystem si può aggiungere un logging unix:

  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(

    job_name          => 'JOB_TEST_CRT_SQL',

    argument_position => 2,

    argument_value    => '/opt/oracle/product/23ai/dbhomeFree/bin/sqlplus -s                              A00/oracle@FREEPDB1 

                        @/opt/sched_cdd/wrapper_0.0/sql/TESTCRT_2_parametri.sql                                                 "VALORE_P1" "VALORE_P2"'

   );

   END;

/

• Se dopo l'esecuzione dovessimo ricevere un errore SP2-0667 allora occorre settare le variabili di ambiente dell'ORACLE_HOME e quindi modificare quanto indicato sopra in:

SET DEFINE OFF;
BEGIN
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
    job_name          => 'JOB_TEST_CRT_SQL',
    argument_position => 2,
    argument_value    => 'export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree &&             

                          expot PATH=$ORACLE_HOME/bin:$PATH &&     

                          export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH &&

sqlplus -s A00/oracle@FREEPDB1 @/opt/sched_cdd/wrapper_0.0/sql/TESTCRT_2_parametri.sql "VALORE_P1" "VALORE_P2"'

  );

END;

/

• Se dopo l'esecuzione riceviamo l'errore SP2-0546 allora tocca modificare lo script sql sostituendo &p1 ed &p2 con &1 ed &2 e rinomino il file sql da eseguire in TESTCRT_job.sql

[oracle@localhost ~]$ more /opt/sched_cdd/wrapper_0.0/sql/TESTCRT_job.sql 

DECLARE 

BEGIN

EXECUTE IMMEDIATE 'create table appo_wrapper_&1  as select * from cat ';

EXECUTE IMMEDIATE 'create table appo_wrapper_&2 as select * from cat ';

END;

/

SET DEFINE OFF;

BEGIN

  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(

    job_name          => 'JOB_TEST_CRT_SQL',

    argument_position => 2,

    argument_value    => 'export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree && 

                          export PATH=$ORACLE_HOME/bin:$PATH && 

                          export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH && 

                            sqlplus -s A00/oracle@FREEPDB1 @/opt/sched_cdd/wrapper_0.0/sql/TESTCRT_job.sql

                            "VALORE_P1" "VALORE_P2"'

  );

END;

PL/SQL procedure successfully completed.

•  PASSO : Abilita ed esegue il job

Verifichiamo l'orario di esecuzione cosi da verificarlo anche sulle tabelle di loh

SELECT TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS') data FROM DUAL;
DATA             
-----------------
20251118 18:41:58

BEGIN
  DBMS_SCHEDULER.ENABLE('JOB_TEST_CRT_SQL');
END;

PL/SQL procedure successfully completed.

• PASSO : Verifica il risultato (dopo 5-10 secondi)

SELECT   owner,job_name,status,log_date,additional_info, output,error#

FROM    user_scheduler_job_run_details

WHERE job_name = 'JOB_TEST_CRT_SQL'

ORDER BY log_date DESC

FETCH FIRST 1 ROWS ONLY;

OWNER         JOB_NAME          STATUS     LOG_DATE                         ADDITIONAL_INFO                                     ERROR#      OUTPUT

------------- ----------------- ---------- -------------------------------- -------------------------------------------------- -----------  ----------------------------------------------------------------------------------------

METADATA_GEST JOB_TEST_CRT_SQL  SUCCEEDED  18-NOV-25 18:43:15,189036000 GMT EXTERNAL_LOG_ID="job_96768_80906",USERNAME="oracle"  0          

"old   4: EXECUTE IMMEDIATE 'create table appo_wrapper_&1  as select * from cat ';                             new   4: EXECUTE IMMEDIATE 'create table appo_wrapper_VALORE_P1  as select * from cat ';

 old   5: EXECUTE IMMEDIATE 'create table appo_wrapper_&2 as select * from cat ';

 new   5: EXECUTE IMMEDIATE 'create table appo_wrapper_VALORE_P2 as select * from cat ';   

• PASSO : Interpretazione del risultato

Se STATUS = 'SUCCEEDED' → Credential corretta

Se STATUS = 'FAILED' e ERROR# = 27369 → Password errata o utente non esiste

Se STATUS = 'FAILED' e ERROR# = 27486 → Privilegi insufficienti

Se STATUS = 'FAILED' e EXTERNAL_LOG_ID="job_96768_80884",

            ORA-27369: job of type EXECUTABLE failed with exit code: 1 Error 6 initializing SQL*Plus

            SP2-0667: Message file sp1<lang>.msb not found

            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

            → settare le variabili di ambiente

Se STATUS = 'FAILED' e "Enter value for p1: 

            SP2-0546: User requested Interrupt or EOF detected.

            Help: https://docs.oracle.com/error-help/db/sp2-0546/"

            

• PASSO : Verifica sul DB

SELECT * FROM CAT where table_name like 'APPO%'

TABLE_NAME                TABLE_TYPE 

------------------------- -----------

APPO_WRAPPER_VALORE_P1    TABLE      

APPO_WRAPPER_VALORE_P2    TABLE      

Di seguito le query di controllo delle esecuzione dei diversi job e verifiche varie:

======================

-- GESTIONE DEL JOB

======================

-- Per eseguire immediatamente il job (test manuale)

BEGIN

  DBMS_SCHEDULER.RUN_JOB(

    job_name => 'JOB_TEST_CRT_SQL',

    use_current_session => FALSE

  );

END;

/

-- Disabilitare il job

BEGIN

  DBMS_SCHEDULER.DISABLE('JOB_TEST_CRT_SQL');

END;

/

-- Eliminare il job

BEGIN

  DBMS_SCHEDULER.DROP_JOB(

    job_name => 'JOB_TEST_CRT_SQL',

    force    => TRUE

  );

END;

/

-- Eliminare la credential

BEGIN

  DBMS_SCHEDULER.DROP_CREDENTIAL('OS_CREDENTIAL');

END;

/

======================

-- Query DB oracle

======================

-- Verificare lo stato del job

SELECT job_name, state, enabled, last_start_date, next_run_date, failure_count

FROM user_scheduler_jobs

WHERE job_name = 'JOB_TEST_CRT_SQL';

-- Vedere il log delle esecuzioni

SELECT log_date, status, error#, additional_info

FROM user_scheduler_job_run_details

WHERE job_name = 'JOB_TEST_CRT_SQL'

ORDER BY log_date DESC;

-- Vedere i dettagli dell'output del job

SELECT log_date, owner,operation,status,credential_name,additional_info

FROM user_scheduler_job_log

WHERE job_name = 'JOB_TEST_CRT_SQL'

ORDER BY log_date DESC;

=================================

-- VERIFICA ESITO ESECUZIONE JOB

=================================

-- 1. Stato corrente del job

SELECT 

    job_name,

    state,              -- SCHEDULED, RUNNING, COMPLETED, etc.

    enabled,

    last_start_date,

    last_run_duration,

    next_run_date,

    run_count,

    failure_count

FROM user_scheduler_jobs

WHERE job_name = 'JOB_TEST_CRT_SQL';

-- 2. Storico delle esecuzioni (dettagliato)

SELECT 

    log_id,

    log_date,

    job_name,

    status,             -- SUCCEEDED, FAILED, STOPPED, etc.

    error#,

    additional_info

FROM user_scheduler_job_run_details

WHERE job_name = 'JOB_TEST_CRT_SQL'

ORDER BY log_date DESC

FETCH FIRST 10 ROWS ONLY;

-- 3. Log completo con output

SELECT 

    log_date,

    status,

    req_start_date,

    actual_start_date,

    run_duration,

    cpu_used

FROM user_scheduler_job_log

WHERE job_name = 'JOB_TEST_CRT_SQL'

ORDER BY log_date DESC;

-- 4. Se il job è FALLITO, vedi i dettagli dell'errore

SELECT 

    log_date,

    status,

    error#,

    additional_info

FROM user_scheduler_job_run_details

WHERE job_name = 'JOB_TEST_CRT_SQL'

AND status = 'FAILED'

ORDER BY log_date DESC;

-- 5. Verifica se il job è in esecuzione in questo momento

SELECT 

    job_name,

    session_id,

    running_instance,

    elapsed_time,

    cpu_used

FROM user_scheduler_running_jobs

WHERE job_name = 'JOB_TEST_CRT_SQL';

-- 6. Output dettagliato del job (se configurato)

SELECT 

    *

FROM user_scheduler_job_log

WHERE job_name = 'METADATA_GEST.JOB_TEST_CRT_SQL'

ORDER BY log_date DESC;

--Interpretazione dei risultati:

--STATUS = 'SUCCEEDED' → Esecuzione completata con successo

--STATUS = 'FAILED'    → Esecuzione fallita (guarda error# e additional_info)

--STATUS = 'STOPPED'   → Job interrotto manualmente

--STATE  = 'RUNNING'   → Job in esecuzione ora

--STATE  = 'SCHEDULED' → Job in attesa della prossima esecuzione

-- 1. Verifica il dettaglio completo dell'errore

SELECT 

    log_date,

    status,

    error#,

    additional_info,

    actual_start_date

FROM user_scheduler_job_run_details

WHERE job_name = 'JOB_TEST_CRT_SQL'

ORDER BY log_date DESC

FETCH FIRST 1 ROWS ONLY;

"EXTERNAL_LOG_ID="job_96755_80368",

ORA-27369: job of type EXECUTABLE failed with exit code: 127 /bin/bash: sqlplus: command not found

STANDARD_ERROR="/bin/bash: sqlplus: command not found

""

-- 2. Verifica se la CREDENTIAL è valida

SELECT 

 credential_name,    username,    comments

FROM user_scheduler_credentials

WHERE credential_name = 'OS_CREDENTIAL';