Hi. I have a JDBC client to Oracle, and I want to initialize two
session settings in one SQL string sent to the DBMS. I can do things
like sending two normal updates in one string like:

"BEGIN insert into mytable values(...); insert into myOthertable values(...); END;"

I am naively trying:

"BEGIN alter session set nls_date_format = 'MM/DD/YYYY'; set role xxxx_role identified by xxxx; END;".

This fails*. Is there working syntax for what I want to do?
Thanks in advance,

Joe Weinstein at BEA

* ORA-06550: line 1,column 7:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
begin declare exit for goto if loop mod null pragma raise return select update
while <an identifier> <a double-quoted delimited-identifier> <a bind variable>
close current delete fetch lock insert open rollback
savepoint set sql commit <a single-quoted SQL string>
The symbol "update was inserted before "ALTER" to continue.
ORA-06550: line 1, column 61:
PLS-00103: Encountered the symbol "ROLE" when expecting one of the following:
transaction

"Joe Weinstein" <joeNOSPAM (AT) bea (DOT) com> wrote

use EXECUTE IMMEDIATE to issue each statement as dynamic SQL -- PL/SQL
basically only support DML directly:

begin
execute immediate 'alter session set nls_date_format=''mm/dd/yy''';
execute immediate 'alter session set optimizer_mode = choose';
end;

;-{ mcs

On Fri, 20 Feb 2004 09:07:48 -0800, Joe Weinstein <joeNOSPAM (AT) bea (DOT) com>
wrote:

multiposting? You are wasting bandwith and my time.


--
Sybrand Bakker, Senior Oracle DBA