Create a Job (run it every day) VARIABLE v_jobnum NUMBER; BEGIN dbms_job.submit(:v_jobnum, ‘MY_STORED_PROCEDURE;’, sysdate,’sysdate+1′); END; print v_jobnum BEGIN DBMS_JOB.SUBMIT(JOB => :jobnumber, WHAT => ‘DBMS_STATS.GATHER_DATABASE_STATS(options => ”GATHER AUTO”);’, NEXT_DATE => to_date(’11:30 01/23/06′,’HH24:MI MM/DD/YY’), INTERVAL => ‘SYSDATE + 1′); COMMIT; END; Running Existing Job While the point is to automate jobs, occasionally you may find you need [...]
Archive for the ‘Packages’ Category
25 Apr
Oracle Supplied Packages
DBMS_SQL Using DBMS_SQL, one can write stored procedures that use dynamic SQL. It can issue data definition language statements in PLSQL CREATE OR REPLACE PROCEDURE delete_all_rows (tab_name IN VARCHAR2, rows_del OUT NUMBER) IS cursor_name : = DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE ( cursor_name, ‘DELETE FROM ‘ ||tab_name, DBMS_SQL.NATIVE); rows_del := DBMS_SQL.EXECUTE(cursor_name); [...]
