oracle

latest

Since the docker version gave problems, a new attempt is made with an oracle linux VM. Here is a work instruction.

Install OL8 via vagrant, this is what my Vagrantfile looks like

Vagrant.configure("2") do |config|
    config.vm.box = "oraclelinux/8"
    config.vm.box_url = "https://oracle.github.io/vagrant-projects/boxes/oraclelinux/8.json"
    config.vm.network "private_network", ip: "192.168.33.10"
end

Do vagrant up, and login and changes the passwords for vagrant and oracle, since they are not given.

vagrant ssh
sudo su 
passwd oracle
passwd vagrant

Enable ssh to allow Password Login. restart sshd, you can now login remotely.

Install oracle-xe. visit

yum install vim # or you will get mad
wget https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm
rpm -ivh downloads/oracle-xe-21c.1.0-1.0.x86_64.rpm
# watch the outputm it will say the exact commands, something like:
/etc/init.d/oracle-xe-21c configure

You have to come up with a password for SYS users here ! Also this will mention some interesting things.

Global Database Name:XE
System Identifier(SID):XE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/XE/XE1.log" for further details.

Connect to Oracle Database using one of the connect strings:
    Pluggable database: localhost.localdomain/XEPDB1
    Multitenant container database: localhost.localdomain
Use https://localhost:5500/em to access Oracle Enterprise Manager for Oracle Database XE

The SID name will be needed later. Now you have to set some environment variables to continue. Since I wanted these systemwide, i ended up adding this file

/etc/profile.d/oracle.sh

export ORACLE_SID="XE" export ORACLE_HOME="/opt/oracle/product/21c/dbhomeXE/" export PATH="$PATH:$ORACLE_HOME/bin" 

Now we still need to start the database with

bash /etc/init.d/oracle-xe-21c start 

Now you can use sqlplus directly. Execute this to make the interface available from outside the vm.

sqlplus system

SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

PL/SQL procedure successfully completed.

If all is ok, you should be able to connect.

hostname : 192.168.1.251 port : 1521 sid : xe username : system password :

quick-start

If you really want an oracle install as fast as possible try this site.

visit

tldr

docker pull oracleinanutshell/oracle-xe-11g
docker run -d -p 49161:1521 -e ORACLE_ALLOW_REMOTE=true oracleinanutshell/oracle-xe-11g

now you can connect with these settings :

hostname: localhost
port: 49161
sid: xe
username: system
password: oracle

Installation

This is all about oracle XE/express, because that's free ;) I tried versions 10,11 and 12. Some systems are easier than others, here is a little overview of what i tried:

• oracle XE 10 (32 bits), debian 64 bits : working but without apex:8080
• oracle XE 11G (32 bits), windows 32. Working out of the box.
• oracle XE 11G (64 bits), debian 64 bits, not working yet... lot's of problems
• oracle XE 11G (64 bits), fedora 64 bits : working but without apex:8080
• oracle XE 12G (64 bits). debian 64 bits : in progress

Overall most problems occur when trying to install on debian, but i am not going to switch distro's for oracle yet.. Since it seems to be developed on RedHat, fedora and CentOS work best. I guess solaris being from oracle also works ok ? The next sections will contain a LOT of problems and mostly solutions for debian.

Debian 64 bits, oracle express 11G

Keep with this guid for debian, since 10g is 32 bits. This is a combination of 2 sources : https://forums.oracle.com/thread/2229554 http://stackoverflow.com/questions/16545412/problems-installing-oracle-database-express-edition-11g Please follow it exactly, all steps seem to be needed.

delete old installation

If needed of course, but this guide gives you a clean slate to start with.

sudo su -
/etc/init.d/oracle-xe stop
dpkg --purge oracle-xe
rm -r /u01/app
rm /etc/default/oracle-xe
update-rc.d -f oracle-xe remove

preparation

Granted: i don't know if theses steps are really necessary, but they won't hurt. See if you have enough swap space :

su - 
cat /proc/meminfo

You will need at least 2GB, so this will probably not be needed unless you have very low memory to start with. But to enlarge the swap : To install a 1 GB swapfile (so in addition it will be 2GB) named swapfile in /, for example:

sudo dd if=/dev/zero of=/swapfile bs=1024 count=1048576
sudo mkswap /swapfile
sudo swapon /swapfile
sudo cp /etc/fstab /etc/fstab.orig
sudo echo '/swapfile swap swap defaults 0 0' >> /etc/fstab

sudo swapon -a
sudo swapon -s

Now set some kernel parameters for oracle, create a file called /etc/sysctl.d/60-oracle.conf, and fill it with this :

# Oracle 11g XE kernel parameters
fs.file-max=6815744
net.ipv4.ip_local_port_range=9000 65000
kernel.sem=250 32000 100 128
kernel.shmmax=536870912

The kernel.shmmax should contain the max memory you have , so take it from

cat /proc/meminfo

And paste the MemTotal value there, it is both Kb. Then activate with :

su -
service procps start

Verify with :

sysctl -q fs.file-max

It should contain the value specified : 6815744 Now we need to create the chkconfig tool since that is a redhat tool and debian does not have it. So create a file called /sbin/chkconfig with these contents.

