Create Stored Procedure with a Declare statement - Compilation Error.

I can execute the code below as a PLSQL block successfully,  I am trying to create a stored procedure so that I can put it on a job and have it executed daily at night.

When i try to create a store procedure with the plsql block, i get a compilation error.

Can someone point me in the right direction, i need to have the following as a stored procedure so that I can use "execute procedurename"

my error is

Error(2,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:     begin function pragma procedure subtype type <an identifier>    <a double-quoted delimited-identifier> current cursor delete    exists prior external language The symbol "begin" was substituted for "DECLARE" to continue. 
Error(104,4): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ( begin case declare end exception 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 current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge 

Open in new window


Procedure I am trying to create.

CREATE OR REPLACE PROCEDURE SEND_REPORT_MAIL AS
DECLARE


  CURSOR emp_cur IS
    SELECT 
     SUM(ORDERS)||chr(13)   ORDERS,
     SUM(NORM_ROUTES)||chr(13)   Doras_Routes_Dispatched,
     to_char(round(SUM(CUBE)),'999,999')||chr(13)   Cubes,
     to_char(round(SUM(PIECES)),'999,999')||chr(13)  Pieces,
     to_char(round(SUM(WEIGHT)),'999,999')||chr(13)   Weight,
     to_char(round(SUM(CASES)),'999,999')||chr(13)   CASES,
     to_char(SUM(Order_Total), 'FML999,999.99')||chr(13)  TOTAL
FROM (SELECT 
    COUNT(*) ORDERS, 
    count(distinct route_id) ROUTES,
    case when substr(route_id,1,1) = 4 then 1 else 0 end ALC_ROUTES,
    case when substr(route_id,1,1) = 8 then 1 else 0 end NORM_ROUTES,
    sum(size1) CUBE, 
    sum(size2) PIECES, 
    sum(size3) WEIGHT,
    sum(cases) CASES,
    sum(order_total) ORDER_TOTAL
FROM RN_ORDERS
where route_id not in (8240,8283,8285) and orderdate = to_char(sysdate,'yyyymmdd')
group by route_id);


  v_connection_handle  UTL_SMTP.CONNECTION;
  v_from_email_address VARCHAR2(30) := 'it@somedomain.com';
  v_to_email_address   VARCHAR2(30) := 'someguy@somedomain.com';
  v_smtp_host          VARCHAR2(30) := '127.0.0.1'; 
  v_subject            VARCHAR2(30) := 'Report Server Daily Stats';
  l_message            VARCHAR2(200) := 'Automatically generated daily statistics report.';


  PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
  BEGIN
    UTL_SMTP.WRITE_DATA(v_connection_handle,
                        pi_name || ': ' || pi_header || UTL_TCP.CRLF);
  END;

BEGIN

  v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
  UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
  UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
  UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
  UTL_SMTP.OPEN_DATA(v_connection_handle);
  send_header('From', '"some I.T. Department" <' || v_from_email_address || '>');
  send_header('To', '"infotech@somedomain.com" <' || v_to_email_address || '>');
  send_header('Subject', v_subject);

  --MIME header.
  UTL_SMTP.WRITE_DATA(v_connection_handle,
                      'MIME-Version: 1.0' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(v_connection_handle,
                      'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(v_connection_handle,
                      ' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
                      UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

  -- Mail Body
  UTL_SMTP.WRITE_DATA(v_connection_handle,
                      '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(v_connection_handle,
                      'Content-Type: text/plain;' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(v_connection_handle,
                      ' charset=US-ASCII' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
  FOR i IN emp_cur LOOP
    UTL_SMTP.WRITE_DATA(v_connection_handle,
'Orders Shipped......... ' || i.orders ||UTL_TCP.CRLF||
'Routes Dispatched.... ' || i.doras_routes_dispatched||UTL_TCP.CRLF||
'Cubes Shipped.......... ' || i.cubes||UTL_TCP.CRLF||
'Pieces Shipped.......... ' || i.pieces||UTL_TCP.CRLF||
'Total Weight.............. ' || i.weight||UTL_TCP.CRLF||
'Total Cases Shipped... ' || i.cases||UTL_TCP.CRLF||
'Order Total................ ' || i.total || UTL_TCP.CRLF||
UTL_TCP.CRLF||
UTL_TCP.CRLF|| 'some text.'
|| UTL_TCP.CRLF|| UTL_TCP.CRLF|| UTL_TCP.CRLF||' Information Technology Department');
  
  END LOOP;

  UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);


  UTL_SMTP.WRITE_DATA(v_connection_handle,
                      '--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(v_connection_handle,
                      UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);

  UTL_SMTP.CLOSE_DATA(v_connection_handle);
  UTL_SMTP.QUIT(v_connection_handle);
  
EXCEPTION
  WHEN OTHERS THEN
    UTL_SMTP.QUIT(v_connection_handle);
    RAISE;
END;

Open in new window

FutureDBA-Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
DECLARE is for anonymous pl/sql blocks not procedures.

Delete the declare line and try again:

CREATE OR REPLACE PROCEDURE SEND_REPORT_MAIL AS
  CURSOR emp_cur IS
...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FutureDBA-Author Commented:
thank you sir, worked very well.. so did the job scheduler, i ran a couple of test. i achieved my goal.

as always, thank you
0
slightwv (䄆 Netminder) Commented:
Glad to help. Don't forget to close the question.
0
FutureDBA-Author Commented:
questioned closed, but can you please explain the difference between an anonymous pl/sql block and the pl/sql block in a stored procedure.

confused about the anonymous part.

wish to understand for archiving purposes
0
slightwv (䄆 Netminder) Commented:
A stored procedure/function is compiled code that is permanently stored in the data dictionary of the database.

An anonymous code block is just a 'script' that is executed at run time.

anonymous block:
begin
    dbms_output.put_line('Hello');
end;
/

Open in new window


You put that into a sqlplus window and it executes.  It isn't 'stored' anywhere.  You cannot reuse the code.

Now if you create a function/procedure:
create or replace function myfunc return varchar2
as
begin
    return 'Hello';
end;
/

Open in new window


It is stored so anyone can call it:
select myfunc from dual;

Open in new window


or use it in pl/sql code:
begin
dbms_output.put_line(myfunc);
end;
/

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.