Ways of connection - sqlcl and the connect command

Where it looks for connections is a bit long winded:
Check -optl if this does not quite match what you are seeing.

Variables we support:
TNS_ADMIN (for finding tnsnames.ora)
LOCAL (windows=TWO_TASK)
TWO_TASK (non windows i.e. my database connection information e.g. tnsnames.ora entry name or EZCONNECT)
ORACLE_HOME (for finding tnsnames.ora)
SQLPATH (for finding login.sql)
PATH for finding .dll
LD_LIBRARY_PATH for finding .so
ORACLE_SID used in bequeath connection by thick oci driver.


tnsnames.ora - looks for tnsnames.* for example so might pick up an old setting in a .SAV file.

connect -oci username/password
nothing given:if currently connected try same utl different connection.
nothing given:not currently connected and -oci try bequeath

try TWO_TASK/LOCAL (if these are not null go to  'something given')
try localhost:1521/orcl
try 127.0.0.1:1521:$ORACLE_SID

if something given
try LDAPCON environment variable http://totierne.blogspot.in/2015/02/introducing-ldapcon-helper-feature-for.html
try tnsnames.ora
try NET xml store of connections http://totierne.blogspot.in/2015/04/net-command-persistently-store-network.html
try passing on to the driver untouched (could be (DESCRIPTION= or hostname resolution for example)
try hostname resolution ie @hostname or @hostname:1521/hostname (in case hostname=service name) (try with or without domain eg oracle.com - or domain got from looking up resolved host)
(if -oci try thick then try thin for each possibility (there are one or two that currently do not try thick))

On no connection give error output for each attempt. (optl shows what it is trying whether it connects or not).

If password is missing prompt for it, if connection fails and there was a prompt - prompt for everything up to 3 times.
At the username prompt: user@dbname is not currently supported.

SQLCL command line options

SQLCL options:

new:
-oci - try looking for 12.1.0.2 oci 'thick' c based library
-optl - turn on logging
-sshkey -sshhost I have not used these could be related to: http://barrymcgillin.blogspot.in/2015/05/sqlcl-cloud-connections-via-secure.html seems related.

From sqlplus
-h -help - show help page
-l -login - do not reprompts for user/password on failure
-R[estrict] 1 -R 2 -R 3 - restrict sqlcl for example R 3 - do not use @
-s[ilent] - suppress console output
-v - version
/nolog - do not log in
-nologin - do not read login.sql
@file.sql - run this file and come back if it does  not end in exit
username/password@connectionidentifier as sysdba|sysbackup|sysdg|syskm|sysasm|sysoper - login ([as role] is optional)

-optl shows you logging info:
where it looks for tnsnames.* and what urls it is trying
$ ./sql -optl notauser/notapassword@notanid

SQLcl: Release 4.1.0 Release Candidate on Thu May 07 14:34:11 2015

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


May 07, 2015 2:34:11 PM oracle.dbtools.raptor.utils.TNSHelper checkForTns
INFO: Checking for tnsnames.* in :/home/notauser
May 07, 2015 2:34:11 PM oracle.dbtools.raptor.utils.TNSHelper checkForTns
INFO: Checking for tnsnames.* in :/etc
May 07, 2015 2:34:11 PM oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL
INFO: Attempting to connect using URL= "jdbc:oracle:thin:@notanid"
May 07, 2015 2:34:11 PM oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL
INFO: Attempting to connect using URL= "jdbc:oracle:thin:@notanid:1521/notanid"
  USER          = notauser
  URL           = jdbc:oracle:thin:@notanid
  Error Message = IO Error: Unknown host specified
  USER          = notauser
  URL           = jdbc:oracle:thin:@notanid:1521/notanid
  Error Message = IO Error: Unknown host specified

Warning: You are no longer connected to ORACLE.

now try the thick driver: -oci (throws an error because I have  not got the .so  library in the path)
./sql -oci -optl notauser/notapassword@notanid

SQLcl: Release 4.1.0 Release Candidate on Thu May 07 14:36:10 2015

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


May 07, 2015 2:36:10 PM oracle.dbtools.raptor.utils.TNSHelper checkForTns
INFO: Checking for tnsnames.* in :/home/notauser
May 07, 2015 2:36:10 PM oracle.dbtools.raptor.utils.TNSHelper checkForTns
INFO: Checking for tnsnames.* in :/etc
May 07, 2015 2:36:10 PM oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL
INFO: Attempting to connect using URL= "jdbc:oracle:oci8:@notanid"
May 07, 2015 2:36:11 PM oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL
INFO: Attempting to connect using URL= "jdbc:oracle:thin:@notanid"
May 07, 2015 2:36:11 PM oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL
INFO: Attempting to connect using URL= "jdbc:oracle:thin:@notanid:1521/notanid"
  USER          = notauser
  URL           = jdbc:oracle:oci8:@notanid
  Error Message = no ocijdbc12 in java.library.path
  USER          = notauser
  URL           = jdbc:oracle:thin:@notanid
  Error Message = IO Error: Unknown host specified
  USER          = notauser
  URL           = jdbc:oracle:thin:@notanid:1521/notanid
  Error Message = IO Error: Unknown host specified

Warning: You are no longer connected to ORACLE.

Next post will be about how it attempts to connect.

Pause prompt works per page:

bash-4.1$ bash sql scott/[the password]@[THE DATABASE}

SQLcl: Release 4.1.0 Release Candidate on Tue May 05 16:21:03 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.x.x.x.x - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> set pause "Press any key to continue..."

SQL> set pause on

SQL> show pause
PAUSE is ON and set to "Press any key to continue..."

SQL> show pagesize
pagesize 24


SQL> select * from emp;

Press any key to continue...
     EMPNO ENAME      JOB              MGR HIREDATE
---------- ---------- --------- ---------- -------------------------------
       SAL       COMM     DEPTNO
---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80                      
       800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81                      
      1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81                      
      1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81                      
      2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81                      
      1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81                      
      2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81                      

Press any key to continue...
     EMPNO ENAME      JOB              MGR HIREDATE
---------- ---------- --------- ---------- -------------------------------
       SAL       COMM     DEPTNO
---------- ---------- ----------
      2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87                      
      3000                    20

      7839 KING       PRESIDENT            17-NOV-81                      
      5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81                      
      1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87                      
      1100                    20

      7900 JAMES      CLERK           7698 03-DEC-81                      
       950                    30

      7902 FORD       ANALYST         7566 03-DEC-81                      
      3000                    20

Press any key to continue...
     EMPNO ENAME      JOB              MGR HIREDATE
---------- ---------- --------- ---------- -------------------------------
       SAL       COMM     DEPTNO
---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82                      
      1300                    10


 14 rows selected


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.x.x.x.x - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Command Line: sqlcl startup shutdown of pdb and non pdb databases (Should even work from a Mac - pure Java)

Tested against release 16th April 2015 drop.

Startup and shutdown of pluggable and non pluggable datbases.

bash-4.1$ ./sql sys@_non_pluggable__

SQLcl: Release 4.1.0 Release Candidate on

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


Connected to an Idle instance, startup command available.
SQL> startup
Started up
Database Mounted
Database Open

Total System Global Area   12345 bytes    
Fixed Size                    54321 bytes    
Variable Size               12345 bytes    
Database Buffers            54321 bytes    
Redo Buffers                 12345 bytes    
SQL> shutdown
Instance stopped
Database closed.
Database dismounted.
Database shutdown.
SQL> connect sys@__pluggable__ as sysdba
Connected

SQL> shutdown
Pluggable database closed
SQL> startup
Pluggable database opened
SQL>

One point to remember - configure your listener for remote (or 127.0.0.1 loopback) startup/shutdown if you are not using thick/bequeath Oracle client 'C' code, so the listener can find the database when the database is down.

listener.ora entry:

SID_LIST_LISTENER =
 (SID_LIST=
  (SID_DESC=
   (GLOBAL_DBNAME=THEDATABASE)
   (ORACLE_HOME=THE_ORACLE_HOME)
   (SID_NAME=THEDATABASE)
  )
 )

Should even work from a Mac - pure Java..

NET Command - persistently store network details with tab completion

Example using tab completion: (need to be in sql for tab complete to work e.g. ./sql /nolog)

SQL> net unique=localhost:1521/mysid;
SQL> net list
unique
SQL> net list unique
unique
------
localhost:1521/mysid
SQL> connect username/password@uni__TAB_FOR_COMPLETION__

SQL> connect username/password@unique

tab completion works for net shortcuts and tnsnames.ora details.

Behind the scenes net is very similar to the alias command.

**************************************************************
If anyone has useful alias command uses or wanted extensions
to alias command I would be interested to know. 
(Jeff Smith is the Product Manager if you want to go through official channels).
**************************************************************

Turloch

The Net help page:

>help net
NET
------

Command line only - not for sqldeveloper.

NET is a command which allows you to save a network details and assign it a shortcut command.
"net" - print a list of net short cuts
"net list " - list the contents of the short cut
"net name=localhost:1521/XE;" simple net command
"net drop name" - delete the short cut called name

net is single line terminated by newline
net ..=.. is terminated by ';'
net on its own gives a list of existing short cuts.

controlled by
set net on|off|readonly - default ON
   readonly means only do try to enter a net shortcut on successful connect command
set noverwrite on|off|warn - default WARN
   net overwrite: warn prints a warning if an override would otherwise happen.

Data Pump Wizard enhancement

If the master table is kept (now the default) SQL Developer 4.1 (EA1 and above) can find the log file name.

Steps - launch export/import as usual then:

1/Go to dbaview -> Data Pump -> export (or import) jobs

2/Pick a job that is 'Not Running'

3/LOG FILES output shows the Log file from the server.

Issues:
Log file viewer contains a few extra lines at the top.
State (schema/job id) is not cleared if you change connection.
Requires UTL_FILE

Introducing LDAPCON Helper feature for LDAP (for thin), in Jan30 release:

Either
1/By environmental variable, or
2/sdsql /nolog
then
set LDAPCON jdbc:oracle:thin:@ldap://ldapmachine.us.oracle.com:389/#ENTRY#,cn=OracleContext,dc=ie,dc=lcom
(the above is one line from set to lcom)

For example by bash environmental variable:
>set LDAPCON=jdbc:oracle:thin:@ldap://ldapmachine.us.oracle.com:389/#ENTRY#,cn=OracleContext,dc=ie,dc=lcom
(the above is one line from set to lcom)

>sdsql "sys/__the_password__@THEADDRESS" as sysdba
connects via: jdbc:oracle:thin:@ldap://ldapmachine.us.oracle.com:389/THEADDRESS,cn=OracleContext,dc=ie,dc=lcom

ldap.ora for sqlplus will contain:
Machine:ldapmachine.us.oracle.com port:389
and
context:cn=OracleContext,dc=ie,dc=lcom

I have not automated the removal of this information from ldap.ora

LDAP for thick:
As sqlplus configure via ldap.ora and sqlnet.ora

Note the file to kick off the command line product has been through a few iterations, seems to be currently sql (or sql.bat)

Alias command - a little like shell alias

SQL> help alias
ALIAS
------

Not a sqlplus command.

alias
alias name=select :one from dual;
SQL> alias fred=select :one from dual;
SQL> fred 1
Command=fred
:ONE
--------------------------------
1

SQL> alias db= begin dbms_output.put_line('hi'); end;
2  /
SQL> db
Command=db
anonymous block completed
hi

alias is single line terminated by newline
alias ..=.. is terminated by ';'
alias ..=begin or ..=declare is terminated by newline/
alias on its own gives a list of existing aliases.

SQL>REM loaded on demand and saved on exit (sdsql only). WARNING - two sessions - last to exit will overwrite.
SQL>REM show current aliases
SQL> alias
locks
tables
SQL> alias mysql=select 'mysql' from dual;
SQL> mysql
Command=mysql
'MYSQL'
-------
mysql

SQL> set serveroutput on
SQL> alias myplsql=begin
  2  dbms_output.put_line('some output');
  3  end;
  4  /
SQL> myplsql
Command=myplsql
anonymous block completed
some output

SQL>REM plsql (given above - note abusing / a little - call it 'executing a plsql alias assignment command'), q string or bind

SQL>REM q string:(Complex option 1: for use by the 1% of use cases with multiple commands)

SQL> alias mysql=q'[select 'mysql' from

  2  dual]';