#!/bin/bash
# Oracle 11gR2 XE installer chkconfig hack for Debian by Dude
file=/etc/init.d/oracle-xe
if [[ ! `tail -n1 $file | grep INIT` ]]; then
   echo >> $file
   echo '### BEGIN INIT INFO' >> $file
   echo '# Provides:             OracleXE' >> $file
   echo '# Required-Start:       $remote_fs $syslog' >> $file
   echo '# Required-Stop:        $remote_fs $syslog' >> $file
   echo '# Default-Start:        2 3 4 5' >> $file
   echo '# Default-Stop:         0 1 6' >> $file
   echo '# Short-Description:    Oracle 11g Express Edition' >> $file
   echo '### END INIT INFO' >> $file
fi
update-rc.d oracle-xe defaults 80 01

And make it executable :

chmod 755 /sbin/chkconfig
cp /usr/bin/awk /usr/bin

That last line is to anticipate the error you will get next, and that's a hard path to /bin/awk, probably also the redhat location. With settings like this, you will get the infamous errors mentioned in the troubleshooting section if you continue.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

or ...

ORA-00845: MEMORY_TARGET not supported on this system

This is handled by creating this file /etc/init.d/oracle-shm, containing

#! /bin/sh 
# /etc/init.d/oracle-shm 
# 
case "$1" in 
start) 
echo "Starting script /etc/init.d/oracle-shm" 
# Run only once at system startup  
if [ -e /dev/shm/.oracle-shm ]; then 
echo "/dev/shm is already mounted, nothing to do" 
else 
rm -f /dev/shm 
mkdir /dev/shm 
mount --move /run/shm /dev/shm 
mount -B /dev/shm /run/shm 
touch /dev/shm/.oracle-shm 
fi 
;; 
stop) 
echo "Stopping script /etc/init.d/oracle-shm" 
echo "Nothing to do" 
;; 
*) 
echo "Usage: /etc/init.d/oracle-shm {start|stop}" 
exit 1 
;; 
esac 
# 
### BEGIN INIT INFO 
# Provides: oracle-shm 
# Required-Start: $remote_fs $syslog 
# Required-Stop: $remote_fs $syslog 
# Default-Start: 2 3 4 5 
# Default-Stop: 0 1 6 
# Short-Description: Bind /run/shm to /dev/shm at system startup. 
# Description: Fix to allow Oracle 11g use AMM. 
### END INIT INFO

But note.. this did not work on another machine, that probably had too small /run/shm so i could enlarge that in the file : /etc/default/tmpfs Uncomment the SHM_SIZE parameter and make it 2GB :

SHM_SIZE=20971520

And reboot, or find a way to restart tmpfs and log it here how ;) Make oracle-shm executable :

chmod +x /etc/init.d/oracle-shm 

Now to make sure this starts at reboot... reboot. After that try :

df -kh /dev/shm

To check if it worked. Make sure you also restart oracle after running the script :

/etc/init.d/oracle-ex restart

The file that seems to work best was oracle-xe-11.2.0-1.0.x86_64.rpm.zip It is an rpm files, so of course you need to install extra software to install rpm's on debian:

apt-get install alien libiao1 unixodbc

Create a debian package of it :

alien  --to-deb --scripts oracle-xe-11.2.0-1.0.x86_64.rpm

Now run dpkg

dpkg -i oracle-xe_11.2.0-1.0_amd64.deb

If any of the steps fail you might end up with a running listener, and that hogs the ports 8080 or 1521 in a new install. You can stop the listener with :

lsnctl
LSNCTL> stop

If that gives an error, the executable occupying the port is tnslsnr so this will certainly help :

sudo killall -9 tnslsnr

Apex (application express) install

Download from : visit First set the SID :

export ORACLE_SID=XE

Then login with sqlplus :

. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
sqlplus /nolog
connect SYS as SYSDBA

When logged in run :

@apexins.sql SYSAUX SYSAUX TEMP /i/

This.....will... take.....a.........long.......time......... Pending... have not got this working yet !

troubleshooting

Installation of 11G goes ok up to the configuration step. The script is /etc/init.d/oracle-xe which fails on line : 274, which is a call to another script : $ORACLE_HOME/config/scripts/XE.sh. And that call boils down to :

ORACLE_SID=XE; export ORACLE_SID
/u01/app/oracle/product/11.2.0/xe/bin/sqlplus -s /nolog @/u01/app/oracle/product/11.2.0/xe/config/scripts/XE.sql

The .sql files are all scripts run in sqlplus, so if we run these commands line by line : The first error given is :

ORA-12162: TNS:net service name is incorrectly specified,

This is usually cause by te fact that the TNS/SID name is unknown, the default is XE, so this will probably help :

export ORACLE_SID=XE

Run sqlplus again for the next error :

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

The seconds is very probably caused by the first, so let's focus on that. It just seems oracle is not running. I can't find where it would have been started, but running it by hand just silently fails.

ORA-00845: MEMORY_TARGET not supported on this system

lsnrctl

Message 850 not found; No message file for product=network, facility=NL

You need to set ORACLE_HOME to your installation, it is the directory where bin,scripts, etc are. As an actual example, i have this in my .bashrc file :

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export PATH=:$PATH:$ORACLE_HOME/bin

But that's over to the next error on startdb.sh, this is in $ORACLE_HOME/scripts/startdb.sh. It is the script that is run when you do "Start Database" in the oracle menu's.

Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%hostname%)(PORT=%port%)))

Or start lsnrctl and run status for the same message. Try lsnrctl and run the start command.

lsnrctl
LSNRCTL> start

In $ORACLE_HOME/network/admin/listener.ora the contents were :

