Search code examples
oracleplsqlconcatenationexecuteutl-mail

execute immediate oracle string concatenate issue

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''';
Solution

  • 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.