execute immediate oracle string concatenate issue

Question

I am having issues concatenating a string to be called be execute immediate. Anybody have any idea what is wrong with the following statement below.

EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''||SMTPServer||''';

select smtpserver into SMTPServer from mytable;

if(SMTPServer is not null)
then
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''||SMTPServer||''';
  --*****above does not work, below does work****
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''10.1.1.1''';

Answer 1

From the standpoint of building the string you want, you're missing a couple of single quotes. You'd need 3 single quotes before and four single quotes after the SMTPServer local variable

'ALTER SESSION SET smtp_out_server = ''' || smtpServer || ''''

Personally, I'd find it easier to do something like this where you declare a template (using the q quoting syntax since it has embedded single quotes), call replace to replace the templated values, and then call EXECUTE IMMEDIATE. It's a bit more code but it generally makes life easier.

l_sql_stmt := q'{ALTER SESSION SET smtp_out_server = '##server##'}';
l_sql_stmt := replace( l_sql_stmt, '##server##', smtpServer );
EXECUTE IMMEDIATE l_sql_stmt;

Are you sure that the second statement really works, though? smtp_out_server isn't supposed to be modifiable without a database restart. I've seen folks say that they've been able to modify it successfully with an ALTER SYSTEM at runtime but haven't seen anyone try to do it with an ALTER SESSION. I'd hesitate to build code that depends on behavior that goes against the documentation. If you need to control the SMTP server you're using at runtime, I'd strongly suggest using the utl_smtp package rather than utl_mail.

Answer 2

It's not really good providing the statement for EXECUTE IMMEDIATE replacing the parameters, since for the optmizer it's a completely different statement every single time so it prepares another execution plan for each. You should use binding in your EXECUTE IMMEDIATE clause.

Example:

DECLARE
  SMTPServer mytable.smtpserver%type;

  l_set_smtpserver_stmt VARCHAR2(600) := q'#ALTER SESSION SET smtp_out_server = ':p_smtp_server'#'

BEGIN
  SELECT smtpserver INTO SMTPServer FROM mytable;

  IF SMTPServer IS NOT NULL THEN
  EXECUTE IMMEDIATE l_set_smtpserver_stmt USING SMTPServer;
  END IF;


  EXCEPTION
    -- handle no_data_found exception since you're using select .. into statement
    WHEN NO_DATA_FOUND THEN
        -- handle exception
    WHEN OTHERS THEN
        -- handle exception
END;
/