Re: doing two session-setting ops in one SQL submission...

"Joe Weinstein" <joeNOSPAM_at_bea.com> wrote in message news:40350f2e$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 with a string:
>
> "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
>

You can try execute immediate:

SQL> begin
  2 execute immediate 'alter session set nls_date_format = ''dd/mm/yy''';   3 execute immediate 'set role none';
  4 end;
  5 /

PL/SQL procedure successfully completed.

Anurag Received on Sat Feb 21 2004 - 11:30:46 CST