This discussion is archived
2 Replies Latest reply: Jan 8, 2013 7:52 AM by hh*394375*91 RSS

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

hh*394375*91 Newbie
Currently Being Moderated
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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points