Develop with Oracle SQLcl

Oracle SQLcl is a powerful java-based command line interface (CLI) for Oracle Database. You can use SQLcl to connect to your Oracle Database Exadata Express Cloud Service and develop on the database.

Topics:

Manage Database Objects with SQLcl

Oracle SQLcl allows you to create and manage various database objects in your Oracle Database Exadata Express Cloud Service.

To execute basic commands for managing the database objects using SQLcl:

Using SQLcl, connect to your Exadata Express service. See Connect SQLcl.

To create a table named emp, enter this command:

SQL> CREATE TABLE emp(
  2  empno NUMBER(5) PRIMARY KEY,
  3  ename VARCHAR2(15) NOT NULL,
  4  job VARCHAR2(10),
  5  mgr NUMBER(5),
  6  hiredate DATE DEFAULT(sysdate),
  7  deptno NUMBER(3) NOT NULL);

Command results:

Table EMP created.

To add a new column named email to the emp table, enter this command:
```
SQL> ALTER TABLE emp
  2  ADD email VARCHAR2(15);
```
Command results:
```
Table EMP altered.
```
To rename a column named email to email_address in the emp table, enter this command:
```
SQL> ALTER TABLE emp RENAME COLUMN email TO email_address;
```
Command results:
```
Table EMP altered.
```
To delete a column named email_address from the emp table, enter this command:
```
SQL> ALTER TABLE emp DROP COLUMN email_address;
```
Command results:
```
Table EMP altered.
```
To rename the emp table as temp, enter this command:
```
SQL> RENAME emp TO temp;
```
Command results:
```
Table renamed.
```
To drop the temp table, enter this command:
```
SQL> DROP TABLE temp;
```
Command results:
```
Table TEMP dropped.
```

To know more about the SQL commands used to manage the database objects, see Managing Tables in Database Administrator's Guide.

Query and Manipulate Data Using SQLcl

Oracle SQLcl allows you to query and manipulate data in your Oracle Database Exadata Express Cloud Service by entering commands in the command-line.

To execute basic commands for querying and manipulating data using SQLcl:

Using SQLcl, connect to your Exadata Express service. See Connect SQLcl.

To retrieve all the records of employees whose salary is greater than 12000, from the table named employees, enter this command:

SQL> select * from employees where salary > 12000;

The records for all the employees whose salary is greater than 12000 are displayed without any formatting.

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
        100 Steven               King
SKING                     515.123.4567         17-JUN-03 AD_PRES         24000
                                     90

        101 Neena                Kochhar
NKOCHHAR                  515.123.4568         21-SEP-05 AD_VP           17000
                      100            90

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------

        102 Lex                  De Haan
LDEHAAN                   515.123.4569         13-JAN-01 AD_VP           17000
                      100            90

        108 Nancy                Greenberg
NGREENBE                  515.124.4569         17-AUG-02 FI_MGR          12008

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
                      101           100

        145 John                 Russell
JRUSSEL                   011.44.1344.429268   01-OCT-04 SA_MAN          14000
            .4        100            80

        146 Karen                Partners

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
KPARTNER                  011.44.1344.467268   05-JAN-05 SA_MAN          13500
            .3        100            80

        201 Michael              Hartstein
MHARTSTE                  515.123.5555         17-FEB-04 MK_MAN          13000
                      100            20


EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------

        205 Shelley              Higgins
SHIGGINS                  515.123.8080         07-JUN-02 AC_MGR          12008
                      101           110


8 rows selected.

As you can see that the above query’s result is not formatted and hence difficult to associate the values with their corresponding columns. Using SQLcl, you can display the records from a select query in a formatted way. The SET SQLFORMAT command helps you format your query results.

SQL> set sqlformat csv
SQL> select * from employees where salary > 12000;

This time, the records from the select query are displayed in csv format:

"EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT"
,"MANAGER_ID","DEPARTMENT_ID"
100,"Steven","King","SKING","515.123.4567",17-JUN-03,"AD_PRES",24000,,,90
101,"Neena","Kochhar","NKOCHHAR","515.123.4568",21-SEP-05,"AD_VP",17000,,100,90
102,"Lex","De Haan","LDEHAAN","515.123.4569",13-JAN-01,"AD_VP",17000,,100,90
108,"Nancy","Greenberg","NGREENBE","515.124.4569",17-AUG-02,"FI_MGR",12008,,101,100
145,"John","Russell","JRUSSEL","011.44.1344.429268",01-OCT-04,"SA_MAN",14000,0.4,100,80
146,"Karen","Partners","KPARTNER","011.44.1344.467268",05-JAN-05,"SA_MAN",13500,0.3,100,80
201,"Michael","Hartstein","MHARTSTE","515.123.5555",17-FEB-04,"MK_MAN",13000,,100,20
205,"Shelley","Higgins","SHIGGINS","515.123.8080",07-JUN-02,"AC_MGR",12008,,101,110

