Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. 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

RAII for OCCI

As we have seen in the previous post, Oracle OCCI is just cool, if you think what you have to write to get the same result using directly Oracle OCI. Still there is room to improvement, especially if you think of what C++0x makes available.

To execute a query we have to create an Environment object, that we use to create a Connection, that we use to create a Statement, that we (finally) could run to get a ResultSet back.

After that we should clean it up. So we should close the ResultSet from the Statement, then terminate the Statement from the Connection, terminate the Connection from the Environment, and (happy ending) terminate the Environment.

In our simple example there is no reason to complain. The code is reasonably straightforward, and looks easy to understand. Still, trouble is lurking from these lines. What happens if an exception breaks the natural execution flow? And what if future changes make the code less immediate? Maybe some part of the code could try to do silly things, not respecting the implicit contract that we should maintain to write OCCI code.

To make our code safer, we should make more explicit our requirements, using language constructs.

Let's think to the Environment object. It shouldn't be copied - maybe it could be moved, but we should do it carefully - it should be the first object in the OCCI chain to be created, and the last one to be deleted. We could verbosely comment our code to make it clear to the reader, but I have a sort of suspect that this is not a strategy that pays off.

Better using a smart pointer, and since I am writing this code explicitly for VC++ 2010, we can take advantage of its implementation for std::unique_ptr, that fits exactly to the requirements of our problem.

So, the initial situation is that we have a piece of code that looks like this:
try
{
   // setup
   oc::Environment* env = oc::Environment::createEnvironment(); // 1
   // ...

   // execution
   // ...
   while(res->next())
      std::cout << res->getString(1) << ' ' << res->getString(2) << ' ' << res->getInt(3) << std::endl;

   // cleanup
   // ...
   oc::Environment::terminateEnvironment(env); // 2
}
catch(const oc::SQLException& e)
{
   // ...
And we want apply a couple of changes on it:
1. Instead of a raw pointer we want to deal with an object on the stack, so that we could leave to the compiler (through the stack unwinding mechanism) the nuisance of taking care of its correct destruction, even in case of exceptions.
2. As direct consequence of (1), no explicit cleanup should be performed for our Environment object.

This is the code we want to get instead:
try
{
   OcciEnvironment sEnv = OcciWrapper::createEnvironment();
   // ...

   // ...
   while(sRes->next())
      std::cout << sRes->getString(1) << ' ' << sRes->getString(2) << ' ' << sRes->getInt(3) << std::endl;

   // ...
}
catch(const oc::SQLException& e)
{
   // ...
At the end of the day, when all the OCCI raw pointers are wrapped in smart pointers, there would be no more need for a cleanup section, and the code would be more robust.

But, as usual, there is no free lunch, we just moved the complexity of the issue elsewhere. And if large part of it would stay hidden in the standard library implementation, some of it will sneak in this piece of code that we are about to put in an include file available to our source:
#pragma once

#include <functional>
#include <occi.h>
namespace oc = oracle::occi;

typedef std::unique_ptr<oc::Environment, std::function<void(oc::Environment*)> > OcciEnvironment; // 1

namespace OcciWrapper
{
   OcciEnvironment createEnvironment() // 2
   {
      oc::Environment* env = oc::Environment::createEnvironment(); // 3
      return OcciEnvironment(env, std::bind(oc::Environment::terminateEnvironment, env)); // 4
   }
}
1. This is not so terrible, after all. Instead of using a raw pointer to an oracle::occi::Environment object, we plan to use a smart pointer, but since its definition is a bit complex, I think it is better to typedef it to a more friendly name.
The std::unique_ptr is a scoped limited smart pointer that can't be copied (but could be moved) and that lets its user a way to define what should be called by its dtor. But when you want to use this feature we have to specify in the class template the type of the deleter. So we say to the compiler that we want to be able to pass a function that returns void and requires in input a raw pointer to Environment.
2. Actually, we should write a createEnvironment() overload for each oracle::occi::createEnvironment() we plan to use in our code. Let's assume that currently we could do with just this one.
3. Firstly we create a raw pointer, and then we use it to create its smart big brother.
4. Time to be smart. The raw pointer is wrapped in an unique_ptr, and the Environment cleanup function is passed as the deleter associated to this object. Notice that we had to use std::bind to let it know what it should use as parameter.

Go to the full post

Oracle OCCI for Visual Studio 2010

I wrote a sort of hello world application using OCCI for Oracle 11 and VC++ 2010. A simple example that runs a select statement on the standard Oracle HR schema and print on the console the resulting rows. If you have a previous experience developing with Oracle OCI you would certainly appreciate the difference.

I'd say the development team has made a good job, but still both the setup and the usage require a bit of attention.

Firstly, I warmly suggest you to use Oracle Database Instant Client, since it makes much easier the job. To be used with VC++ 9 and 10, you need also to download a patch.

Secondly, you should ensure your application has a way to access the tnsnames.ora file where is defined the SID for the database you want to access; you can do that ensuring that ORACLE_HOME or TNS_ADMIN is defined as environment variable, and it is associated with the right path.

If ORACLE_HOME is used, it should be something like c:\ ... \oracle\product\11.2.0\server, where under server you should have network\admin, and in admin you should find tnsnames.ora - TNS_ADMIN should be the path containing tnsnames.ora.

Third step is about setting the Property Pages for the VC++ project that is going to actually use OCCI.

In the Configuration Properties:

1) C/C++
a) General - Additional Include Directories
Add the include directory for OCI. You should find it in you Oracle 11 server installation, something like ... \server\oci\include.
b) Code Generation - Runtime Library
Ensure ist value is set to /MDd or /MD, meaning Multithreaded (Debug) DLL.