# listener.ora Network Configuration File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = %hostname%)(PORT = %port%))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

I have no idea what hostname, and port will contain,or where they are set, so I changed that line to the hard values :

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

This takes care of the error.

debian 64 bits, oracle 10G universal

The best success so far on debian (and only really working install) was the universal XE install. It is version 10 of the database, and the express (free) edition. This is a debian package so that gives some hope. But... it is 32 bits and it The newer ones are not available for download (12) or only for windows 32-bit (11g) or linux redhat 64 bit(also 11g). So download the universal version : oracle-xe-universal_10.2.0.1-1.1_i386.deb Words are you also need bc:i386 installed and libaio. After installation you need to configure the installation. Also make the /var/local/subsys directory to prevent an error along the way.

su
dpkg -i oracle-xe-universal_10.2.0.1-1.1_i386.deb
mkdir /var/lock/subsys
/etc/init.d/oracle-xe configure

Then startup the database with:

/etc/init.d/oracle-xe start

If you get an error about not being able to write to the log stream. You did not start as user oracle:

su
su - oracle
lsnrctl
LSNCTRL> start

If ... thank's so far oracle, you get ANOTHER error like this :

TNS-12555 / TNS-12560 / TNS-00525 Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

It turns out that the .oracle directory in /var/tmp is owned by root so :

chown -R oracle.dba /var/tmp/.oracle/

Next... Problem. The apex installation on port 8888 did not start. I installed it on port 8888, but nmap does not list that port, however it does list 1521 so oracle seems to be running. Unresolved so far, continuing with oracle developer and remote clients. additional : this is a common problem for 11g, it tried both fedora and centos because after all 11g comes as an .rpm file. But the same problems arise !!

cannot find oracle in nmap dump

I had this after setting the ip address to 192.168.2.5 instead of localhost. Make sure you also nmap on that address not localhost !!

cannot connect to local install

The message is :

TNS:listener does not currently know of SID given in connect descriptor

When trying to connect with clients, java or c interface functions.

Sqldeveloper

In absence of the web interface, this tool is quit usable. Download,unpack and go into the bin directory and run

bash ./sqldeveloper.sh

You will probably have to enter the path to a working java sdk, after that it will be stored in ~/.sqldeveloper/jdk. It has to be a JDK directory, JRE alone is not enough. be sure when you make a new connection you connect to the listener address, so localhost or 192.168.2.5 according to what you specified in listener.ora

ORA-00917: missing comma

Mainly caused by the use of single quotes in an INSERT query. I had escaped these quotes with :

esceep = content.replace("'","'\'");

But the 'standard' escape is not used in oracle. While the above will work for both mysql as mongodb, oracle needs to escape it with another single quote. So :

esceep = content.replace("'","''");

ORA-01704: string literal too long

Use setinputsizes() :

def import_page(fname,content):
    print("Importing : " + fname);
    esceep = content.replace("'","''");
    #cursor.setinputsizes(None,cx_Oracle.CLOB);
    cursor.setinputsizes(None,100000);
    ct = "INSERT INTO html_page(name,content) VALUES (:1,:2)";
    print ct
    cursor.execute(ct,(fname,esceep));

Both the uncommented as the commented versions work, but beware that sqlplus truncates the result for a select. See next chapter.

truncated output sqlplus

When inserting larger objects (CLOB) into the database, your select in sqlplus will be cut short. You can enlarge the output with :

SET SERVEROUTPUT ON SIZE 1000000;
SET LINESIZE 32768;
set pagesize 50000;
set long 50000

apache problems

This occurs when running oci_ functions in php under apache :

oci_connect(): OCIEnvNlsCreate() failed. There is something wrong with your system - please check that ORACLE_HOME and LD_LIBRARY_PATH are set and point to the right directories in /var/www/klopt.web/doc/oracle.php on line 15

The same script will run under the command line because variables like ORACLE_HOME etc are set. Not so in apache. You need to add this to the configuration.

Options FollowSymLinks
AllowOverride None
SetEnv ORACLE_HOME /u01/app/oracle/product/11.2.0/xe


Options Indexes FollowSymLinks MultiViews
AllowOverride None
Order allow,deny
SetEnv ORACLE_HOME /u01/app/oracle/product/11.2.0/xe
allow from all

Though this does not solve my complete problem.. it did lead to a different error ;-) Oracle development ==================

Oracle uses instance identifiers or System identifiers to connect to a database. So this is the SID we talk about. The instance is associated with a database In express edition i think you are stuck with one database: xe.

SQL

The SQL is rather standard, all commands i type MySQL style work. Some exceptions of course. First watch that you need to commit your changes to see them though other channels. For instance :

SQL>select * from orders;
    ID TITLE
---------- ----------------------------------------------------------------
     5 vijf
     1 eerste
     2 tweede
     3 derde
     4 vierde
     1 eerste
SQL>delete from orders where id=1;
SQL>select * from orders;
    ID TITLE
---------- ----------------------------------------------------------------
     5 vijf
     2 tweede
     3 derde
     4 vierde

Note that both are deleted, and that you cannot use the LIMIT command like in MySQL. So far so good, but if you now run a select from for instance the java program :

#:5 Name:vijf
#:1 Name:eerste
#:2 Name:tweede
#:3 Name:derde
#:4 Name:vierde
#:1 Name:eerste

Not so expected, only after you do

SQL>commit;

Will you get the expected :

