Home » SQL & PL/SQL » SQL & PL/SQL » Create a Job to run Oracle Package stored procedure (11.0.2.10)
Create a Job to run Oracle Package stored procedure [message #660349] Wed, 15 February 2017 04:47 Go to next message
OraFerro
Messages: 316
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 Go to previous messageGo to next message
ThomasG
Messages: 3205
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.
Re: Create a Job to run Oracle Package stored procedure [message #660372 is a reply to message #660349] Wed, 15 February 2017 08:18 Go to previous messageGo to next message
joy_division
Messages: 4823
Registered: February 2005
Location: East Coast USA
Senior Member
I see other issues.

For one, enclosing the schema name of 'schema' in double quotes forces that your schema name must be in lower case. Do you really have a schema named schema?
Re: Create a Job to run Oracle Package stored procedure [message #660410 is a reply to message #660372] Wed, 15 February 2017 22:11 Go to previous messageGo to next message
OraFerro
Messages: 316
Registered: July 2011
Senior Member
@joy
Thanks for the observation, I changed the schema name in order not to share the real one.

Thanks,
Ferro
Re: Create a Job to run Oracle Package stored procedure [message #660412 is a reply to message #660410] Wed, 15 February 2017 23:05 Go to previous message
OraFerro
Messages: 316
Registered: July 2011
Senior Member
@ThamasG

Thanks a lot for the help. In fact I've read a few posts and an article (Oracle Help Center) without finding a specific example.

Thanks again.
Ferro
Previous Topic: Oracle BLOB DATA
Next Topic: cumulative sum & product
Goto Forum:
  


Current Time: Sat Feb 24 17:33:11 CST 2018

Total time taken to generate the page: 0.10236 seconds