2) Linker
a) Additional Library Directories
It should point to the directory where you put the VC++ 10 patch for the instant client.
b) Input - Additional Dependencies
Here you add the library, oraocci11.lib or oraocci11d.lib, specific for VC++ 10 and Oracle 11.

Fourth step is about making available instant client DLLs (generic and specific ones), adding their paths to the PATH environment variable, or copying them in the directory where the executable resides.

Once we have done all this stuff, it is relatively easy writing the code we talked about at the beginning of the post:
#include <iostream>
#include <occi.h>

namespace oc = oracle::occi;

int main()
{
   try
   {
      // setup
      oc::Environment* env = oc::Environment::createEnvironment(); // 1
      oc::Connection* conn = env->createConnection("hr", "password", "MYDB"); // 2
      oc::Statement* stmt = conn->createStatement("select * from countries where country_name like 'A%'"); // 3

      // execution
      oc::ResultSet* res = stmt->executeQuery(); // 4
      while(res->next()) // 5
         std::cout << res->getString(1) << ' ' << res->getString(2) << ' ' << res->getInt(3) << std::endl;

      // cleanup
      stmt->closeResultSet(res); // 6
      conn->terminateStatement(stmt);
      env->terminateConnection(conn);
      oc::Environment::terminateEnvironment(env);
   }
   catch(const oc::SQLException& e) // 7
   {
      std::cout << "Exception: " << e.what() << std::endl;
   }

   system("pause");
}
1. All the connections are created in an environment, that could have different setups. Here, we create a plain environment, that is enough for the current task.
2. Given an environment, we can create a connection. The third parameter is the SID identifying the database in the tnsnames.ora file, the first two parameters represent user and password.
3. Once we have a connection, we can create a statement in it. Here it is a select query.
4. When the statement is a query, we can execute it, getting back as a result a resultset.
5. Now we can loop on the resultset to access all the found rows.
6. Inverting the construction order, we cleanup the object we created: resultset, statement, connection and, finally, the environment.
7. Catching the exceptions that our OCCI calls could throw.

Not bad, if you compare this code with what you should write to get the same result using OCI. But it is a bit painful to see all these pointers, and thinking what happens in case of an exception, say, just after [3.] - the answer is: no cleanup function is called. A bit of RAII would help to make it more robust.

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

Toad and ORA-6413

If you install Toad for Oracle on a 64 bit Windows system, and you get the ORA-6413 error when you try to connect for the first time to the database, maybe it is not such a big problem as it looks.

At least in my case was a file name issue, and I solved it deinstalling Toad and reinstalling in a clean directory (no blank, parenthesis and other silly characters in it).

Toad is a 32 application, so Windows, by default, installs it in a folder under C:\Program Files (x86). Some piece of code does not expect round brackets in file names - and here it comes our issue.

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

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

Table alterations

Instead of dropping an recreating a table we usually prefer a more conservative approach, altering the existing table, keeping the structure.

Here is how we want to add a column to the table:

alter table doughnut
add description varchar2(20);

We can even change the name of a table, maybe we want extend our business and doughnut is seen too limiting as a name, and we would like to rename the table pastry:

alter table doughnut
rename to pastry;

Or we could strip a column out of a table, if not used anymore:

alter table pastry
drop column created;

Post written while having fun reading Head First SQL

Go to the full post

Auto-incrementing Primary Key

Our doughnut table has a serious issue: we forgot to give it a primary key.

Luckily it contains just a small useless bunch of fake data, so we can happily dump it and rebuild from scratch.

Here is how to destroy (for good!) that table:
drop table doughnut;

Our new doughnut table will have a new shiny column named id that we plan to use as primary key. The issue is that Oracle does not provide the handy autoincrement attribute for a primary key that so many other database make available, so we emulate it, doing it by hand.

Firstly we create a sequence like this one:

create sequence seq_doughnut
increment by 1
start with 1;

Secondly we have to create a trigger, that basically is a function called by the database when an event is, how to say, triggered. This is not so intuitive like the sequence, but it is not too complex either:

create or replace trigger trg_doughnut
before insert on doughnut
for each row
begin
if :new.id is null then
select seq_doughnut.nextval into :new.id from dual;
end if;
end;

If no id is passed to the database by the insert statement, a value coming from the sequence is used.

