Create an Oracle Database Docker Image

We often need to create or run applications that rely on databases like Oracle or SQL Server. The big problem is that we are often unable to access a corporate server for our tests. Another problem is that third parties' actions in a shared database can affect our tests or development. For these scenarios, using a Docker image is a great ally, as we can have an exclusive bank available for our development activities.

In this tutorial, we will:

• Create a docker image for Oracle Database.
• Connect them using Red Hat JBoss Fuse.
• Perform a SELECT and INSERT. 

Let's start

Prepare the Container

Firstly, download or clone the files of the repository: https://github.com/oracle/docker-images/.

The second thing to do is download the binary files for the Oracle Database version of your choice. I will use version 19.3.0 for Linux. The link to download is: https://www.oracle.com/database/technologies/oracle-database-software-downloads.html

After downloading the file (in my case, LINUX.X64_193000_db_home.zip), we need to copy it to the folder referring to the oracle version in the cloned folder. In this case, 19.3.0:

xxxxxxxxxx

$ cp ~/Download/LINUX.X64_193000_db_home.zip ./docker-images/OracleDatabase/SingleInstance/dockerfiles/19.3.0/

The third step is to build the image. Go to the folder with the Docker files in the cloned project (one level before the versions folder). To build the image, we will use the below script:

xxxxxxxxxx

$ ./docker-images/OracleDatabase/SingleInstance/dockerfiles/19.3.0/buildDockerImage.sh -v 19.3.0 -e

If all goes well, we will have the image created. To confirm this, run the below command:

xxxxxxxxxx

$ docker images

REPOSITORY                                TAG           IMAGE ID       CREATED          SIZE

oracle/database                           19.3.0-ee     d8be8934332d   53 minutes ago   6.54GB

Now, we can run our database. For this, use the below command: 

x

docker run

docker run --name myOracle1930 \

 -p 1521:1521 \

 -p 5500:5500 \

 -e ORACLE_SID=ORCLCDB \

 -e ORACLE_PDB=ORCLPDB1 \

 -e ORACLE_PWD=root \

 -e INIT_SGA_SIZE=1024 \

 -e INIT_PGA_SIZE=1024 \

 -e ORACLE_CHARACTERSET=AL32UTF8 \

 oracle/database:19.3.0-ee

When the container I ready, you can use the Enterprise Manager to verify it. Use port 5500 to access. In this case, https://127.0.0.1:5500/em. Use the username "system" and the password entered in the ORACLE_PWD property.

From this point, we have a Docker container running an Oracle Database, and we can connect it using any database tool like Oracle  SQL Developer, DBeaver Community, or creating a JDBC connection.

Connect Them in the Container

For this step, I will use a Red Hat JBoss Fuse 7.8 application, and I will perform queries using the component camel-sql.

The application I created is available on GitHub at https://github.com/iam-roger-io/fuse-oracle-example. Download the version from the branch called fuse78-camel-sql.

Basically, we need to create a data source. 

x

    @Bean(name = "oracleDS")

    public DataSource dataSource() {             

        DriverManagerDataSource dataSource = new DriverManagerDataSource();

        dataSource.setUrl("jdbc:oracle:thin:@172.17.0.1:1521/ORCLCDB");

        dataSource.setUsername("camel");

        dataSource.setPassword("1qaz@WSX");

        return dataSource;

    }

}

Notice that the IP address is the Docker IP and the SID is the name used "docker run .."

We need to create the username and password using a SQL tool. The default username, "system," doesn't has privileges for insert, only SELECT queries.

Before we do this, create a table called "person" in Oracle.

xxxxxxxxxx

CREATE TABLE PERSON

(

  ID NUMBER(*, 0) NOT NULL 

, NAME VARCHAR2(50 BYTE) NOT NULL 

, CONSTRAINT PERSON_PK PRIMARY KEY 

  (

    ID 

  )

)

After the table is created, create the user "camel" like below. 

xxxxxxxxxx

alter session set "_ORACLE_SCRIPT"=true;

create user camel identified by "1qaz@WSX"

GRANT create session TO camel; 

GRANT

  SELECT,

  INSERT,

  UPDATE,

  DELETE

ON

  sys.person

TO

  camel;

In the project POM file, I set the dependency for the JDBC driver as ojdbc8. 

x

        <dependency>

            <groupId>com.oracle.database.jdbc</groupId>

            <artifactId>ojdbc8</artifactId>

        </dependency>    

It is recommended to use the Oracle BOM mechanism. Notice in the pom.xml file that there are two BOM definitions. One definition for Fuse 78 and a second definition for Oracle JDBC.

x

    <dependencyManagement>

        <dependencies>

            <dependency>

                <groupId>org.jboss.redhat-fuse</groupId>

                <artifactId>fuse-springboot-bom</artifactId>

                <version>7.8.0.fuse-sb2-780038-redhat-00001</version>

                <type>pom</type>

                <scope>import</scope>

            </dependency>

            <dependency>

              <groupId>com.oracle.database.jdbc</groupId>

               <artifactId>ojdbc-bom</artifactId>

               <version>21.1.0.0</version>

               <type>pom</type>

               <scope>import</scope>

             </dependency>          

        </dependencies>

    </dependencyManagement>

In the project, one route performs select all registers in the Oracle Database and, in sequence, insert a new register.

xxxxxxxxxx

@Component

public class Routes  extends RouteBuilder {  

    @Override

    public void configure() throws Exception {            

         from("timer://foo?fixedRate=true&period=1000")

        .toD("sql://SELECT * FROM SYS.PERSON?dataSource=#oracleDS")

        .log("### NUMBER OF RECORDS IN  THE TABLE PERSON : ${body.size}")

        .process(new Processor() {            

            @Override

            public void process(Exchange exchange) throws Exception {

                @SuppressWarnings("unchecked")

                List<Object> id =  exchange.getIn().getBody(List.class);

                Integer number = id.size()+1;

                String name = " Person " +number;                  

                exchange.getIn().setHeader("personId", number);

                exchange.getIn().setHeader("personName", name);              

            }

        })       

        .toD("sql://INSERT INTO SYS.PERSON (ID, NAME) VALUES (:#personId, :#personName)?dataSource=#oracleDS")

        .log("### INSERTED NEW RECORD IN THE TABLE PERSON ${header.personName}");

    }

}

And that's all folks, run the application and notice its behavior. It is the same for Oracle in Docker or traditional installations; however, for us developers, the capacity of reproducing a complex database like an Oracle database in a simple container is a great facility.