ALTER SESSION in TOracleQuery leads to "ORA-02248: invalid option for ALTER SESSION"

[nwgarside]

Hello!

I'm using DOA 4.0.7.1 with Delphi 2007 on a Windows 7 x64 laptop and version 11.2.0.1.0 of the 32-bit Oracle Instant Client.

I've added a TOracleQuery with the following SQL:

ALTER SESSION SET smtp_out_server = '<my_smtp_server>'

BEGIN
  utl_mail.send(
    sender     => '<sender>',
    recipients => '<recipient>',
    subject    => 'A test of UTL_MAIL in Oracle 10g',
    message    => 'This is a test...');
END;

When I execute the query I get the following error:

Project raised exception class EOracleError with message 'ORA-02248: invalid option for ALTER SESSION'.

However, if I separate the two command into two different TOracleQueries, it works!

Why is that, and how can the two commands be combined in a single TOracleQuery?

Thanks!

/Neil

 

[Marco Kalter]

You will have to create one PL/SQL Block. You can use the "execute immediate" command to execute non-PL/SQL commands like "alter session".

For example:

BEGIN
  execute immediate 'ALTER SESSION SET smtp_out_server = ''<my_smtp_server>''';
  utl_mail.send(
    sender     => '<sender>',
    recipients => '<recipient>',
    subject    => 'A test of UTL_MAIL in Oracle 10g',
    message    => 'This is a test...');
END;

 

[nwgarside]

Thanks Marco. That worked a treat!

Why does the ORA-02248 error occur in DOA? If I save the original code as a SQL script it can be run successfully in SQL*Plus.

Thanks again!

/Neil

 

[Marco Kalter]

The TOracleQuery component can only execute one SQL statement or PL/SQL Block at a time. It cannot run SQL*Plus scripts with multiple commands.

For large scripts that cannot be encapsulated in a single PL/SQL Block you can use the TOracleScript component instead.