okcli

Man Okcli for Oracle Database.

An Oracle-DB command line client with auto-completion and syntax highlighting that emulates the functionality of sqlplus.

index

install
user guide
usage
faq
ipython

install

Install okcli from pypi with pip.

> sudo pip install okcli

or without sudo credentials

> pip install --user okcli

documentation

For documentation and config options see the user guide or type help from within the app.

demo

usage

Usage: okcli [OPTIONS] [SQLPLUS]

  An Oracle-DB terminal client with auto-completion and syntax highlighting.

  Examples:
    - okcli -u my_user -h my_host.com -D schema
    - okcli user/password@tns_name 
    - okcli user/password@tns_name -D schema 
    - okcli user/password@tns_name -e "query"
    - okcli user@tns_name -@ query_file.sql

Options:
  -h, --host TEXT         Host address of the database.
  -P, --port INTEGER      Port number to use for connection.
  -u, --user TEXT         User name to connect to the database.
  -p, --password TEXT     Password to connect to the database.
  -v, --version           Output okcli's version.
  -D, --database TEXT     Database to use.
  -R, --prompt TEXT       Prompt format (Default: "\t \u@\h:\d> ").
  -l, --logfile FILENAME  Log every query and its results to a file.
  --okclirc PATH         Location of okclirc file.
  --auto-vertical-output  Automatically switch to vertical output mode if the
                          result is wider than the terminal width.
  -t, --table             Display batch output in table format.
  --csv                   Display batch output in CSV format.
  --warn / --no-warn      Warn before running a destructive query.
  --login-path TEXT       Read this path from the login file.
  -e, --execute TEXT      Execute command and quit.
  -@, --filename TEXT     Execute commands in a file.
  --help                  Show this message and exit.

user-guide

display help text
app config
set colour scheme
execute host shell commands
execute commands from file
describe
stored procedures
favourite commands
escape to an editor to finish writing an SQL statement
change output format
list all schemas in database
list all tables in a schema
spool (append) query output to a file
ipython
exit the app

help

The help command displays help text for all other commands.

config

The file ~/.okclirc is created upon installation with config for okcli.

Things like colour-scheme, prompt-format, log-file location etc. can be updated there.

colours

The syntax_style parameter in the config-file sets the syntax colour scheme, select from the following:

# Syntax coloring style. Possible values (many support the "-dark" suffix):
# manni, igor, xcode, vim, autumn, vs, rrt, native, perldoc, borland, tango, emacs,
# friendly, monokai, paraiso, colorful, murphy, bw, pastie, paraiso, trac, default,
# fruity.

Other style options (eg. the status bar) can also be set in the config-file.

shell

Start a statement with ! to execute it as a shell command.

For example

Oracle-18c oracle@system:hr> ! echo Hello Okcli
Hello Okcli

exec-file

Execute sql statements from a file by passing it as an argument with -@.

For example:

 > cat date_query.sql 
select sysdate from dual
 > okcli hr@xe:HR -@date_query.sql
SYSDATE
2019-03-12 16:42:34

describe

The describe command will show for a given table or view:

each column name, its datatype, if it's nullable
primary-key constraints (if it's a table)
foreign-key constraints (if it's a table)
the SQL query used to create the view (if it's a view)

For example:

Oracle-11g hr@xe:HR> desc HR.EMPLOYEES
+----------------+-----------+-------------+----------+
| COLUMN_NAME    | DATA_TYPE | DATA_LENGTH | NULLABLE |
+----------------+-----------+-------------+----------+
| EMPLOYEE_ID    | NUMBER    | 22          | N        |
| FIRST_NAME     | VARCHAR2  | 20          | Y        |
| LAST_NAME      | VARCHAR2  | 25          | N        |
| EMAIL          | VARCHAR2  | 25          | N        |
| PHONE_NUMBER   | VARCHAR2  | 20          | Y        |
| HIRE_DATE      | DATE      | 7           | N        |
| JOB_ID         | VARCHAR2  | 10          | N        |
| SALARY         | NUMBER    | 22          | Y        |
| COMMISSION_PCT | NUMBER    | 22          | Y        |
| MANAGER_ID     | NUMBER    | 22          | Y        |
| DEPARTMENT_ID  | NUMBER    | 22          | Y        |
+----------------+-----------+-------------+----------+
Time: 0.098s

+---------------------+
| PRIMARY_KEY_COLUMNS |
+---------------------+
| EMPLOYEE_ID         |
+---------------------+
Time: 0.370s

+---------------+---------------------------+
| COLUMN_NAME   | FOREIGN_KEY_CONSTRAINT    |
+---------------+---------------------------+
| DEPARTMENT_ID | DEPARTMENTS.DEPARTMENT_ID |
| JOB_ID        | JOBS.JOB_ID               |
| MANAGER_ID    | EMPLOYEES.EMPLOYEE_ID     |
+---------------+---------------------------+
Time: 2.228s

stored-procedures

Stored-procedures can be run with the exec command.

For example

Oracle-11g hr@xe:HR> exec some_schema.my_procedure(arg1, 'arg2')

favourite-commands

The \fs [name] command will save the current statement with a name.

The \f [name] command will load the statement with that name or list all the saved statements if no name is given.

The \fd [name] command will delete the saved statement.

For example

Oracle-11g hr@xe:HR> \fs depts select  * from HR.DEPARTMENTS where MANAGER_ID > 200                                                                           
Saved.                                                                                                                                                        
Time: 0.003s                                                                                                                                                  
Oracle-11g hr@xe:HR> \f depts                                                                                                                                 
> select  * from HR.DEPARTMENTS where MANAGER_ID > 200                                                                                                        
+---------------+------------------+------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_NAME  | MANAGER_ID | LOCATION_ID |
+---------------+------------------+------------+-------------+
| 20            | Marketing        | 201        | 1800        |
| 40            | Human Resources  | 203        | 2400        |
| 70            | Public Relations | 204        | 2700        |
| 110           | Accounting       | 205        | 1700        |
+---------------+------------------+------------+-------------+
4 row s in set
Time: 0.002s
Oracle-11g hr@xe:HR> \f
+-------+------------------------------------------------------+
| Name  | Query                                                |
+-------+------------------------------------------------------+
| depts | select  * from HR.DEPARTMENTS where MANAGER_ID > 200 |
+-------+------------------------------------------------------+
Time: 0.001s

No favorite query:
Time: 0.000s
Oracle-11g hr@xe:HR> \fs depts_2 select  * from HR.DEPARTMENTS where MANAGER_ID < 200
Saved.
Time: 0.001s
Oracle-11g hr@xe:HR> \f
+---------+------------------------------------------------------+
| Name    | Query                                                |
+---------+------------------------------------------------------+
| depts   | select  * from HR.DEPARTMENTS where MANAGER_ID > 200 |
| depts_2 | select  * from HR.DEPARTMENTS where MANAGER_ID < 200 |
+---------+------------------------------------------------------+
Time: 0.001s

edit

When writing a statement you can escape to your favourite editor (set by $EDITOR) by adding ed to the start of the query.

When you save and exit the file it will take you back to the CLI with the statement that you finished editing in the file.

For example:

Oracle-11g hr@xe:HR> ed select * from

format

The format command sets the format of the query-output (if there is any).

The supported output formats are:

        jira                                                                                                                                                  
        latex                                                                                                                                                 
        github                                                                                                                                                
        latex_booktabs                                                                                                                                        
        vertical                                                                                                                                              
        simple                                                                                                                                                
        plain                                                                                                                                                 
        psql                                                                                                                                                  
        pipe                                                                                                                                                  
        moinmoin                                                                                                                                              
        orgtbl                                                                                                                                                
        textile                                                                                                                                               
        mediawiki                                                                                                                                             
        html                                                                                                                                                  
        grid                                                                                                                                                  
        double                                                                                                                                                
        tsv                                                                                                                                                   
        ascii
        csv
        fancy_grid
        rst

For example:

Oracle-11g hr@xe:HR> format  fancy_grid
Changed table format to fancy_grid
Time: 0.000s
Oracle-11g hr@xe:HR> select * from hr.DEPARTMENTS where MANAGER_ID >200
╒═════════════════╤═══════════════════╤══════════════╤═══════════════╕
│   DEPARTMENT_ID │ DEPARTMENT_NAME   │   MANAGER_ID │   LOCATION_ID │
╞═════════════════╪═══════════════════╪══════════════╪═══════════════╡
│              20 │ Marketing         │          201 │          1800 │
├─────────────────┼───────────────────┼──────────────┼───────────────┤
│              40 │ Human Resources   │          203 │          2400 │
├─────────────────┼───────────────────┼──────────────┼───────────────┤
│              70 │ Public Relations  │          204 │          2700 │
├─────────────────┼───────────────────┼──────────────┼───────────────┤
│             110 │ Accounting        │          205 │          1700 │
╘═════════════════╧═══════════════════╧══════════════╧═══════════════╛
4 row s in set
Time: 0.003s
Oracle-11g hr@xe:HR> format csv
Changed table format to csv
Time: 0.000s
Oracle-11g hr@xe:HR> select * from hr.DEPARTMENTS where MANAGER_ID >200
DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID
20,Marketing,201,1800
40,Human Resources,203,2400
70,Public Relations,204,2700
110,Accounting,205,1700

4 row s in set
Time: 0.002s

list

The list command shows all the schemas available.

For example

Oracle-11g hr@xe:HR> list
+-------------+
| OWNER       |
+-------------+
| MDSYS       |
| CTXSYS      |
| HR          |
| SYSTEM      |
| APEX_040000 |
| XDB         |
| SYS         |
+-------------+

show

The show command shows all the tables in a schema.

For example

Oracle-11g hr@xe:HR> show HR
+------------------+
| TABLE_NAME       |
+------------------+
| LOCATIONS        |
| EMPLOYEES        |
| EMP_DETAILS_VIEW |
| REGIONS          |
| JOBS             |
| COUNTRIES        |
| JOB_HISTORY      |
| DEPARTMENTS      |
+------------------+

spool

The spool command will append the output of subsequent statements to a file.

nospool will stop appending the output to the file.

once spools the output for only the next command.

For example:

Oracle-11g hr@xe:HR> spool output.txt
Time: 0.001s
Oracle-11g hr@xe:HR> select * from hr.DEPARTMENTS where MANAGER_ID > 200
+---------------+------------------+------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_NAME  | MANAGER_ID | LOCATION_ID |
+---------------+------------------+------------+-------------+
| 20            | Marketing        | 201        | 1800        |
| 40            | Human Resources  | 203        | 2400        |
| 70            | Public Relations | 204        | 2700        |
| 110           | Accounting       | 205        | 1700        |
+---------------+------------------+------------+-------------+
4 row s in set
Time: 0.003s
Oracle-11g hr@xe:HR> exit
root@b809269946dd:/# cat output.txt 
Oracle-11g hr@xe:HR> select * from hr.DEPARTMENTS where MANAGER_ID > 200
+---------------+------------------+------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_NAME  | MANAGER_ID | LOCATION_ID |
+---------------+------------------+------------+-------------+
| 20            | Marketing        | 201        | 1800        |
| 40            | Human Resources  | 203        | 2400        |
| 70            | Public Relations | 204        | 2700        |
| 110           | Accounting       | 205        | 1700        |
+---------------+------------------+------------+-------------+

ipython

okcli has support for ipython (and hence Jupiterhub notebooks), giving full support for eg. auto-complete on queries from within ipython.

To drop into an okcli shell from an ipython session, load the okcli.magic module. On exiting the okcli shell you drop back into the ipython shell with the last query results, as shown below.

The database connection is cached so subsequent okcli calls from the ipython session will drop back into the okcli shell already logged in.

root@6df4c32479df:/# ipython
Python 2.7.15rc1 (default, Nov 12 2018, 14:31:15)
Type "copyright", "credits" or "license" for more information.

IPython 5.8.0 -- An enhanced Interactive Python.
?         -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help      -> Python's own help system.
object?   -> Details about 'object', use 'object??' for extra details.

In [1]: %load_ext okcli.magic

In [2]: okcli system/oracle@xe
Connected to: xe
Oracle-11g system@xe:SYSTEM> select  * from hr.COUNTRIES where REGION_ID=1
+------------+----------------+-----------+
| COUNTRY_ID | COUNTRY_NAME   | REGION_ID |
+------------+----------------+-----------+
| BE         | Belgium        | 1         |
| CH         | Switzerland    | 1         |
| DE         | Germany        | 1         |
| DK         | Denmark        | 1         |
| FR         | France         | 1         |
| IT         | Italy          | 1         |
| NL         | Netherlands    | 1         |
| UK         | United Kingdom | 1         |
+------------+----------------+-----------+
8 row s in set
Time: 0.002s
Oracle-11g system@xe:SYSTEM> exit
0 rows affected.
Out[2]:
[(u'BE', u'Belgium', 1),
 (u'CH', u'Switzerland', 1),
 (u'DE', u'Germany', 1),
 (u'DK', u'Denmark', 1),
 (u'FR', u'France', 1),
 (u'IT', u'Italy', 1),
 (u'NL', u'Netherlands', 1),
 (u'UK', u'United Kingdom', 1)]

In [3]: res = _

exit

Exit the CLI app with exit, quit or \q.

faq

DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory". See https://oracle.github.io/odpi/doc/installation.html#linux for help

If you see this error message make sure that the $ORACLE_HOME/lib is on $LD_LIBRARY_PATH. This is needed by cx-oracle to make the database connection. As a sanity check ls $ORACLE_HOME/lib should list the oracle libraries.

Update the library-path with:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

windows support

In principle okcli should work on Windows but it has only been tested on Linux. If you're interested in testing on Windows please raise an issue.

thanks

Thanks to mycli. Most of the features (e.g. syntax highlighting, auto-complete) were implemented by the mycli core team for MySQL.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

README.md

README.md

okcli

index

install

documentation

demo

usage

user-guide

help

config

colours

shell

exec-file

describe

stored-procedures

favourite-commands

edit

format

list

show

spool

ipython

exit

faq

DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory". See https://oracle.github.io/odpi/doc/installation.html#linux for help

windows support

thanks

Files

README.md

Latest commit

History

README.md

File metadata and controls

okcli

index

install

documentation

demo

usage

user-guide

help

config

colours

shell

exec-file

describe

stored-procedures

favourite-commands

edit

format

list

show

spool

ipython

exit

faq

DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory". See https://oracle.github.io/odpi/doc/installation.html#linux for help

windows support

thanks