plsql job on oracle

Referring to this AskTom thread, you cannot do this until 12c, at least without the use of sh or bat files:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9528221800346588826

What version do you use?

I have solved such task in Oracle 11g.
The trick is to put the SQl script (after some modifications) in a PL/SQL procedure.

Sample:

CREATE OR REPLACE PROCEDURE gevos_script
IS
  v_count   INTEGER;
  v_message VARCHAR2(373);
BEGIN
 INSERT INTO schufa_migration_load_log (running_date, exit_code,  activity,  note)
                              VALUES  (SYSDATE,      'run', 'start', 'Script_16_12_2015');
 COMMIT;
   UPDATE bsi_case_frame ccc
   SET ccc.STATUS_UID       = 75959
   WHERE ccc.case_frame_nr IN
  (SELECT cf.case_frame_nr
  FROM bsi_case cc
  JOIN bsi_case_frame cf
  ON cc.case_frame_nr = cf.CASE_FRAME_NR
  WHERE cc.evt_end   IS NOT NULL
  AND cc.STATUS_UID  IN (117526,75959)
  AND cc.X_VSS_QUEUE IS NOT NULL
  AND cf.STATUS_UID   = 75958
  );
 COMMIT;
 INSERT INTO schufa_migration_load_log (running_date, exit_code,  activity,  note)
                              VALUES  (SYSDATE,      'success', 'stop', 'Script_16_12_2015');
 COMMIT;
EXCEPTION
  WHEN OTHERS THEN
  v_message := SUBSTR(sqlerrm,1,254);
  INSERT INTO schufa_migration_load_log (running_date, exit_code,  activity,            note)
                             VALUES     (SYSDATE,      'error!!', 'Script_16_12_2015',  v_message);
  COMMIT;
END gevos_script;
/


Run it once time;

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'GEVOS',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN  gevos_script; END;',
   start_date           =>  TO_TIMESTAMP_TZ('16-DEZ-2015 18.30.00 Europe/Berlin' ,'DD-MON-YYYY HH24:MI:SS TZR'),
   repeat_interval      =>  NULL,
   end_date             =>  NULL,
   enabled              =>  TRUE,
   comments             => 'Procedure Calls PLSQL once');
END;
/

i using 12c oracle database.

@schwertner: this is NOT what she asked for!

@Jasmin shahrzad: perfect! then your're able to do something like it's shown here: https://oracle-base.com/articles/12c/scheduler-enhancements-12cr1#sql-script

@schwertner. it's exactly what i did. i create my sqlscript as function and schedule my procedure to run .
but my problem is i have more than 100 package (func. proc.)now. then maintenance for package and all jobs.
what i want is remove all sql (package) and move sql to linux. and schedule sql scripts in oracle database. then i can use it also the same in other
(test, developer) database.
@Alexander: i don't understand this article.
is it mean, if i have a sql like that:
SPOOL /tmp/test.lst
SELECT SYSDATE, USER FROM dual;
SPOOL OFF';

then i can schedule it like that,
 DBMS_SCHEDULER.create_job(
    job_name        => l_job_name,
    job_type        => 'SQL_SCRIPT',
    job_action      => l_script,
    credential_name => 'oracle_ol6_121',
    enabled         => TRUE
  );
END;
/

or what. i can't get it to work.

or what. i can't get it to work.

The guide/link describes quite well how to schedule SQL scripts in 12c. What exactly don't you understand or what errors do you get?!

It is not good to put your SQl script in a function. Because the functions return one value. Whom will you return a value? The PL/SQL block runs on the server and there is no  classical "client" to get the result. So first thing is to use a procedure and to decide what are the results and where to store then. But possibly I have understood you wrong.

I have scheduled may maintenance procedures (to maintain the tables, to send reports via emails and so on) using scheduled procedures. The PL/SQL procedures are the most powerful tool in Oracle. The only problem is that can't directly correspond the client. So (if they do not change the parameters of the Oracle server) you can either store the result data in a table (or update existing tables) or to send mails with reports.

@schwertner: Your comment is far too general and vague as we still don't know, what the asker is up to. What he asked for is a way to run SQL files via dbms_scheduler. This can be done from 12c onwards as mentioned above. It would be interesting to know what he really like to archive and what's inside those SQL files being executed. But that's just another question and in fact not being asked here ;-)
Apart from that:
1.

