PL/SQL (MOSC)

MOSC Banner

drop statpack automatation job.

edited Feb 27, 2014 12:00PM in PL/SQL (MOSC) 3 commentsAnswered

Hi,

I wrote below PL/SQL to remove exising dbms_job from database, which was configured by spauto.sql script. but it giving error "invalid SQL"

set serveroutput on;

DECLARE

cursor c1 is SELECT DISTINCT job FROM dba_jobs WHERE what='statspack.snap;';

jnumber c1%rowtype;

sqmt  varchar2(100);

BEGIN

open c1;

loop

fetch c1 into jnumber;

EXIT WHEN c1%NOTFOUND;

sqmt := 'EXECUTE DBMS_JOB.REMOVE(' || jnumber.job || ');';

execute immediate sqmt;

end loop;

close c1;

commit;

dbms_output.put_line('Statpack JOBS remove sucessfully');

END;

/

DECLARE

*

ERROR at line 1:

ORA-00900: invalid SQL statement

ORA-06512: at line 11

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center