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.

No comments:

Post a Comment