Toad World® Forums

Login Script/Threaded Queries

We use a login script in Toad to set some session and application variables. This has worked great for us to be able to look at the session browser and know whether the session is called from Toad and who the os user is when a shared schema is the one logged in.

We do use threaded queries and I’ve noticed that though I can see the correct data in the session browser, when I run a query that pulls SYS_CONTEXT(‘USERENV’, ‘CLIENT_IDENTIFIER’) in a separate window from the one I ran the login script in, I do not get the new client_identifier, but if I run this in the same window, then I correctly get this value.

Is this something we are doing wrong, or is is something in the way Toad handles the threaded queries?

This is the code in my login script.

BEGIN

DBMS_SESSION.set_identifier(SYS_CONTEXT(‘USERENV’, ‘OS_USER’));

DBMS_APPLICATION_INFO.set_client_info(SYS_CONTEXT(‘USERENV’, ‘OS_USER’));

DBMS_APPLICATION_INFO.set_module(‘TOAD’, ‘TOAD’);

END;


Phyllis Helton

Data Magician
Digital Products & Strategies, Cru | Data Sciences & Analytics
Office :phone: 407-515-4452

phyllis.helton@cru.org

Mine 5 c thoughts…

From Oracle doc:
SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.
For namespace and parameter, you can specify either a string or an expression that resolves to a string designating a namespace or an attribute. The context namespace must already have been created, and the associated parameter and its value must also have been set using the DBMS_SESSION.set_context procedure. The namespace must be a valid SQL identifier. The parameter name can be any string. It is not case sensitive, but it cannot exceed 30 bytes in length.

SYS_CONTEXT( namespace, parameter [, length] )
parameter:
A valid attribute that has been set using the DBMS_SESSION.set_context procedure.

Obviously, namespace or attribute is not assigned in this Toad release (or it’s associations is somehow malformed).

Phyllis, go into options and set your file under Options -> Startup -> File to execute on new connections. Specify your file there and it will get executed in threaded query sessions. I don’t know why we have both this and login.sql, but we do.

I don’t know why we have both this and login.sql, but we do.

I thik that poster has previously mentioned that code is in login script but doesn’t work, correct Phyllis?

Oh, and I forgot to mention - your setting for DBMS_APPLICATION_INFO.set_module won’t stick because the very next thing that Toad does after running the “file to execute on new connections” is DBMS_APPLICATION_INFO.set_module with ‘TOAD background query session’ as the module.

Damir, I’m suggesting she use “File to execute on new connections”, not “Login Script”.

Thanks John, I understand that small area of Toad more deeper … after some self testing as well…

:slight_smile:

Ah, that was it. I have the file there, but don’t have the box checked that says to execute on new threaded query sessions.

That’s fine that Toad changes the application info. As long as I can tell it is Toad that is great, my problem was that our homemade row-level security was not returning any records for me, making it very difficult to test. :slight_smile:

Thank you!

On Tue, May 24, 2016 at 8:50 AM, Damir Vadas bounce-damirvadas_250@toadworld.com wrote:

RE: Login Script/Threaded Queries

Reply by Damir Vadas
Thanks John, I understand that small area of Toad more deeper … after some self testing as well…

:slight_smile:

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag this post as spam/abuse.


Phyllis Helton

Data Magician
Digital Products & Strategies, Cru | Data Sciences & Analytics
Office :phone: 407-515-4452

phyllis.helton@cru.org