#:5 Name:vijf
#:2 Name:tweede
#:3 Name:derde
#:4 Name:vierde

PL/SQL

The procedural language extension for SQL, however i never used it because i always connect from another language like java, C or php that have their own procedural constructs. Mostly you will want to run (pl/)sql scripts and not type everything on the sqlplus command line, you use the @ sign for that :

SQL> @myscipt.sql
SQL> @../myscipt.sql
SQL> @/home/kees/sql/myscipt.sql
SQL> @myscipt

The last one to show you that the .sql is filled in so it will save you same typing. This runs as script located in the directory where you started sqlplus, full path names are required otherwise, or relative paths as the example shows. Here are some major operations to be performed in PL/SQL, they will be reflected in the other connector chapters : java, C++ and php in the same order.

preliminaries

A lot of these examples are to be run in sqlplus, and it becomes tedious to connect every time. So use this format to login directly :

sqlplus username/password@192.168.2.5/xe

If you have trouble running PL/SQL scripts and get a prompt, like :

SQL> @myscipt.sql
 21

Try typing a slash "/" and return, this acts as an 'execute'. You can also directly to the scripts. I will try to include it in the examples here. Also, you can directly in the script from the command line with :

sqlplus username/password@192.168.2.5/xe @hello.sql

After this you will remain in the sqlplus cli unless you also run 'exit' as the last command Let's say hello.sql contains this :

set serveroutput on;
DECLARE
   message  varchar2(20):= 'Hello, World!';
BEGIN
   dbms_output.put_line(message);
END;
/
/
exit

The first line is needed This is because the default for pl/sql is not to display 'dbms_output'. And it is per session, so at the next connect it will be 'off' again. Also i intentionally put two slashes to make it run twice. The output of this will be :

SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 28 14:06:42 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Hello, World!

PL/SQL procedure successfully completed.

Hello, World!

PL/SQL procedure successfully completed.

Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

A little bit too much for my taste, so use the -S silent option :

sqlplus -S kees/poipoi@192.168.2.5/XE @hello.sql 
Hello, World!

PL/SQL procedure successfully completed.

Hello, World!

PL/SQL procedure successfully completed.

Still too much !!, it seems you also need to set feedback of, so the start of the file will be :

set serveroutput on;
set feedback off;
...

And finally :

Hello, World!
Hello, World!

Now one more warning, do not use set feedback off or use -S without an exit at the end. You will have to type exit blind to get out of sqlplus in that case. Neat... let's start.

create table

A working pl/sql program for creating a table, in my case a table for storing html pages by name, and since i want to limit each html chapter i think the maximum for VARCHAR2 (32k) should suffice :

set serveroutput on;
-- set feedback off;
CREATE TABLE page (name VARCHAR2(64), content VARCHAR2(16384));
/
exit;

Note i commented the feedback (comments are "--") because i want it this time. However the documented maximum value 32767 for VARCHAR2 gives an error. I had to get it down to 4000 and that in fact is becoming too small for a decent html chapter. So .. let's drop the table again :

DROP TABLE page;

There is no "IF EXISTS" clause in pl/sql, so you should do it using the PL logic of PL/SQL :

DECLARE
   does_not_exist   EXCEPTION;
   PRAGMA EXCEPTION_INIT (does_not_exist, -942);
BEGIN
   execute immediate 'DROP TABLE my_page';
EXCEPTION
   WHEN does_not_exist
   THEN 
      dbms_output.put_line('Table did not exist, so not deleted !');
END;    
/       

CREATE TABLE my_page (name VARCHAR2(64), content VARCHAR2(4000));
DESCRIBE my_page;

To run this code, you need to start sqlplus and issue @.sql. But if you make a mistake all what you get is :

Warning: Function created with compilation errors.

Which is not very helpful, so to reveal the errors, you will have to type show errors, or if you are lazy ...

sho err

Of course you could put it in the script itself... but that would mean you expect an error ;-), just take it out when it works. Oracle and C++ ==============

Since occi download only states windows i use libocci, though on linux they call it libclntsh for god knows what reason (windows is ociw32.dll) for both C and C++. When trying to download libocci i also noted that it wanted me to install odbc (?!?). If it uses ODBC what am i using libocci for then ?!?. But on this site i was assured that this is not the case. Here are the advantages of OCI. Maybe it's only occi hat used odbc ?

• OCI is optimized for queries. Transparent prefetch buffers reduce round-trips and improve performance and scalability. As a result, there is reduced memory usage on the server.
• OCI is optimized for round-trips. No-reply requests are batched until the next call is generated for the server. This allows certain calls to be lazily propagated.
• OCI is thread safe. You do not need to mutex (use mutual exclusivity locks) any of the OCI handles. ODBC is not thread safe, so you have to mutex most data structures.
• OCI provides an asynchronous event notification API for active databases.
• OCI provides enhanced array data manipulation language (DML) operations that reduce round-trips.
• OCI returns ROWIDs for all rows selected for update in a single round-trip. Using ROWID allows for more efficient SQL access.
• ODBC has no concept of sessions. OCI decouples connections, sessions and transactions. Multiple users can use a single connection; they get serialized on the connection for SQL operations. Multiple transactions can exist per user. This allows users to scale and service more users than there are connections available. Sessions and transactions can be migrated between connections to the same server.
• ODBC does not support object types, large objects (LOBs), and other new Oracle data types.
• ODBC affects server scalability. Using ODBC and having n number of concurrent users forces the server to have n number of processes service the clients if Oracle8i is operating in dedicated server mode. Your operating system may or may not support so many connections and processes.
• ODBC is a wrapper around OCI so it is slower.

