Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

UTL_SMPT Oracle Database 19C

RajeshAlexMar 24 2021 — edited Mar 24 2021

Hi All,
We are implementing send email in 19C.
I saw several docs, however nothing specific to 19C.
Here is what I have done so far

Login as SYS

begin
-- Allow SMTP access for user SCOTT
dbms_network_acl_admin.append_host_ace(
host =>'mailer.abc.com',
lower_port => 25,
upper_port => 25,
ace => xs$ace_type(
privilege_list => xs$name_list('SMTP'),
principal_name => 'SCOTT',
principal_type => xs_acl.ptype_db));
end;
COMMIT ;

CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS

mail_conn utl_smtp.connection;
username varchar2(1000):= 'rajesh_alex@abc.com';
msg_from varchar2(50) := 'rajesh_alex@abc.com';
mailhost VARCHAR2(50) := 'mailer.itc.abc.com';

BEGIN
mail_conn := UTL_smtp.open_connection(mailhost, 25);
utl_smtp.starttls(mail_conn);

-- UTL_SMTP.AUTH(mail_conn, username, passwd, schemes => 'PLAIN');

utl_smtp.mail(mail_conn, msg_from);
utl_smtp.rcpt(mail_conn, msg_to);

UTL_smtp.open_data(mail_conn);

UTL_SMTP.write_data(mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, 'To: ' || msg_to || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, 'From: ' || msg_from || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, 'Subject: ' || msg_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, 'Reply-To: ' || msg_to || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, msg_text || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_smtp.close_data(mail_conn);
UTL_smtp.quit(mail_conn);

EXCEPTION
WHEN UTL_smtp.transient_error OR UTL_smtp.permanent_error THEN
UTL_smtp.quit(mail_conn);
dbms_output.put_line(sqlerrm);
WHEN OTHERS THEN
UTL_smtp.quit(mail_conn);
dbms_output.put_line(sqlerrm);
END;

execute send_mail('rajesh_alex@abc.com', 'Email from Oracle Autonomous Database', 'Sent using UTL_SMTP');
However it gave below error
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SYS.UTL_SMTP", line 110
ORA-06512: at "SYS.UTL_SMTP", line 235
ORA-06512: at "SYS.UTL_SMTP", line 215
ORA-06512: at "SYS.UTL_SMTP", line 630
ORA-06512: at "SYS.UTL_SMTP", line 641
ORA-06512: at "AFS.SEND_MAIL", line 39
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_TCP", line 63
ORA-06512: at "SYS.UTL_TCP", line 314
ORA-06512: at "SYS.UTL_SMTP", line 290
ORA-06512: at "SYS.UTL_SMTP", line 296
ORA-06512: at "SEND_MAIL", line 15
ORA-06512: at line 1
Do I need to use wallet in 19C for authentication ?
Can someone give the exact steps ?

Thank you
Rajesh

Comments

Hoek
Please remove this part from your code, since it is a bug: 
EXCEPTION
WHEN OTHERS THEN
pErrMsg := SQLERRM;
ROLLBACK;
RETURN 0;
"do not be a sheep in wolf's clothing." ;)
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5080528400346352393#tom5211388400346093295

And do not commit in a loop.
Commit only once, when your complete transaction is done.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4951966319022

Edited by: Hoek on Aug 13, 2012 11:47 AM
jurgenk
And where do you assign a value to mTEMP?
Keith Jamieson
Can you not find all the tasks which are not done in one sql statement?
Do you have a common column between task_done and task_list, eg task_id.
If not, you should have.

Assuming you have a column task_id in task_list and task_done:
for cur in
(
SELECT tl.* FROM TASK_LIST  tl
   WHERE ISACTIVE = 1 
   and not exists(select 1 from task_done td where td.task_id = tl.task_id)
)
loop
  -- only deal with tasks  from the select here
  null; 
