asked on

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FutureDBA-

ASKER

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

slightwv (䄆 Netminder)

Glad to help. Don't forget to close the question.

FutureDBA-

ASKER

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

slightwv (䄆 Netminder)

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