So it's the other way round it seems: odbc is built upon oci. You can either do this via odbc, or as i have done here with libocci. The library is included in the oracle express installation, and is called lclntsh. Compiling the source used could be something like :

gcc cdemocp.c -I/u01/app/oracle/product/11.2.0/xe/rdbms/public -L/u01/app/oracle/product/11.2.0/xe/lib -lclntsh

A collection of demo programs was once available in the demo directory $ORACLE_HOME/rdbms/demo, but that is cut down to a handful of examples. You can still download it from here :

wget -r http://www10.atpages.jp/~tymo1914/DB/rdbms/demo/

Although let it be noted, these are older version still they can be used to shed some light on this dark matter. Some functions like oparse(), are pre-version 8.0 and should not be used. see this page. As an example, i want to set up the web database in oracle just so that will be the example here. Again, here is a full example of connecting, create database, fill some row, print the rows, delete one row, and drop the table again. One note beforehand.. some examples used OCIConnectionPoolCreate, and subsequently the poolName that was returned from that call for the call to OCILogon(). The poolname is a string that looks like :

OCI:CP:7GPobls/ydXgQAB/AQFxLB

That works but it takes an awful long time to connect, and just using the database name works instantly. So that is of course the way i use it in this example :

#include <oci.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#include <iostream>

/* docs : http://docs.oracle.com/cd/B10501_01/appdev.920/a96584/oci01int.htm */

using namespace std;

static CONST OraText *database = (OraText *)"power/XE";
static CONST OraText *username = (OraText *)"kees";
static CONST OraText *password = (OraText *)"poipoi";
static CONST OraText *appusername = (OraText *)"kees";
static CONST OraText *apppassword = (OraText *)"poipoi";

dvoid oci_error(void);
static ub4 conMin = 1;
static ub4 conMax = 3;
static ub4 conIncr = 1;

static OCIError   *errhp;
static OCIEnv     *envhp;
static OCICPool   *poolhp;
static OraText    *poolName;
static sb4         poolNameLen;

static void checkerr (OCIError *errhp, sword status);

class Oracle
{
    sword lstat;
    OCISvcCtx *svchp;
    OCIStmt   *stmthp;
    string tablename;
    bool drop;

public:
    Oracle(bool drop) {
        this->drop = drop;
        tablename = "HIGHLY_UNLIKELY_TABLE_NAME";
    }

