Solved

Create Stored Procedure with a Declare statement - Compilation Error.

Posted on 2014-07-30
5
1,663 Views
Last Modified: 2014-07-31
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

0
Comment
Question by:FutureDBA-
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40230809
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
 

Author Comment

by:FutureDBA-
ID: 40230924
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40232835
Glad to help. Don't forget to close the question.
0
 

Author Comment

by:FutureDBA-
ID: 40233112
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40233124
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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question