Home » SQL & PL/SQL » SQL & PL/SQL » getting error in Procedure (9.0.4.0)
getting error in Procedure [message #309043] Wed, 26 March 2008 05:06 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,
I have created one procedure to send a mail from the database.When I am executing this proceudre I am getting the following error . Can u please look into this.
SQL>  CREATE OR REPLACE PROCEDURE send_mail (
  2   pSender    VARCHAR2,
  3   pRecipient VARCHAR2,
  4   pSubject   VARCHAR2,
  5   pMessage   VARCHAR2) IS
  6     mailhost  CONSTANT VARCHAR2(30) := 'smtp01.us.oracle.com';
  7     crlf      CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
  8     mesg      VARCHAR2(1000);
  9     mail_conn utl_smtp.connection;
 10     
 11     BEGIN
 12        mail_conn := utl_smtp.open_connection(mailhost, 25);
 13     
 14        mesg := 'Date: ' ||
 15             TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
 16                'From: <'|| pSender ||'>' || crlf ||
 17                'Subject: '|| pSubject || crlf ||
 18                'To: '||pRecipient || crlf || '' || crlf || pMessage;
 19     
 20        utl_smtp.helo(mail_conn, mailhost);
 21        utl_smtp.mail(mail_conn, pSender);
 22        utl_smtp.rcpt(mail_conn, pRecipient);
 23        utl_smtp.data(mail_conn, mesg);
 24        utl_smtp.quit(mail_conn); 
 25     EXCEPTION
 26       WHEN INVALID_OPERATION THEN
 27         NULL;
 28       WHEN TRANSIENT_ERROR THEN
 29         NULL;
 30       WHEN PERMANENT_ERROR THEN
 31         NULL;
 32       WHEN OTHERS THEN
 33         NULL; 
 34     END send_mail;
 35  /

Warning: Procedure created with compilation errors.

SQL> shoe err
SP2-0042: unknown command "shoe err" - rest of line ignored.
SQL> show err
Errors for PROCEDURE SEND_MAIL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
26/11    PLS-00201: identifier 'INVALID_OPERATION' must be declared


Thank you.
Re: getting error in Procedure [message #309050 is a reply to message #309043] Wed, 26 March 2008 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Isn't the error obvious?

POST THIS IN SQL & PL/SQL NEWBIE FORUM

Regards
Michel
Re: getting error in Procedure [message #309283 is a reply to message #309043] Thu, 27 March 2008 00:23 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
What is the use of Exception block in your code??


Instead of :-
EXCEPTION
        WHEN INVALID_OPERATION THEN
          NULL;
        WHEN TRANSIENT_ERROR THEN
          NULL;
        WHEN PERMANENT_ERROR THEN
          NULL;
        WHEN OTHERS THEN
          NULL; 


Use:-
EXCEPTION
        WHEN OTHERS THEN
          NULL; 

Re: getting error in Procedure [message #309284 is a reply to message #309283] Thu, 27 March 2008 00:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
NO NO NO

Remove the when others.
Only catch what you KNOW you want to catch.
All other exceptions should be handled by the calling program.
Re: getting error in Procedure [message #309305 is a reply to message #309284] Thu, 27 March 2008 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Say it louder: NO NO NO

Regards
Michel

[Updated on: Thu, 27 March 2008 01:45]

Report message to a moderator

Re: getting error in Procedure [message #309378 is a reply to message #309305] Thu, 27 March 2008 04:51 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
Shout it louder: NO NO NO
Re: getting error in Procedure [message #309524 is a reply to message #309043] Thu, 27 March 2008 15:08 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Try to use the package name UTL_SMTP before each of the exception.
eg.
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
NULL;
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
NULL;
WHEN UTL_SMTP.PERMANENT_ERROR THEN
NULL;
WHEN OTHERS THEN
NULL;

I hope this helps....

Thanks,
Joice
Re: getting error in Procedure [message #309525 is a reply to message #309524] Thu, 27 March 2008 15:14 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't you see the NO NO NO?
Remove this WHEN OTHERS THEN NULL. Remove it from your brain.

Regards
Michel
Previous Topic: Compilation error
Next Topic: Nested Loop
Goto Forum:
  


Current Time: Tue Dec 06 13:59:06 CST 2016

Total time taken to generate the page: 0.14717 seconds