?
Solved

Create Stored Procedure with a Declare statement - Compilation Error.

Posted on 2014-07-30
5
Medium Priority
?
1,781 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 2000 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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.
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…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

801 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