Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Calling a Stored Function from JDBC

The code we should write to call a stored procedure or function via JDBC is slightly different from the one we write to perform a SQL SELECT. A CallableStatement is used instead of a Statement plus a ResultSet to set the parameter, execute the call, and extract the result.

To check it, I have firstly created a stored function in my Oracle 12 instance, on the HR schema. It takes a SQL CHAR in input, and returns the same string with 'suffix' appended:
create or replace FUNCTION foo (val CHAR)
RETURN CHAR AS
BEGIN
    RETURN val || 'suffix';
END;
I ensured it works as I expected running this query:
SELECT foo('hello') FROM DUAL;
The I have written a couple of tests to see how to get the same result in Java. Not just one, because we can use either the JDBC escape and the PL/SQL block syntax to achieve the same result, being the first
{? = call foo(?)
and the latter
begin ? := foo(?); end;
The core of both tests is in these few lines:
cs = conn.prepareCall(call);  // 1
cs.registerOutParameter(1, Types.CHAR);  // 2
cs.setString(2, "aa");
cs.execute();
String result = cs.getString(1);  // 3
1. Assuming conn is a good java.sql connection to the HR user on my Oracle database, and call is either the JDBC escape or the PL/SQL block string showed above, the prepareCall() should return a good CallableStatement.
2. The callable statement has to know the type of the output parameter is a character string. Then we set the other parameter, with the string that we want to pass as input.
3. After executing the callable statement, we get the first (and only) result as a string.

The actual code, that you could find on GitHub, class GettingStartedTest methods callFoo(), testFunctionCallJdbcEscape(), and testFunctionCallPlSqlBlock(), is a bit more verbose because I have to provide all the standard boilerplate, initializing, testing, cleaning up.

Reference: Oracle Database JDBC Developer's Guide 12c Release 2 (12.2) 2.8 Stored Procedure Calls in JDBC Programs

Go to the full post

Selecting on Oracle via JDBC

Once we decided how to establish a connection to the database, performing a SELECT on it is pretty simple. Here I'm going to use OracleDataSource as connection provider, using pure JDBC doesn't add any complexity.

The only problem in this Java code is that is slightly boring. So boring that there are a few wrapper aiming at keeping the advantages of JDBC easing its usage. See for instance the Spring JdbcTemplate.

Anyway, firstly we open a connection, then a statement, on which we execute a query (a select, in this case) that return a resultset. We normally do something on the resultset, and then we can close what we have opened, in reversed order, resultset, statement, connection. The code is made even more boring by the need of try-catching the SQLExceptions that could be thrown. Last straw in this boredom parade, is that before closing the JDBC objects we have to ensure they have been created, checking them for not being null.

So, our typical plain JDBC code could be seen in three steps:

Initialization

// ...
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
    conn = ods.getConnection();  // 1
    stmt = conn.createStatement();
    rs = stmt.executeQuery("SELECT first_name FROM employees");  // 2

    // ...
1. I'm using the OracleDataSourceConnector I described in the previous post. Nothing fancy, anyway, I simply get a connection to a Oracle database for the well known hr example user.
2. Having extracted a statement from the connection, I execute a query, that is plain SQL SELECT. The resulting table, here consisting of just one column, is returned in a ResultSet.

Operation

Let's do something with the fetched data. Here I copy the first names to a list of string, then I sort them and I ensure the first and last are what I expect. Usually this part of the job has more sense.
List<String> results = new ArrayList<String>();
while (rs.next()) {
    results.add(rs.getString(1));
}

assertEquals(107, results.size());
Collections.sort(results);
assertEquals("Adam", results.get(0));
assertEquals("Winston", results.get(106));
The employees table on HR should have 107 rows, first names should range from Adam to Winston, however, your result may vary.

Cleanup

Finally (actually, the code is normally performed in the finally block following the try one where the initialization and operation happened - meaning that we would always perform this step, whatever happens there), being done with the data, we can close result set, statement, and connection.
assertNotNull(rs);
rs.close();

assertNotNull(stmt);
stmt.close();

assertNotNull(conn);
conn.close();
Notice that we have to strictly follow the inverted order seen in initialization.
Notice also that each close() method could throw and exception so, again, these calls have to be in a try-catch block.

See full code on GitHub, class GettingStartedTest, method testSelect().

Reference: Oracle Database JDBC Developer's Guide 12c Release 2 (12.2) 2.4 Sample: Connecting, Querying, and Processing the Results

Go to the full post

Connecting to Oracle via JDBC

There are a couple of ways to connect to a recent Oracle Database, going through the standard DriverManager class or the specific OracleDataSource one. Let's see both of them. And, when we are there, let's add also some consideration on connecting to MySql via DriverManager.

It's a bit of an overkill, but I have written a little abstract class that is going to be the root of a hierarchy of classes for providing access to database through JDBC:
public abstract class Connector {
    public abstract Connection getConnection() throws SQLException;
 
    public String getDatabaseVersion(Connection conn) throws SQLException {
        return conn.getMetaData().getDatabaseProductVersion();
    }
}
The point of it is that each different concrete class has its own way to get a database connection but all of them would use it in the same way. So the getConnection() method is abstract where the actual method performing JDBC operation, like getDatabaseVersion(), would act in the same way.

Using OracleDataSource

The OracleDataSourceConnector extends Connector, and its raison d'être is keeping an instance of a OracleDataSource object as its private data member. Initialized in the constructor, is used by the the getConnection() method to return a database connection.
public class OracleDataSourceConnector extends Connector {
    private OracleDataSource ods;

    public OracleDataSourceConnector(String url, String user, String password) throws SQLException {
        ods = new OracleDataSource();
        ods.setURL(url);
        ods.setUser(user);
        ods.setPassword(password);
    }

    @Override
    public Connection getConnection() throws SQLException {
        return ods.getConnection();
    }
}
The good thing about OracleDataSource is that it has its own pool of connections that is managed implicitly. On the flip side, it is not standard JDBC. That means extra work if we want to adapt our code to use a different database.

I have written a tester to check the functionality, OracleDataSourceConnectorTest. See it on GitHub for full reference. There are only a few things that I want to stress here.
public class OracleDataSourceConnectorTest {
    private static final String URL = "jdbc:oracle:thin:@localhost:1521/orclpdb";  // 1
    private static final String USER = "hr";
    private static final String PASSWORD = "hr";

    private static OracleDataSourceConnector ods;
    private static Connection conn;

    @BeforeClass
    public static void setUp() {  // 2
        try {
            ods = new OracleDataSourceConnector(URL, USER, PASSWORD);
            conn = ods.getConnection();
        } catch (SQLException e) {
            fail(e.getMessage());
        }
    }

    // ...
}
1. I'm using the thin Oracle JDBC driver, the other choice is the OCI one. See the Oracle documentation if you wonder which one to use. Short answer is, usually thin is the one you want to peek. My database is local, on the standard port, and the service is named orclpdb. Your setup may vary.
2. I setup the connector and a connection through this static method called only once before the tests in the class are called. The sense is that I don't want to repeat expensive operations without a reason. So, when nothing is against it, I would reuse connector and connection in more tests.

And here is a test that requires its own connector, because I want to perform a disruptive negative test:
@Test
public void testBadUser() {
 OracleDataSourceConnector connector = null;
 try {
  connector = new OracleDataSourceConnector(URL, "Unknown", PASSWORD);  // 1
 } catch (SQLException e) {
  fail(e.getMessage());
 }
 
 try {
  connector.getConnection();  // 2
  fail("No connection expected for unknown user");
 } catch (SQLException e) {
  String expectedState = "72000";
  assertEquals(expectedState, e.getSQLState());
  
  int expectedCode = 1017;
  assertEquals(expectedCode, e.getErrorCode());
 }
}
1. I pass a bas user name to the connector. No failure is expected here, since no connection is actually done.
2. I expect the failure to happen here. Oracle should react with a ORA-01017 error code, that is included in the SQL state 72000, SQL execute phase errors.

The other test ensures that I can actually get to the database:
@Test
public void testGetDatabaseNameVersion() {
 try {
  String expected = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production";
  String actual = ods.getDatabaseVersion(conn);
  assertEquals(expected, actual);
 } catch (SQLException e) {
  fail(e.getMessage());
 }
}
Using DriverManager

If I am not interested in the connection pooling service offered by OracleDataSource, and I prefer to keep as generic as I can, I could use these other solution. In the old days, it required to perform an explicit registration of the JDBC driver:
Class.forName(klass)
Where klass is the actual class name, like "oracle.jdbc.driver.OracleDriver" or "com.mysql.jdbc.Driver".

Now this is done implicitly by the DriverManager, that leads to a very slim connector:
public class PlainConnector extends Connector {
 private String url;
 private String user;
 private String password;

 public PlainConnector(String url, String user, String password) {
  this.url = url;
  this.user = user;
  this.password = password;
 }

 @Override
 public Connection getConnection() throws SQLException {
  return DriverManager.getConnection(url, user, password);
 }
}
The constructor just store a copy of the data for connecting to the database, the getConnection() uses them going through DriverManager.

I have written another test case to see that actually the plain connector works as the OracleDataSource one. An then another one to see what I have to change to access with the same class a different database, here MySql.

This test case is accessing a MySql database that I have installed locally and on which I have added a user named "hr", to keep it close to the Oracle one. You can see how minimal are the required changes.

I had to change the URL:
private static final String URL = "jdbc:mysql://localhost:3306/hr?useSSL=false";
Notice the parameter useSLL set to false, to remove the MySql warning "Establishing SSL connection without server's identity verification is not recommended." In this test we can live without id verification.

In case of bad user, I expect now 28000 as SQL state and 1045, access denied, as error code.

And, the database version now should be something like "5.7.19-log"

Ah, right. Remember to put in your application build path the jar for oracle JDBC (currently named ojdbc8.jar) and for MySql (mysql-connector-java-5.1.43-bin.jar) and to have the databases up and running, if you want your tests to succeed.

Reference: Oracle Database JDBC Developer's Guide 12c Release 2

Full Java code on GitHub.

Go to the full post

Installing and configuring Oracle 12c

It was a while since the last time I have installed an instance of the Oracle database for local use. Nothing much has changed in the process, with the noticeable exception of the introduction of pluggable databases. Here I jot down a few remarks I made during the setup of the version 12 release 2.

1. In the page Typical Installation we have to specify the name for our pluggable database. Remember your choice!
2. The Enterprise Manager Database Express is available on https://localhost:5500/em, access it through the SYS user (with the password specified during setup)
3. Add in the tnsnames.ora (in the network\admin directory under the database home) the network configuration settings to access the pluggable database we have created in (1). Something like:
PDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )
4. Use sqlplus to connect to the pluggable database with SYS as sysdba:
sqlplus sys/Oracle_1@pdborcl as sysdba;
5. As usual, unlock the hr user to access the test tables:
alter user hr identified by hr account unlock;
So far so good. All standard stuff. A problem raises if you plan to stop the Oracle service, that should be named OracleServiceORCL. Since it is always memory hungry, sometimes I just want it stay out of my RAM. The first time I did it, I had the surprise of getting a nasty error when I tried to connect to the database after restarting. Basically, the issue was that the pluggable database was mounted but not started.

