Computing: Website and Database Programming

Web development environment setup on MS Windows.

1.7.6.	Oracle database server.
1.7.6.0.	Oracle Database introduction.
	Oracle Database is not only available for mainframes, but also for PCs. There are distributions for Windows, Linux and macOS, both commercial and free. Oracle Database Enterprise Edition, available for several platforms (or as Docker container) may be used for developing, prototyping, and testing in a non-production environment. If you have a commercial license for Oracle Database, you can download all supported versions from the Oracle Software Delivery Cloud. Oracle Autonomous Database is a free, fully managed cloud database on Oracle Cloud Infrastructure. Oracle Database Free is available as 64-bit binary for Windows, and as RPM for Linux; it is also available as container image for Docker (ideal for macOS, Linux, and other platforms). This distribution is entirely free to develop, deploy, and distribute Oracle database related applications. To download the Windows setup files of the latest release (this tutorial uses Oracle Database 23ai Free), visit the Oracle software download page. Perhaps you wonder why, after having published tutorials concerning half a dozen of database systems, I only speak about Oracle Database now. The reason is that I had the impression that Oracle Database is something complicated and requiring lots of documentation reading to use it. It's true that Oracle Database is complex from several point of views. However, if you are aware of a certain number of things, using it is not more complicated than using DB2, PostgreSQL, or MariaDB. Important to know before you start. Please, consider the following: This is bad news for lots of you, I guess: Oracle Database may not be used on Windows Home Edition (the tutorial actually is about the installation on Windows 11 Professional). There is a requirement that all directories, except the root directory (e.g. C:\) of the installation path cannot be modified by the Authenticated Users group. If you are interested in details concerning this rather complicated topic, have a look at the article Installing Oracle Database Free in the Installation Guide for Microsoft Windows at Oracle Help Center. Not really necessary to read and understand all that; just accept the default installation path and all will be as it should (if you choose other folder names within the path, be sure that they don't contain any spaces). Starting with Oracle Database 21c, a multitenant container database (CDB) is the only supported architecture (non-container databases are no longer supported). This architecture, described in the article Introduction to Oracle Database at Oracle Help Center is really complex and understanding it requires without any doubts an advanced knowledge concerning database administration. But again, this is not really required in order to work with Oracle Database... Oracle Database 23ai Free overview. In order to successfully work with Oracle Database, I think that you should read the following paragraphs, and I also think that understanding these basic concepts is sufficient to use the database in your applications. Let's start with some definitions: A multitenant container database (CDB) is a set of files (control file, online redo log files, and data files), managed by the database instance. It contains one or more user-created PDBs and, optionally, application containers. A pluggable database (PDB) is a portable collection of schemas, schema objects, and non-schema objects that appears to applications as a separate database. A PDB can be unplugged in order to move or archive it; it is not usable as database, until it is plugged into a CDB. An application container is an optional, user-created container within a CDB that stores data and metadata for one or more applications. The figure below (taken from the Oracle documentation) shows the layout of the multitenant container database architecture of Oracle Database. Oracle Database 23ai Free comes with the default database FREEPDB1 installed and configured. FREEPDB1 is also automatically started when the database server starts up, thus directly accessible from the client software. In this tutorial, we will create the tables to be used with our example programs within this PDB. The creation of a new PDB is not covered in the tutorial. If you need to create a new PDB, or if you are interested in that topic, please have a look at the article Creating a PDB from Scratch at the Oracle Help Center website. The download files include most that you need to work with Oracle Database, in particular the command line client sqlplus. Oracle also provides a GUI client, called SQL Developer; we'll see where to download, how to install and how to use it further down in the text. The fact that Oracle is something huge is well shown by the fact that FREEPDB1 contains 2581 tables. We could use one of those to test connection from PHP, Perl, etc. Downloading and installing the Oracle Sample Database is a better choice; this will provide us with real-world-like data, what we can do with what we want.
1.7.6.1.	Installing Oracle Database.
	The download file is a big ZIP archive, that you can extract wherever you want. From the file extraction directory, run the program setup.exe to start the installation. The setup program starts with the Preparing to install window. On my Windows 11 Professional, this was all I got. I suppose that the program aborted, because I noticed a high CPU usage of Windows Problem Reporting during a whole time. No idea what really happened. Anyway, relaunching setup.exe was all that I had to do to successfully start the installation with the display of the Welcome window. After you have accepted the license agreement, you are asked for the installation directory. As I explained above, you should except the default installation path that is C:\app\<user-name>\product\23ai. You are then asked for the password, that will be used for users SYS, SYSTEM, and PDBADMIN. And we're ready to go; in the Summary window, push the Install button to start file copy. After the files have been copied, the setup program continues with the configuration of the components. This operation, performing without user intervention, will take a rather long time. Finally, if all went well, the installation wizard should terminate with the message "Oracle Database Free installed successfully". The installation program doesn't set any environment variables. Not really needed (I think), but for your convenience, you might want to set ORACLE_HOME to <installation-path>\dbhomeFree, and add the "bin" directory to your PATH. Note: If you have to re-install Oracle Database, or before installing another version, be sure to remove ORACLE_HOME. Otherwise, the installation will fail!
