[00:00] In this lesson, we're going to learn how to create connections to Oracle databases. I'll be demonstrating how to do this with Oracle Database AI Free, which is the newest free database offering from Oracle. This is the successor to Oracle Express, which we've used in previous versions of this IU lesson, and what we learned today can be used to create connections to either database offering. Before we start creating this connection in the gateway webpage, there are some common points of failure I want to address. The first one we'll talk about is related to Windows devices and that's how the database is going to be authenticated. After a fresh install, Oracle databases want to use Windows authentication, which is more complex to implement and less commonplace than authenticating directly against the database. To keep things simple in this tutorial, let's go ahead and make sure this is disabled, and we can do this by changing one line in an install file. If you're not connecting on a Windows device today, then you can skip ahead to the next chapter of this lesson.

[01:05] I have this file here and what you're looking for is the sqlnet.ora file. The default path to this file is shown right here and you can go find this for yourself and open it in a text editor. We'll want to take a look at this line SQL net authentication services. If this is set to NTS, it basically means the DB will use Windows authentication, but we can change this within the parentheses by setting it to none, and this lets us authenticate against the database. Let's go ahead and save this change. Next we're going to open the command line interface for our database so I'll open the start menu and type out SQL Plus and open that program. This utility comes with all Oracle databases and it allows us to ensure our DB is ready to connect and work with Ignition. As soon as we open the program were prompted to log in and I'm gonna log in with the sys user as the sysdba role, which will let us make all the changes we're going to need here.

[02:03] I'll press enter on my keyboard and I'm prompted for a password. I'll type in mine and you should have one set during installation and keep in mind while typing this out, the cursor will not be moving. Now, Oracle databases use a multi-tenant architecture where database engines and databases are separated. Databases are pluggable, meaning they can be used in different engines, and part of the process of moving databases to the new engine involves placing them in different states. To view the state of the database we want to connect to, let's type out the command show PDBS and a semi colon, and then enter. Here we can see the name of my database free PDB one, and this was set during the Oracle AI installation. And if you installed Oracle Express, then this would say XE PDB one instead of free. What's important here is this open mode column, and if your database is set to a mounted state, that means its tables and data are closed off and Ignition will not be able to connect to it. Mine's currently in read write mode, so I'm good, but if you see mounted here, then you can use these two commands to place the database in read write mode.

[03:11] The first one opens the database and the second one ensures this change is saved whenever the database service is stopped or restarted. The last thing we'll do here in SQL Plus is give our database users some grants, and this ensures that some ignition subsystems like the tag, historian and manual queries are able to work properly in our connection. First, we need to move into the database with this command alter sessions set container equals, and then your database name. Once you have that, go ahead and press enter. I'll pass in this last command that adds the create trigger and create sequence grants to the system user. We'll press the enter key and we should see this success message saying, grant succeeded. With all of our database preparation done, we can now move over to the gateway webpage to create the database connection. We'll navigate to the connections tab and then the connections page under this database section.

[04:04] Let's click on the create database connection button. The first step is to choose the JDBC driver for Oracle database. However, we can see that this option is grayed out and this error message at the top is letting us know that we're missing some necessary files for the driver. This happens because Ignition doesn't come with them pre-installed, so we've included this helpful link to our user manual and once here you would scroll down until you see this installing and connecting to a database section. There's a brief message for adding in a connector, and we can click on this, simply add it in yourself link, which takes us to our JDBC drivers and translators page. I'll be sure to include this page as well in the resources below the video, so you don't need to navigate the user manual as much, but once here you would scroll down again to this table of database provider websites and we'll click on this link for the Oracle listing. There's a lot of information here on this page for drivers and their companion jar files, and what we actually need to gather here is the JAR file. That is what is missing from our driver in Ignition.

[05:05] Scrolling down again, there's another table here and the jar file I can recommend here is this 11 dot jar, which works well with the most up-to-date version of Ignition, so let's go ahead and download that file and once we have it, we can navigate back to the gateway webpage and repair the driver. We'll close out of the configuration window and briefly leave the connections page to go to the settings page. Here we can see our Oracle driver is errored and we can repair it by clicking on the more options or settings button next to it and selecting the managed jar files option. Let's go ahead and upload the 11.jar file from our downloads folder and then save these changes. That error should have gone away, and now the installed status is here letting us know the driver is repaired. Let's go back to our connections page and click on the create database connections button. Again, we're now able to choose the Oracle driver and we can can click on Next. Let's give this database a name and I'll just call mine Oracle AI. Next, we need to provide a username and password, and I'll use the system account I gave the grants to and I'll pass in its password with the embedded option.

[06:11] We'll scroll down to the Connect URL property, which tells Ignition where the database is. This consists of three parts, and the first is the host name or IP address. Since I have mine on the same machine as Ignition, I can leave this as local host, but you may need to specify a host name or IP address if you are connecting remotely. Next is the port number and the default one for Oracle is 1521, so if you change this, then you'd be able to pass that in where you see 1521. Finally, there's the system ID or SID that identifies the database. This can be a bit different for the version of the database that's installed. For Oracle Express the default would be xe, so you would type that in where you see the word test after the colon with database AI free. This is actually referred to as the service name, and we would use the free PDB one text and we can replace the proceeding colon with a forward slash.

[07:07] There's many more properties you can configure, but I'll leave those alone for now and refer you to our user manual and the resources below. Finally, we can click on the create database connection button and we should see a new entry for our Oracle database with a valid status.