This is the recipe I followed to solve it:

1. From the shell, enter sqlplus without logging.
sqlplus /nolog
2. In sqlplus, connect as sysdba.
connect / as sysdba
3. Brutally shutdown the database.
shutdown abort
4. Restart it.
startup mount
5. Recover the database, then open it.
recover database 
alter database open;
6. Open all the pluggable database, and then ask them to remember their status, so that we don't have to repeat the maneuver each time.
alter pluggable database all open;
alter pluggable database pdb_name save state;

Go to the full post

Spring and JdbcTemplate

Let's say that we want to write a Spring application that requires some simple database activity. We decide to use an embedded database, H2 and, instead of choosing a higher level persistency framework, we decide to go for the JDBC support, mediating it with JdbcTemplate, a Spring class aimed at simplifying its use.

I create a Spring project using the STS wizard (File >> New >> Spring Starter Project).
Since I want to use JDBC and the H2 database support, I select these two options from the dependencies page:
From database schema to POJOs

In the source/main/resources folder, I put a file named schema.sql, this is enough for Spring, that knows it has to get it and run it at startup, so there I put the SQL commands to create my tables. I create two tables, spitter and spittle, connected by the id of the first, that is a foreign key in the second.

The tables defined in the database are reflected in a couple of plain old Java classes, Spitter.java and Spittle.java, having as fields what in the tables are the columns.

