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!

Function call with multiple values return

SynerdgyJan 16 2017 — edited Feb 5 2017

Hi everyone!  I have a requirement to enter user 's name in an HTML email from a table with multiple users( then of course send the email).  After reading here in the forum I was able to understand I need to create a "function" to pass the variable to the html text.  Success.  However now I need to send an individual email to each user with correct username from the function.  My html email works successfully using a loop but I am unable to insert User names for each individual email sent to user.  I hope this makes sense but I promise to elaborate if necessary. If there is an article out there please share.  Here is my current function I am using but I receive an error upon executing the function from the html procedure (PLS-00306: wrong number or types of arguments in call to 'EMAILDETAILSNAME'). Any help would be greatly appreciated.  Thank you.

create or replace function EMAILDETAILSNAME

( flname_in IN varchar2 )

RETURN varchar2

IS

   flname varchar2(150);

  

  cursor c1 is

   select FLNAME

   from email_test7 WHERE FLNAME = flname_in;       

BEGIN

  

   open c1;

   fetch c1 into flname;

  

   if c1%notfound then

   DBMS_OUTPUT.PUT_LINE ('No name found');

   end if;

  

   close c1;

  

   RETURN flname;

  

  

end;
This post has been answered by John Spencer on Jan 16 2017
Jump to Answer

Comments

Cookiemonster76

Well the error means there's something wrong with the parameters you're passing to the function.

We've got no of knowing what is wrong with the parameter unless you show us the code that calls the function.

Synerdgy

Here ya go.

DECLARE

l_html varchar2(32767);

    variableContent varchar2(32767);

   -- l_msg varchar2(30)       := 'Welcome';

begin

    /* use your function to get your variable content */

    variableContent := EMAILDETAILSNAME();

    l_html := '

            <html>

              <body>

             

                <h2><p>' || EMAILDETAILSNAME ||' </p></h2>

              </body>

            </html>';

   FOR m IN (SELECT *

            FROM email_test7

        )

LOOP

  send_mail2(p_to        => m.email,

            p_from      => 'admin@company.com',

            p_subject   => m.subject,

            p_text_msg  => 'This is a test message.',

            p_html_msg  => l_html,

            p_smtp_host => 'smtp.company.com');

           

        UPDATE email_test7

      SET sent_yet = 'Y',

      trans_dt = systimestamp

      WHERE EMAIL_ID = m.email_id;

END LOOP;

END;

John Spencer
Answer

Your declaration of the function says:

create or replace function EMAILDETAILSNAME (flname_in IN varchar2 ) RETURN varchar2

You call the function as:

variableContent := EMAILDETAILSNAME();

Your call need to match the declaration, that is, it needs a varchar2 variable to receive the output of the function, which you have, and you need to pass a varchar22 variable, or a literal string with a specific flname (whatever that is).

John

Marked as Answer by Synerdgy · Sep 27 2020
Billy Verreynne

What John said. It is called the signature of the function. And the signature you coded, "EMAILDETAILSNAME()", does not match the signature it is defined with (with finame as a formal string parameter).

The signature can be variable. In other words you can assign a default value to a parameter, which means the caller can use it, or not. If not, the default applies.

The caller can also use parameter-by-position and parameter-by-name call notation. The latter should be used when the call signature contains 3 or more actual parameters.

And keep in mind that code needs to be readable. The call EMAILDETAILSNAME() is not readable when written in shouty uppercase, whereas EmailDetailsName() is. Use pascalcase and use camelcase as needed (e.g. former for code unit names and latter for variable and parameter names). And forget about writing reserved words in uppercase. That is just a plain silly standard. PL/SQL language is an implementation of the Ada language. Use proper Ada standards instead as per  Ada Quality and Style  Guide for Professional Programmers .

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 13 2017
Added on Jan 16 2017
#email, #function, #plsql
4 comments
698 views