Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2760
  • Last Modified:

about UTL_mail

I am trying to develop plsql code to generate automatic email to send as a reminder.I followed the procedure which was stated in the UTL_MAIL in the metalink about the UTL_MAIL.. But iam getting an error message.
I configured the spfile with the servername and port like

alter system set smtp_out_server = 'mail.xxxx.com :25' scope = spfile.

I tried scope=both ; but that dint work so i kept as spfile
and later
create pfile from spfile.


the error is
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SYS.SEND_EMAIL_TEST102 is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

but the procedure was complied properly and no errors were displayed



@d:\oracle\infra\rdbms\admin\utlmail.sql;
 
@d:\oracle\infra\rdbms\admin\prvtmail.plb;
 
---both were successfully created and i restarted the database before after configuring the spfile 
 
CREATE OR REPLACE PROCEDURE send_email_test102
   BEGIN  
     UTL_MAIL.SEND(sender => 'kxxxx@richards.com', recipients => 'soxxxxn@richards.com', cc => 'sxxxx@richards.com', bcc => 'sxxxx@richards.com', subject => 'Testmail', message => 'Hello');
   EXCEPTION
   WHEN OTHERS THEN
    --  dbms_output.put_line('Fehler');
     raise_application_error(-20001,'The following error has occured: ' || sqlerrm);   
   END;
 
 
exec send_email_test102;
 
here is the pfile
INITinfra.ORA  = pfile
*.aq_tm_processes=1
*.background_dump_dest='D:\Oracle\admin\infra\bdump'
*.compatible='10.1.0.2.0'
*.control_files='D:\Oracle\oradata\infra\CONTROL01.CTL','D:\Oracle\oradata\infra\CONTROL02.CTL','D:\Oracle\oradata\infra\CONTROL03.CTL'
*.core_dump_dest='D:\Oracle\admin\infra\cdump'
*.db_block_size=8192
*.db_cache_size=50331648
*.db_domain='richards.com'
*.db_file_multiblock_read_count=16
*.db_name='infra'
*.db_recovery_file_dest='D:\Oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer)','(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)'
*.java_pool_size=67108864
*.job_queue_processes=5
*.large_pool_size=8388608
*.max_commit_propagation_delay=0
*.open_cursors=300
*.pga_aggregate_target=33554432
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=400
*.shared_pool_size=150994944
*.smtp_out_server='mail.richards.com :25','smtp.richards.com :25','mail.richards.com :','smtp.richards.com :2500'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='D:\Oracle\admin\infra\udump'
 
and also executed utl_smtp just in case
 
@D:\Oracle\infra\rdbms\ADMIN\utlsmtp.sql
 
and everything was in sys as username

Open in new window

0
tbksandeep
Asked:
tbksandeep
  • 2
  • 2
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
CREATE OR REPLACE PROCEDURE send_email_test102
   BEGIN  
     UTL_MAIL.SEND(sender => 'kxxxx@richards.com', recipients => 'soxxxxn@richards.com', cc => 'sxxxx@richards.com', bcc => 'sxxxx@richards.com', subject => 'Testmail', message => 'Hello');
   EXCEPTION
   WHEN OTHERS THEN
    --  dbms_output.put_line('Fehler');
     raise_application_error(-20001,'The following error has occured: ' || sqlerrm);  
   END;
/   ---> we should have a / here to first execute this statement which creates the procedure. I am not sure whether you have missed this while pasting your code or your actual code itself does not have this.

Run the above and retry.

Thanks
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SYS.SEND_EMAIL_TEST102 is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The above means the SYS.SEND_EMAIL_TEST102 is invalid. If you create/compile it properly, then it will be valid and you should be able to execute it without issues.

if you want to check its status,

select owner, object_name, object_type, status
from dba_objects
where object_name ='SEND_EMAIL_TEST102 ';

The status should be valid in the above query output.

Thanks
0
 
tbksandeepAuthor Commented:
thanks for the solution which you gave me
0
 
tbksandeepAuthor Commented:
nav_kum_v:

thanks for the reply what you posted for checking the status but its not working. so is there someother way to check the status of the procedure

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now