Tags

, ,

–You can take the all dba_jobs create script at a one time.

SET serveroutput on size unlimited
DECLARE
log_user VARCHAR2 (30);
priv_user VARCHAR2 (30);
schema_user VARCHAR2 (30);
INTERVAL VARCHAR2 (200);
what VARCHAR2 (100);
broken VARCHAR2 (1);
CURSOR explain_rec
IS
SELECT log_user, priv_user, schema_user, INTERVAL, what, broken
FROM dba_jobs;
BEGIN
FOR rec IN explain_rec
LOOP
IF (rec.broken = ‘Y’)
THEN
DBMS_OUTPUT.put_line (‘DECLARE’);
DBMS_OUTPUT.put_line (‘job_num NUMBER;’);
DBMS_OUTPUT.put_line (‘nlsvar VARCHAR2 (4000);’);
DBMS_OUTPUT.put_line (‘envvar RAW (32);’);
DBMS_OUTPUT.put_line (‘BEGIN’);
DBMS_OUTPUT.put_line (‘SELECT nls_env, misc_env’);
DBMS_OUTPUT.put_line (‘INTO nlsvar, envvar’);
DBMS_OUTPUT.put_line (‘FROM dba_jobs’);
DBMS_OUTPUT.put_line (‘WHERE ROWNUM job_num,’);
DBMS_OUTPUT.put_line ( ‘luser => ”’
|| rec.log_user
|| ””
|| ‘,’
);
DBMS_OUTPUT.put_line ( ‘puser => ”’
|| rec.priv_user
|| ””
|| ‘,’
);
DBMS_OUTPUT.put_line ( ‘cuser => ”’
|| rec.schema_user
|| ””
|| ‘,’
);
DBMS_OUTPUT.put_line (‘what => ”’ || rec.what || ””
|| ‘,’
);
DBMS_OUTPUT.put_line
( ‘next_date => TO_DATE(”01-02-2014 18:00:00”,”dd-mm-yyyy hh24:mi:ss”)’
|| ‘,’
);
DBMS_OUTPUT.put_line ( ‘INTERVAL => ”’
|| rec.INTERVAL
|| ””
|| ‘,’
);
DBMS_OUTPUT.put_line (‘broken => TRUE,’);
DBMS_OUTPUT.put_line (‘nlsenv => nlsvar,’);
DBMS_OUTPUT.put_line (‘env => envvar’);
DBMS_OUTPUT.put_line (‘)’);
DBMS_OUTPUT.put_line (‘;’);
DBMS_OUTPUT.put_line (‘DBMS_OUTPUT.put_line (job_num);’);
DBMS_OUTPUT.put_line (‘END;’);
DBMS_OUTPUT.put_line (‘/’);
DBMS_OUTPUT.put_line (‘COMMIT;’);
ELSE
DBMS_OUTPUT.put_line (‘DECLARE’);
DBMS_OUTPUT.put_line (‘job_num NUMBER;’);
DBMS_OUTPUT.put_line (‘nlsvar VARCHAR2 (4000);’);
DBMS_OUTPUT.put_line (‘envvar RAW (32);’);
DBMS_OUTPUT.put_line (‘BEGIN’);
DBMS_OUTPUT.put_line (‘SELECT nls_env, misc_env’);
DBMS_OUTPUT.put_line (‘INTO nlsvar, envvar’);
DBMS_OUTPUT.put_line (‘FROM dba_jobs’);
DBMS_OUTPUT.put_line
(‘WHERE ROWNUM job_num,’);
DBMS_OUTPUT.put_line ( ‘luser => ”’
|| rec.log_user
|| ””
|| ‘,’
);
DBMS_OUTPUT.put_line ( ‘puser => ”’
|| rec.priv_user
|| ””
|| ‘,’
);
DBMS_OUTPUT.put_line ( ‘cuser => ”’
|| rec.schema_user
|| ””
|| ‘,’
);
DBMS_OUTPUT.put_line (‘what => ”’ || rec.what || ””
|| ‘,’
);
DBMS_OUTPUT.put_line ( ‘next_date => TO_DATE(”01-02-2014 18:00:00”,”dd-mm-yyyy hh24:mi:ss”)’|| ‘,’);
DBMS_OUTPUT.put_line ( ‘INTERVAL => ”’
|| rec.INTERVAL
|| ””
|| ‘,’
);
DBMS_OUTPUT.put_line (‘broken => FALSE,’);
DBMS_OUTPUT.put_line (‘nlsenv => nlsvar,’);
DBMS_OUTPUT.put_line (‘env => envvar’);
DBMS_OUTPUT.put_line (‘)’);
DBMS_OUTPUT.put_line (‘;’);
DBMS_OUTPUT.put_line (‘DBMS_OUTPUT.put_line (job_num);’);
DBMS_OUTPUT.put_line (‘END;’);
DBMS_OUTPUT.put_line (‘/’);
DBMS_OUTPUT.put_line (‘COMMIT;’);
END IF;
END LOOP;
END;
/

Advertisements