menu
  Home  ==>  papers  ==>  db  ==>  oracle  ==>  oracle_express_installation   

Oracle Express Intallation - Felix John COLIBRI.

  • abstract : Downloading, installing the Oracle Express Server and the Instant Oracle Client. Step by step installation and checking, finally using Delphi and FireDac to display the Scott / Tiger EMP table
  • key words : Oracle Express, Instant Oracle Client, TnsName, TnsPing, SQL*Plus, FireDac, the Scott / Tiger database, the HR database
  • software used : Windows 7 Pro 64 bits, Delphi Xe8
  • hardware used : Intel Quad, 4*2Ghz, 4Go Ram, 320 G hard disc
  • scope : Delphi 5, 6, 7, 8, Delphi 2005 to 2010, Delphi Xe to Xe9, Delphi Seatle, Berlin, Tokyo
  • level : Delphi developer
  • plan :


1 - Oracle Express

Oracle Express is a light Oracle Server installation. It allows us to use simple Oracle Database

We will present

  • Oracle Express Server: the download, installation, Sql*Plus testing
  • Oracle Instant Client download and installation
  • using the Oracle Database in Delphi using then FireDac database access components



1.1 - the Oracle Express Server

1.2 - Downloading Oracle Express

Google will tell you where to download Oracle Express.
   We used Oracle Database Express Edition 11g Release 2 page

oracle_express_donwload_page

   select the "Accept License Agreement"

   select one of the versions. We selected the Oracle Database Express Edition 11g Release 2 for Windows x64 version

Be aware that using the 64 bit version will not enable any design time use of Oracle in the (32 bit) Delphi Ide (connection using the Data Explorer or in any Form or DataModule)

   Oracle presents a login account

oracle_dowload_login

   since we do not have an Oracle account, we click "Create an account"

   an Oracle account form is presented

accouont_creation

   enter your parameters

   an email is sent

   click the email
   the account is validated

   go back to the download form, enter your email and password

   the OracleXE112_Win64.zip file is downloaded (324 Meg)

   unzip the file

   the setup is present

oracle_setup

   start Setup.Exe

   Instalshield prepares the Installation Wizard is presented:

oracle_installation_wizard

   click "Next"

   a license agreement is presented

oracle_license_agreement

   click "I accept" and "Next"

   the installation directory is suggested

oracle_intallation_directory

   accept (or select another directory)

   a password is required

oracle_xe_password

   type and confirm a password. We entered aaaaaa1-

   a summary of your parameters is presented

oracle_parameter_summary

   save those parameters and click "Install"

   the installation proceeds (the progress bar is grey, with green bursts). It creates the files, the services, configures the database

oracle_instllation

After around 5 minutes the end dialog is presented

oracle_instllation_complete

   click "Finish"


1.3 - What has been installed

  • on disk, we have the C:\oracleexe directory (Oracle XE)

    oracle_directory

    The interesting parts are

    • the c:\oraclexe\app\oracle\product\11.2.0\server\bin folder, containing most of the server exes and dlls
    • the c:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN folder containing the all-important TNSNAMES.ORA parameter file:

       
      XE =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = win72)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = XE)
          )
        )
      EXTPROC_CONNECTION_DATA =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
          )
          (CONNECT_DATA =
            (SID = PLSExtProc)
            (PRESENTATION = RO)
          )
        )
      ORACLR_CONNECTION_DATA =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
          )
          (CONNECT_DATA =
            (SID = CLRExtProc)
            (PRESENTATION = RO)
          )
        )

      Note that in addition to the "true" TnsNames.Ora, the c:\OracleEx\app\oracle\product\11.2.0\server\  folder contains 2 other sample TNSNAMES.ORA files:

       
                      tnsnames.ora.sample 2014-05-29 12:06:08   250 hs\admin 
                      tnsnames.oRA        2014-05-29 12:14:48 3.032 network\ADMIN\sample 
                      tnsnames.ora        2018-02-14 10:50:00   608 network\ADMIN 
                      

  • the Windows main menu now contains a new "Oracle" item:

    oracle_express_windows_menu

  • the following services have been installed

    oracle_services

  • the registry has an ORACLE entry, with the ORACLE_HOME key

    oracle_registry_entry

    Note that the registry also contains many entries for Oracle Microsoft data providers, OleDb, Odbc and java entries.



1.4 - Start the Oracle services

To Start the Oracle service
   select "Start Menu | Oracle Database 11g | Start Database"

   the console window of the start database exe is displayed

oracle_start_database



