12 February, 2014

login.sql does not require a login

Oracle's sqlplus can use a login.sql file to execute commands -- e.g. setup options.
This file is read and executed when you start sqlplus, even without having logged in to a database.

Here's a quick demo :

I start an sqlplus session without a login.sql

[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ ls -l login.sql
ls: login.sql: No such file or directory
[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 12 08:01:43 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show pagesize 
pagesize 14
SQL> show linesize
linesize 80
SQL> show sqlprompt
sqlprompt "SQL> "
SQL> 

Now, I create a login.sql and invoke sqlplus without logging in to the database.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ vi login.sql
[oracle@localhost ~]$ cat login.sql
set pagesize 60
set linesize 132
set sqlprompt 'HemantSQL>'
[oracle@localhost ~]$ 
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 12 08:05:24 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

HemantSQL>show pagesize
pagesize 60
HemantSQL>show linesize
linesize 132
HemantSQL>show user
USER is ""
HemantSQL>

Without having connected to a database (and created a database session), the login.sql was executed.

I can also have it dynamically use a variable --- e.g. the sqlprompt changing based on my login username.

HemantSQL>exit
[oracle@localhost ~]$ vi login.sql
[oracle@localhost ~]$ cat login.sql
set pagesize 60
set linesize 132
set sqlprompt '_USER>'
[oracle@localhost ~]$ 
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 12 08:08:12 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

>
>show user
USER is ""
>connect hemant/hemant
Connected.
HEMANT>show user
USER is "HEMANT"
HEMANT>connect hr/oracle
Connected.
HR>show user
USER is "HR"
HR>
HR>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ 

Notice how the sqlprompt was simply ">" when no user was logged in ? On the "HEMANT" and "HR" logins, the prompt did change.

.
.
.

No comments: