-
Notifications
You must be signed in to change notification settings - Fork 9
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
- exit the app
The help command displays help text for all other commands.
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.
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.
Start a statement with ! to execute it as a shell command.
For example
Oracle-18c oracle@system:hr> ! echo Hello Okcli
Hello Okcli
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
The describe command will show for a given table:
- each column, its datatype, if it's nullable
- primary-key constraints
- foreign-key constraints
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 can be run with the exec command.
For example
Oracle-11g hr@xe:HR> exec some_schema.my_procedure(arg1, 'arg2')
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
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
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
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 |
+-------------+
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 |
+------------------+
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 |
+---------------+------------------+------------+-------------+
Exit the CLI app with exit, quit or \q.