[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to format subject in Oracle trigger

Posted on 2009-04-27
16
Medium Priority
?
467 Views
Last Modified: 2013-12-18
Greeting,
I have a trigger below which send a txt message out to the mobile phones when there ia a new record being inserted. But I got ISO-8859-1 in the subject from the text message. How to format it so only shows the text in the subject?

Below is my trigger from oracle 10g

CREATE OR REPLACE TRIGGER MyTBL
AFTER INSERT
    ON MyTBL
    FOR EACH ROW

DECLARE
CRLF        CHAR(2) := CHR(10) || CHR(13);

BEGIN
 IF (:new.order_type='S') THEN
       SYS.UTL_MAIL.SEND( sender =>'xxx@yahoo.com,
                          recipients =>'xxxxxx@txt.att.net',
                          subject => ||:new.Proposal|| CRLF,
                          message => 'Work Order: '|| CRLF
                          ||:new.Proposal|| CRLF|| CRLF|| CRLF    
                          ||'Description: '|| CRLF
                          ||:new.description|| CRLF|| CRLF|| CRLF
                          );
END IF;
0
Comment
Question by:mrong
  • 9
  • 7
16 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 24244398
crlf  is chr(13) || chr(10),  you have them backwards.

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24244428
I'm not sure why you're appending a crlf (or lfcr) to the subject anyway
0
 

Author Comment

by:mrong
ID: 24244526
sdstuber:
I changed it to the following but still the same.

CREATE OR REPLACE TRIGGER MyTBL
AFTER INSERT
    ON MyTBL
    FOR EACH ROW

DECLARE
CRLF        CHAR(2) := CHR(13) || CHR(10);

BEGIN
 IF (:new.order_type='S') THEN
       SYS.UTL_MAIL.SEND( sender =>'xxx@yahoo.com,
                          recipients =>'xxxxxx@txt.att.net',
                          subject => :nre.Proposal,
                          message => 'Work Order: '|| CRLF
                          ||:new.Proposal|| CRLF|| CRLF|| CRLF    
                          ||'Description: '|| CRLF
                          ||:new.description|| CRLF|| CRLF|| CRLF
                          );
END IF;
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:mrong
ID: 24244533
It is subject => :new.Proposal.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24244574
what is proposal for the row?

is it  'ISO-8859-1'  if not,  where is that value coming from?
0
 

Author Comment

by:mrong
ID: 24244611
Here is what I have in the subject from mobile phone text. I am not sure where it comes from:(

?ISO-8859-1?00000001?=|
0
 

Author Comment

by:mrong
ID: 24244627
The proposal is a number fileld from MyTBL.
PROPOSAL                                  NOT NULL VARCHAR2(15)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24246011
can you show an insert statement that is producing the wrong subject?
0
 

Author Comment

by:mrong
ID: 24249693
sdstuber:
Not sure what you mean. The insert is done by an application and I don't think it will producing a wrong subject.
thanks.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24249720
the reason I want to see the insert, is based on what you've posted above...

subject => :new.Proposal

will only produce '?ISO-8859-1?00000001?=|'

if the insert statement from your application is inserting '?ISO-8859-1?00000001?=|'
0
 

Author Comment

by:mrong
ID: 24249951
sdstuber:

I did a test which sent same message to an email addr and a nextel phone. The subject is fine for the email, but got "will only produce ?ISO-8859-1?00000001?=|' for the phone.

btw, the subject should only show 00000001 which is the proposal.

thanks.
0
 

Author Comment

by:mrong
ID: 24249988
Also, I show 'Proposal' in both the subject and the message body. only the subject got this kinna stuff.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24250360
so, the same message body and subject are fine when sent to email but not fine when sent to a phone?

then that seems like a problem with the phone and not the email transmission.

remove the trigger and just test utl_mail call itself to both email and phone
BEGIN
    sys.UTL_MAIL.send(
        sender       => 'xxx@yahoo.com',
        recipients   => 'xxxxxx@txt.att.net',
        subject      => '00000001',
        MESSAGE      =>   'Work Order:'
                       || CHR(13) || CHR(10)
                       || '00000001'
                       || CHR(13) || CHR(10)
                       || CHR(13) || CHR(10)
                       || CHR(13) || CHR(10)
                       || 'Description: '
                       || CHR(13) || CHR(10)
                       || 'TestDescription'
                       || CHR(13) || CHR(10)
                       || CHR(13) || CHR(10)
                       || CHR(13) || CHR(10)
    );
END;

Open in new window

0
 

Author Comment

by:mrong
ID: 24250454
sdstuber:
I called itself to both email and phone and still the same. The message subject is fine which sent to the email addr, but not the one sent to phone.

Is it something to do with the mail reader for the phone. Is there anyway to format the subject to ASCII from "ISO-8859"?

thanks
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 200 total points
ID: 24250474
not that I know of, the reader might be configurable within the phone though
0
 

Author Comment

by:mrong
ID: 24272669
sdstuber:
thank you for your help. I think it is something to do with the phone. I would give you credit on helping me out here.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question