    void run(void) {
        OCIEnvCreate (&envhp, OCI_THREADED | OCI_OBJECT, (dvoid *)0,  NULL,
        NULL, NULL, 0, (dvoid **)0);

        (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
                        (size_t) 0, (dvoid **) 0);

        (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &poolhp, OCI_HTYPE_CPOOL,
    /* CREATE THE CONNECTION POOL */
        if (lstat = OCIConnectionPoolCreate(envhp,
                        errhp,poolhp, &poolName, &poolNameLen,
                        database,(sb4)strlen((const char *)database),
                        conMin, conMax, conIncr,
                        appusername,(sb4)strlen((const char *)appusername),
                        apppassword,(sb4)strlen((const char *)apppassword)
                        ,OCI_DEFAULT))
        {
        checkerr(errhp,lstat);
        exit(1);
        }

        connect();
        //cout << ret << endl;
        bool found = list_tables();
        if (found) {
            if (!drop) {
                cout << "Table : " << tablename << " already exists, not recreating\n";
                cout << "run DROP TABLE << " << tablename << " manually or run this program with -drop option\n";
                exit(0);
            }
            drop_table();
        }
        int ret = create_table();
        fill_table();
        print_table();
        delete_row();
        print_table();
        drop_table();

        disconnect();
    }

    void disconnect(void)
    {
        checkerr(errhp, OCILogoff(svchp, errhp));
    }
    void connect(void) {
        svchp  = (OCISvcCtx *) 0;
        stmthp = (OCIStmt *)0;
        if (lstat = OCILogon(envhp, errhp, &svchp,
        (CONST OraText *)username, (ub4)strlen((const char *)username),
        (CONST OraText *)password, (ub4)strlen((const char *)password),
        (CONST OraText *)database, (ub4)strlen((const char *)database) ))
        {
            checkerr(errhp,lstat);
            exit(1);
        }
    }

    bool list_tables() {
        printf("\nList tables, and check for tablename ...");

        int colc;
        string stmtn = "SELECT object_name from user_objects where object_type='TABLE' ";
        cout << stmtn << endl;
        text  *crtstmt = (text *) stmtn.c_str();

        OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0,
                (dvoid **)0);

        int status = OCIStmtPrepare (stmthp, errhp, (text *)crtstmt,
                (ub4)strlen((const char *)crtstmt), OCI_NTV_SYNTAX,
                OCI_DEFAULT);
        checkerr(errhp, status);
        status = OCIStmtExecute (svchp, stmthp, errhp, (ub4)0, (ub4)0,
                (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT );

        checkerr(errhp, status);
#define COLUMNS 1
    OCIDefine *defhp[COLUMNS];
        int num;
        bool found=false;
        text name[100];

        checkerr(errhp, status);
        checkerr(errhp,OCIDefineByPos (stmthp, &defhp[0], errhp, (ub4)1,
                (dvoid *)&name, (sb4)sizeof(name), (ub2)SQLT_STR,
                (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT));

        printf("Displaying the records\n");
        status = OCIStmtFetch(stmthp,errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT);
        int count=100;
        while (status != OCI_NO_DATA)
    {
            if (lstat)
                checkerr(errhp, lstat);
            if (strcmp((const char *)name,tablename.c_str()) ==0) found=true;
            printf( "Name:%s\n", name);
            status = OCIStmtFetch(stmthp,errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT);
            if (count-- < 0)
                break;
        }

        checkerr(errhp, OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT));

        return found;
    }

    bool print_table() {
        int colc;
        string stmtn = "SELECT * from " + tablename;
        text  *crtstmt = (text *) stmtn.c_str();

        OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0,
                (dvoid **)0);

        int status = OCIStmtPrepare (stmthp, errhp, (text *)crtstmt,
                (ub4)strlen((const char *)crtstmt), OCI_NTV_SYNTAX,
                OCI_DEFAULT);
        checkerr(errhp, status);
        status = OCIStmtExecute (svchp, stmthp, errhp, (ub4)0, (ub4)0,
                (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT );

        checkerr(errhp, status);
#undef COLUMNS
#define COLUMNS 2
    OCIDefine *defhp[COLUMNS];
        int num;
        bool found=false;
        text name[100];

        checkerr(errhp, status);
        checkerr(errhp,OCIDefineByPos (stmthp, &defhp[0], errhp, (ub4)1,
                (dvoid *)&num, (sb4)4, (ub2)SQLT_INT,
                (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT));

        checkerr(errhp,OCIDefineByPos (stmthp, &defhp[0], errhp, (ub4)2,
                (dvoid *)&name, (sb4)sizeof(name), (ub2)SQLT_STR,
                (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT));

        printf("Displaying the records\n");
    status = OCIStmtFetch(stmthp,errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT);
        int count=100;
        while (status != OCI_NO_DATA)
        {
            if (lstat)
                checkerr(errhp, lstat);
            if (strcmp((const char *)name,tablename.c_str()) ==0) found=true;
            printf( "Num : %u, Name:%s\n", num, name);
            status = OCIStmtFetch(stmthp,errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT);
            if (count-- < 0)
                break;
        }

        checkerr(errhp, OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT));

        return found;
    }

    int delete_row() {
        printf("\nDeleting row...");

        int colc;
        string stmtn = "DELETE FROM " + tablename + " WHERE id=3";
        text  *crtstmt = (text *) stmtn.c_str();

        OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0,
                (dvoid **)0);

        int status = OCIStmtPrepare (stmthp, errhp, (text *)crtstmt,
                (ub4)strlen((const char *)crtstmt), OCI_NTV_SYNTAX,
                OCI_DEFAULT);
        checkerr(errhp, status);
        status = OCIStmtExecute (svchp, stmthp, errhp, (ub4)1, (ub4)0,
                (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT );

        checkerr(errhp, OCITransCommit(svchp,errhp,(ub4)0));
        checkerr(errhp, OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT));
    }

int drop_table() {
        printf("\nDropping table...");

        int colc;
        string stmtn = "DROP TABLE " + tablename;
        text  *crtstmt = (text *) stmtn.c_str();

        OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0,
                (dvoid **)0);

        int status = OCIStmtPrepare (stmthp, errhp, (text *)crtstmt,
                (ub4)strlen((const char *)crtstmt), OCI_NTV_SYNTAX,
                OCI_DEFAULT);
        checkerr(errhp, status);
        status = OCIStmtExecute (svchp, stmthp, errhp, (ub4)1, (ub4)0,
                (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT );

        checkerr(errhp, OCITransCommit(svchp,errhp,(ub4)0));
        checkerr(errhp, OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT));
    }

    int create_table() {
        printf("\nCreating table...");

        int colc;
        string stmtn = "CREATE TABLE " + tablename + " (id NUMBER(10), name VARCHAR2(100))";
        cout << stmtn << endl;
        text  *crtstmt = (text *) stmtn.c_str();

        OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0,
                (dvoid **)0);

        int status = OCIStmtPrepare (stmthp, errhp, (text *)crtstmt,
                (ub4)strlen((const char *)crtstmt), OCI_NTV_SYNTAX,
                OCI_DEFAULT);
        checkerr(errhp, status);
        status = OCIStmtExecute (svchp, stmthp, errhp, (ub4)1, (ub4)0,
                (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT );

        checkerr(errhp, OCITransCommit(svchp,errhp,(ub4)0));
        checkerr(errhp, OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT));
    }
int fill_table() {
        printf("\nFilling table...");

        int colc;
        string stmtn = "INSERT ALL ";
        stmtn += "INTO " + tablename + " (id,name) VALUES (1,'eerste')";
        stmtn += "INTO " + tablename + " (id,name) VALUES (2,'tweede')";
        stmtn += "INTO " + tablename + " (id,name) VALUES (3,'derde')";
        stmtn += "INTO " + tablename + " (id,name) VALUES (5,'vijfde')";
        stmtn += " SELECT 1 FROM DUAL";

        text  *crtstmt = (text *) stmtn.c_str();

        OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0,
                (dvoid **)0);

        checkerr(errhp, OCIStmtPrepare (stmthp, errhp, (text *)crtstmt,
                (ub4)strlen((const char *)crtstmt), OCI_NTV_SYNTAX,
                OCI_DEFAULT));
        checkerr(errhp, OCIStmtExecute (svchp, stmthp, errhp, (ub4)1, (ub4)0,
                (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT ));
        checkerr(errhp, OCITransCommit(svchp,errhp,(ub4)0));
        checkerr(errhp, OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT));
    }
};

