Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

dbms_scheduler.create_job() error

user482717Jul 24 2006 — edited Jul 31 2006
Hi, I need to create a job to run the external file to an external table, and then load the data to the table that is already defined.
my sql commands works fine, but when i put them into the dbms_scheduler.create_job(), the commands does not work any more. what should i do?
here is the code:

-- Create jobs.
BEGIN
-- Job defined entirely by the CREATE JOB procedure.
DBMS_SCHEDULER.create_job (
job_name => 'job4',
job_type => 'PLSQL_BLOCK',
job_action => '
BEGIN
drop table c_listing_external;
CREATE TABLE crs_listing_external
( CODE VARCHAR2(4) ,
STATUS VARCHAR2(5) ,
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY reg_sched_dir
ACCESS PARAMETERS
(
records delimited by newline
badfile reg_bad_dir:"crstxttest.bad"
logfile reg_log_dir:"crstxttest.log"
fields terminated by ","
missing field values are null
(
CODE ,
STATUS))
LOCATION ("R1.TXT", "R2.TXT", "R3.TXT", "R4.TXT"))
PARALLEL
REJECT LIMIT UNLIMITED;

delete from c_listing_load_test;
insert into c_listing_load_test
select count(*) from c_listing_load_test;
exec insertcrsload;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=minutely; byminute=2',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined.');
END;
/

I can't user drop table, create table commands in this. what should I do to overcome it?

Thanks!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 28 2006
Added on Jul 24 2006
5 comments
5,702 views