Login.sql and AfterConnect.sql

[Scott Lloyd]

Hi Marco,

Great job on PL/SQL Developer 12! Lots of nice enhancements, that have got me exploring all the other features I ignored, such as the Command Window. I like it enough that I want to try using this as a replacement for my trusted SQL*Plus.

In putting the Command Window into production, I've struggled with the SQL Prompt setting. Since I manage multiple databases, it is very important to me to identify the User and DB with each prompt. I discovered that that my old prompt logic did not work:

SET TERMOUT OFF
COLUMN RTN_CHAR NEW_VALUE RTN_CHR
SELECT ''||CHR(10)||'' RTN_CHAR FROM DUAL;
COLUMN SQL_PRMPT NEW_VALUE SQL_PRMPT
SELECT 'SQL> ' SQL_PRMPT FROM DUAL;
COLUMN CONNECT_ID NEW_VALUE CONNECT_ID
SELECT UPPER('_CONNECT_IDENTIFIER') CONNECT_ID FROM DUAL;
set sqlprompt "_USER@&&CONNECT_ID &&RTN_CHR&&SQL_PRMPT"
SET TERMOUT ON

I was hoping to see

ADMIN@MYSCHEMA
SQL>

Instead, the variables were treated like literals and the resulting prompt was

_USER@&&CONNECT_ID &&RTN_CHR&&SQL_PRMPT>

Fortunately, I discovered this PSD syntax in another thread:

set sqlprompt [user]@[db]

But when I put it into AfterConnect.sql, it still did not work. I even tried adding various useful startup queries like

SELECT COUNT(*) FROM DAILY_ERROR_LOG;

I noticed that when I ran the Command Window, a little notification appeared at the bottom indicating that "login.sql" had executed. I use login.sql in my Oracle SQL*Plus configuration, and I couldn't understand how PSD was finding it, or how I could change it.

After an hour or so of debugging and reviewing documentation, I realized that Command Window uses it's own login.sql file.

So, my question:

What is the difference between login.sql and AfterConnect.sql, and how are they to be used? Also, what other variables are available? Can Command Window handle breaks and computes?

A document on this would be nice.

 

[Marco Kalter]

When searching the Login.sql file the precedence is as follows:

1. The current directory (by default the installation directory)
2. The command file directory from the preferences
3. The directory set by the SQLPATH environment variable
4. The directory set by the ORACLE_PATH environment variable

The login.sql file is executed when a Command Window opens. It can contain SQL commands, PL/SQL Blocks, but also Command Window commands such as SET PROMPT.

The AfterConnect.sql file is executed for all sessions of all window types. It can only contain SQL commands and PL/SQL Blocks.

 

[Jeff81]

I've never gotten this to work.
My command file directory is empty in preferences.
I have SQLPATH set in my environment variable.

My login.sql scripts works just fine for sqlplus on my computer.

 

[johnsok]

This information should be in the online documentation user guide.