There is one thing that I think is interesting. The SQL spittle table has, among the other fields, a datetime, postedTime. We need to find a counterpart for it in the Java world. Since we are not interested here in time zones and the such, just in a plain timestamp, I decided to use Java 8 java.time.Instant.

That's it. If the SQL stuff is simple, and I have just a couple of tables, I could give it for done. To see that, I have created a basic version for the SpringBootApplication that implements the CommandLineRunner interface:
public class UsingJdbcApplication implements CommandLineRunner {
    private static final Logger log = LoggerFactory.getLogger(UsingJdbcApplication.class);

    @Autowired
    private JdbcTemplate template;  // 1

    public static void main(String[] args) {
        SpringApplication.run(UsingJdbcApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        Long counter = template.queryForObject("SELECT count(id) FROM spitter", Long.class);  // 2
        log.info("Rows on spitter table: " + counter);

        log.info("Pushing a spitter in");
        Object[] admin = new Object[] { "admin", "password", "Admin", "admin@example.dd" };
        template.update("INSERT INTO spitter(username, password, fullname, email) VALUES(?, ?, ?, ?)", admin);  // 3

        template.query("SELECT * FROM spitter WHERE username = ?", new Object[] { "admin" },  // 4
                (rs, rowNum) -> new Spitter(rs.getLong("id"), rs.getString("username"), rs.getString("password"),
                        rs.getString("fullname"), rs.getString("email"), rs.getBoolean("updateByEmail")))
                .forEach(spitter -> log.info("Spitter: " + spitter.toString()));
    }
}
1. Sort of magic. We ask to Spring to wire this JdbcTemplate object. Since the H2 jar is in the classpath, it understands it has to use it as database. We don't provide any settings, and it uses a whole bunch of defaults. Usually not a good idea for production code, but very handy for prototyping.
2. Let's run a simple query that returns a single value. This line shows that actually Spring has initialized the database using the script named schema.sql that it found in the main resources folder. And how JdbcTemplate makes our life so much simpler. Notice that the queryForObject() method has a first parameter that is the SQL statement we want to run, and the second one that is the type for the expected return value.
3. A parametrized update(). Its first parameter is a parametrized JDBC SQL insert statement, the second one a vector of objects containing the values that we want to usein the query.
4. Something a bit more complicated. The query() method execute a query in a way similar to the update() seen above but, as third parameter, it accepts a lambda function that maps each row in the resulting resultset to a new Spitter object. We tipically have no use for the rowNum parameter, just access any resultset field we need.
This query() call returns a list of Spitter. On that list we apply a forEach() so that each element in the list is logged. Actually, here we are expecting just one Spitter, however this is a common pattern that we should learn to recognize and use.

Running the Spring Boot App, I can see in the console window the expected log.

Repositories and configuration

Usually we don't want to scatter SQL statements all over our Java code. So we create two interfaces, one for Spittle and one for Spitter, that act as repository for them, and then we implement them for our JDBC provider.
The two classes are quite similar. Let's see some parts of JdbcSpittleRepository, that is a tad more complicated.
// ...
private static final String SELECT = "select sp.id, s.id as spitterId, s.username, s.password, s.fullname, s.email, s.updateByEmail, sp.message, sp.postedTime from Spittle sp, Spitter s where sp.spitter = s.id";
private static final String SELECT_RECENT = SELECT + " order by sp.postedTime desc limit ?";

public List<Spittle> findRecent(int count) {
    return template.query(SELECT_RECENT, new SpittleRowMapper(), count);  // 1
}

// ...
private long insertSpittle(Spittle spittle) {
    SimpleJdbcInsert insert = new SimpleJdbcInsert(template).withTableName("Spittle");  // 2
    insert.setGeneratedKeyName("id");
    Map<String, Object> args = new HashMap<String, Object>();
    args.put("spitter", spittle.getSpitter().getId());
    args.put("message", spittle.getMessage());
    args.put("postedTime", Timestamp.from(spittle.getPostedTime()));
    long spittleId = insert.executeAndReturnKey(args).longValue();
    return spittleId;
}

// ...
private static final class SpittleRowMapper implements RowMapper<Spittle> {  // 3
    public Spittle mapRow(ResultSet rs, int rowNum) throws SQLException {
        long id = rs.getLong("id");
        String message = rs.getString("message");
        Instant postedTime = rs.getTimestamp("postedTime").toInstant();
        long spitterId = rs.getLong("spitterId");
        String username = rs.getString("username");
        String password = rs.getString("password");
        String fullName = rs.getString("fullname");
        String email = rs.getString("email");
        boolean updateByEmail = rs.getBoolean("updateByEmail");
        Spitter spitter = new Spitter(spitterId, username, password, fullName, email, updateByEmail);
        return new Spittle(id, spitter, message, postedTime);
    }
}
1. Almost an innocent JdbcTemplate wrapper to a JDBC query, if we don't pay much attention to the second parameter, that would convert the ResultSet coming from the SQL select in a list of Spittles. See the SpittleRowMapper below for details.
2. Here we see how SimpleJdbcInsert helps us in writing readable code when we want to insert a row in a table, in the case where the database will take the burden of generating a key for us, and we need to return it to the caller. After specifying the table we are working on, we set the name of the field that is a generated key, and then, as a map, the field names and associated values. Finally we have just to pass them to the SimpleJdbcInsert by executeAndReturnKey().
3. The SpittleRowMapper seen in action in (1). Its mapRow() method maps a single row from the underneath table in a Spittle object.

In some way, Spring has to configure these repositories, passing to them a viable JdbcTemplate object. In this app, it is done by JdbcConfig, annotated as a Spring Configuration class. It generates a few beans (in the Spring sense of the term) representing the DataSource, JdbcTemplate, the repositories, and also a transaction manager. We'll see them at work in the tester. It interesting seeing how the DataSource is set, when we don't rely on the default initialization by Spring.
@Configuration
public class JdbcConfig {
    @Bean
    public DataSource dataSource() {
        return new EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.H2)
                .addScripts("schema.sql", "test-data.sql")
                .build();
    }
 
