Toad World® Forums

RE: Setting session information

Yes – same way as SQL Plus. TOAD honors your login.sql or glogin.sql file
settings. So place a login.sql file in your sql path and set the toad option to
use it. Then you can have any settings you like such as this auto run for you.
We used to have a screen with dozens of check boxes – but this approach is
much more flexible and also has the advantage that it does not require screen
mods when oracle adds or retires commands.

Is there a way to only run the login.sql for specific connections though?

I think what you might be able to do is script it such that you query for the
instance name, then if it comes back as your FINANCIALS system, then it runs the
ALTER SESSION SET stuff – but it will run on every connection is how I
understand it.

That would require making that option connection specific – which then
opens up Pandora’s box about all the connections that one could wish were
connection oriented L

Well, you can script it at least in the SQL code.

Why not have an entry in the connections window grid that would allow the user
to select a sql file to run on login for that connection?

Idea Pond?

Chris

You can make your login.sql run a script only if it is connected to a particular
database, right? I can’t remember off the top of my head how to do it, you
can probably figure it out after a few minutes of googling….

If database = then

Start

a broader Idea would be to have an App/Action dropdown in that grid, to run an
App or Action upon Connection. That could include a script execution . I think
Bert suggested this to me privately awhile back. I think it’s in my stack of
stuff

You can make your login.sql run a script only if it is connected to a
particular database, right? I can’t remember off the top of my head how to
do it, you can probably figure it out after a few minutes of googling…

If database = then
Start

Interesting – I’ve been playing with this idea in the beta, but can’t get
any “ALTER SESSION” commands to stick in Toad from the login.

I’ve got the “execute login scripts” checked and edited/saved the login.sql
script from the Toad options to have a single line:

ALTER SESSION SET nls_date_format = ‘YYYY/MM/DD HH24:MI:SS’;

I then disconnected and reconnected, but when I look at V$NLS_PARAMETERS in
the Editor, my nls_date_format is still “MM/DD/YYY…”. I confirmed that
the login.sql is being properly executed on login to the same connection
from the same ORACLE_HOME on my XP SP3 laptop running the 10.1.0.5.0 Oracle
Client. Also, I tried with/without threads and separate Toad sessions, plus
I have the SB (as well as Editor) launching on connect.

My thought was to have something like “@login&&user.sql” in the login.sql,
and then a “loginRICH.sql”, “loginJOE.sql”, etc. But before I could explore
the security ramifications of that, I thought I’d just try to get it to
work. No such luck!

Rich – [TeamT]

Disclaimer: And since we’ve no place to go…

Hey Rich,

I just tried this myself and found the same thing you did. Then I tried Options
-> Startup -> File to Execute on New connections, and that worked.

One of the other devs will have to speak up to tell us why we have both –
I can’t remember. Maybe because one runs in the foreground and the other
the background.

-John

Rich – [TeamT]
Disclaimer: And since we’ve no place to go…

Are you lucky enough to be getting snow? I’m jealous.

Nate Schroeder
Saint Louis, Missouri, USA - cold but the ground is brown, not white.
This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled
to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and
all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its
subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of “Viruses” or other “Malware”.
Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying
this e-mail or any attachment.

The information contained in this email may be subject to the export control laws and regulations of the United States, potentially
including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of
Treasury, Office of Foreign Asset Controls (OFAC). As a recipient of this information you are obligated to comply with all
applicable U.S. export laws and regulations.

Yes – you said you’d keep that on back burner for this spring J

From a developer, the best option would be to modify the login script so it runs
per session.

The reason I say that is because if one wants a login script to be database
specific (connection specific) then one would want that login script to behave
the same way no matter what tool one was using. So it’d behave
consistently whether you were using Toad or SQL Plus (which I still use a lot
of).

My login script sets my prompt according to the database I log in to. In a brief
fashion:

DEFINE pname=’Undefined>’

COLUMN db_name NEW_VALUE pname;

SELECT CASE GLOBAL_NAME

WHEN ‘DEV’ THEN ‘DV>’

ELSE ‘UNKN>’ END as db_name

FROM GLOBAL_NAME;

– note that the above does not work pre 9i,

– case is slightly “flawed” and you have to

– spell out the source and comparison in the

– when statement

SET SQLPROMPT &pname;

The above is what I’m using to dynamically set a session value. I’m
sure you could use the same logic to set any session value.

Roger S.

They’ve promised/threatened a bit for tonight. J

Daniel B Madvig
Computer Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323
image001.jpeg

Are you lucky enough to be getting snow? I’m jealous.

Nate Schroeder
Saint Louis, Missouri, USA - cold but the ground is brown, not white.

Not yet, but we’re supposed to pick up a dusting (2") of powder
tonite/tomorrow. The western part of the Cheesehead State is forecasted for
around 4. We’re just getting started, me hearties!

Rich – [TeamT]

Disclaimer: An inch of ice? THAT’s enough for fishing, right?

Thanks everybody for the feedback on this.

I use SQL*Plus so rarely now that I’d forgotten about the login.sql &
glogin.sql, though they only meet my needs half-way - as suggested by others.
Mark’s idea would be ideal.

Ian

login.sql & glogin.sql … only meet my needs half-way

Out of curiosity, what particular functionality would you need that dynamically
setting the session information would not provide?

Roger S.

The ability to execute an App or Action upon connection is out in today’s beta
image001.jpeg

Hope this thread is still read by someone.

My use of the alter session would be to set _HASH_JOIN = TRUE for only a Toad session. Can’t use glogin.sql or login.sql as that is too broad for my use. Hash joins really speed up access to Oracle internal tables and views.

For instance - this join: “FROM GV$SESSION S, GV$SESS_IO si” in Toad takes 84 secs with NL join and 3secs with a hash join. But my application vendor demands that the _HASH_JOIN be set to FALSE.

The performance limits my use of Toad greatly.

It’s alive ::slight_smile:

OK - there are on the toad options screen the optimizer hints tab/section where you can specify that you want hash joins used on all DBA_* , ALL_* and USER_* views. Take a look at that first. You also can just open up an editor window and execute the alter session command - toad generally speaking uses just one session (there are exceptions, such as PL/SQL debugger requires two sessions, etc). Try that second.

Then post again if we’re still missing the boat …

Hmmm - I may have spoke too soon on first option/idea. I don’t think that’s going to offer what you want. But I think you could suggest on toad world idea pond that this screen be more user customizable and easier to set mass overrides. Then if we can get some votes for it maybe it would then work as you ened.

Still review it and consider option #2 too …