Search My Oracle Blog

Custom Search

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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016