Convert script to PL/SQL procedure for dbms_scheduler

Question

I'm fairly new to SQL. I'm trying to convert a script I have into a stored procedure so that I can run the:

dbms_scheduler.create_job (job_type => 'STORED_PROCEDURE')

scheduler.

Here is my SQL:

insert into EBA_PROJ_STATUS_HEALTH (DATESTAMP, WEEK, PROJECT_HEALTH, PROJECT_NAME, PROJECT_ID)
select
TO_DATE (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "DateStamp",
TO_CHAR (SYSDATE, 'WW') "WeekStamp",
decode (PROJECT_STATUS, '26080667600194118828749753718250690144', 'Red', '26080667600198954532028212234949514848', 'Green', '2608066760019489567412685398756201486', 'Yellow', '32151851918961403472745066957057582129', 'Terminated') Health,
PROJECT,
ID
from
EBA_PROJ_STATUS

move your insert statement to a procedure (do not forget commit;) and then configure all relevant parameters of the create_job procedure — Pavel Gatnar, Sep 08 '15 at 20:51
https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6009.htm#SQLRF01309 — , Sep 08 '15 at 21:22
`TO_DATE (SYSDATE)` is totally senseless - actually it's plain wrong. `to_date()` expects a `varchar`, `sysdate` is a `date`. `TO_DATE (SYSDATE)` will first implicitely convert `sysdate` to a `varchar` just to convert it back to a `date` again. ***Never, ever use `to_date()` on a `date`*** — , Sep 08 '15 at 21:25

score 0 · Accepted Answer · edited May 23 '17 at 10:28

Create the procedure as:

CREATE OR REPLACE
PROCEDURE p_ins_EBA_PROJ_STATUS_HEALTH
AS
BEGIN
  INSERT
  INTO EBA_PROJ_STATUS_HEALTH
    (
      DATESTAMP,
      WEEK,
      PROJECT_HEALTH,
      PROJECT_NAME,
      PROJECT_ID
    )
  SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "DateStamp",
    TO_CHAR (SYSDATE, 'WW') "WeekStamp",
    DECODE (PROJECT_STATUS, '26080667600194118828749753718250690144', 'Red', '26080667600198954532028212234949514848', 'Green', '2608066760019489567412685398756201486', 'Yellow', '32151851918961403472745066957057582129', 'Terminated') Health,
    PROJECT,
    ID
  FROM EBA_PROJ_STATUS;
END;
/

You can add parameters to the procedure to avoid the hard-coded values.

Create the job and schedule inline as:

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'insert into EBA_PROJ_STATUS_HEALTH',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN p_ins_EBA_PROJ_STATUS_HEALTH; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
    enabled         => TRUE);
END;
/

On a side note,

Never use TO_DATE on a DATE, It will implicitly convert it into string and then back to date using locale-specific NLS format.

Convert script to PL/SQL procedure for dbms_scheduler

1 Answers1