CODEWITHSUNDEEP
jdbcweblogicdatasource
fgysin
fgysin

Reputation: 11923

Weblogic JDBC Connections: multi-statement init SQL

When setting up a data source in WLS we can give it an init SQL statement, which is executed immediately after creation of every connection for this data source.

So far we are using..

SQL ALTER SESSION SET current_schema=user01

.. as we are logging in using an application user02 which has less rights than the owner user01, but we still want the data source to see the schema.

Now we'd like to add some session meta information to the DS connections, for debugging purposes on the DB side. The statements..

DBMS_APPLICATION_INFO.set_client_info('bar');
DBMS_APPLICATION_INFO.set_module('qux', 'garp');

.. allow you to set custom strings as identifiers, which will then show up in colums of V_$SESSION, thus giving the DBA some more information about a DB connection.

My problem
How is it possible to have multi-line init SQL statements? Note that the init SQL syntax expects an SQL command which is preceded by SQL and which does not end in a semicolon ;.

Edit:
What I tried so far is something along the lines of..

begin
execute immediate 'ALTER SESSION SET current_schema=uzms01';
DBMS_APPLICATION_INFO.set_client_info('bar');
DBMS_APPLICATION_INFO.set_module('qux', 'garp');
end;

.. but I keep getting errors. :(

Upvotes: 1

Views: 2493

Answers (1)

fgysin
fgysin

Reputation: 11923

Looks like there was a small syntax irregularity which I did not spot because SQL developer executes it without problems...

The following snippet works (note the parentheses after immediate):

SQL BEGIN
execute immediate('alter session set current_schema=user01');
DBMS_APPLICATION_INFO.set_client_info('my client');
DBMS_APPLICATION_INFO.set_module('my module', 'my action');
END;

Upvotes: 1

Related Questions