8 rows selected.

To display the output of a query in ansiconsole format, enter this command:

SQL> set sqlformat ansiconsole
SQL> select * from employees where salary > 12000;

The records from the select query are displayed in ansiconsole format. The ansiconsole formats the data to best fit the display for each page of results. SQL*Plus style report formatting like COL will be ignored in this mode.

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  EMAIL     PHONE_NUMBER        HIRE_DATE  JOB_ID   SALARY  COMMISSION_PCT
  MANAGER_ID  DEPARTMENT_ID
100          Steven      King       SKING     515.123.4567        17-JUN-03  AD_PRES  24000
              90
101          Neena       Kochhar    NKOCHHAR  515.123.4568        21-SEP-05  AD_VP    17000
  100         90
102          Lex         De Haan    LDEHAAN   515.123.4569        13-JAN-01  AD_VP    17000
  100         90
108          Nancy       Greenberg  NGREENBE  515.124.4569        17-AUG-02  FI_MGR   12008
  101         100
145          John        Russell    JRUSSEL   011.44.1344.429268  01-OCT-04  SA_MAN   14000   0.4
  100         80
146          Karen       Partners   KPARTNER  011.44.1344.467268  05-JAN-05  SA_MAN   13500   0.3
  100         80
201          Michael     Hartstein  MHARTSTE  515.123.5555        17-FEB-04  MK_MAN   13000
  100         20
205          Shelley     Higgins    SHIGGINS  515.123.8080        07-JUN-02  AC_MGR   12008
  101         110


8 rows selected.

Note:

The set sqlformat <setting> command allows you to display the output in many formats like html, json, insert, xml, loader, or fixed. Executing set sqlformat default, will return the formatting to default. To know more about set sqlformat <setting> command, use the HELP <command>. See Use the help command in SQLcl.

To insert a new record for a country named Hongkong in the countries table, enter this command:
```
SQL> INSERT INTO countries(country_id,country_name,region_id) VALUES('HK','HONGKONG',3);
```
Command results:
```
1 row inserted.
```
To modify the value of country_code for the record inserted above, enter this command:
```
SQL> UPDATE countries SET country_id = 'HN' WHERE country_id = 'HK';
```
Command results:
```
1 row updated.
```
To delete the record created in the step above, from countries table, enter this command:
```
SQL> DELETE FROM countries WHERE country_id = 'HN';
```
Command results:
```
1 row deleted.
```

To see the list of columns in the employees table, enter this command:

SQL> describe employees;

Command results:

Name           Null?    Type
-------------- -------- ------------
EMPLOYEE_ID    NOT NULL NUMBER(6)
FIRST_NAME              VARCHAR2(20)
LAST_NAME      NOT NULL VARCHAR2(25)
EMAIL          NOT NULL VARCHAR2(25)
PHONE_NUMBER            VARCHAR2(20)
HIRE_DATE      NOT NULL DATE
JOB_ID         NOT NULL VARCHAR2(10)
SALARY                  NUMBER(8,2)
COMMISSION_PCT          NUMBER(2,2)
MANAGER_ID              NUMBER(6)
DEPARTMENT_ID           NUMBER(4)

The DESC command is a SQL*plus command that SQLcl supports. Additionally, SQLcl supports Info[rmation] <schema.object> which provide more details about the objects requested as, column comments, indexes, references, and so on.

SQL> information employees

Command results:

TABLE: EMPLOYEES
         LAST ANALYZED:2016-01-12 22:00:38.0
         ROWS         :107
         SAMPLE SIZE  :107
         INMEMORY     :DISABLED
         COMMENTS     :employees table. Contains 107 rows. References with departments,
                       jobs, job_history tables. Contains a self reference.

Columns
NAME             DATA TYPE           NULL  DEFAULT    COMMENTS
*EMPLOYEE_ID     NUMBER(6,0)         No                   Primary key of employees table.
 FIRST_NAME      VARCHAR2(20 BYTE)   Yes                  First name of the employee. A not null column.
 LAST_NAME       VARCHAR2(25 BYTE)   No                   Last name of the employee. A not null column.
 EMAIL           VARCHAR2(25 BYTE)   No                   Email id of the employee
 PHONE_NUMBER    VARCHAR2(20 BYTE)   Yes                  Phone number of the employee; includes country cod
e and area code
 HIRE_DATE       DATE                No                   Date when the employee started on this job. A not
