Develop with Oracle SQL*Plus

You can use SQL*Plus to connect to your Oracle Database Exadata Express Cloud Service and develop on the database.

Topics:

Enter and Execute Commands

After you connect to your Oracle Database Exadata Express Cloud Service using SQL*Plus, you can execute commands from the command-line to develop the database.

To execute a command from SQL*Plus, type the command in the command-line, and press the Return key. The SQL*Plus allows you to enter three types of commands as:

SQL commands
PL/SQL blocks
SQL*Plus commands

To learn more about the types of commands that can be executed, see Entering and Executing Commands in SQL*Plus® User's Guide and Reference.

Manage Database Objects with SQL*Plus

Oracle SQL*Plus allows you to manage various database objects in your Oracle Database Exadata Express Cloud Service by entering commands in the command-line.

To execute basic commands for managing the database objects using SQL*Plus:

Using SQL*Plus, connect to your Exadata Express service. See Connect SQL*Plus.

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 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 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 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 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 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 SQL*Plus

Oracle SQL*Plus 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 SQL*Plus:

Using SQL*Plus, connect to your Exadata Express service. See Connect SQL*Plus.

To retrieve all the records from the table named countries, enter this command:

SQL> select * from countries;

Command results:

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
AR Argentina                                         2
AU Australia                                         3
BE Belgium                                           1
BR Brazil                                            2
CA Canada                                            2
CH Switzerland                                       1
CN China                                             3
DE Germany                                           1
DK Denmark                                           1
EG Egypt                                             4
FR France                                            1

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
IL Israel                                            4
IN India                                             3
IT Italy                                             1
JP Japan                                             3
KW Kuwait                                            4
ML Malaysia                                          3
MX Mexico                                            2
NG Nigeria                                           4
NL Netherlands                                       1
SG Singapore                                         3
UK United Kingdom                                    1

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
US United States of America                          2
ZM Zambia                                            4
ZW Zimbabwe                                          4

25 rows selected.

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 created.
```
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 countries table, enter this command:

SQL> DESC countries;

Command results:

Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 COUNTRY_ID                                NOT NULL CHAR(2)
 COUNTRY_NAME                                       VARCHAR2(40)
 REGION_ID                                          NUMBER

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 SQL*Plus to Data Load

Using Oracle SQL*Plus, you can load data from an on-premise database to your Oracle Database Exadata Express Cloud Service or vice-versa. Similarly, you can also use the SQL*Plus features to load data between your Exadata Express service and any other cloud service which supports connecting through SQL*Plus.

SQL*Plus supports loading bulk data or copying database objects from one database to another by various means. As your Exadata Express service allows you to connect from SQL*Plus, it also supports features to load data between your database on the service and any other database either on cloud or on-premise. The following options are available for you to load data to/from your Exadata Express service:

Running SQL scripts to create database objects & load data.

Follow these steps, to execute SQL scripts with Exadata Express service:

Using SQL*Plus, connect to your Exadata Express service. See Connect SQL*Plus.
To execute a script of SQL commands to load data into your Exadata Express service, execute the following commands at the command prompt:
```
START <filename> or @<filename>
```
SQL*Plus runs the commands in the file, and displays the status of the execution.

Note:

SQL*Plus assumes the file has a .sql extension by default.

For further information on using SQL*Plus for executing SQL scripts, see Running Scripts in SQL*Plus® User's Guide and Reference .

Using SQL *Loader Utility

A typical SQL*Loader session takes as input a control file, which controls the behavior of SQL*Loader, and one or more datafiles. The output of SQL*Loader is an Oracle database (where the data is loaded), a log file, a bad file, and potentially, a discard file. It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile. SQL Loader conventional path and direct path work remotely as long as there is a service for the target database that the user can provide in the connect string. It can be run from a remote machine where the data files are present, and therefore, there is no need to send files to the target machine using SFTP.

For further information, see SQL*Loader Command-Line Reference in Oracle Database Utilities.