Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Programming: A Developer's Workbook

Expert

Q:

25-39.

As we’ve seen, the REMOVE procedure doesn’t stop a running job; you need, instead, to use the ALTER SYSTEM KILL SESSION command, which requires two parameters: the SID of the job you want to kill and the job’s serial number. You can find the SID in the DBA_JOBS_RUNNING view, and the job’s serial number in the V$SESSION view:

SELECT r.job,
       r.sid,
       s.serial#
  FROM dba_jobs_running r,
       v$session s
 WHERE r.sid = s.sid
   AND r.job = 47;

      JOB       SID   SERIAL#
--------- --------- ---------
       47         7         3

The next step is to mark the job broken to prevent future execution:

DBMS_JOB.BROKEN( 47, TRUE);
COMMIT;

Finally, issue the KILL command:

ALTER SYSTEM KILL SESSION '7,3';

Q:

25-40.

You’ll find a suggested procedure in the runjob.sp file on the book’s web page.

Q:

25-41.

We have:

next_date IN VARCHAR2,

in ISUBMIT and:

next_date IN DATE DEFAULT sysdate,

in SUBMIT. Since this makes no sense, it’s probably just somebody’s mistake during the creation of the DBMS_JOB package. What is really strange, however, is that this mistake migrates from version to version without changes, which causes two problems. The first is that since the next_date parameter doesn’t have a default value, you can’t omit the parameter in the ISUBMIT procedure. The second is that when you use an NLS_DATE_FORMAT different from the current default for the database instance, the ISUBMIT procedure truncates next_date to the beginning of the ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page