int main(int argc, char *argv[])
{
    bool drop=false;

    if (argc > 1) {
        if (strcmp(argv[1],"-drop") == 0) drop=true;
    }
    Oracle o(drop);

    o.run();
    return 0;
}

void checkerr(OCIError *errhp, sword status)
{
    text errbuf[512];
    sb4 errcode = 0;

    switch (status)
    {
    case OCI_SUCCESS:
    break;
    case OCI_SUCCESS_WITH_INFO:
    (void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
    break;
    case OCI_NEED_DATA:
    (void) printf("Error - OCI_NEED_DATA\n");
    break;
    case OCI_NO_DATA:
    (void) printf("Error - OCI_NODATA\n");
    break;
    case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %.*s\n", 512, errbuf);
    break;
    case OCI_INVALID_HANDLE:
    (void) printf("Error - OCI_INVALID_HANDLE\n");
    break;
    case OCI_STILL_EXECUTING:
    (void) printf("Error - OCI_STILL_EXECUTE\n");
    break;
    case OCI_CONTINUE:
    (void) printf("Error - OCI_CONTINUE\n");
    break;
    default:
    break;
    }
}

Oracle and java

java connection

This is a page about connecting to an oracle database server with java (JDBC).

Java Installation

This is a java example, but the problems described here are probably the same for any client. First the jdbc jar is needed to run this program. sauce The jar file is called ojdbc14.jar, get it from here The installation from scratch was troublesome, so if you have any problem, search in the troubleshooting section below to see if your problem is described there. For now i assume you have a working setup. Here is the sample java file OracleJDBC.java:

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

import java.sql.ResultSet;
import java.sql.Statement;

public class OracleJDBC {
    static boolean drop=false;
    static Connection connection;
    static String tablename="HIGHLY_UNLIKELY_TABLE_NAME";
    public static void main(String[] argv) {

        System.out.println("-------- Oracle JDBC Connection Testing ------");

        if (argv.length > 0 && argv[0].equals("-drop")) drop=true;

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {

            System.out.println("Where is your Oracle JDBC Driver?");
            e.printStackTrace();
            return;

        }

        System.out.println("Oracle JDBC Driver Registered!");

        connection = null;

        try {
            connection = DriverManager.getConnection(
                    "jdbc:oracle:thin:@192.168.2.5:1521:XE", "kees",
                    "poipoi");

        } catch (SQLException e) {

            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            return;

        }

        if (connection != null) {
            //System.out.println("You made it, take control your database now!");
        } else {
            System.out.println("Failed to make connection!");
        }

        try {
            if (ListTables() == true) {
               if (drop==false) {
                    System.out.println("table " + tablename + " already exist, not recreating, run DROP TABLE " + tablename + " manually");
                    System.out.println("or run this program with the -drop option");
                    System.exit(0);
                }
                DropTable();
            }
            CreateTable();
            FillTable();
            PrintTable();
            DeleteRow();
            PrintTable();
            DropTable();
            connection.close();
        } catch (SQLException e) {
            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            return;
        }
    }

    static boolean ListTables() throws SQLException
    {
        boolean found=false;
        Statement stmt = null;
        ResultSet rs = null;

        stmt = connection.createStatement();
        //only for Oracle
        rs = stmt.executeQuery("select object_name from user_objects where object_type = 'TABLE'");

        while (rs.next()) {
            String tableName = rs.getString(1);
            System.out.println("tableName=" + tableName);
            if (tableName.equalsIgnoreCase(tablename) == true) found = true;
        }

        stmt.close();
        return found;
    }

    static void DropTable() throws SQLException
    {

        String sql = "DROP TABLE " + tablename;
        Statement statement = connection.createStatement();
        statement.execute(sql);
    }

    static void CreateTable() throws SQLException
    {

        String sql = "CREATE TABLE " + tablename + "(id NUMBER(10), name VARCHAR2(100))";
        Statement statement = connection.createStatement();
        statement.execute(sql);
    }

    static void PrintTable() throws SQLException
    {
        Statement stmt = null;
        ResultSet rs = null;

        stmt = connection.createStatement();
        rs = stmt.executeQuery("select * from " + tablename);

        while (rs.next()) {
            String id = rs.getString(1);
            String name = rs.getString(2);
            System.out.print("id=" + id);
            System.out.println(",name=" + name);
        }

        stmt.close();
    }

    static void DeleteRow() throws SQLException
    {
        String sql = "DELETE FROM " + tablename + " WHERE id=3";
        Statement statement = connection.createStatement();
        statement.execute(sql);
    }

    static void FillTable() throws SQLException
    {
        String sql = "INSERT ALL ";
        sql += "INTO " + tablename + " (id,name) VALUES (1,'eerste') ";
        sql += "INTO " + tablename + " (id,name) VALUES (2,'tweede') ";
        sql += "INTO " + tablename + " (id,name) VALUES (3,'derde') ";
        sql += "INTO " + tablename + " (id,name) VALUES (5,'vijfde') ";
        sql += " SELECT 1 FROM DUAL";
        Statement statement = connection.createStatement();
        statement.execute(sql);
    }
}

And here is how to run it :

java -cp ojdbc14.jar:. OracleJDBC

Again, this program demonstrates the main operations of connecting, list tables, create a new table, print the contents, delete one row, and drop the table.

ojdbc troubleshooting

Now here is a list of problems I encountered while trying to get this to work.

No connection

-------- Oracle JDBC Connection Testing ------
Oracle JDBC Driver Registered!
Connection Failed! Check output console
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Ok, this means it cannot understand XE in the connection string :

"jdbc:oracle:thin:@192.168.2.5:1521:XE", "kees", ...

So i changed it to a SID_NAME that was present, see the listener.ora file earlier in this page.

"jdbc:oracle:thin:@192.168.2.5:1521:PLSExtProc", "kees", ...

not a usable SID_NAME

Using PLSExtProc lead to the next error :

-------- Oracle JDBC Connection Testing ------
Oracle JDBC Driver Registered!
Connection Failed! Check output console
java.sql.SQLException: No more data to read from socket

The PLSExtProc seems to be some other service ? TODO (find out what it does) but it does not handle any API functions, so the interwebz gave me a hint on this: add another name to the listener.ora file like this :

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = XE)
      (ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
      (PROGRAM = extproc)
    )
  )