    // ...
This data source represents an embedded database, H2 type, that would run a couple of scripts at startup. We already seen the first one, schema.sql, here I added a second one, that contains a bunch of SQL insert for spitters and spittles rows.

Testing

There are a couple of JUnit test, one for Spitter the other one for Spittle repository. They are quite similar. Let's have a fast look at first one.
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = JdbcConfig.class)  // 1
public class JdbcSpitterRepositoryTest {
    @Autowired
    JdbcSpitterRepository spitterRepository;  // 2

    // ...
    @Test
    @Transactional  // 3
    public void findAll() {
        List<Spitter> spitters = spitterRepository.findAll();  // 4
        assertEquals(4, spitters.size());
        assertSpitter(0, spitters.get(0));
        assertSpitter(1, spitters.get(1));
        assertSpitter(2, spitters.get(2));
        assertSpitter(3, spitters.get(3));
    }

    //...
}
1. Here I'm saying to Spring to fetch the JdbcConfig class and use it to configure itself for testing.
2. Thanks to (1) we can ask to Spring to autowire the spitter repository.
3. Having defined a PlatformTransactionManager, we can use it to keep very simple the transaction management. A transaction is started and completed, eventually rolled-back, behind the curtains.
4. The spitters come from the test-data.sql called at startup by JdbcConfig dataSource().