SQL> mysql

Command=mysql

'MYSQL'

-------

mysql

SQL>REM bind: (Complex option 2: for use by the 0.1% of use cases that require preprocessing of alias text or other 'maximum' flexibility)

SQL> variable bind clob;

SQL> begin :bind:='select ''frombind'' from dual'; end;

  2  /

anonymous block completed

SQL> alias mysql=:bind;

SQL> mysql

Command=mysql

'FROMBIND'

----------

frombind

SQL>REM using bind: 

SQL>  alias mysql2=select :vv from dual;

SQL> mysql2 'hello vv'

Command=mysql2

:VV

--------------------------------

hello vv

SQL> REM You can use existing or prompted for substitution variables - arguments are all (new) binds

SQL> REM obvious usecase capture sql from a live job, run unchanged as an alias using the argument to bind facility. 

SQL> REM running from a clob bind variable opens up lots of possibilities 1/ download a script from a database and run it 2/ run without accessing the file system 3/dynamically change (preprocess) the bind value (equivalent of #ifdef, or changing strings) before creating the alias.

EDIT1: Warning around infrequent higher flexibility use cases.

EDIT2: The bind arguments are assumed to be varchar2 (as opposed to numeric or date for example)
- automatic type conversion done by the sql layer
- use TO_DATE(:thevarchar,'the_date_mask') if you prefer explicit conversion.