2 Replies Latest reply: Jan 8, 2013 9:52 AM by hh*394375*91 RSS

    Creating DDL to give to DBA and DDL for scheduling is causing a problem.

    hh*394375*91
      Creating DDL to give to DBA and DDL for scheduling is causing a problem.

      I need to pass DDL to another team to implement.

      I want to pass DDL to create the table, procedure and schedule the job.

      The part to schedule job is erroring. It errors on the begin statement in the DDL to schedule job

      The error statement is:
      Error(14,1): PLS-00103: Encountered the symbol "BEGIN"

      I can create the table and procedure using this script if I exclude the job scheduling ddl. How do I pass the DDL to schedule the job also.
      --------------------------------------------------------
      --  DDL for Table HALL_COUNTRIES
      --------------------------------------------------------
      
        CREATE TABLE "HR"."HALL_COUNTRIES" 
         (     "COUNTRY_ID" CHAR(2 BYTE), 
           "COUNTRY_NAME" VARCHAR2(40 BYTE), 
           "REGION_ID" NUMBER
         ) SEGMENT CREATION IMMEDIATE 
        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        TABLESPACE "USERS" ;
      --------------------------------------------------------
      --  Constraints for Table HALL_COUNTRIES
      --------------------------------------------------------
      
        ALTER TABLE "HR"."HALL_COUNTRIES" MODIFY ("COUNTRY_ID" NOT NULL ENABLE);
        
      --------------------------------------------
      -- DDL for procedure hall_test
      --------------------
      
      
      create or replace
      procedure hr.hall_test
      is
      begin
      execute immediate 'TRUNCATE TABLE hr.hall_countries';
      insert into hr.hall_countries (country_id,country_name,region_id)
      select country_id,country_name,region_id from hr.countries;
      commit;
      end;
      
      ---------------------------------------
      -- DDL to schedule job
      -----------------
      
      begin
      Dbms_Scheduler.create_job(
      job_name => 'DEMO_HALL_TEST_SCHEDULE',
      job_type => 'STORED_PROCEDURE',
      job_action => 'HR.HALL_TEST',
      start_date      => SYSTIMESTAMP,
      repeat_interval => 'freq=daily; BYHOUR=8,9,10; byminute=0; bysecond=0;',
      enabled => TRUE,
      comments => 'Demo for job schedule.');
      END;