1.5 - TnsPing

We can check that the server is started and listening by launching the TnsPing utility
   open an cmd.exe console window and ping your Oracle Server host ("localhost" for our local installation)

  tnsping localhost

   TnsPing confirms that the connection was established : "OK (20 msec)"

oracle_tnsping



1.6 - The SQL*Plus Client

SQL*Plus is a special Oracle client which allows us to interact with the Oracle Server. It is installed along with the Oracle XE installation (no need to download some special Oracle Client).
   "Start menu | Oracle Database 11g | Run sql command line"

   the SQL*Plus console windows is opened

oracle_run_sql_command_line

   to connect, type "connect system/<your_pass>;" and hit enter

    connect system/aaaaaa1-;

   you are connected to your database:

oracle_run_sql_connect

We could also
  • use separate rows for user and password (no ";", and the password is not displayed)
       We typed

        connect system <enter>
        aaaaaa1- <enter>

       and we are connected:

    oracle_sql_plus_connxion

  • or, Instead of using the "Start menu | Oracle" menu, we could as well
       start a command line window
       type

        sqlplus / as sysdba

       we are connected

    oracle_start_sqlplus_from_cmd_exe

    no user password required since the current user belongs to the oracle dba group.



1.7 - The Scott database

The Scott database is a demo database. Its creation script is in

  C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin  



To use this database
   open a cmd.exe console window
   CD to the scott.sql directory
   start SQL*Plus

   execute the script

    @scott

   check

    show user

   connect back as sysdba

    conn / as sysdba

   as sysdba, we can display the dba_users table

    desc dba_users

   here is the display:

oracle_execute_scott_database_script



and
   describe the dba_tables

    desc dba_tables

   here is the display:

oracle_dba_tables

   display SCOTT's table names

    select table_name
        from dba_tables
        where owner='SCOTT'

   here is the display:

oracle_select_table_name



Finally we can use the Scott database:
   we connect as scott/TIGER (the password is case sensitive) :

    conn scott/TIGER;

   we request the emp table description

    desc emp

   the emp description is:

oracle_desc_scott_emp

   we request some rows;

    select ename, sal
        from emp
        where sal> 2000;

   and here is the result :

orace_scott_emp_sal



1.8 - Using the HR demo database

The installation has created scripts for other demo database, like the hr (Human Resources) database:

oracle_hr_schema_scripts



We can install this database taking the following steps:
   start SQL*Plus

   unlock the demo database

    alter user hr account unlock ;

   HR is unlocked

oracle_unlock_hr_schema



1.9 - The Apex Oracle GUI

The Oracle Express installation also provides a graphical user interface
   select "Start Menu | Oracle | Getting Started"

   the graphic user interface page is displayed

oracle_get_started



Note
  • the url is http://127.0.0.1:8080/apex/f?p=4950
  • we tried to start the interface first, without success. Some web posts mention that we should open the 8080 port. But, after using the sql console mode, this was not necessary.



2 - The Oracle Instant Client

SQL*Plus is an Oracle Client, but to use Delphi we must install a standard Oracle Client.

We chose to install Instant Client which is a lightweight Client.
   download the Oracle Instant Client from

    http://www.oracle.com/technetwork/topics/winx64soft-089540.html

   we selected "accept" and clicked the instantclient-basiclite-windows.x64-12.2.0.1.0.zip (37 Meg) link

   the Oracle login is displayed

   enter your Oracle user / password

   the dowload dialog is displayed

orace_instant_client_zip

   save the .zip and decompress it. We used the c:\instant_client folder :

orace_instant_client_files



Among the many files are the OCI.DLL, as well as the ORAOCI*.DLL files.




3 - Oracle and Delphi

The simplest way is to initialize the connection by code:
   open Delphi, click "File | New | Vcl Forms Application"
   set the target to 64 bit Windows : "Project manager | Target Platforms | Right Click | Add platform", and select "64 bit Windows"

   drop a tFdPhysOracleDriverLink, a tFdGUIxWaitCursor and a tFdConnection on your Form
   add a tButton and connect to Oracle:

Procedure TForm1.connect_oracle_firedac_Click(SenderTObject);
  Begin
    With FDPhysOracleDriverLink1 Do
    Begin
      VendorHome := 'C:\oracle_client\';
      VendorLib := 'OCI.DLL';
      DriverId:= 'my_driver_id';
    End// with FDPhysIBDriverLink1

    With FdConnection1 Do
    Begin
      With Params Do
      Begin
        Close;
        DriverId:= 'my_driver_id';
        Add('User_Name=scott');
        Add('Password=TIGER');
        Add('Database=XE');

        Add('HostName=localhost');
      End;

      Open;
      Label1.Caption:= 'connected';
    End// with FdConnection1

   compile and run
   Label1 displays "connected"