The only problem is that can't directly correspond the client

Not fully true! There are ways to do things like that!
2.

The PL/SQL procedures are the most powerful tool in Oracle.

Nope! If possible and applicable, always stick to PACKAGES, rather than using mere procedures and/or functions as there are numerous advantages that come "out of the box" (like caching)!

@ Alexander Eßer:
?????????

Sind Ihre Aussagen nicht ueberfluessig?

@schwertner:
!!!!!!!!!

Sind Ihre Aussagen nicht ueberfluessig?

Das denke ich nicht! Es sind eher Ihre Kommentare, die hier nichts bringen!

Ihre Aussagen helfen hier nicht sonderlich weiter, sind irrefüührend und teilweise schlichtweg falsch bzw. inkorrekt!

@schwertner: Abgesehen davon: Offensichtlich sind Sie null kritikfähig. Warum nur?!

what i did was.
i create a credential
BEGIN
  DBMS_CREDENTIAL.create_credential(
    credential_name => 'myoracle',
    username        => 'oracle',
    password        => 'oracle123'
  );
END;
/
i have a simple sql in /home/oracle/sql/ab.sql
it's (set serveroutput on
spool /home/oracle/sql/ab.log
select sysdate from dual;
spool off; )
then i create a dbms_scheduler.create_program
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
   program_name           => 'check_time',
   program_action         => '/home/oracle/sql/ab.sql',
   program_type           => 'EXECUTABLE',
   comments               => 'My comments here');
END;

then i create a dbms_job

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  time_job,
   program_name        =>  check_time,
   start_date          =>  '24-MAR-17 1.00.00AM',
   enabled             =>  TRUE,
   comments            =>  'check time');
END;
/

nothing happen all plsql are successfully completed.


it's not working.
/
dbms_

One way to understand what has happen is to query to log views:


   COLUMN owner FORMAT a5
   SELECT owner,
          job_name,
          job_action,
          start_date,
          next_run_date
   FROM   dba_scheduler_jobs;

Please change 'SCRIPT_EVENING' with the name of your dbms_scheduler job:

SELECT * FROM dba_scheduler_jobs WHERE job_name = 'SCRIPT_EVENING';



set linesize 1000
set pagesize 1000
COLUMN "owner" FORMAT A10;
COLUMN "start_date" FORMAT A20;
COLUMN "repeat_interval" FORMAT A12;
COLUMN "end_date" FORMAT A20;
COLUMN "enabled" FORMAT A10;

SELECT owner, job_name,job_action,start_date,repeat_interval,end_date,enabled FROM dba_scheduler_jobs WHERE job_name = 'SCRIPT_EVENING';



SELECT * FROM dba_scheduler_job_log WHERE job_name = 'SCRIPT_EVENING';


set linesize 1000
set pagesize 1000
COLUMN "owner" FORMAT A10;
COLUMN "log_date" FORMAT A20;
COLUMN "job_name" FORMAT A20;
COLUMN "operation" FORMAT A20;
COLUMN "status" FORMAT A10;  
 
SELECT owner, TO_CHAR(log_date,'dd-MON-yyyy HH24:MI:SS') AS log_date, job_name, operation, status
FROM dba_scheduler_job_log
WHERE job_name = 'SCRIPT_EVENING'
ORDER BY log_date;


SELECT additional_info
FROM dba_scheduler_job_log
WHERE job_name = 'SCRIPT_EVENING'
ORDER BY log_date;

 



set linesize 1000
set pagesize 1000
COLUMN "owner" FORMAT A10;
COLUMN "schedule_name" FORMAT A40;
COLUMN "schedule_type" FORMAT A20;
COLUMN "start_date" FORMAT A25;
COLUMN "repeat_interval" FORMAT A60;  
 

SELECT owner, schedule_name, schedule_type, TO_CHAR(start_date,'dd-MON-yyyy HH24:MI:SS') AS start_date, repeat_interval
FROM dba_scheduler_schedules;

I've never used this method but check the provided example.

You have:
   program_type           => 'EXECUTABLE',

The example uses:  SQL_SCRIPT

btw: you don't need to query any of the scheduler log views as the "problem" is crystal clear, why nothing happens as described above ;-)

Thank you very much It's working now.

Thanks.