Go to the full post

Advanced selecting

To play around with select in Oracle, there is nothing better than using the preset hr schema. There is a number of way we can access it through our test user. We could connect to the database using the hr user, or we could change the current_schema used by the current session, using this command:
alter session set current_schema = hr;
Or maybe we could use the qualified name for the table we want to access:
select * from hr.countries;

The american countries in the the countries database have region_id 2, so to select all of them we write this query:
select * from countries where region_id = 2;
The star (*) means "all the columns".

The american countries in the the countries database have region_id 2, so to select all of them we write this query:
select * from countries where region_id = 2;
The star (*) means "all the columns".

If we are not interested in seeing all the columns we could specify just the ones we want to see:
select country_name from countries where region_id = 2;
We can have a better selection ANDing more conditions:
select * from employees where department_id = 90 and salary > 20000;
And what if we'd like to select all the employees having first name starting with S? We could write something like that:
select * from employees where first_name > 'S' and first_name < 'T';
Actually, there is another way to get the same result, using the "like" keyword:
select * from employees where first_name like 'S%';
The percent means any number of any character. If we want just one 'jolly' character we use the underscore:
select * from countries where country_id like 'C_';
Another way to select a range of values is achieved using the between keyword:
select * from employees where first_name between 'S' and 'T';

We can also ORring different clauses:
select * from employees where department_id = 40 or department_id = 50;

A different approach is required to find the rows having a specific field set to NULL. If we are looking for the boss, we can get it knowing he has no manager, meaning his manager_id is NULL:
select * from employees where manager_id is null;

If we know the values we are looking for, instead of ORring all the conditions referring to them we can use the IN clause:
select * from employees where first_name in ('Steven', 'John');
Or we can select all the rows that are not in a set of values:
select * from employees where department_id not in (40, 50);

Post written while reading Head First SQL

Go to the full post

Variations on create

We have seen a basic usage of the SQL CREATE TABLE command. Now we see how to say that a field must be specified and how to specify a default value.

No field is mandatory in a table. If we don't specify it in a INSERT statement Oracle simpy put a NULL in it. If we want to force the caller to specify a value for a given field we could use the NOT NULL attribute. If we have a reasonable default for a field we can specify it using the DEFAULT attribute.

I'm not sure what is the sense in using NOT NULL and DEFAULT together, but we can do it. Just remember that, for Oracle, you should put first the DEFAULT attribute and then NOT NULL:

CREATE TABLE doughnut (
name VARCHAR2(10) NOT NULL,
price DEC(5,2) DEFAULT 2.99 NOT NULL,
stock INT,
created DATE
);

If we have a doughnut table created in this way and we try to insert a row in this way:

INSERT INTO doughnut (PRICE, STOCK, CREATED)
VALUES (2.99, 33, to_date('12-01-2007','dd-mm-yyyy'));

We get an error: cannot insert NULL.

Post written while reading Head First SQL

Go to the full post

A new database is born

I'm about to do some database testing using Oracle 10 on Windows 7. First step I do is creating a new user. Then I create a first table.

To create a user I refer to the Oracle home page page available through web browser. In the Administration section, Database Users, I select the Create User option.

I specify a lousy username (test) and an even lousier password (password) count on the fact that I'm going to put in a bunch of worthless data on a local database. For the same reason I'm not be picky in assigning priviledges to this use, I give it all of them. Not a good idea, usually.

In any case, I click on the Create button, and that's done. I have my test user ready to act.

To work on it, I use Oracle SQL Developer, I create a new Oracle connection, specifying user and password as just created.

When I open the newly created connection, a new working space window is opened. In there I could now create a table for the test user:

CREATE TABLE doughnut (
name VARCHAR2(10),
price DEC(5,2),
stock INT,
created DATE
);

In this way we are saying that DOUGHNUT is the name of a new table containing four fields:
NAME a string that can't be longer that 10;
PRICE is a floating point number, maximum five digits, two of them are are reserved for fractional side, so we can use not more that three digits for the integer side;
STOCK is a integer;
CREATE is a date.

To see what we actually have created we can ask to Oracle to describe the table:
desc doughnut;
If we are not happy anymore with a table, we can remove it for good using the drop command:
drop table doughnut;
We can insert an item in our table using the insert command:

INSERT INTO doughnut (NAME, PRICE, STOCK, CREATED)
VALUES ('Blueberry', 2.99, 33, to_date('12-01-2007','dd-mm-yyyy'));

We ask Oracle to insert into our table a new row. We specify the fields we want to set and then the values. Notice that the string is delimited by single quotes, and that we specify explicitely the date format, to avoid ambiguity.

Head First SQL is about SQL - as one could expect - but seen from the point of view of a MySQL user. I'm actually reading it just for fun (I know, I'm a bit on the nerdy side in these days) but I'm working with Oracle 10, and not with MySQL. That means I twist a bit the examples showed in the book to adapt them to the environment I'm using.

Go to the full post