Forum Stats

  • 3,873,041 Users
  • 2,266,498 Discussions
  • 7,911,411 Comments

Discussions

Function call with multiple values return

Synerdgy
Synerdgy Member Posts: 8 Blue Ribbon
edited Feb 5, 2017 10:16AM in SQL & PL/SQL

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;

Tagged:

Best Answer

  • John Spencer
    John Spencer Member Posts: 8,567 Bronze Crown
    edited Jan 16, 2017 1:06PM 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

Answers

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Jan 16, 2017 11:04AM

    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
    Synerdgy Member Posts: 8 Blue Ribbon
    edited Jan 16, 2017 2:51PM

    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      => '[email protected]',

                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
    John Spencer Member Posts: 8,567 Bronze Crown
    edited Jan 16, 2017 1:06PM 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

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond
    edited Jan 16, 2017 11:55PM

    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 .

This discussion has been closed.