How to execute a procedure with DBMS_SCHEDULER.CREATE_JOB procedure

Question

I want to create a job that would drop a database object at a given date. The job created all right but the procedure is not executed. Tried executing the procedure alone and it works.

Here's the code for the create job

v_jobnam := v_objnam; v_jobnam := DBMS_SCHEDULER.generate_job_name (v_jobnam);

 v_startdate := to_timestamp(v_startdate); 
 select sysdate + (v_delhrs/1440) 
    into v_startdate
    from dual;

          DBMS_SCHEDULER.CREATE_JOB(job_name => v_jobnam, job_type => 'PLSQL_BLOCK', JOB_ACTION => 'BEGIN DROP_OBJ1(' || v_objnam|| ', ' || v_objtyp || ', '|| v_schema || ',' || v_objid ||'); END;', start_date => SYSTIMESTAMP, repeat_interval => 'freq=secondly; bysecond=0', end_date => NULL, enabled => TRUE, comments => 'Calls PLSQL once');

where v_delhrs is a number. Here's the code for the procedure:

PROCEDURE DROP_OBJ1 ( p_objnam IN CHAR, p_objtyp IN CHAR, p_copyto IN ALL_OBJECTS.OWNER%TYPE, p_objid IN NUMBER ) IS

v_objnam VARCHAR2 (30); v_objtyp VARCHAR2 (30); v_copyto VARCHAR2 (30); v_objid NUMBER (3);

BEGIN

    v_objnam := UPPER (p_objnam);
    v_objtyp := UPPER (p_objtyp);
    v_copyto := UPPER (p_copyto);
    v_objid  := p_objid;
    --v_copyby := UPPER (p_copyby);

     EXECUTE IMMEDIATE ( ' DROP '
                       || v_objtyp
                       || ' '
                       || v_copyto
                       || '.'
                       || v_objnam
                       );

     EXECUTE IMMEDIATE ( ' DELETE FROM COPY_OBJ_DET WHERE OBJ_ID = '
                        || v_objid
                        );


 COMMIT;

END;

I know its a very minor problem. Thanks in advance gurus.

Cheers!

Answer 1

It apears to me that you have missed some quote marks in the DBMS_JOBS setup call.

Try this:

DBMS_SCHEDULER.CREATE_JOB(job_name        => v_jobnam,
                          job_type        => 'PLSQL_BLOCK',
                          JOB_ACTION      => 'BEGIN DROP_OBJ1(''' || v_objnam 
                                             || ''', ''' || v_objtyp || ''', '''
                                             || v_schema || ''',' || v_objid 
                                             || '); END;',
                          start_date      => SYSTIMESTAMP,
                          repeat_interval => 'freq=secondly; bysecond=0',
                          end_date        => NULL,
                          enabled         => TRUE,
                          comments        => 'Calls PLSQL once');

If this is not the solution I will setup your code on a database and try it myself.