Oraora
Oraora is a command-line utility for interacting with Oracle database.
Features
- Command line history
- Input TAB-completion
- Password file support
- Metadata querying
- Context-aware SQL
- Readable colored output
- su/sudo as SYS
Installation
Oraora comes bundled as a Ruby gem. To install just run:
$ gem install oraora
If you don't have Ruby, check one-click installer for Windows or rvm for Linux.
Usage
Connecting to database
Start oraora passing connection string as argument, just like you would connect to SQL*Plus:
$ oraora user/password@DB
OS authentication is supported (pass /
).
Roles are supported (append as SYSDBA
/ as SYSOPER
).
Passfile
Oraora attempts to read file .orapass
in your home directory if it exists. It should contain connection strings in
user/password@DB
format. Then it's enough to provide user@DB
when connecting and oraora will automatically fill
the password.
$ oraora --log-level=debug user@DB
[DEBUG] Connecting: user/password@DB
Context
Use c
command to navigate through database like directory structure.
~ $ c some_table # Starting at home schema, navigate into table some_table
~.SOME_TABLE $ c col1 # Navigate into column col1
~.SOME_TABLE.COL1 $ c - # Navigate up
~.SOME_TABLE $ c -- # Navigate two levels up - to database level. You could also use 'cd .'
. $ c HR.EMPLOYEES # Navigate to schema HR, table EMPLOYEES
HR.EMPLOYEES $ c -/DEPARTMENTS # Navigate up, then to table DEPARTMENTS
HR.DEPARTMENTS $ c . # Navigate to root (database level)
/ $ c # Navigate to your home schema
Note: c
is aliased as cd
for unix addicts.
Listing and describing objects
Use d
command to describe object currently in context.
HR.EMPLOYEES $ d
Schema: HR
Name: EMPLOYEES
Partitioned: NO
Use l
command to list for object currently in context. In database schemas are listed. In schema - objects. In table,
view or materialized view - columns, etc.
HR.EMPLOYEES $ l
EMPLOYEE_ID PHONE_NUMBER COMMISSION_PCT
FIRST_NAME HIRE_DATE MANAGER_ID
LAST_NAME JOB_ID DEPARTMENT_ID
EMAIL SALARY
You can also provide context path as additional parameter for list and describe:
HR.EMPLOYEES $ l .SYS.DUAL
DUMMY
For list provide filter as last segment of the path:
~ $ l .HR.EMPLOYEES.*NAME
FIRST_NAME LAST_NAME
Note: l
is aliased as ls
. d
is aliased as desc
and describe
.
SQL
Any command starting with SQL keyword like SELECT
, CREATE
, ALTER
, etc. is treated as SQL and passed to
database for execution
~ $ SELECT * FROM dual;
D
-
X
[INFO] 1 row(s) selected
Context-aware SQL
Within specific context, you can omit some obvious keywords or identifiers at the beginning of SQL statements. For example, in context of a table following statements will work:
~.SOME_TABLE $ SELECT; # implicit 'SELECT * FROM SOME_TABLE'
~.SOME_TABLE $ WHERE col = 1; # implicit 'SELECT * FROM SOME_TABLE WHERE col = 1'
~.SOME_TABLE $ SET col = 2; # implicit 'UPDATE SOME_TABLE SET col = 2'
~.SOME_TABLE $ ADD x INTEGER; # implicit 'ALTER TABLE SOME_TABLE ADD x INTEGER'
Some other examples:
~ $ IDENTIFIED BY oraora; # implicit 'ALTER USER xxx IDENTIFIED BY oraora'
~.SOME_TABLE.COL $ WHERE x = 1; # implicit 'SELECT COL FROM SOME_TABLE WHERE x = 1'
~.SOME_TABLE.COL $ RENAME TO kol; # implicit 'ALTER TABLE SOME_TABLE RENAME COLUMN col TO kol'
Tab completion
Oraora has bash-style tab completion - hit <TAB>
to autocomplete current word with SQL keyword or context. If there are
multiple possible completions hit <TAB>
twice to see the list.
Also there are several quick templates defined. For example, type S*
, then hit <TAB>
to expand it into
SELECT * FROM
. Available templates are as follows:
s => SELECT
s* => SELECT * FROM
c* => SELECT COUNT(*) FROM
i => INSERT
u => UPDATE
d => DELETE
a => ALTER
c => CREATE
cr => CREATE OR REPLACE
Su / sudo
su
and sudo
allow to switch to SYS session temporarily or execute a single statement as SYS, similarly to their
unix counterparts. If you don't have SYS password for current connection in orafile, you will be prompted for it.
$ oraora test@DB
~ $ CREATE TABLE test (a INTEGER);
[ERROR] ORA-01031: insufficient privileges at 0
~ $ sudo GRANT CREATE TABLE TO test;
[INFO] 0 row(s) affected
~ $ CREATE TABLE test (a INTEGER);
[INFO] 0 row(s) affected
Limitations
This is an early alpha version. Things may crash and bugs are hiding out there.
PL/SQL blocks are not supported (yet).
Oraora does not implement SQL*Plus-specific commands. rem
, set
, show
, desc
, exec
, etc. are not
supported.