Starting up with SQL*Plus

Even though you have access to Oracle SQL Developer, it makes sense knowing how to get around with SQL*Plus, a quite user-unfriendly tool that has the advantage of being available almost everywhere.

You should find it in the server/bin folder of your Oracle installation, and you run it calling sqlplus from the console.

You can save some time providing username and password at command line (usually regarded as a bad idea, for security considerations):

sqlplus hr/password

In this case I'm trying to connect to Oracle through the account of the hr user, assuming its password is the infamous self-descriptive word.

If you don't want to be pushed by sqlplus to provide you username/password at startup, you can call it passing the option /NOLOG - in this case you should call the sqlplus command CONNECT to actually connect to Oracle.

In a real working environment, providing username and password it is usually not enough to estabilish a connection: you should also say which database you actually want to connect to. That means you should also specify, after an '@', the requested service name. If you look for a file named tnsnames.ora (usually in the oracle server/network/admin folder) you should find a list of the available service names; and you will se how the name you pass is usually resolved to a machine name with a specific port on which estabilish the connection.

Once we are connected to our Oracle database, we can run a sql query simply writing it (remember the semicolon at the end):

SQL> select * from countries where region_id = 1;

If you are connected to the Oracle test hr user you should get this output:

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
BE Belgium                                           1
CH Switzerland                                       1
DE Germany                                           1
DK Denmark                                           1
FR France                                            1
IT Italy                                             1
NL Netherlands                                       1
UK United Kingdom                                    1

Running PL/SQL is not more difficult. We just write the code we want to execute, we just have to enter a slash ('/') on a new line at the end, to ask SQL*Plus to run the code:

SQL> begin
  2    dbms_output.put_line('Hello');
  3  end;
  4  /

Actually, we should remember to ask to SQL*Plus to let us see to output buffer used by the DBMS_OUTPUT package:

SET SERVEROUTPUT ON

Otherwise we won't read the hello message, but just a confirmation message from SQL*Plus:

PL/SQL procedure successfully completed.

A short PL/SQL could be execute by the SQL*Plus EXECUTE (or EXEC) command:

EXEC dbms_output.put_line('Hello')

I'm writing this post while reading Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein. I suggest you to get a copy of it, if you want to read more on this stuff.