Home » SQL & PL/SQL » SQL & PL/SQL » string_api package that... has no body? (Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production 0 Windows)
| string_api package that... has no body? [message #683097] |
Wed, 02 December 2020 17:38  |
 |
Darth Waiter
Messages: 41
Registered: October 2020
|
Member |
|
|
Hi All:
In the attempt to piece together a stored procedure that could send emails via an SMTP server that requires authentication, I went to https://oracle-base.com/articles/misc/email-from-oracle-plsql and took the snippet that sends to multiple recipients, added bits that allow attachments, and arrived at the procedure that I attach to this post. I call it as follows:
DECLARE
l_html VARCHAR2(32767);
BEGIN
l_html := '<html><head><title>Test HTML message</title></head><body><p>This is a <b>HTML</b> <i>version</i> of the test message.</p></body></html>';
send_mail(p_to => 'info@domain.com',
p_from => 'info@domain.com',
p_subject => 'Test Message',
p_text_msg => 'This is a test message.',
p_html_msg => l_html,
p_smtp_host => '192.168.0.14',
p_smtp_port => 25,
p_user_name => 'info@domain.com',
p_password => 'secret'
);
END;
Error report -
ORA-04067: not executed, package body "SYS.STRING_API" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "SYS.STRING_API"
ORA-06512: at "SYS.SEND_MAIL", line 27
ORA-06512: at "SYS.SEND_MAIL", line 39
ORA-06512: at line 14
04067. 00000 - "not executed, %s does not exist"
*Cause: Attempt to execute a non-existent stored procedure.
*Action: Make sure that a correct name is given.
What is missing for the string_api package? Am I not compiling it right, or am I not calling it right?
Thank you!
Al
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: string_api package that... has no body? [message #683117 is a reply to message #683116] |
Thu, 03 December 2020 14:38  |
|
Darth Waiter
Messages: 41
Registered: October 2020
|
Member |
|
|
I dropped the package and stored procedure from SYS schema and recompiled under my own user. Both compile without errors.
I still get the same error when I try to execute the SP, and the package still does not have a body.
ORA-04067: not executed, package body "U0.STRING_API" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "U0.STRING_API"
I executed just the BODY part, and the package now has a body. Still the same error.
[Updated on: Thu, 03 December 2020 14:51] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: string_api package that... has no body? [message #683125 is a reply to message #683122] |
Fri, 04 December 2020 07:40 |
|
Darth Waiter
Messages: 41
Registered: October 2020
|
Member |
|
|
How does UTL_SMTP.rcpt distinguish among TO, CC, and BCC addresses? I do not see in the docs that it does. It sounds like it piles all of them into one block of addresses, probably into TO, since the description says:
E-mail address of the user to which the message is being sent
This is very confusing. There should be a way to supply CC and BCC, since UTL_MAIL.send does it.
[Updated on: Fri, 04 December 2020 07:41] Report message to a moderator |
|
|
|
|
|
| Re: string_api package that... has no body? [message #683130 is a reply to message #683125] |
Fri, 04 December 2020 10:36 |
 |
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Darth Waiter wrote on Fri, 04 December 2020 14:40How does UTL_SMTP.rcpt distinguish among TO, CC, and BCC addresses? I do not see in the docs that it does. It sounds like it piles all of them into one block of addresses, probably into TO, since the description says:
E-mail address of the user to which the message is being sent
This is very confusing. There should be a way to supply CC and BCC, since UTL_MAIL.send does it.
UTL_MAIL is built upon UTL_SMTP.
UTL_SMTP.rcpt does not distinguish, it sends to all recipients and, as John said, you distinguish and define them in the data part of the message using WRITE_DATA procedure (as you define the "From" and "Reply-to" parts).
[Updated on: Fri, 04 December 2020 10:37] Report message to a moderator |
|
|
|
|
|
| Re: string_api package that... has no body? [message #683132 is a reply to message #683131] |
Fri, 04 December 2020 11:41 |
|
Darth Waiter
Messages: 41
Registered: October 2020
|
Member |
|
|
Now that the stored procedure and package work, I am facing a challenge on C# side.
When I call my SEND_MAIL procedure and pass all parameters, it works. But if I try to omit the parameters that have defaults on Oracle side, I get this error:
{"ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'SEND_MAIL'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored"}
My C# code is as follows:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SEND_MAIL";
cmd.Parameters.Add("p_to", OracleDbType.Varchar2, toAddress, ParameterDirection.Input);
if (!string.IsNullOrEmpty(ccAddress))
{
cmd.Parameters.Add("p_cc", OracleDbType.Varchar2, ccAddress, ParameterDirection.Input);
}
if (!string.IsNullOrEmpty(bccAddress))
{
cmd.Parameters.Add("p_bcc", OracleDbType.Varchar2, bccAddress, ParameterDirection.Input);
}
cmd.Parameters.Add("p_from", OracleDbType.Varchar2, fromAddress, ParameterDirection.Input);
cmd.Parameters.Add("p_subject", OracleDbType.Varchar2, subject, ParameterDirection.Input);
if ((format == NotificationFormat.Default ? Format : format) == NotificationFormat.Html)
{
cmd.Parameters.Add("p_html_msg", OracleDbType.Varchar2, body, ParameterDirection.Input);
}
else
{
cmd.Parameters.Add("p_text_msg", OracleDbType.Varchar2, body, ParameterDirection.Input);
}
if (attachments != null && attachments.Count() > 0)
{
cmd.Parameters.Add("p_attach_name", OracleDbType.Varchar2, fileName, ParameterDirection.Input);
cmd.Parameters.Add("p_attach_mime", OracleDbType.Varchar2, mimeType, ParameterDirection.Input);
cmd.Parameters.Add("p_attach_blob", OracleDbType.Blob, Encoding.Default.GetBytes(string.Join(Environment.NewLine, attachments)), ParameterDirection.Input);
}
cmd.Parameters.Add("p_smtp_host", OracleDbType.Varchar2, host, ParameterDirection.Input);
cmd.Parameters.Add("p_smtp_port", OracleDbType.Int32, portNumber, ParameterDirection.Input);
cmd.Parameters.Add("p_user_name", OracleDbType.Varchar2, smtpUserName, ParameterDirection.Input);
cmd.Parameters.Add("p_password", OracleDbType.Varchar2, smtpPassKey, ParameterDirection.Input);
create or replace PROCEDURE send_mail (
p_to IN VARCHAR2,
p_cc IN VARCHAR2 DEFAULT NULL,
p_bcc IN VARCHAR2 DEFAULT NULL,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_html_msg IN VARCHAR2 DEFAULT NULL,
p_attach_name IN VARCHAR2 DEFAULT NULL,
p_attach_mime IN VARCHAR2 DEFAULT NULL,
p_attach_blob IN BLOB DEFAULT NULL,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25,
p_user_name IN VARCHAR2 DEFAULT NULL,
p_password IN VARCHAR2 DEFAULT NULL)
AS
Does Oracle .NET connector not allow to omit optional parameters, or does it need any options set up on the command or connection for that?
|
|
|
|
| Re: string_api package that... has no body? [message #683133 is a reply to message #683132] |
Fri, 04 December 2020 12:09 |
|
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:Now that the stored procedure and package work
What was wrong?
You got help now help others with your solution.
Quote:I am facing a challenge on C# side.
Create a new topic in Windows (sub-)forum speciying which version of Windows, C#, which interface with Oracle you use (ODBC, OLE DB...) and which version.
But first answer the question above.
[Updated on: Fri, 04 December 2020 12:12] Report message to a moderator |
|
|
|
|
|
| Re: string_api package that... has no body? [message #683156 is a reply to message #683155] |
Tue, 08 December 2020 18:28 |
flyboy
Messages: 1901
Registered: November 2006
|
Senior Member |
|
|
Darth Waiter wrote on Tue, 08 December 2020 23:34I had no way of knowing that SqlDeveloper - the official Oracle's development IDE - may not be compiling both the package declaration and body. Once I realized that the body was not being compiled with no messages to that effect, I compiled the package body, and everything worked.
Yet, you still did not provide exact steps you initially took in sqldeveloper. I admit it is quite hard to reproduce them when using any GUI though; that's why you were ask to show output of sqlplus where it is easy to follow the work flow.
My guess is, as the script contains two statements, that in the initial "compilation" only the (first) statement under cursor (Execute Statement, default shortcut Ctrl+Enter or F9) was executed instead of running the whole script (Run Script, default shortcut F5) or executing both statements one-by-one (as you most probably did later; again, your post does not contain what exactly was run).
The difference is also described in the documentation: https://docs.oracle.com/en/database/oracle/sql-developer/20.2/rptug/sql-developer-concepts-usage.html#GUID-FB7B5B33-3B34-497D-B12A-C3 0779DE2322
[Edit: precised terminology in the guess]
[Updated on: Tue, 08 December 2020 18:58] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Tue Mar 28 04:33:35 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
