SQL & PL/SQL :: UTL_SMTP.DATA / Why Body Of Email Is Missing
Jul 23, 2012
I have a procedure called SANDEEP_INC105657, which calls another procedure called "Send_Prodcontrol_Mail_Pr". This procedure should be sending out an email with the following message:
From: <sandeep.tanjore@xxx.xx.ca>
Subject: Remit Advice Slips Report (TR000336).
To: sandeep.tanjore@xxx.xx.ca
Report TR000336 - Remit Advice Slips.
Total number of records that needs slips printed are: 1
However what's happening is we had a database change from 10g to 11g and ever since this has happened the email is sent with "From", "Subject" and "To" but the body(content of the email : Total number of records that needs slips printed are: 1) is missing.The code in Send_prodcontrol_Mail_pr is as follows:
CREATE OR REPLACE PROCEDURE Send_Prodcontrol_Mail_Pr( v_mailhost IN VARCHAR2,
v_sender IN VARCHAR2,
v_recipient IN VARCHAR2,
v_subject IN VARCHAR2,
v_message1 IN VARCHAR2,
v_message2 IN VARCHAR2,
v_message3 IN VARCHAR2)
[code]....
Any reason why the body of the email is missing? I tried the following:
replaced utl_smtp.data(mail_conn,mesg); with utl_smtp.write_data(mail_conn, utl_tcp.CRLF||mesg);
It sends out the email as required but it repeats the "From" two times with no "TO", and "Subject"... however then embeds the whole "from" "to" and the body in an email.
View 4 Replies
ADVERTISEMENT
Mar 5, 2012
I tried to create a UTL_SMTP package using '@?/rdbms/admin/utlmtp.sql' script but there are no package body for UTL_SMTP is created. There are only UTL_SMTP package is create.
View 4 Replies
View Related
Dec 30, 2011
I'm trying to send e-mail using UTL_SMTP with UTF-8 coding. I have a problem that the UTF-8 letters become `?`.
So here is the
p_to varchar2(250);
p_from varchar2(250):='email_address';
p_subject varchar2(250):='...vienotā...';
P_smtp_hostname varchar2(255) := 'IP';
P_smtp_portnum varchar2(5) := '25';
[code].......
The code is taken an example from internet source. Everything works fine but the UTF-8 not .
View 21 Replies
View Related
Jun 21, 2013
I am trying to add an image as attachment to an e-mail.
I am reading the image from the table and then passing it as a BLOB in my procedure.
Following is the part of the code which adds the attachment
{code}
PROCEDURE add_mail_attachment
(
/*
[Code].....
View 10 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
Jun 19, 2009
I am sending email with attachment (word, pdf) without any problem. Now what i want is, instead of attachment that pdf / word file should come in body part , like means in internet explorer pdf open inside the browser itself while surfing. so means as soon as user open email, content of word / pdf should display in body part. how can i do this. using forms 6i, and database 10g.
View 7 Replies
View Related
May 9, 2013
this is procedure to send email to multiple receipents
CREATE OR REPLACE PROCEDURE mail ( subject IN VARCHAR2,recievers VARCHAR2)
IS
sender VARCHAR2(30) := 'mailid';
[Code]...
i got procedure sucessfully created
execute mail('hi','mail id1,maildi2');
when i execute the procedure i get the following error
Error at line 1
ORA-29279: SMTP permanent error: 554 5.5.1 Error: no valid recipients
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 271
ORA-06512: at "SUPPLIER.MAIL", line 47
ORA-06512: at line 1
View 7 Replies
View Related
Nov 8, 2012
i ahave a table with below
01-oct-12,ARTACT001,Memory
04-oct-12,ARTACT001,Memory
01-oct-12,ARTACT001,OS
05-oct-12,ARTBRIO,CPU
Required output as below which dates are not available my table
02-oct-12,ARTACT001,Memory
03-oct-12,ARTACT001,Memory
01-oct-12,ARTACT001,OS
02-oct-12,ARTACT001,OS
[Code]...
View 5 Replies
View Related
Jun 6, 2012
formatting the mail message sent using utl_mail, i have created one table and it has around 17 rows inside as sample and it may increase as well, my present email format is in single line..find below the code i have written along with test case, you may substitute your emailid for test, especially my main focus is on the column mrk , where all the mrk are comming in one line but i want to be arranged in a line not more thant 10 rows, if it exceeds it should go to next line.
CREATE TABLE FAB_LOG ( MRK VARCHAR2(30))
INSERT ALL
INTO FAB_LOG VALUES ('1018017M-6001')
INTO FAB_LOG VALUES ('1018017M-6002')
INTO FAB_LOG VALUES ('1018017M-6003')
INTO FAB_LOG VALUES ('1018017M-6004')
INTO FAB_LOG VALUES ('1018017M-6005')
INTO FAB_LOG VALUES ('1018017M-6006')
[code]....
View 39 Replies
View Related
Aug 10, 2012
Database version
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
I have this query
select dept_id,qc_subtype_id,equip_code,drive_id
from
(select distinct dept_id,
decode(qc_subtype_id,
[code]...
View 3 Replies
View Related
Jul 19, 2012
My requirement is to find the closest date from col B(a.p_date) to the dates in col A(d.p_date). I got the following which works great:
SELECT
d.p_no_s,
d.p_date,
MIN(a.p_date),
MIN(a.p_date)-d.p_date||' Number of Days' NUM_OF_DAYS
[Code]...
Output:
p_no_s p_date MIN(a.p_date) MIN(a.p_date)-d.p
-------------------- ---------- ---------- -------------------------------------------------------
Z1575560 15/06/2008 29/07/2008 44 Number of Days
Z1575560 15/07/2008 29/07/2008 14 Number of Days
Z1575560 21/11/2008 27/12/2008 36 Number of Days
Z1575560 17/12/2008 27/12/2008 10 Number of Days
Problem:
For 1st and 2nd row,
I am getting 29/07/2008 as a.p_date for both 15/06/2008 and 15/07/2008 which is wrong in my scenario. This is because data is missing in the second table for row 1 (similarly for row 3). So What I want is :
Z1575560 15/06/2008
Z1575560 15/07/2008 29/07/2008 14 Number of Days
Z1575560 21/11/2008
Z1575560 17/12/2008 27/12/2008 10 Number of Days
Say for eg: a person is in city A. He is departing on (15/06/2008 ) and arriving on 29/07/2008 and again departing on 15/07/2008 which is not possible.
It should be departing on (15/06/2008 ) and arriving between 15/06/2008 and 15/07/2008 (missing data hence null is required here) and departing on 15/07/2008 and arriving on 29/07/2008 .
let me know how to achieve this.
View 4 Replies
View Related
Dec 29, 2011
user can't use utl_smtp. I tried - [URL] can use utl_smtp but other user can't.
I selected:
SELECT NVL(dbms_network_acl_admin.check_privilege_aclid(aclid, 'user123', 'connect'), 0)
FROM xds_acl;
And the result is:
1
0
0
0
0
0
0
0
0
0
0
0
View 6 Replies
View Related
Aug 8, 2013
SELECT c.table_name CHILD_TABLE, p.table_name PARENT_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND c.table_name = UPPER('ODS_TSAF_MES_PC');
and output is
child table parent table
ODS_TSAF_MES_PC ODS_TSAF_MES_PCTYP
ODS_TSAF_MES_PC ODS_TSAF_MES_PC
ODS_TSAF_MES_PC ODS_TSAF_MES_PCSTAT
i tried
SELECT A.piecestatus from ods_TSAF_MES_PCSTAT A WHERE NOT EXISTS
(SELECT * FROM ODS_TSAF_MES_PC B WHERE B.piecestatus = A.piecestatus);
and i found one piecestatus values is 'I' but i am not getting where it is related to the table and in which row it is getting affected?
View 6 Replies
View Related
Jul 20, 2010
As data completeness, data consistency, data integrity are some of features of good quality data. What is best approach and best practice to deal with missing or null values in database? I am facing such type of situation. In a main table, there are several columns have null or missing values.
Get rid of null or missing values. Should I consult source documents from where data was fed into database to achieve maximum data completeness?
View 14 Replies
View Related
Apr 23, 2013
While Configuring Data Guard for ORacle 10g (10.2.0.4) 64 bits on Windows 2007 Server 64 bits.I got few questions
1. What is the Default mode of Standby Database?
2. Should we Always Start Physical Standby Database to Recover Missing Redo Archive Log?
SQL> startup mount;
ORACLE instance started.
Total System Global Area 591396864 bytes
Fixed Size 2067496 bytes
Variable Size 163578840 bytes
Database Buffers 419430400 bytes
Redo Buffers 6320128 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
3. When there are missing Redo Log Archives e.g.
----On Standby Database--------
SQL> SELECT RESETLOGS_ID,SEQUENCE#,STATUS,ARCHIVED FROM V$ARCHIVED_LOG
2 ORDER BY RESETLOGS_ID,SEQUENCE#;
RESETLOGS_ID SEQUENCE# S ARC
------------ ---------- - ---
812980008 15 A YES
812980008 16 A YES
812980008 17 A YES
812980008 18 A YES
[code]....
65 rows selected. Log 8, 9, 10, 11, 12, 13, 14, 15 are missing.
How to Apply / Recover These Logs on Standby Database?
View 11 Replies
View Related
Feb 17, 2004
how to send an email to multiple recipients. Is there a delimeter that i have to use to separate the email addresses, or do i have to do the utl_smtp.rcpt(c,email) mulitple number of times.
View 9 Replies
View Related
Aug 2, 2006
I tried using UTL_SMTP to send simple emails. My code is as follows:-
PROCEDURE prc_send
IS
- variable to hold the smtp server connection
v_smtp_connection utl_smtp.connection;
-- variable to hold the contents of the email message
v_smtp_host VARCHAR2(100) DEFAULT 'smtp.server.com';
-- variable to hold the smtp port
v_smtp_port NUMBER DEFAULT 25;
BEGIN
-- establish the connection to the smtp server
[code].........
When I execute this, It shows authentication error. Error looks like ORA-29278: SMTP transient error: 454 5.7.3 Client was not authenticated. I know my smtp server requires password authentication. But I am not aware of how to do it with UTL_SMTP.
View 9 Replies
View Related
Aug 20, 2013
I have issue with application ,When I am trying to upload any a csv file of 3 MB in email through apexIts only uploading 32Kb of data remaining is ignoring for more then 32kbMay I know why this issue is and is there any process or methods to track this. I am using the apex version (Application Express 4.1.1.00.23).
View 2 Replies
View Related
May 18, 2011
I did the datapump export and import from one schema to a new schema in the same database. I had to use different tablespace. I used the following parameters in the parfiles :
export parfile
directory
dumpfile
logfile
parallel
import parfile
directory
dumpfile
logfile
parallel
remap_schema
remap_tablespace
Tell me whether I need to use different parameters than the one I used? Can I use both remap_schema and remap_tablespace at a time?
View 1 Replies
View Related
Jun 5, 2013
I performed a switchover test of my Exadata databases last night. Both databases are running 11.2.0.2 (BP7) on top of GI of the same version.I'm using Data Guard Broker to administer the Data Guard configuration.
I have, as you'd expect, the standby_file_management set to AUTO, so any file changes/additions/deletions that are made on Primary should be applied to Standby also.And they have been. Until last night.
When I had switched over to running Primary on the Standby site, I got this error message:
Tue Jun 04 22:27:12 2013
Errors in file /u01/app/oracle/diag/rdbms/exdw1pdg/exdw1pdg1/trace/exdw1pdg1_ora_26630.trc:
ORA-25153: Temporary Tablespace is Empty
I checked and my two temp tablespaces existed, but had no files in them. These files are 200Gb and 448Gb in size, so you'd think you'd notice them going missing. This wasn't by any means the first time we switched over (and, yes, I did create temp files for Standby when I built it and first switched over)
We've switched over to Standby multiple times and even ran a whole day's processing against it and haven't seen this. Ultimately, it wasn't a big deal, because I just created a tempfile for each of the tablespaces and off we went.Nothing in MOS seems to mention something like this. Basically, it looks like the switchover process decided to eat my tempfiles but keep my temp tablespace defintion. Odd.
View 5 Replies
View Related
Oct 9, 2013
I developed a program to send e-mails from Oracle using both UTL_MAIL as well as UTL_SMTP. My requirement now is to send around 75,000 emails per day, When I tested for 1000 emails it is taking around 6 mins., Then for 75,000 it would be around 7-8 hours. Not to put the server busy for these many hours. Can we really send this much of e-mails from Oracle? UTL_MAIL is little faster than UTL_SMTP)
View 12 Replies
View Related
Feb 7, 2012
I want to attache an external file to a email using UTL_SMTP package.
A file which attach will be present in directory.
View 3 Replies
View Related
Feb 12, 2013
OS: Solaris
DB: Oracle 11.2.0.1 EE
Not Using ASM or RAC
I have a Production database that is in archivelog mode and a Standby DR server. Both servers (Prod, Standby) have exact same structure and db name/version.
We manually scp archive logs and recover them to a manual standby database via SQL Scripts "cron". (I.E. set autorecovery on; recover standby database;)
We recently got out of sync with our log files and have not been applying them to the standby. As part of Prod Maintenance, these log files were deleted and are not available anymore.
I've tried several ways to "rebuild" our standby database. I have tried to Shutdown prod, backup all the db files and scp them to standby, re-create standby controlfile and startup mount and recover standby. Every time I try to apply a new archive log via recover standby, these are the errors:
ORA-00279: change 211077622 generated at 1/27/2012 12:18:42 needed for thread 1
ORA-00289: suggestion : /oradump/arch/PROD/PROD_arch_1_69486_736618850.arc
ORA-00280: change 211077622 for thread 1 is in sequence #69486
ORA-00308: cannot open archived log '/oradump/arch/PROD/PROD_arch_1_69486_736618850.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oradevices/PROD/oraPRODsystem1.dbf'
When I check v$log_history, the new logs have not been applied. I've also tried the "Restore from incremental backup via SCN" method with same results. Is there a way to re-create the standby clean and ensure that the log chain that is currently broken gets fixed or reset? I would eventually like to get DataGuard in here, but that's not the case at the moment.
View 5 Replies
View Related
Sep 14, 2010
I am sending mail thru UTL_SMTP. But i am unable to send the CLOB in it.If i break the CLOB into varchar2 then also it doesn't work.
Variables:-
vBuffer VARCHAR2 (4000);
l_amount BINARY_INTEGER := 4000;
l_pos PLS_INTEGER := 1;
l_clob_len PLS_INTEGER;
[code].....
While using the write_data procedure it is giving error as "Invalid OPeration"...how we can send the mail with CLOB.
View 1 Replies
View Related
May 20, 2012
In my database I found some anomalies like
SQL> select SEGMENT_NAME, SEGMENT_TYPE, BLOCKS, EXTENTS, BYTES/1024
2 from user_segments
3 where SEGMENT_TYPE='TABLE'
4 ORDER BY SEGMENT_NAMe;
[code]....
The countries table is missing in user_segments data dictionary view.But I can queries the countries using select statement.
SQL> SELECT * FROM COUNTRIES;
CO COUNTRY_NAME REGION_ID
-- ---------------------------------------- ----------
AR Argentina 2
AU Australia 3
BE Belgium 1
BR Brazil 2
[code]...
why the country table is missing in user_segments data dictionary table.
View 8 Replies
View Related
Apr 11, 2010
I am getting the error (ORA-29266: end-of-body reached) at utl_http.read_text. below is a code
create or replace procedure sendMail(p_fromaddress IN VARCHAR2, -- Optional
p_toaddress IN VARCHAR2,
p_bcc IN VARCHAR2, -- Optional
p_cc IN VARCHAR2, -- Optional
p_subject IN VARCHAR2,
[code]....
View 6 Replies
View Related
Jan 6, 2013
I'm trying to create a package body but i keep getting an error message saying package created with complication.
CREATE OR REPLACE PACKAGE BODY MY_PACK
IS
PROCEDURE MY_PROC(A NUMBER, B NUMBER)
IS
C NUMBER(4);
BEGIN
C:=A+B;
[code].......
View 2 Replies
View Related
Aug 25, 2010
Up until today we assumed that compiling a package BODY-only would not cause invalidation of other code. However when we compiled the body of the HTP package, the OWA package became invalid and required re-compilation.
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
View 6 Replies
View Related
Mar 31, 2011
I want to see a stored procedure definition which is inside the body of a package and the package is wrapped.So I am not able to see the content of the package body.I am new to oracle and I don't know how to decrypt the wrapped package.how to read the content of a wrapped package inside oracle.
View 1 Replies
View Related
Jun 28, 2011
When i compile the package body i get errors.
CREATE OR REPLACE PACKAGE BODY CEE_OSPCM_PKG AS
PROCEDURE CEEOT_WORK_TYPE_PRC IS
CURSOR CUR_WORK_TYPE IS
SELECT *
[code]...
View 7 Replies
View Related