Create a Job to run Oracle Package stored procedure [message #660349] |
Wed, 15 February 2017 04:47 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I have a package that includes a SP with arguments. The stored procedures uses UTL_MAIL send emails and I can run it independently from SQLPLUS or my Oracle client.
I want to create a job that runs this SP. I am facing a problem with trying to pass the package name as follows:
- Job creation code:
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"schema"."EMAIL_ALERT_EMP_RES_EXP"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
CALL MAIL_ALERT.P_MY_SpROC( 'USER_NAME', 'MODULE_NAME', 'OPTIONAL_cc@MAIL.COM' );
end;',
repeat_interval => 'FREQ=HOURLY;INTERVAL=3',
start_date => systimestamp at time zone '-3:00',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'This job call a SP that sends email to employees who need to ....y',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"schema"."EMAIL_ALERT_EMP_RES_EXP"', attribute => 'restartable', value => TRUE);
sys.dbms_scheduler.enable( '"schema"."EMAIL_ALERT_EMP_RES_EXP"' );
END;
- Error:
ORA-06550: line 3, column 6: PLS-00103: Encountered the symbol "MAIL_ALERT" when expecting one of the following: := . ( @ % ; The symbol ":=" was substituted for "MAIL_ALERT" to continue.
Can you please show me what I need to correct?
Many thanks,
Ferro
[Updated on: Wed, 15 February 2017 04:48] Report message to a moderator
|
|
|
Re: Create a Job to run Oracle Package stored procedure [message #660352 is a reply to message #660349] |
Wed, 15 February 2017 05:03 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Remove the "call"
SQL> begin
2 CALL Dbms_Output.put_line('foo');
3 end;
4 /
CALL Dbms_Output.put_line('foo');
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting one of the
following:
:= . ( @ % ;
The symbol ":=" was substituted for "DBMS_OUTPUT" to continue.
SQL>
SQL>
SQL> begin
2 Dbms_Output.put_line('foo');
3 end;
4 /
PL/SQL procedure successfully completed.
|
|
|
|
|
|