1.7.6.2.	Connecting to Oracle Database.
	We can quickly test the connection to Oracle Database using the command line client sqlplus, located in the "bin" subdirectory of ORACLE_HOME. To connect to the CDB, run the command     sqlplus system@localhost After having entered your password, the connection should succeed with the display of the database version. Normally, you would prefer to connect to a PDB instead. To do so for the default database FREEPDB1, use the command     sqlplus system@localhost/FREEPDB1 Oracle Database does not include a SHOW TABLES command, as does MySQL. To show all tables of the PDB, use the SQL statement     SELECT table_name from dba_tables;
1.7.6.3.	Installing SQL Developer.
	Oracle SQL Developer is a GUI client that you can use to manage Oracle databases. There are free downloads available for Windows, macOS, and Linux. The simplest way to install it on Windows is to download the Windows 64-bit with JDK 17 included distribution, what you can do from the SQL Developer download page. The download is a ZIP archive, that contains the application folder structure. Unpack the files to some directory on your harddisk (use a folder outside your Oracle Database installation!); I actually use C:\Programs\sqldeveloper. If you intend to seriously work with Oracle Database, you should create a shortcut of sqldeveloper.exe on your desktop or in Windows Start menu. When starting the software for the first time, you are asked if you want to import settings from a previous installation. Then the GUI starts up. If you don't want to share usage data with Oracle, unselect the corresponding checkbox in the dialog box, displayed the first time that you launch SQL Developer. The first thing that we have to do is to create a connection to FREEPDB1. To display the New/Select Database Connection window, push the blue Create Connection Manually button. We'll create here a connection for user system. Enter a name for the connection (I use "system"; a name in relationship with FREEPDB1 would probably be more appropriate). Enter "system" as username, and enter the password, that you configured when installing Oracle Database. Use the connection type "Basic" (default), and set the connection options as follows: Hostname: localhost. Port: 1521 (Oracle Database default port). Select the Service name radio button, and set this option to FREEPDB1. To test the connection, push the Test button. The message "Status: Success" should be displayed in the bottom left corner of the window. To save the new connection, push the Test button. The new connection will now be listed in the upper left pane of the GUI. After having closed the window, you can connect to FREEPDB1 as user "system" by double-clicking this connection's icon; you will be asked for your password when doing so. As similar software, SQL Developer uses tabs for SQL statement input and query result output. The Query Builder tab allows you to enter SQL statements. To run them, use the Run Script button (second icon from the left in the icons bar).
1.7.6.4.	Installing Oracle Sample Database.
	The article Oracle Sample Database at the Oracle Tutorials website describes an Oracle sample database, which is based on a global fictitious company that sells computer hardware, including storage, motherboards, RAM, video cards, and CPUs. The article includes the database layout and the field definitions of the different tables. It's also from this page, that you can download the SQL to create this database. The download is a ZIP archive, that you can unpack in whatever directory you want. In fact, the archive contains a single file, called oracle_sample_database.sql. The content of this file is the SQL statements that allow to create all tables and other objects of the database. Before creating the database, we will create a new user. This may be done (connected to FREEPDB1 as user "system") either in sqlplus, or in SQL Developer. To create a user, called "aly", and the user's password being "oracle", run the following SQL statement:     CREATE USER aly IDENTIFIED BY oracle; The new user will appear listed in the left pane of SQL developer beneath "Other Users". Next, we have to grant the necessary privileges to the new user (privileges to connect to FREEPDB1, and to create the databse objects). Discussing Oracle Database privileges is outside the scope of this tutorial. Just consider that giving "aly" the privileges CONNECT, RESOURCE, and DBA makes sure that this user has all permissions that he needs to create and manage the objects of the sample database. To grant these privileges to "aly", run the SQL command:     GRANT CONNECT, RESOURCE, DBA TO aly; Note: In SQL Developer, granting privileges to a user may also be done "graphically": Right-click the user name in the left pane, choose Edit User from the context menu, and select the checkboxes corresponding to the privileges that you want to grant. In order to connect as "aly" in SQL Developer, we'll have, of course, to create a new connection (I called it "aly"). This is done in a similar way as the connection creation for user "system", described further up in the text. Creating the Oracle Sample Database in SQL Developer is really easy. First, close the actual connection (user "system"), and connect as the newly created user. Then use File > Open from the menu bar, to load the script oracle_sample_database.sql into Query Builder. Finally, push the Run Script button to execute the SQL. That's it! The screenshot shows the script oracle_sample_database.sql, opened in SQL Developer. When the database is created, you can view its tables by expanding "Tables" in the left pane of SQL Developer. Double-clicking a table will open the Columns tab for this table, i.e. the table structure will be displayed. To display the table content, i.e. display the table data, switch to the Data tab.
