Oracle Database/Database Interfaces

SQL*Plus

SQL*Plus is a command interface provided with the DBMS. On Windows it can be launched either from:

• The start menu, Oracle folder, Run SQL Command Line shortcut.
• The path C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe.
• But the best is to use the environment variable and to connect to the DBMS at the same time, with a shell console. By default it can be done with:

sqlplus / as sysdba

Otherwise if you already have an account, the syntax is:

sqlplus MyAccount/MyPassword@localhost

vignette

The first step is to create a user (eg: root):

CREATE USER root IDENTIFIED BY MyPassword;

The second allow him to connect and to confer him the necessary privileges:

GRANT create session to root;
GRANT sysdba to root;

Web interface

A second interface is provided with the DBMS: the web interface. It can be accessed from:

• For SE 12c :
  • https://localhost:5500/em/shell
• For XE 11g :
  • The start menu, Oracle folder, Get Started shortcut.
  • The URL http://localhost:8080/apex/f?p=4950.
• For XE 10g :
  • The start menu, Oracle folder, Database control.
  • http://localhost:1158/em/console/logon/logon.

Then we must connect to the DBMS:

• User name: sys (sometimes sysman for system manager)
• Password: the one chosen during the installation.
• Connect as: SYSDBA.

The console appears after, allowing to modify the database configuration (service restart, architecture, performances, backups...).

By clicking on Application Express, we can optionally create a new connection user account, which will be used to connect to Oracle. Once logged, it can access to some the database manipulation tools, for example SQL Workshop\SQL Commands to enter some SQL code.

Attention: if you create a first account with the GUI, they will need the default SQL*Plus account for GRANT.

Oracle SQL Developer

Oracle SQL Developer is an IDE developed in Java. It's provided with SE, but for XE the rich client must be downloaded on http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html.

Once installed and launched, we have to set a connection to let it be authenticated on the Oracle databases. Just fill the account created on the web interface, to access to the data manipulation options.

For example by clicking on the top left on New (the plus icon or CTRL + N), we can open an SQL file to begin to execute some code.

DBCA

Database Configuration Assistant (DBCA) is a graphical interface available on Windows or the *nixes.

Hello world

Once one SQL console of those seen previously launched, it becomes possible to execute some PL/SQL (Procedural Language/Structured Query Language): the procedural language created by Oracle, and specific to its relational database.

set serveroutput on
BEGIN
  DBMS_OUTPUT.put_line ('Hello World!');
END;
/

The slash tells the program to stop the multiline instruction.