is it possible to schedule a create table job ? 1

[Crystalboy1]

hi there.

i want to create a stored procedure, which will drop a table A frist, then will create the table A again wid fresh data e,g

create stored procedure ABC as
begin
execute immediate 'drop table A';
create table A as
select * from longQuery....
;
end

now i want to sechedule a job to exec this stored procedure ? is it possible if not, whats the alternative...

 

[LKBrwnDBA]

Yes, it is possible, just add EXECUTE IMMEDIATE to the create table dml and schedule the procedure using DBMS_JOB package.

BUT why drop/create? Does the table structure change?
If the table structure does not change, then you can just use TRUNCATE TABLE instead of DROP and do an INSERT...SELECT... statement.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Crystalboy1]

Yes there might be times when the table structure will change...

 

[SkipVought]

Yes there might be times when the table structure will change...

Care to elaborate?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]