SQL & PL/SQL :: Error When Tried Sending Email Through Stored Procedure?
Jan 6, 2011
I have this stored procedure that does implements some business logic and then sends an email.
I got the below error when it tried to send the email
Quote:Returned values:
ERR_CODE =-29279,
ERR_MESSAGE = "MSS DATABASE INCONSISTENCYOTHERS EXCEPTION in XCOM_X080_SUBMITORDER - SQL -29279 SQLERRM: ORA-29279: SMTP permanent error: 501 5.5.2 Syntax error in parameters scanning "TO""
How can I handle this error using exception and in case sending email fails instead of abnormal exit ?
View 2 Replies
ADVERTISEMENT
Jun 17, 2011
I would like to send an email with attachment using a STORE PROCEDURE, with a PL/SQL, I was testing a package called SRW.RUN_REPORT but the problem is that the report generate a pdf file but this file is storage in application server because the report server run over the application, wo we can not get the physical file to send as attach.
the problem is generate the pdf file save in a place that we can get easier.
View 8 Replies
View Related
Apr 4, 2011
How to send an email through oracle FORMS6i ?
View 1 Replies
View Related
Nov 23, 2010
How to Sending Email With PDF File attachment.
View 3 Replies
View Related
Feb 14, 2010
I have been working on sending an email with a pdf attachment.I succeeded in PDF attachments with size <32 K.The steps I Used :
1.created a directory 'EMAIL' that points to the directory containing my pdf file.
2.
declare
vInHandle utl_file.file_type;
flen number;
bsize number;
ex boolean;
l_Output raw(32767);
fname varchar2(30) := 'labels.pdf';
vSender varchar2(30) := some1@hct.edu.om';
[code].....
It send the email <32 k.But for >32 K it give me the error :
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.UTL_ENCODE", line 243
ORA-06512: at "SYS.UTL_MAIL", line 118
ORA-06512: at "SYS.UTL_MAIL", line 324
ORA-06512: at "SYS.UTL_MAIL", line 414
ORA-06512: at line 18
I know it has something to do with blob or clob ....where i can modify my code to work.
View 17 Replies
View Related
Sep 3, 2013
In the database there is a job that monitors the delivered date.5 weeks before this date a email is send.I this mail i would like to implement a notification button. the receiver will get a mail with notification:o Yes i will succeedo No, i wil not succeed this answer has to be send back to the sender . how to do this. note: the receivers can have different mail clients!
View 10 Replies
View Related
May 1, 2009
I have the following code to send an email with a report as an attachment via forms. everything is running fine.
Now i do not have a message body in the email, so my question is how do i add some text as an email body
if i add the following parameter, its not working
ADD_PARAMETER(thelist, 'BODY',TEXT_PARAMETER, 'Check the attached Report');
DECLARE
report_id REPORT_OBJECT := find_report_object('checklist');
report_message VARCHAR2(100) :='';
rep_status VARCHAR2(100) :='';
[code]....
View 6 Replies
View Related
Apr 23, 2010
How to send email from Oracle forms10g to a yahoo or gmail account using authentication.
View 5 Replies
View Related
Jun 15, 2010
I am using the following code on a button and once I pressed the button the form disconnected.
Declare
Outlook_Object client_OLE2.OBJ_TYPE;
Mail_Object client_OLE2.OBJ_TYPE;
Item1 client_OLE2.OBJ_TYPE;
Item2 client_OLE2.OBJ_TYPE;
OLEPARAM client_OLE2.list_type;
[code]....
View 2 Replies
View Related
Dec 11, 2012
I the past when working with apex 3 I was able to work with apex mail send very nice. In apex 4.0 with oracle 11g ee I'm not able to send emails anymore.
Last error message in the queue is:
ORA-29279: SMTP permanent error: 550 through this server without authentication.
the smtp server has not been changed, the code is fine. How I can go further?
View 20 Replies
View Related
Apr 22, 2013
What is the usage of UTL_HTTP?
Will I be able to send e-mail with UTL_HTTP to an external mail id?
View 5 Replies
View Related
Apr 22, 2009
I have a refresh group that will refresh several materialized views at 4:00am. Sometimes the job failed. We need to enter to the enterprise manager to check the status of the refresh group.
Our target is that notification email is sent to our email once the refresh job failed. I have found some software that can do that. However, we need to handle ourselves. I would like to ask whether we can do that using enterprise manager.
View 7 Replies
View Related
Feb 24, 2013
I have Oracle 11g on Linux...and i have set up SMTP of my Enterprise Manager Database control. I click on Test and an email was sent successfully. What i don't know is how to add more emails as the recipient of the alerts.
I have setup metrics like number of sessions, cpu usage, buffer cache hits, swapping, tablespace usage, etc...Upon saving and waiting for transactions to come in, alerts were shown the the EM homepage..but i did not receive any emails.
View 2 Replies
View Related
Apr 16, 2010
I have wrote a Stored Procedure Function that get all the rows from a Staging Table and assigns it to a CLOB and returns the CLOB. The issue is I'm getting the dreaded
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "F_CLOB_TEST", line 21
ORA-06512: at line 7
The error does not occur when I remove the 'PRC_ID', 'S_FIL_NAME' & 'exportDt' from the query. It works fine. The PRC_ID has data such as "700$702$7 05$706$707$708$709$710$711$712$713$714$715$294404$294405$294407$294408$294409$294410"
and S_FIL_NAM is the same for all columns - "SBENE_FILID810-2010-04-07-10.59.17"
The query returns 829 rows. Also I have to include a few more columns in the query which have data larger than the 'PRC_ID', but I have not included them here in the sample code, as this code by itself returns the ORA-6502 error.
create or replace
FUNCTION "F_CLOB_TEST" (job_id in Number)
return clob
is
c_clob clob;
[code]....
View 26 Replies
View Related
Nov 18, 2010
Procedure: CREATE OR REPLACE procedure test (a number, b varchar2) is
begin
dbms_output.put_line(a ||'->'||b);
end;
Anyonymous Block:
begin
exec test(1,'m');
end;
/
When i run this i am getting this error
ORA-06550: line 2, column 7:
PLS-00103: Encountered the symbol "TEST" when expecting one of the following
[code]...
View 5 Replies
View Related
Oct 7, 2013
I have to embed a picture as a header with an e-mail content for my work, I google'd and tested with different options but I failed. (The JPG is stored in a table as a blog object) When I tried with the below code the JPG file is e-mailed as an attachment with an e-mail content, I want it to be displayed inside an e-mail.
I tried att_inline: TRUE option as well - still not working.
Here is my code I tested and worked as an attachment.
DECLARE
/*LOB operation related varriables */
v_src_loc BLOB :=EMPTY_BLOB;
l_buffer RAW(54);
l_amount BINARY_INTEGER := 54;
l_pos INTEGER := 1;
l_blob BLOB := EMPTY_BLOB;
[code]....
View 3 Replies
View Related
Oct 2, 2008
I have a update statement that works fine when its run in SQL window but when i try it as part of a Stored Procedure its giving me the Insufficient privileges error on that table.
If i dont have privilege to update that table wont it not let me update while doing it outside the stored procedure as well?
View 4 Replies
View Related
Oct 19, 2011
I have the following error when I try to pass a Procedure parameter carrying db_link name to a stored procedure:
SQL> CREATE OR REPLACE PROCEDURE AFESD.P_DM_VCONTRACT_ITEM (CON_CONNECTION VARCHAR2)
2 IS
3 T_CONNECTION VARCHAR2(50);
4 T_SQL VARCHAR2(500);
5 BEGIN
6 T_CONNECTION := 'X_DM_TEST@' || CON_CONNECTION;
[code]....
The insert statement succeeds when I try to run DBMS_OUTPUT.put_line output.
View 4 Replies
View Related
Mar 14, 2011
I have written a stored procedure that has started returning the error:
Error starting at line 1 in command:
call p_glpost('DSTUK', 'L', '2008-01-01', '2008-01-01', '2011-02-18', 1, 1, 1, 0, 'Hi there')
Error report:
SQL Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "CLARITY.P_GLPOST", line 173
06502. 00000 - "PL/SQL: numeric or value error%s"
I can't seem to find a tool that will let me step into the actual stored procedure line by line to see where the error occurs. It mentions line 173, which seems to be a red-herring, as line 173 is simply one of the 'END IF' lines within this block:
IF NVL(r_dist.transtype,'wild') = 'wild' THEN
NULL;
elsif r_wip.transtype = r_dist.transtype THEN
v_matchCount := v_matchCount+1;
elsif r_wip.transtype <> r_dist.transtype THEN
[code]......
Tell me if it is possible to trace through a SP, and which tool is best (I am trying to use Oracle SQL Developer).
View 8 Replies
View Related
Aug 5, 2012
I have a system that has issues with sending some emails.
When the email sending process is activated the pages just hang.
Are there any logs or anything like that anywhere that i could look at to try and make sense of it.
View 1 Replies
View Related
Feb 18, 2013
On a test instance with Oracle 11gR2 with Enterprise Manager Database Control I try to activate the Email Notifications in Enterprise Manager, so that I can use them for monitoring.
So i configured following settings:
Under Setup -> Notification method I gave him a valid mail address with a vaild SMTP server. The test mail was send successfully.
Under Preferences -> General i gave SYS a valid mail address to receive. The execution plan i let in standard configuration, so every time at everyday i will get an email, when something happens. the test mail was send successfully too.
Now i created an own rule to test the configuration:
I went to Preferences -> Rules an add an rule. Name and description I named "test" and set "database instance". At availability I activated all checkboxed, so i should be informed when the instance goes down, comes up, getting an error, when the error is fixed, when agent is not available, when agent is available again, when there is a metric error, when the emetic error is fixed, when a blackout starts and when it ends.
Metrics, policies and jobs there wasn't defined anything in standard, so i let it so and set nothing here. I activate the checkbox for sending emails to sys and sending repeat emails every 15 minutes 3 times at all.
Now, to test this notification i logged into my testserver and killed the listener process, to see, what will happened. After 1 Hour waiting i didn't get any email, so i restarted the listener. after the listener was started my outlook was fluted by emails from the server, that the listener was down. But why i didn't get them earlier.
I checked the procedure with the database to, here i get it down by "shutdown abort" and "shutdown immediate". But i get the mails, that the database is down, when its up already. And thats to late!
When I want to use such email notifications for productive servers and someting is going wrong, for that a notifications is configured of course, I want to get the mail directly, when that happened and not, hen the problem is fixed!
For example, here an email from testing, where the listener was down, that i've get at 13:01 :
Target Name=LISTENER_<hostname>
Target Type=Listener
Host=<hostname>
Metric=Status
[code].........
View 2 Replies
View Related
Dec 24, 2012
Execute sp1 param1...param6
Execute sp2 param1...param8
Execute sp3 param1...param4
All these stored procedures deals with insert/updated transactions . i need to create a new stored procedure to execute all this in a single stored procedure which will be something like
create procedure sp4(param1...param8)
as
begin
Execute sp1 param1...param6
rollback if any error
Execute sp2 param1...param8
rollback if any error
Execute sp3 param1...param4
rollback if any error
end;
View 6 Replies
View Related
Nov 24, 2010
send script to send mail with excel attachment using smtp from Oracle 9i
View 1 Replies
View Related
May 6, 2012
I just Found emailing code . so i tried to execute on my test database.
My Database Version : PL/SQL Release 9.2.0.1.0 - Production
My Toad Version : 7.2
Code I tried To Execute :
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'Oracle9.2';
[code].........
While i execute procedure i get Transient Error..
This code is look like simple, then whats problem, because i m not attaching any file also...then why this error occurred.
View 1 Replies
View Related
Jan 19, 2010
Am facing a problem in sending an email to certain group of people, if the recipient just one person it will work but if more it will not work, I tried to read every one as a single recipient through a cursor but it was not working, the following is the sending
v_mail=:control.test;
mail_pkg.send
( p_sender_email => 'test@test.com',
p_from => 'test@test.com',
p_to => mail_pkg.array(V_MAIL),
[code].......
:control.test data is quotes between to quotes for each address.
View 2 Replies
View Related
Dec 9, 2010
Need info regarding sending an excel file attachment from plsql.I am using utl_smtp package in procedure and want to attach some sql query results in excel file.Is it possible to use sql query in same stored procedure that i am using to send the email attachment or i need to use external directory path to load the file as attachment.
View 9 Replies
View Related
Oct 13, 2010
I'm trying to send email using utl_mail...code is below
BEGIN
UTL_MAIL.send(
sender => 'account@test1.com'
,recipients => 'acount@test2.com'
,subject => 'Test Mail'
,message => 'Hello World'
,mime_type => 'text; charset=us-ascii'
,priority => 3);
END;
but getting following error
ERROR at line 1:
ORA-29279: SMTP permanent error: 550 <account@test2.com[/email]> No such user here
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 99
ORA-06512: at "SYS.UTL_SMTP", line 241
ORA-06512: at "SYS.UTL_MAIL", line 424
ORA-06512: at "SYS.UTL_MAIL", line 594
ORA-06512: at line 2
View 4 Replies
View Related
Sep 24, 2010
I've written a pl/sql to send mail. It works fine. Now i want to modify it to send an attachment txt/scv/xls file.This file is residing on client's pc.How to do it?
Following is pl/sql for sending mail.
create or replace PROCEDURE SEND_MAIL(P_SENDER IN VARCHAR2,P_RECIPIENT IN VARCHAR2,P_MESSAGE IN VARCHAR2,P_SUBJECT IN VARCHAR2 )IS
MAILHOST VARCHAR2(30) := 'MAILHOST.TIFR.RES.IN';
MAIL_CONN UTL_SMTP.CONNECTION;
BEGIN
MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(MAILHOST, 25); -- TYPICALY 25 S THE PORT NUMBER FOR INCOMMING SMTP REQUESTS ON YOUR SMTP MAIL HOST
UTL_SMTP.MAIL(MAIL_CONN, '<'||P_SENDER||'>'); -- THE '<' AND '>' DEPEDS ON WHAT TYPE OF SMTP MAIL HOST U'RE RUNNING THIS ONE'S A NETSCAPE SMTPHOST
[code]......
View 2 Replies
View Related
Mar 18, 2010
I am using Oracle 10.2.0.1.0. My intention is to create a new database instance.
Here are the steps I have taken,
1. Using DBCA, I created a new database instance, oracle sid = MYORCL0318
2. I created the tablespaces for this instance.(I obviously have not imported any data into this database yet.)
then, I went to Enterprise Manager,
Login = sys
password = xxxxxxx
role = SYSDBA
After I login, I would get an Error like this
java.lang.Exception: Exception in sending Request :: null
View 3 Replies
View Related
Jan 29, 2012
We need to display a pdf file in the email generated through the oracle database procedure.
We created a blob object to store the pdf file and it is working fine then we created a package to read the file and display it but when i am running the procedure from the sqlplus prompt the following message is appearing
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 325
ORA-06512: at "SYS.OWA_UTIL", line 377
ORA-06512: at "WHADMIN.IMAGE_GET", line 14
ORA-06512: at line 1
i am copying the procedure and package that i created for the same
create table demo
( id int primary key,
theBlob blob
)
declare
l_blob blob;
l_bfile bfile;
[code]....
when i am running image_get.pdf(1) from sqlplus prompt following error message is displayed on the screen
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 325
ORA-06512: at "SYS.OWA_UTIL", line 377
ORA-06512: at "WHADMIN.IMAGE_GET", line 14
ORA-06512: at line 1
View 4 Replies
View Related
