Hi,
I am new to oracle SQL developer and PL/SQL, I have written following statements below in open SQL worksheet. if i run it as a script it works perfect. now i want to schedule this to run daily:
if i use the schedule job wizard and copy paste the below statements in pl/SQL block or a prodedure with begin and end statement it fails with the error (Job: GCC_FOOTPRINT_LIC.pexupdate Event: JOB_FAILED Date: 16-SEP-14 04.37.16.705801 PM AUSTRALIA/SYDNEY Log id: 1068439 Job class: DBMS_JOB$ Run count: 1 Failure count: 1 Retry count: 0 Error code: %error_code %Error message: ORA-06550: line 2, column 1: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close currentelete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge) )
any help would be highly apreciated- Thanks
script:
--creates a temporary table by pulling data from a function within public synonym
create table S_PEX_TEMP as
Select
FIRSTNAME,
LASTNAME,
LANID,
ISACTIVE,
CACHEDATE as "pexdate",
sysdate as "last updated"
from table(public_synonym.GETALLCACHEDEMPLOYEES);
-- deletes the data in the permanent table so that the new data from the temp table can be inserted
delete S_PEX;
-- Iinserts/copies data from the temp table to the permanent table in database
Insert into S_PEX select * from S_PEX_TEMP;
-- drops the temp table so that the first statement can be re-executed.
drop table "Schema_name"."S_PEX_TEMP";