Re: How do I send two session-setting calls in one SQL string?

"Joe Weinstein" <joeNOSPAM_at_bea.com> wrote in message news:40363ec9$1_at_news.beasys.com...
| 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
|

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 Received on Sat Feb 21 2004 - 14:01:21 CET