Oracle Database/Database Interfaces

From Wikibooks, open books for an open world
Jump to: navigation, search

SQL*Plus[edit]

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 to confer him the necessary privileges:

GRANT sysdba to root;

Web interface[edit]

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

Then we must connect to the DBMS:

  • User name: sys (sometimes sysman)
  • 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[edit]

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.

Sql developer main window.png

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.

Hello world[edit]

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.

BEGIN
  DBMS_OUTPUT.put_line ('Hello World!');
END;