Open any table in a dbGrid:
   drop
  • a tFdQuery
  • drop a tDataSource on the Form
    set DataSet to FdQuery1
  • drop a tDbGrid on the Form
    set DataSource to DataSource1
  • drop a tButton on the Form and open EMP

    Procedure TForm1.open_table_Click(SenderTObject);
      Begin
        With FdQuery1 Do
        Begin
          Close;
          Sql.Text:= 'Select * From Emp';
          Open;
        End;
      End// open_table_Click
   compile and run
   here is the design time form:

oracle_firedac_connection_form.png

and the execution :

oracle_firedac_connection




4 - Download the Sources

Here are the source code files: The .ZIP file(s) contain:
  • the main program (.DPROJ, .DPR, .RES), the main form (.PAS, .ASPX), and any other auxiliary form or files
  • any .TXT for parameters, samples, test data
  • all units (.PAS .ASPX and other) for units
Those .ZIP
  • are self-contained: you will not need any other product (unless expressly mentioned).
  • will not modify your PC in any way beyond the path where you placed the .ZIP (no registry changes, no path outside from the container path creation etc).
To use the .ZIP:
  • create or select any folder of your choice.
  • unzip the downloaded file
  • using Delphi, compile and execute
To remove the .ZIP simply delete the folder.

The Pascal code uses the Alsacian notation, which prefixes identifier by program area: K_onstant, T_ype, G_lobal, L_ocal, P_arametre, F_unction, C_lass etc. This notation is presented in the Alsacian Notation paper.



As usual:

  • please tell us at fcolibri@felix-colibri.com if you found some errors, mistakes, bugs, broken links or had some problem downloading the file. Resulting corrections will be helpful for other readers
  • we welcome any comment, criticism, enhancement, other sources or reference suggestion. Just send an e-mail to fcolibri@felix-colibri.com.
  • or more simply, enter your (anonymous or with your e-mail if you want an answer) comments below and clic the "send" button
    Name :
    E-mail :
    Comments * :
     

  • and if you liked this article, talk about this site to your fellow developpers, add a link to your links page ou mention our articles in your blog or newsgroup posts when relevant. That's the way we operate: the more traffic and Google references we get, the more articles we will write.



5 - References




6 - The author

Felix John COLIBRI works at the Pascal Institute. Starting with Pascal in 1979, he then became involved with Object Oriented Programming, Delphi, Sql, Tcp/Ip, Html, UML. Currently, he is mainly active in the area of custom software development (new projects, maintenance, audits, BDE migration, Delphi Xe_n migrations, refactoring), Delphi Consulting and Delph training. His web site features tutorials, technical papers about programming with full downloadable source code, and the description and calendar of forthcoming Delphi, FireBird, Tcp/IP, Web Services, OOP  /  UML, Design Patterns, Unit Testing training sessions.
Created: mar-18. Last updated: dec-2019 - 103 articles, 239 .ZIP sources, 1292 figures
Contact : Felix COLIBRI - Phone: (33)1.42.83.69.36 / 06.87.88.23.91 - email:fcolibri@felix-colibri.com
Copyright © Felix J. Colibri   http://www.felix-colibri.com 2004 - 2019. All rigths reserved
Back:    Home  Papers  Training  Delphi developments  Links  Download
the Pascal Institute

Felix J COLIBRI

+ Home
  + articles_with_sources
    + database
      + interbase
      – firebird_trans_simulator
      + sql_server
      + bdp
      – db_refactoring
      – sql_parser
      – sql_to_html
      – sniffing_interbase
      – eco_tutorial
      – dbx4_programming
      – blackfishsql
      – rave_pdf_intraweb
      – rave_reports_tutorial
      – rave_reports_video
      – embarcadero_er/studio
      + firedac
      – bde_unidac_migration
      + oracle
        – oracle_express_install
    + web_internet_sockets
    + rest_services
    + oop_components
    + uml_design_patterns
    + debug_and_test
    + graphic
    + controls
    + colibri_utilities
    + colibri_helpers
    + delphi
    + IDE
    + firemonkey
    + compilers
    + vcl
  + delphi_training
  + delphi_developments
  + sweet_home
  – download_zip_sources
  + links
Contacts
Site Map
– search :

RSS feed  
Blog