learned sth great
A reader, January 12, 2003 - 5:26 pm UTC
Very good solution!
Brett Gaston, January 12, 2003 - 10:04 pm UTC
That is the framework for what I am trying to do. I think it will be easy for me to make it work in my application now. Thanks a lot.
Mikhail Veramchuk, January 14, 2003 - 4:53 am UTC
Hi, Thom!
I repeat your code in my 9.2 database, but my job doesn't started. In trace file I have
*** SESSION ID:(15.3641) 2003-01-14 09:33:39.000
*** 2003-01-14 09:33:39.000
ORA-12012: error on auto execute of job 121
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 10
But dbms_job.RUN(121) is executed successfully ...
Thanks
January 14, 2003 - 7:47 am UTC
ok, so read line 10 of your code and figure out why you getting more than one row!
you see - my example has no line 10. you have a bug in your code -- a select into that returns more than one row.
Mikhail Veramchuk, January 14, 2003 - 8:41 am UTC
create table email_parameters
( id number primary key,
address varchar2(100) );
create or replace procedure send_email(p_job in number)
is
l_rec email_parameters%rowtype;
begin
select * into l_rec from email_parameters where id = p_job;
delete from email_parameters where id = p_job;
end;
/
create or replace trigger send_email_trg
after insert on employees
for each row
declare
l_job number;
begin
dbms_job.submit (job => l_job,
what => 'send_email(job);' );
insert into email_parameters
values (l_job, 'tru-la-la-la-la-la');
end send_email_trg;
/
Table email_parameters has only one record:
10 tru-la-la-la-la-la
In trace file:
*** SESSION ID:(30.207) 2003-01-14 14:26:34.000
*** 2003-01-14 14:26:34.000
ORA-12012: error on auto execute of job 10
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 10
But dbms_job.run(10) run and remove this job from queue
January 14, 2003 - 9:11 am UTC
I cannot reproduce that -- but -- do this, put an exception block in there -- catch the too_many_rows exception and use raise_application error to prove it is where we think it might be
create or replace procedure send_email(p_job in number)
is
l_rec email_parameters%rowtype;
l_cnt number;
begin
begin
select * into l_rec from email_parameters where id = p_job;
exception
when too_many_rows then
select count(*) into l_cnt from email_parameters where id= p_jod;
raise_application_error( -20001, 'Too many rows, id = ' || p_job
|| ' count = ' || l_cnt );
end;
delete from email_parameters where id = p_job;
end;
/
Mikhail Veramchuk, January 14, 2003 - 10:46 am UTC
Thanks, Thom!
It seems, I have some problems with my 9.2.0.2 server.
Above code works fine on 8.1.7.
But I haven't any idea how to solve this problem.
It seems below message points to line 10 beyond my code
*** SESSION ID:(78.62) 2003-01-14 16:21:13.000
*** 2003-01-14 16:21:13.000
ORA-12012: error on auto execute of job 11
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 10
January 14, 2003 - 10:58 am UTC
alter system set sql_trace=true scope=spfile;
shutdown;
startup;
let it run and fail, as soon as it does
alter system set sql_trace=false scope=spfile;
shutdown;
startup;
and grep the trc files in udump/bdump for
$ grep 'err=1422' *.trc
edit the resulting trace file, search for err=1422, it'll look like this:
ERROR #3:err=1422 tim=1176756592
search backwards for (in this case, since it was #3)
PARSING IN CURSOR #3
that'll be the block of code that is causing the issue. that might shed some light on it.
i could not reproduce on 9202
Mikhail Veramchuk, January 15, 2003 - 3:21 am UTC
Thanx for your time and explanation. I found error in my logon trigger.
email
mo, April 24, 2003 - 12:43 pm UTC
Tom:
1. If I have an after insert trigger that sends an email using mail_pkg.send would not send the email before I commit? (not using the job scheduler).
2. If I want to have p_body be a table created by a sql query, would you assign the query results to a string variable and pass it in as a parameter? Can you control the formatting of the table here?
3. Is there a size limit on the P_body?
Thank you,
April 24, 2003 - 12:46 pm UTC
1) not really a question? unless you use dbms_job to send in the background -- the email goes right away. DEFINITELY not recommended at all.
2) er? are you asking how to send HTML? as written, this sends ascii text,
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1739411218448 <code>
shows howto HTML
3) 32k as implemented, change implementation to be a clob or a blob and 4gig would be the theoretical limit.
email
mo, April 30, 2003 - 9:46 am UTC
Tom:
1. I have email_parameters table with a colum email_type (long). When I insert into the table, I have a variable v_data varchar2(1000).
v_data:=chr(10)||'The subject Request created on '||to_char(v_request_date,'mm/dd/yyyy')||' has been shipped.' ||chr(10)||chr(10)||'To view the the Shipment Packing List please click the link below.'||chr(10)||chr(10);
v_data:=v_data||'</code>
http://xxx.xxx.xxx:yyyy/pls/devd/print_packing_list? shipment_id='||:new.shipment_id||'&i_user_id='||:new.user_id;
When I get the email in MS Outlook it looks fine. However when the email gets received at Novell groupWise client it cuts off the hyperlink at the 72th character and the remaining text is show as regular text. Thus when you click the link it gives a blank page.
Do you know whether this may be caused by the database and if there is a way to force the whole text after " http://" <code>as a hyperlink?
Thank you,
April 30, 2003 - 10:00 am UTC
it is not the database.
It is your email client.
The only solution -- use a smaller URL -- each client has a differnent limit. It is like the email I send to you guys when I ask for more info -- I put right in it this text:
Please add additional information to your question using the link:
http ://asktom.oracle.com/pls/ask/f?p=4950:15:::NO::F4950_P15_ID:nnnnnnnnn
It is important that you use the above URL (watch out for word wrapping by
your email client! That URL works). We get many emails and the only way
we can keep track of things is to have the questions in the database. If
you do not use the link but instead email us directly we cannot promise
you will receive an answer.
email
mo, April 30, 2003 - 10:14 am UTC
Tom:
1. How can I make my URL smaller if the procedure (for example) requires two parameters? It will not work otherwise.
2. I tried a test where I sent the same email to a yahoo account and it showed up fine. Then I forwarded the yahoo email to a groupwise email and it showed up fine. As a second test, I copied the link and pasted it in groupwise and sent it to another groupwise account and it showed up fine. Does that indicate it may not be the client?
3. Can these things be fixed in the email clients (configuration) or it can not?
Thank you,
April 30, 2003 - 10:18 am UTC
look at how short we made the "url" to dbms_job -- all that is passed is "job"
you could do the same. create a table, stuff the big parameters in there, give them a sequence number and send a url like
http ://host/pls/x/y?p=12345456
y is your procedure.
as for the email stuff -- time to dig out a book or two or three and look around. I'm not an email expert (i keep saying "courier 8 is the only font I need" don't i). I got the email into your inbox -- you need to take it from there.
email
mo, April 30, 2003 - 10:47 am UTC
Tom:
To make sure I understand what you are saying:
1. I made a duplicate procedure (pl) instead of the long name that i had.
2. My email_parameters table already has a unique identifier "ID" which is the job id. Do I still need to create a sequence number and store it there too. Then I should create another procedure that only receives that sequence as an input parameter and then pull out all other required data from the email_parameters table?If this is correct can't I use the job ID instead.
Thank you,
April 30, 2003 - 6:59 pm UTC
2) design decision. something you can handle I'm sure. use whatever -- you have the "concept" -- you need nothing more then the concept to develop YOUR implementation.
pop up
juancarlosreyesp@yahoo.com, April 30, 2003 - 6:28 pm UTC
Hi Tom is possible in some way that oracle read messages from email server?, maybe using java,
thanks
April 30, 2003 - 7:27 pm UTC
sure, learn the javamail API, load it into the database and read away.
pop3
A reader, April 30, 2003 - 7:03 pm UTC
April 30, 2003 - 7:30 pm UTC
c/8\.1\.5/9i/g
if you know vi or sed, that'll make sense ;)
A reader, May 01, 2003 - 2:58 pm UTC
Thanks Tom :)
Receiving from ,date,subject of email in oracle database
Muhammad Mustafa, March 11, 2004 - 5:53 am UTC
Dear Sir
Is it possible to store incoming mail date,subject in the oracle database if yes please give me any example.
Thanks & Regards
March 11, 2004 - 11:40 am UTC
well, we have Collaboration Suite which is email in Oracle.
Otherwise, you would load up the javamail API in order to "read" email (i don't have an example of reading via javamail, only SENDING via javamail. basically, you would have to write a program that read mail)
dbms_job, utl-smtp to send results
karma, July 19, 2004 - 3:19 pm UTC
We need to run pre-canned QA scripts to run at certain time or on-demand and then email results. I would like to be able to allow users to add new QA scripts which would be picked by process when it runs. Is dbms_job and utl_smtp the best tools to use for this type of problem ?
How can we store long complex SQL in database and pass that as argument to run within dbms_job? Any example ?
July 19, 2004 - 4:45 pm UTC
lots of questions here....
what does a "new qa script" look like exactly?
I suppose you would create a table with sql statements stored in it.
you would have a "qa routine" that queried said table, pull the sql out, dynamically executed it and printed it out using utl_smtp.
this qa routine is what dbms_job would call -- the qa routine would have to have its logic to figure out what sql to run and where to send the results.
dbms_job- send email
karma, July 19, 2004 - 10:16 pm UTC
Qa scripts are complec select statements. One set of sql statements will do count(*) to check all kind of erros and if greater then zero then email indicating problems. Then run the same sql to send sample records i.e. first 50 records. Reason we do this is because we are not sure if we can run this as one sql statement
July 20, 2004 - 8:43 am UTC
ok, so what i described above was fine. you just need to develop a small schema to hold your metadata (queries and such) and create a procedure to run them using dbms_sql so you can dump and print the results out (search for dump_csv on this site for an example).
Emailing after inserting into database tables
sangita saxena, August 13, 2004 - 9:24 am UTC
Tom ...I find this very useful but have a question regarding this .
You say "remember to set job_queue_processes and job_queue_interval on the server!" . Is there a number it should be set at . Ours is set at 5 and our DBA questioned this trigger . Our application is a request workflow application wherein an email is sent out to whoever has an approval awaiting in their queue ..in other words there are around 2-3k emails sent by the application everyday.
August 13, 2004 - 4:53 pm UTC
in current releases of the software, job_queue_interval is already set and not settable by you.
job_queue_processes is something YOU have to decide. how many concurrently executing jobs do you want to support?
How many concurrent emails do you want to be hitting your smtp server with -- that'll help you decide.
email if not inserting
A reader, August 24, 2004 - 3:24 pm UTC
Hi Tom,
I have a table ttest
create table ttest
(
id number,
str varchar2(32)
);
data is continuosly being inserted into this table. if for 10 mins, no data is inserted then I want to send an email. how do we achieve this?
thanks.
August 24, 2004 - 3:45 pm UTC
not quite possible as you don't have anything in the table to indicate when it was last inserted into.
guess you could enable auditing and query the audit trail, but it would be best to have a timestamp in the table and have a job that queries every N minutes for the max timestamp and checks that against the current time.
Emailing for Errors
Jyoti, April 21, 2005 - 6:33 pm UTC
Hi,
This is exactly what I have to do - email to a group in case of Customer Merge failure driven by ERP system to our downstream application.
Thanks!!!
sending emails
smitha, May 22, 2010 - 12:16 am UTC
Nice post...
But still
1.If it was QA database utl_smtp sending mails after commit of dynamic sql insert statement.
2.If it was PRODUCTION database utl_smtp not sending mails after commit of dynamic sql insert statement.
is there any possiblility ?
May 24, 2010 - 12:48 pm UTC
huh? utl_smtp doesn't care if you are in a transaction or not.
probably your when others then null;
erase that code - now and never ever use it again. when others then null is almost certainly a bug in your code, every time you put it there you put another bug in your code.
(I know you have when others then null; from your other posting, get rid of it.)
Can an Oracle trigger fire after I get an email in my office outlook ?
Asit, March 28, 2013 - 7:46 am UTC
Hi Tom,
Is there any way so that my Oracle trigger would fire when I get an email in my Office outlook ?
Regards,
Asit
Much appreciated
A reader, April 30, 2013 - 6:41 am UTC
Thank you so much for your detail clarification.
Much helpful !!
-- Asit