end loop;
710208
Thanks for reply, but my question is regarding the possibilities of getting 1 task executed twice.
the listed code is not the entire code, but the structure of function only.
710208
yes there is a common column.
but my problem is ,some of the tasks in the loop get executed more than once.
note :this happens mostly when the execution time is too long

thanks for the reply.
710208
Entire code is not posted.

Just the flow of code is given. please assume that all those values are assigned and working fine.
Suppose 5 rows are to be processed in the loop. some task say TASK3 get executed twice even though its occurrence
in the list for loop is only 1.
This happens when execution takes a long duration.

thanks .
Purvesh K
user10442658 wrote:
Dear concerned,

I have one function in a package (databse 11g) with following code.
As listed, looping a table TASK_LIST to execute each task after confirming that it is not
already executed (by querying TASK_DONE table).

Problem I have encountered is , at times same task get executed more than once even if I am counter checking
with variable "mSUCCEED". Please guide me in this regard.
I think this happens because of Duplicate Data in TASK_LIST table. Did you check that? If there are Duplicates, either remove them or use a Distinct keyword. However, you would not get Distinct Data with ' SELECT *', you will have to fetch particular columns to get a Distinct. Moreover, you are just using the TASKNAME in the Loop, so why do a "Select *"? Its a Bad practice.
Using this, you will not have to query the Task_Done table, to ensure whether the Job is processed.

Extract from your Other Reply
but my problem is ,some of the tasks in the loop get executed more than once.
note :this happens mostly when the execution time is too long
Plus, the execution time of the code would not be related to Duplicate Tasks being processed. AFAIK, When the "SELECT * FROM TASK_LIST WHERE ISACTIVE = 1" cursor is processed, any change in the data would not be reflected in the current execution. (I stand corrected, if I am wrong.)

Edited by: Purvesh K on Aug 30, 2012 6:07 PM
unknown-7404
>
And do not commit in a loop.
Commit only once, when your complete transaction is done.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4951966319022
>
Well - make up your mind. Should OP commit only once? Or when complete transaction is done? ;)

This code appears to implement a batch process where each step is independent of the other steps. As such each loop iteration does, in fact, represent a different transaction.

Each iteration of the loop is execting a (potentially different) function. Each function could very well be a 'complete transaction' that uses entirely different schemas, objects, tables, queries, etc.

If so then a commit after every function call IS a commit when the transaction is done. If this procedure is nothing more that a batch job that executes a series of independent steps then it is appropriate to issue a COMMIT after each step.

The AskTom link you cited applies to the traditional 'commit within a transaction' case. That, and similar cases, represent users doing frequent commits because they think they are saving resources or protecting the work already done even though there is still more work to do on those same objects.
Sven W.
user10442658 wrote:
Dear concerned,

I have one function in a package (databse 11g) with following code.
omg. this code is so full of bugs, I don't even know how to comment this garbage.
I assume it is not your code and you just have to maintain it. Poor guy. Is the other one already fired? I do hope so.

Well lets start with the most obvious problems.

1) Remove all COMMIT and ROLLBACK commands from it.
2) Remove the when others exception handler from it.

Tell us the result when running it again.
unknown-7404
>
This happens when execution takes a long duration.
>
As Purvesh already state a single execution of this one procedure cannot execute the same task twice unless that task is present twice in the initial SELECT that defines the cursor.

So another possibility is that the procedure is being called a second time while it is still executing the first time.

This can happen when a DBMS_JOB is scheduled to execute and calls the function or if the function is called a second time by another process or user.

There is nothing in your code that locks the task records being processed so a second function call might very well try to execute the same tasks.
Your cursor is 
FOR L IN (SELECT * FROM TASK_LIST WHERE ISACTIVE = 1 )
But it doesn't have a FOR UPDATE clause to lock those records. So another query for tasks "WHERE ISACTIVE = 1" will get the same records.

In fact, I don't see anything in the code you posted that sets ISACTIVE to a value other than '1' but you likely have that somewhere in the procedure.
1 - 10

Post Details

Added on Mar 24 2021
1 comment
3,321 views