title: How to execute a procedure with DBMS_SCHEDULER.CREATE_JOB procedure
body: <p>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.</p>
  
  <p>Here's the code for the create job</p>
  
  <pre><code>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 =&gt; v_jobnam, job_type =&gt; 'PLSQL_BLOCK', JOB_ACTION =&gt; 'BEGIN DROP_OBJ1(' || v_objnam|| ', ' || v_objtyp || ', '|| v_schema || ',' || v_objid ||'); END;', start_date =&gt; SYSTIMESTAMP, repeat_interval =&gt; 'freq=secondly; bysecond=0', end_date =&gt; NULL, enabled =&gt; TRUE, comments =&gt; 'Calls PLSQL once');
  </code></pre>
  
  <p>where v_delhrs is a number.
   Here's the code for the procedure:</p>
  
  <pre><code>  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;
  </code></pre>
  
  <p>I know its a very minor problem.
  Thanks in advance gurus.</p>
  
  <p>Cheers!</p>