-
Why I'm getting following error when excuted following script.
SCRIPT==================================
DECLARE
JobNo INTEGER;
BEGIN
DBMS_JOB.SUBMIT(:JobNo,
'E:\SQL\DataFrom400.sql',
SysDate,
'SysDate + 1/24');
END;
/
--Statement processed.
print JobNo
=====================================
ERROR=================================
SQL> @ e:\sql\x.sql
DECLARE
*
ERROR at line 1:
ORA-06550: line 1, column 94:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
:= . ( @ % ;
ORA-06512: at "SYS.DBMS_JOB", line 71
ORA-06512: at "SYS.DBMS_JOB", line 121
ORA-06512: at line 4
JOBNO
---------
======================================
KED
-
Because you are only allowed to use PL/SQL code for a job.
Here you're trying to invoke a SQL*Plus script and the parser doesn't recognize the 'E:\ ... ' as a valid PL/SQL block.
In addition to that:
* You have to use either an SQL*Plus bind variable or a PL/SQL variable. Here you declare PL/SQL variable JobNo and use SQL*Plus bind variable. If you want to print jobno in this way you have to use bind variable.
* You have to COMMIT after dbms_job.submit.
Code:
variable JobNo number
begin
DBMS_JOB.SUBMIT(:JobNo,
'BEGIN pl_sql_code; END;',
SysDate,
'SysDate + 1/24');
commit;
end;
/
print JobNo
