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 Go to next message
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;
And it throws the following error:

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.
The package STRING_API exists. I have taken it from https://oracle-base.com/dba/script?category=miscellaneous&file=string_api.sql and compiled. I can see that it has been created, but it does not have the "gift box" icon in SqlDeveloper that other packages have. I include the screenshot of what I mean with the stored proc.

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 #683098 is a reply to message #683097] Wed, 02 December 2020 17:40 Go to previous messageGo to next message
Darth Waiter
Messages: 41
Registered: October 2020
Member
Sorry, had to put the screen shot into a separate message, due to the attachment limits
  • Attachment: Capture.PNG
    (Size: 14.39KB, Downloaded 157 times)
Re: string_api package that... has no body? [message #683099 is a reply to message #683097] Wed, 02 December 2020 17:42 Go to previous messageGo to next message
Darth Waiter
Messages: 41
Registered: October 2020
Member
Had to put the screen shot into a separate message, due to the attachment limit.
/foru/forum/fa/14430/0/
  • Attachment: Capture.PNG
    (Size: 14.39KB, Downloaded 156 times)
Re: string_api package that... has no body? [message #683100 is a reply to message #683097] Thu, 03 December 2020 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 67853
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Am I not compiling it right
Most likely but you didn't show us how you compiled it.

NEVER create your objects in SYS schema.
Read SYS/SYSDBA is special.

Re: string_api package that... has no body? [message #683101 is a reply to message #683100] Thu, 03 December 2020 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 67853
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topics:

Michel Cadot wrote on Wed, 04 November 2020 22:01

...Also read SYS/SYSDBA is special.

Michel Cadot wrote on Thu, 05 November 2020 17:15

...What about a feedback in your previous topics?
Re: string_api package that... has no body? [message #683110 is a reply to message #683101] Thu, 03 December 2020 10:08 Go to previous messageGo to next message
Darth Waiter
Messages: 41
Registered: October 2020
Member
Which user should I log and compile these procedures and packages in under? My Oracle is a fresh vanilla install on my local machine.
Re: string_api package that... has no body? [message #683111 is a reply to message #683110] Thu, 03 December 2020 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 67853
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why not your own user?

Re: string_api package that... has no body? [message #683112 is a reply to message #683111] Thu, 03 December 2020 10:42 Go to previous messageGo to next message
Darth Waiter
Messages: 41
Registered: October 2020
Member
I do not know, why. When I ask a question, it is because I do not know, and answering with a question does not provide me with any ideas, clues, or hints.
My line of thinking was that if I compiled packages under my own user, they would be available to my user only, and not to "everyone". But I know too little.
Re: string_api package that... has no body? [message #683113 is a reply to message #683112] Thu, 03 December 2020 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 67853
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you compile in ANY schema it is available to ONLY this schema UNLESS it gives the privilege to execute it to another one.

Database Concepts, Part VII Oracle Database Administration and Application Development

Re: string_api package that... has no body? [message #683115 is a reply to message #683113] Thu, 03 December 2020 13:43 Go to previous messageGo to next message
Darth Waiter
Messages: 41
Registered: October 2020
Member
I do not understand why the rest of packages work fine even though the are in SYS schema but mine does not. If SYS should not be used, why are they under SYS?
Re: string_api package that... has no body? [message #683116 is a reply to message #683115] Thu, 03 December 2020 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67853
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle built-in packages are in SYS schema, users (yours) packages must not be.
SYS is Oracle's and Oracle's only.

Re: string_api package that... has no body? [message #683117 is a reply to message #683116] Thu, 03 December 2020 14:38 Go to previous messageGo to next message
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"
Figures, when STRING_API script compiles, it only creates the package, but the CREATE OR REPLACE BODY par is not being executed.
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 #683118 is a reply to message #683117] Thu, 03 December 2020 14:53 Go to previous messageGo to next message
Michel Cadot
Messages: 67853
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You did something wrong.

If you want us to help you you have to:
- use SQL*Plus
- copy and paste the COMPLETE SQL*Plus session showing what you do and get

Re: string_api package that... has no body? [message #683119 is a reply to message #683118] Thu, 03 December 2020 15:08 Go to previous messageGo to next message
Darth Waiter
Messages: 41
Registered: October 2020
Member
I closed the window with my send script, opened it again, and it worked. Looks like I did nothing wrong, only that in Oracle, the open connection does not know of a body being created in an existing package.
Re: string_api package that... has no body? [message #683122 is a reply to message #683119] Fri, 04 December 2020 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 67853
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 03 December 2020 21:53

You did something wrong.

If you want us to help you you have to:
- use SQL*Plus
- copy and paste the COMPLETE SQL*Plus session showing what you do and get
Re: string_api package that... has no body? [message #683125 is a reply to message #683122] Fri, 04 December 2020 07:40 Go to previous messageGo to next message
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 #683126 is a reply to message #683125] Fri, 04 December 2020 08:21 Go to previous messageGo to next message
John Watson
Messages: 8547
Registered: January 2010
Location: Global Village
Senior Member
You use rcpt for all the recipients, and in your write_data calls you specify the type of recipient. Google aropund and you'll find plenty of examples.
Re: string_api package that... has no body? [message #683130 is a reply to message #683125] Fri, 04 December 2020 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67853
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Darth Waiter wrote on Fri, 04 December 2020 14:40
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.

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 #683131 is a reply to message #683126] Fri, 04 December 2020 10:42 Go to previous messageGo to next message
Darth Waiter
Messages: 41
Registered: October 2020
Member
John Watson wrote on Fri, 04 December 2020 08:21
You use rcpt for all the recipients, and in your write_data calls you specify the type of recipient. Google aropund and you'll find plenty of examples.
Thank you! I missed this part.
Now I see how Tim Hall does this, but I do not pretend that I understand this convoluted way of doing things.
Re: string_api package that... has no body? [message #683132 is a reply to message #683131] Fri, 04 December 2020 11:41 Go to previous messageGo to next message
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"}
The message does not make sense since column 7 of line 1 is the 7th character in the 9-character SP name.
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);
and the SP parameters look as follows:

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
I can call the SP directly in SqlDeveloper, as in the OP, without many of the parameters that have defaults, and it works fine.

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 Go to previous messageGo to next message
Michel Cadot
Messages: 67853
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 #683155 is a reply to message #683133] Tue, 08 December 2020 16:34 Go to previous messageGo to next message
Darth Waiter
Messages: 41
Registered: October 2020
Member
Michel Cadot wrote on Fri, 04 December 2020 12:09
Quote:
Now that the stored procedure and package work
What was wrong?
It is all in http://www.orafaq.com/forum/mv/msg/207280/683117/#msg_683117 above.

I 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.
Re: string_api package that... has no body? [message #683156 is a reply to message #683155] Tue, 08 December 2020 18:28 Go to previous message
flyboy
Messages: 1899
Registered: November 2006
Senior Member
Darth Waiter wrote on Tue, 08 December 2020 23:34
I 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

Previous Topic: Help for a noobie!
Next Topic: REGEXP_REPLACE
Goto Forum:
  


Current Time: Tue May 18 21:21:14 CDT 2021