1.7.6.5.	Schemas and schema objects.
	A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Database objects, in particular tables, created by a given user, are objects of this user's schema. You can find further details concerning schemas in the article Schema Objects at the Oracle Help Center website In a simplified way, we could say that, whereas in MySQL a schema represents a MySQL database, in Oracle Database, it represents some kind of "sub-database" within a PDB. If, instead of naming him "aly", we had, for example, named our new user "hs" (for "hardware_store"), all tables, created by "hs" would be objects of the schema "hs". And accessing the objects (in particular the tables) of this schema would be like accessing the data of the "hardware_store" database. This way to view things, maybe not very professional, is by my opinion justified by the fact that it should make Oracle Database more easily understandable to database beginners. Consider, for example, the task to display all tables of the Oracle Sample Database. As each user owns a single schema, this is finally nothing else than displaying the tables created by "aly". The screenshot below shows, how I connected to FREEPDB1 as "aly", and displayed his tables using the SQL statement     SELECT table_name FROM user_tables ORDER BY table_name; As you can see, the result of this query effectively consists in the display of all tables of the "hardware_store" database. Accessing a table in relational databases means accessing a table, that is an object of a given schema. In MySQL, connecting to a database means using the schema of the same name as the database, and accessing a table with its name always uniquely identifies it. In Oracle Database, this is not the case! In fact, we connect to a PDB, and this PDB may contain tables created by different users, i.e. tables that are objects of different schemas (in our simplified view, we would say, tables of different "sub-databases"). This means, that when accessing a table, we have to tell the database server which schema has to be used. This is done by prefixing the table name with the schema name (using a dot as separator). Omitting the schema name lets the server think that we want to use the default schema for this connection, i.e. the schema with the same name as the user we connected as. Let's take an example. Connected to FREEPDB1 as "aly", the SQL statement     SELECT last_name, first_name FROM employees         where job_title = 'Accountant'         ORDER BY last_name, first_name; would tell the database server to select data form the table "employees" in schema "aly" (aly.employees). This table well exits, and the query is successful (by the way, note that, as a difference with other database systems, in Oracle Database, string literals must be enclosed in single quotes, double quotes resulting in a syntax error). Now, let's connect as "system", and try to run the SQL statement     SELECT COUNT(*) FROM employees This results in the error message Table or view "SYSTEM.EMPLOYEES" does not exist. In fact, as we didn't specify a schema, the database server assumes that the schema "system" has to be used (as this is the name of the user we used to connect). And there is no table named "employees" in the "system" schema. To display the number of records in the "employees" table, when connected a user other than "aly", we'll have to use the SQL statement     SELECT COUNT(*) FROM aly.employees