Reference: Hitting the database with Spring and JDBC, from Spring in Action, Fourth Edition by Craig Walls, chapter ten.

This complete Spring application is on GitHub.

Go to the full post

Varray

In a Oracle database, we can use define datatypes based on VARRAY, a sort of resizable vector with a fixed max size.

Let's say that we have to manage a kindergarten waiting list. We want to put in a table the kid names and the relative parents names. And we want also to be able to manage the case of parents who don't have currently any kid, but still they want to enter in the list (it's a fancy kindergarten with a very long waiting list).

Any kid could have 0, 1, or 2 parents. And this is how we create the type that describes their names:
create type parents_t is varray(2) of varchar2(100);
We could have 0 or 1 kid (twins are not supported):
create type child_t is varray(1) of varchar2 (100);
And now we use these new types for our family table:

create table family (
surname varchar2(100),
parents parents_t,
kid child_t
);

Here is the PL/SQL code to put in the list Ann and Bill Smith, proud parents of Charlie Smith:

declare
parents parents_t := parents_t();
kid child_t := child_t();
begin
parents.extend(2);
parents(1) := 'Ann';
parents(2) := 'Bill';

kid.extend();
kid(1) := 'Charlie';

insert into family (surname, parents, kid)
values('Smith', parents, kid);
end;

And here is the case of Dan Schmidt, single, no kids, but with lot of hopes in the future:

declare
parents parents_t := parents_t();
kid child_t := child_t();
begin
parents.extend(1);
parents(1) := 'Dan';

insert into family (surname, parents, kid)
values('Schmidt', parents, kid);
end;

Chapter 12 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about collections.

Go to the full post

Nested table

When programming in PL/SQL we could use SQL nested tables, that behave like (STL C++) vectors of the underlying data.

Being nested tables SQL type, we could store them in a database schema and use them in any PL/SQL block having SELECT access to that type.

Nested tables could be seen as set, an we can apply on them set operators like MULTISET EXCEPT.

As an example, let's create a nested table type of VARCHAR2, declare a few variables ot this type and use them:

declare
type nt_names_t is table of varchar2(20); // 1.
family nt_names_t := nt_names_t(); // 2.
children nt_names_t := nt_names_t();
parents nt_names_t := nt_names_t();
begin
family.extend(4); // 3.
family(1) := 'Mother'; // 4.
family(2) := 'Father';
family(3) := 'Son';
family(4) := 'Daughter';

children.extend(); // 5.
children(1) := 'Son';
children.extend();
children(2) := 'Daughter';

parents := family multiset except children; // 6.

dbms_output.put_line('Parents:');
for l_row in parents.first() .. parents.last() // 7.
loop
dbms_output.put_line(parents(l_row));
end loop;

dbms_output.put_line('Childrens:');
for l_row in children.first() .. children.last()
loop
dbms_output.put_line(children(l_row));
end loop;

dbms_output.put_line('Family:');
for l_row in family.first() .. family.last()
loop
dbms_output.put_line(family(l_row));
end loop;
end;

1. We define locally a nested table type. To create a nested table type in the current schema we would have written:
create or replace type nt_names_t is table of varchar2(20);
2. Before usage, a nested type variable has to be initialized calling the constructor (C++ style).
3. We have to reserve explicitely memory for new elements.
4. In this way we add a new element to the nested table.
5. By default extend() reserves room for one more element.
6. With MULTISET EXCEPT we get a set that is the difference from the left hand to the right hand passed nested tables.
7. An interesting property of nested tables is that we can loop on them using a for loop delimited by its first() and last() element.

Chapter 12 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about collections.

Go to the full post

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.

Go to the full post

Self join

We have seen how to retrieve data from related tables using an inner join, but sometimes it makes sense even perform an inner join from a table to itself. This is what is usually called a self join.

Let's add another field to our contact table, it looks a bit strange at first view, because it is a foreign key referring to the primary key of the same table. In this case we call it ref_id because it identify the contact who acted as reference for him to be entered in the contact table itself:

alter table contact add(ref_id number(*,0));

alter table contact add constraint
ref_contact_fk foreign key(ref_id ) references contact(id);

Let's say that Jones has id 1 and no one referenced him: his ref_id would be 1. Smith, on the other side, was referenced by Jones, so his ref_id is Jones's - that is 1.

The self join select showing the contact last names and their reference's one is:

select c.last_name, c2.last_name as ref
from contact c
inner join contact c2
on c.ref_id = c2.id;

We are using two different aliases (c and c2) to refer to the same table in the two different roles, c as "left" table (borrowing the outer join terminology) and c2 as "right".

A fun basic book on SQL: Head First SQL.

Go to the full post

Outer join

An inner join select returns a row for each data coming from the two tables involved being connected by the "on" condition (or the "natural" PK -> FK relation).

If there a row in a table that has no relation with a row in the other table is simply skipped from the result.

If we actually want it in the result we use an OUTER JOIN. In this case the fields referring to the other table would report NULL as a value.

Let's add another contact to our table, this one with no job associated:
insert into contact values (3, 'Bill', 'Countless', NULL);


The inner join select we have written in the previous post would run giving the same result. If we want to see Bill last name, and a NULL as its job, we should use an outer join like this:

select c.last_name, j.description
from contact c
natural left outer join job j;

Being a LEFT outer join we have the data coming from the first table cited in the query (contact) and NULL for the missing data on the second one.

If we write the query the other way round, using job as first table, we get all the job descriptions and a NULL for the (eventually) missing relations:

select c.last_name, j.description
from job j
natural left outer join contact c;

We could achieve the same result keeping the tables in the same position and using a RIGHT outer join instead of a LEFT one:

select c.last_name, j.description
from contact c
natural right outer join job j;

The last two outer joins lead to the same result. It is a matter of taste using one style or the other.

A fun basic book on SQL: Head First SQL.

Go to the full post

Inner join

An inner join shows data coming from two tables connected using a comparison operator in a condition.

The generic syntax for an inner join is:

SELECT columns
FROM table1
INNER JOIN table2
ON condition;

Let's add a new column to our contact table, a foreign key to a brand new table, defining the main job of our contact.

This is how we created the job table:

create table job (
job_id number(*,0) not null,
description varchar2(20 byte) not null,
constraint job_pk primary key (job_id)
);

And here is the change we made in the contact table:

alter table contact add(job_id number(*,0));

alter table contact add constraint contact_job_fk foreign key(job_id)
references job(job_id);

We add a few job titles in the job table, and assign a job to each contact. Now we can use an inner join to see name and job for contacts:

select c.last_name, j.description
from contact c
inner join job j
on c.job_id = j.job_id;

This is called equijoin, since is based on an equality check. Sometimes we could be interested in a non-equijoin, that means, getting all the "wrong" cases. In our case, all the possible job our contacts are not associated to:

select c.last_name, j.description
from contact c
inner join job j
on c.job_id != j.job_id;

You may have noticed I gave name job_id to the primary key for the job table, and I used the same name for it as foreign key in the contact table.

This gives us a way of simplify a bit the notation for the inner join, since it id considered just natural using the relation between primary and foreign key with the same name, so natural that we call it a natural join:

select c.last_name, j.description
from contact c
natural join job j;

A fun basic book on SQL: Head First SQL.

Go to the full post

Cartesian join

A cartesian join - also knwon as cross product - is not very commonly used, at least intentionally. The fact is that it generates an awful huge quantity of data, and usually not with much sense in it.

A cartesian join of a n row table with with a m row table gives an n x m table. Any row in the first table is joined with any row in the second one.

When we really want to get a cartesian join, say we want display all the possible combination between the last names in the contact table with the interest descriptions, we write something like this:

select c.last_name, i.description
from contact c
cross join interest i;

Go to the full post

Many to many

In the previous post we created a relation one-to-many between two tables, and probably that was not the best solution, because this means that a contact could have as many interests as it wants (and this is ok) but an interest should be owned by just one contact.

A better one would be estabilish a many-to-many relation between them.

To implement a many-to-many relation we use a joining table, that stores the foreign keys for both tables.

So, we redesign the interest table in this way:

create table interest (
id number(*,0) not null,
description varchar2(20 byte) not null,
constraint interest_pk primary key (id)
);

And add a couple of items to it:

insert into interest (id, description)
values(1, 'trekking');

insert into interest (id, description)
values(2, 'reading');

Now this interests are not related with a specific contact (no FKs is in the table), but could be shared among many of them.

The joining table is:

create table contact_interest
(
contact_id number(*,0) not null,
interest_id number(*,0) not null,
constraint contact_fk foreign key (contact_id) references contact(id),
constraint interest_fk foreign key (interest_id) references interest(id)
);

No actual data in it, just FKs.

Adding trekking and reading to the number of the interests for the contact identified by id 1 is done in this way:

insert into contact_interest (contact_id, interest_id)
values(1, 1);

insert into contact_interest (contact_id, interest_id)
values(1, 2);

Having created the foreign key constraints, we can't "cheat". If we try to add an interest that is not in the table:

insert into contact_interest (contact_id, interest_id)
values(1, 99);

leads to an error.

A fun basic book on SQL: Head First SQL.

Go to the full post

Foreign key

We can put estabilish a relation between two different tables. As a connector, we use a field, the foreign key, that identifies the row from the first table referring to row in the second one.

As an example, think of a table defining contacts in this way:

create table contact (
id number(*,0) not null,
first_name varchar2(20 byte) not null,
last_name varchar2(20 byte) not null,
constraint contact_pk primary key(id)
);

We have a primary key, id, and a couple of data fields, first name and last name.

We want each contact having a undefined number of interests. To implement this requisite we create another table, interest, where we store each interest description in a row, and then we link it to the actual owner of the interest through a foreing key:

create table interest (
id number(*,0) not null,
description varchar2(20 byte) not null,
contact_id number(*,0) not null,
constraint interest_pk primary key (id),
constraint contact_fk foreign key (contact_id) references contact(id)
);

Say that we have inserted a contact like this:

insert into contact (id, first_name, last_name)
values(1, 'tom', 'jones');

We can create a couple of his interests in this way:

insert into interest (id, description, contact_id)
values(1, 'trekking', 1);

insert into interest (id, description, contact_id)
values(2, 'reading', 1);

We use interest.contact_id to estabilish a connection to contact.id.

A fun basic book on SQL: Head First SQL.

Go to the full post

Min, max and group by

Another variation on the same story. Now we want to identify the top salary and the bottom one for each department in our firm.

We spice the SQL statement a bit add the requirement to show in a dedicated column the gap between max and min salary, displaying as column name a short label, and ordering the report accordingly to the gap:

select department_id as dep, max(salary) - min(salary) as gap,
max(salary) as max, min(salary) as min
from employees
group by department_id
order by gap desc;

A fun basic book on SQL: Head First SQL.

Go to the full post

Avg and group by

Now we are interested in the average salary for the departments we have seen in the previous post.

We apply the ROUND() function to the result of AVG() to keep the result more readable. The rest of the statement is quite the same as the previous example.

To add a diversion, we show the result in ascending order, and we put the ASC modified esplicitely in the ORDER BY clause, even though that is not necessary, since ASC is the default:

select department_id, round(avg(salary))
from employees
group by department_id
order by avg(salary) asc;

If you want a fun introduction to SQL, consider reading Head First SQL.

Go to the full post

Sum and group by

If you have an Oracle database available, you should also have access to the hr test schema where are defined a few tables. Among them there is employees, that stores data that I'm about to use in this post.

We want to see how much is the salay costs for all the employees, grouped by the department they are working for.

This is a classical problem that could be solved easily using the SUM() function in conjunction with the GROUP BY clause in a SELECT statement.

We get the department id, the sum of all related employees salary and, to make this report more readable, we order it from the most wealthy department down to the poorest one:

select department_id, sum(salary)
from employees
GROUP BY department_id
order by sum(salary) desc;

If you want a fun introduction to SQL, consider reading Head First SQL. Actually, it uses MySQL, and not Oracle, as I'm doing currently. But this is not a big issue.

Go to the full post

Ordering data

It is a common request showing data in a table ordered accordingly to some requirement. We can achieve that using the ORDER BY directive applied to the SELECT command.

For instance, say that we want to get names and prices of all the pastries with a price less than 3, and we want them ordered by name:

select name, price
from pastry
where price < 3
order by name;

The order is implicitely ascending (ASC) meaning from A to Z, and to 0 to 9.
If we want it backward we can specify DESC (descending) in the ORDER clause.

All pastries name and price, ordered from the most expensive to the cheapest:

select name, price
from pastry
order by price desc;


I'm reading Head First SQL, a fun book good to have an introduction to SQL - based on MySQL implementation.

Go to the full post

Update with case-when

When we want to change a table so massively as shown in the previous post, we usually prefer to have a better control on the rows we are working with.

The case-when(-else) statement is what we are looking for.

Say that we want put as description for each row in the pastry table a different string accordingly with this rule:
All the names ending with "berry" should have a "With berries!" description.
All the other rows having a price less than 3 are a "Special offer".
Anything else would be a "A good choice".

This schema translates very well to this SQL statement:

update pastry
set description =
case
when substr(name,-5) = 'berry' then 'With berries!'
when price < 3 then 'Special offer'
else 'A good choice'
end;

The "else" is optional, if we don't have a default we could simply skip it.
Notice that the destination field is fixed, while I can vary the condition to select the row subset at my wish.

There is a fleeble connection between this post and Head First SQL, chapter six, because I wrote it while reading this fun book.

Go to the full post

A couple of string functions

Lot of the work in a database is about text management, so any good database makes available a good number of string function to help us in the job.

To extract a section of a string we could use substr() in this way:

select substr(name, 1, 2) from pastry;

select substr(name, -2) from pastry;

The first first call shows us the first two character of all the pastry names; the second one all the last two characters.

We can combine the result to another useful string function, upper(), to convert to uppercase the string passed, and we can use the result to populate another field in the table:

update pastry
set description = upper(substr(name, 1, 2));

Here we'll have in description just the first two characters of the name in the same table row.

Post written while having fun reading Head First SQL

Go to the full post