And behold, this gave another error.

invalid username

Oracle JDBC Driver Registered!
Connection Failed! Check output console
java.sql.SQLException: ORA-01017: invalid username/password; logon denied

This figures, on windows you have the apex interface and you are given the change to add a user. We could probably use the SYSTEM account with the password given at installation, but adding a user is a better solution. I did it with the sqldeveloper program, of course here you will have to login as SYSTEM when connecting, right click on the user table and create user. Be sure you put quotes around your password or it will fail to create. Or only use letters and numbers, quotes do not seems to be needed then.

connect string

sqlplus SYSTEM/passwd@192.168.2.5/XE

Or :

export ORACLE_SID=XE
sqlplus /nolog

On to the next error :

Insufficient rights

Oracle JDBC Driver Registered!
Connection Failed! Check output console
java.sql.SQLException: ORA-01045: user KEES lacks CREATE SESSION privilege; logon denied

Were getting there, it could have been done in the dialog for create user. But if you do edit user, you can choose the privileges tab and grant all. Here is how that looks like in sql

-- USER SQL
ALTER USER "KEES" 
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT UNLOCK ;

-- SYSTEM PRIVILEGES
GRANT ALTER TABLESPACE TO "KEES" ;
GRANT EXECUTE ANY PROCEDURE TO "KEES" ;
GRANT DROP ANY TRIGGER TO "KEES" ;
GRANT DROP ANY VIEW TO "KEES" ;
GRANT CREATE USER TO "KEES" ;
GRANT FLASHBACK ANY TABLE TO "KEES" ;
GRANT CREATE ANY OUTLINE TO "KEES" ;
GRANT DROP ANY CONTEXT TO "KEES" ;
GRANT FORCE TRANSACTION TO "KEES" ;
... much , much more

Next,... no tables shown :

no output

Well the most obvious would be that there are no tables, so let's create one in the interface, one through java and one through C++. Note that if you login as 'kees' you are not listed in the "other users" list, which is logical, but nevertheless confusing to say the least. Why not just call it "users" oracle ? From sqldeveloper, choose 'tables' and that explains the empty list. So create one and remember the sql it spawns, and let's make a useful database as well.

CREATE TABLE "KEES"."CLIENTS" 
  ( "ID" NUMBER NOT NULL ENABLE, 
    "COMPANY" VARCHAR2(1024 BYTE) NOT NULL ENABLE, 
    "ADDRESS" VARCHAR2(20 BYTE), 
     CONSTRAINT "CLIENTS_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;

That's a little cryptic but the main idea is readable. Also now our java program finally works :

-------- Oracle JDBC Connection Testing ------
Oracle JDBC Driver Registered!
You made it, take control your database now!
tableName=CLIENTS

Oracle JDBC

So on to programming java for Oracle, list table we have seen before. sauce

ojdbc create table

On to creating a table in java, but a lot simpler :

void CreateTable() throws SQLException
  {

      String sql = "CREATE TABLE orders (id NUMBER(11), title VARCHAR2(64))";
      Statement statement = connection.createStatement();
      statement.execute(sql);
  }

Fit into the former OracleJDBC.java program, this also works. If you look into the SQL tab on sqldeveloper you will see what oracle adds for itself :

CREATE TABLE "KEES"."ORDERS" 
 (    "ID" NUMBER(11,0), 
  "TITLE" VARCHAR2(64 BYTE)
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;

That's more or less the same as the client table, so don't bother putting all that in.

inserting

Well if it's all just SQL sent over a JDBC link, we don't need to go into deleting tables etc, but what is interesting is how results are handled. I need some more data so let's fill that in first. I typed this one MySQL style and it worked in one go :

void FillTable() throws SQLException
{
    String sql = "INSERT INTO orders (id,title) VALUES (1,'eerste')";
    Statement statement = connection.createStatement();
    statement.execute(sql);
}

querying results

I filled in the rest in sqldeveloper as it faster. Now let's list the contents.

... pending..

Oracle and python

python Installation

pip install cx_Oracle

Easy enough ?. now see if you can run this script.

#!/usr/bin/python
import cx_Oracle

con = cx_Oracle.connect("kees/poipoi@192.168.2.5/XE")
print con.version

con.close()

When run it should say something like

11.2.0.2.0