null column.
 JOB_ID          VARCHAR2(10 BYTE)   No                   Current job of the employee; foreign key to job_id
 column of the
                                                                    jobs table. A not null column.
 SALARY          NUMBER(8,2)         Yes                  Monthly salary of the employee. Must be greater
                                                                    than zero (enforced by constraint emp_sa
lary_min)
 COMMISSION_PCT  NUMBER(2,2)         Yes                  Commission percentage of the employee; Only employ
ees in sales
                                                                    department elgible for commission percen
tage
 MANAGER_ID      NUMBER(6,0)         Yes                  Manager id of the employee; has same domain as man
ager_id in
                                                                    departments table. Foreign key to employ
ee_id column of employees table.
                                                                    (useful for reflexive joins and CONNECT
BY query)
 DEPARTMENT_ID   NUMBER(4,0)         Yes                  Department id where employee works; foreign key to
 department_id
                                                                    column of the departments table

Indexes
INDEX_NAME            UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS                COLUMN_EXPRESSION
HR.EMP_JOB_IX         NONUNIQUE   VALID                   JOB_ID
HR.EMP_NAME_IX        NONUNIQUE   VALID                   LAST_NAME, FIRST_NAME
HR.EMP_EMAIL_UK       UNIQUE      VALID                   EMAIL
HR.EMP_EMP_ID_PK      UNIQUE      VALID                   EMPLOYEE_ID
HR.EMP_MANAGER_IX     NONUNIQUE   VALID                   MANAGER_ID
HR.EMP_DEPARTMENT_IX  NONUNIQUE   VALID                   DEPARTMENT_ID


References
TABLE_NAME   CONSTRAINT_NAME  DELETE_RULE  STATUS   DEFERRABLE      VALIDATED  GENERATED
DEPARTMENTS  DEPT_MGR_FK      NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME
EMPLOYEES    EMP_MANAGER_FK   NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME
JOB_HISTORY  JHIST_EMP_FK     NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME

Note:

Use the SQLcl Help <command> to know about other SQLcl commands . See Use the help command in SQLcl.

To know more about the SQL commands used to manage the data, see Querying and Manipulating Data in Database 2 Day Developer’s Guide.

Use the `help` command in SQLcl

Oracle SQLcl provides help command to get assistance in using any command supported by SQLcl.

Typing help from the SQLcl command line, after connecting to your Oracle Database Exadata Express Cloud Service, lists all the commands supported by SQLcl.

For example,

SQL> help
For help on a topic type help<topic>
List of Help topics available:
/
@
@@
ACCEPT
ALIAS
APEX
APPEND
ARCHIVE LOG
BREAK
BRIDGE
BTITLE
CD
CHANGE
CLEAR
COLUMN
COMPUTE
CONNECT
COPY
CTAS
DDL
DEFINE
DEL
DESCRIBE
DISCONNECT
EDIT
EXECUTE
EXIT
FORMAT
GET
HISTORY
HOST
INFORMATION
INPUT
LIST
LOAD
NET
NOHISTORY
OERR
PASSWORD
PAUSE
PRINT
PROMPT
QUIT
REMARK
REPEAT
RESERVED WORDS
REST
RUN
SAVE
SCRIPT
SET
SHOW
SHUTDOWN
SODA
SPOOL
SSHTUNNEL
START
STARTUP
STORE
TIMING
TNSPING
TTITLE
UNDEFINE
VARIABLE
WHENEVER
XQUERY

Note:

The new commands supported by Oracle SQLcl over SQL*plus are highlighted.

By executing help <command> will print the help documentation for that command.

For example,

SQL> help information
INFORMATION
--------

This command is like describe but with more details about the objects requested.

INFO[RMATION] {[schema.]object[@connect_identifier]}
INFO+ will show column statistics

Use SQLcl to Data Load

Oracle SQLcl, allows you to load data to your Oracle Database Exadata Express Cloud Service using multiple insert statements or from a csv file.

Pre-requisites

In order to load data into your Exadata Express service from an external data file, ensure that:

The first row of the file is a header row, and the columns in the header row match the columns defined on the table.
The columns are delimited by a comma and optionally enclosed in double quotes.
The lines are terminated with standard line terminators for windows, unix or mac.
The file is encoded UTF8.

Procedure

Follow these steps, to perform data loading using SQLcl:

Using SQLcl, connect to your Exadata Express service. See Connect SQLcl.
At the SQLcl command prompt, type load <[schema].table_name> <.csv file name>.

All the records listed in the csv file are loaded into the table.

Note:

The load is processed with 50 rows per batch.
If AUTOCOMMIT is set in SQLCL, a commit is done every 10 batches.
The load is terminated if more than 50 errors are found.