1.7.6.6.	Accessing Oracle Database from PHP.
	It is supposed that you have a fully functional Apache webserver running (cf. my tutorial Web development environment setup on MS Windows: Apache webserver), and with PHP 8 installed, you have configured Apache to execute PHP scripts (cf. my tutorial Web development environment setup on MS Windows: PHP). PHP 8 includes the driver for Oracle Database by default. Thus, all we have to do is to enable it in the PHP configuration file PHP.INI. To do so, uncomment the following lines (I think that, in fact, you don't need to enable the PDO extension):     extension=oci8_19     extension=pdo_oci Do not forget to restart Apache after having changed the PHP configuration file. Here is the code of a simple PHP script (I called it oracle.php and placed it into the document directory of Apache), that displays the first and last name of all accountants in the EMPLOYEES table.     <?php         $conn = oci_connect('aly', 'oracle', 'localhost/FREEPDB1');         if (!$conn) {             $e = oci_error();             trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);         }         $stid = oci_parse($conn, "SELECT last_name, first_name FROM employees WHERE job_title = 'Accountant' ORDER BY last_name, first_name");         oci_execute($stid);         echo "<table border='1'>\n";         while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {             echo "<tr>\n";             foreach ($row as $item) {                 echo "    <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : " ") . "</td>\n";             }             echo "</tr>\n";         }         echo "</table>\n";     ?> To run the script, type the following URL in the address bar of your web browser:     localhost/oracle.php The screenshot shows the script output.
1.7.6.7.	Accessing Oracle Database from Perl.
	It is supposed that you have a fully functional Apache webserver running (cf. my tutorial Web development environment setup on MS Windows: Apache webserver), and with Strawberry Perl 64-bit installed, you have configured Apache to execute Perl scripts (cf. my tutorial Web development environment setup on MS Windows: Perl). The nice thing with Perl is the implementation of database access using the database-independent interface DBI. This allows to use a Perl CGI script, written for MySQL for accessing Oracle Database without any major modifications. In fact, all that we have to change is to use the oracle specific driver, instead of the MySQL specific one, and adapting the arguments of the DBI->connect() method. The module with the DBI driver for Oracle is called DBD::Oracle. It is not included by default with Strawberry Perl, thus you'll have to install it. Just run the 64-bit CPAN client and execute the command     install DBD::Oracle Here is the code of a simple Perl CGI script (I called it oracle.cgi and placed it into the cgi-bin directory of Apache), that displays the count of the records in the EMPLOYEES table.     #!C:/Programs/Strawberry/perl/bin/perl.exe     use strict; use warnings;     use CGI qw(:standard);     use CGI::Carp qw(fatalsToBrowser);     use DBI;     print header;     my $dbname = "//localhost/FREEPDB1";     my $user = "aly";     my $pass = "oracle";     my $dbh = DBI->connect("dbi:Oracle:$dbname", $user, $pass);     $dbh->{RaiseError} = 1;     my $sth = $dbh->prepare("SELECT COUNT(*) FROM employees");     $sth->execute;     my ($count) = $sth->fetchrow();     $dbh->disconnect;     print "<html>\n";     print "<head><title>Test Perl access to Oracle Database</title></head><br/><br/>\n";     print "<body><p>Number of employees in the Oracle Sample database = $count</p></body>\n";     print "</html>\n"; Note the somewhat special way to specify the PDB to connect to: "//localhost/FREEPDB1". To run the script, type the following URL in the address bar of your web browser:     localhost/cgi-bin/oracle.cgi The screenshot shows the script output.
1.7.6.8.	Accessing Oracle Database from Python.
	It is supposed that you have a fully functional Apache webserver running (cf. my tutorial Web development environment setup on MS Windows: Apache webserver), and with Python 3 installed, you have configured Apache to execute Python scripts (cf. my tutorial Web development environment setup on MS Windows: Python). Python access to Oracle Database is done using the functionalities of the module python-oracledb. This module is not by default included with Python 3. Thus, you have to install it yourself, what can be easily done using pip. In Windows Command Prompt, run the command:     python -m pip install oracledb For most functionalities, you can run python-oracledb in "thin mode", which connects directly to Oracle Database, without the need of any Oracle Client libraries. For details, have a look at the article Connecting to Oracle Database at the python-oracledb website. Here is the code of a simple Python CGI script (I called it oracle.py and placed it into the cgi-bin directory of Apache), that displays the count of the records in the EMPLOYEES table.     #!C:\Programs\Python313\python.exe     import oracledb     print("Content-type: text/html")     print()     print("<html>")     print("<head><title>Python Oracle test</title></head>")     print("<body>")     # Connect to Oracle Sample database     connection = oracledb.connect(user="aly", password="oracle", dsn="localhost/FREEPDB1")     cursor = connection.cursor()     # Read the number of records in the 'employees' table     cursor.execute("SELECT COUNT(*) FROM employees")     count = cursor.fetchone()[0]     # Print result onto webpage     print("<p>Number of employees in Oracle Sample database = ", count, "</p>")     print("</body>")     print("</html>")     # Disconnect from database     connection.close() To run the script, type the following URL in the address bar of your web browser:     localhost/cgi-bin/oracle.py The screenshot shows the script output.
1.7.6.9.	Accessing Oracle Database with Lazarus/Free Pascal.
	To learn how to access Oracle Database from Lazarus/Free Pascal, using the TOracleConnection component, please have a look at my tutorial Using Oracle Database with Lazarus/Free Pascal in the Lazarus/Free Pascal Programming section of my site.

---

If you find this text helpful, please, support me and this website by signing my guestbook.