Solved

Create Stored Procedure with a Declare statement - Compilation Error.

Posted on 2014-07-30
5
1,445 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-
  • 3
  • 2
5 Comments
 
LVL 76

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now