Saturday, November 21, 2009

Quick Tip: Counting Cursors

Counting the number of open cursors in a database instance can be a confusing activity. Perhaps an application has received an "ORA-01000: maximum open cursors exceeded" error message or perhaps you simply want to get an idea of how many cursors an application has open at some point in its execution. No matter the reason, you've attempted to determine the number of open cursors and have found what seems like incorrect or confusing results (i.e. the value is too high!).

Your first inclination may be to simply execute a query such as the following to determine how many cursors are currently open in an instance:

select count(*) from v$open_cursor

- or -

select sid, count(*) from v$open_cursor group by sid order by sid

This seems like a perfectly reasonable thing to do given that you want to find out how many open cursors there are. However, it is just about a guarantee that this query will not give you what you really want. Why is this so? Some reasons for this are:

  • Sessions that are no longer connected can be reported
  • Cursors that are closed as far as the application is concerned but are cached on the server side can be reported (for example, by using release_cursor=no in Pro*C/C++)
  • Cursors cached by PL/SQL can be counted
  • Cursors from recursive SQL can be counted

In order to get a more representative value for the number of actual open cursors, try a query such as this:

select   a.sid,
         a.value,
         b.name
from     v$sesstat a,
         v$statname b
where    a.statistic# = b.statistic#
and      b.name = 'opened cursors current'
and      a.value != 0
order by a.sid

This query should be a good starting point and can be altered as necessary to suit your needs.

Saturday, September 26, 2009

Getting Started with Oracle Pro*C on Linux (Instant Client Zip Files)

The Oracle Pro*C Precompiler is a popular choice for developing Oracle applications in the C/C++ languages. The primary advantage of using the precompiler is that it allows you to embed SQL (and PL/SQL) directly into your application. Used in combination with Oracle Instant Client packages you can easily begin to develop applications using the Pro*C Precompiler capabilities. For information on getting started using Pro*C on Windows, please see my previous post entitled "Oracle Pro*C on Windows with Express Edition Products".

Many of the components used in the previous post are also used here and the steps are similar (if not the same). For this post, all components are installed on a single host (oel02) running Oracle Enterprise Linux. The components used are (valid at time of posting):

  • Oracle Database 10g Express Edition (available here)
  • Oracle Enterprise Linux (available here)
  • Oracle Instant Client 11.2.0.1 Packages for Linux x86 (available here)
  •     Instant Client Package - Basic Lite
  •     Instant Client Package - SDK
  •     Instant Client Package - Precompiler
  •     Instant Client Package - SQL*Plus

NOTE: The precompiler uses the "standard" Oracle Technology Network license rather than the Instant Client license. Be sure to review the license!

You can, of course, allow for some variation in the above; however, you may then need to make adjustments to the steps that follow. For example, the database can be on another host and/or platform. In order to keep things as simple as possible I have elected to use a single host for everything. In order for the sample code to work unaltered you should have access to the HR sample schema user (included in Express Edition). If you require character set or language support not provided by the "Basic Lite" Instant Client package you should use the "Basic" package. In addition, version 11.1.0.7 of the components should work as well (though I have not tested it).

Installing the Instant Client Packages

I downloaded the following Instant Client packages using the above provided link to my home directory on oel02:

  • instantclient-basiclite-linux32-11.2.0.1.zip
  • instantclient-precomp-linux32-11.2.0.1.zip
  • instantclient-sdk-linux32-11.2.0.1.zip
  • instantclient-sqlplus-linux32-11.2.0.1.zip

Installing each one is simply a matter of unzipping each file:

[markwill@oel02 ~]$ unzip instantclient-basiclite-linux32-11.2.0.1.zip
[markwill@oel02 ~]$ unzip instantclient-precomp-linux32-11.2.0.1.zip
[markwill@oel02 ~]$ unzip instantclient-sdk-linux32-11.2.0.1.zip
[markwill@oel02 ~]$ unzip instantclient-sqlplus-linux32-11.2.0.1.zip

The act of unzipping the files will create an "instantclient_11_2" directory in the directory where the files are unzipped (/home/markwill in my case). The complete list of files installed after unzipping each file is as follows:

[markwill@oel02 ~]$ cd instantclient_11_2/
[markwill@oel02 instantclient_11_2]$ pwd
/home/markwill/instantclient_11_2
[markwill@oel02 instantclient_11_2]$ find .
.
./libsqlplusic.so
./libclntsh.so.11.1
./cobsqlintf.o
./ojdbc6.jar
./sqlplus
./adrci
./libnnz11.so
./SQLPLUS_README
./PRECOMP_README
./libociicus.so
./ojdbc5.jar
./sdk
./sdk/demo
./sdk/demo/demo_procob_ic.mk
./sdk/demo/occiobj.cpp
./sdk/demo/occidemo.sql
./sdk/demo/occiobj.typ
./sdk/demo/occidemod.sql
./sdk/demo/demo_proc_ic.mk
./sdk/demo/demo.mk
./sdk/demo/occidml.cpp
./sdk/demo/procdemo.pc
./sdk/demo/procobdemo.pco
./sdk/demo/cdemo81.c
./sdk/procob
./sdk/rtsora
./sdk/SDK_README
./sdk/ott
./sdk/ottclasses.zip
./sdk/include
./sdk/include/orid.h
./sdk/include/occiAQ.h
./sdk/include/oratypes.h
./sdk/include/nzerror.h
./sdk/include/sqlkpr.h
./sdk/include/oci1.h
./sdk/include/sql2oci.h
./sdk/include/ocikpr.h
./sdk/include/ocidef.h
./sdk/include/oci.h
./sdk/include/ldap.h
./sdk/include/ort.h
./sdk/include/ocixmldb.h
./sdk/include/ocidfn.h
./sdk/include/ocixstream.h
./sdk/include/oraca.h
./sdk/include/sqlcpr.h
./sdk/include/odci.h
./sdk/include/ori.h
./sdk/include/occiCommon.h
./sdk/include/occi.h
./sdk/include/ociapr.h
./sdk/include/sqlca.h
./sdk/include/occiObjects.h
./sdk/include/sqlucs2.h
./sdk/include/occiControl.h
./sdk/include/sqlda.h
./sdk/include/xa.h
./sdk/include/ocidem.h
./sdk/include/oci8dp.h
./sdk/include/ociextp.h
./sdk/include/occiData.h
./sdk/include/ociap.h
./sdk/include/nzt.h
./sdk/include/orl.h
./sdk/include/oro.h
./sdk/include/sqlapr.h
./sdk/proc
./libocci.so.11.1
./libocijdbc11.so
./genezi
./BASIC_LITE_README
./xstreams.jar
./libsqlplus.so
./glogin.sql
./precomp
./precomp/admin
./precomp/admin/pcscfg.cfg
./precomp/admin/pcbcfg.cfg
[markwill@oel02 instantclient_11_2]$

Configure the Environment

One of the nice things about using Pro*C on Linux (and UNIX) is that we do not normally have to configure the Pro*C configuration file (pcscfg.cfg) as would generally be necessary in Windows. As a convenience for setting environment variables (such as the PATH), in my home directory I have created a file I use to do this (I'm using the bash shell here):

[markwill@oel02 ~]$ cat oic11.env
export ORACLE_BASE=/home/markwill
export ORACLE_HOME=$ORACLE_BASE/instantclient_11_2
export LD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$ORACLE_HOME:$ORACLE_HOME/sdk:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:~/bin
[markwill@oel02 ~]$

I can then set my environment to use the newly installed Oracle components as follows:

[markwill@oel02 ~]$ . ./oic11.env

Test the Oracle Installation

Since SQL*Plus was installed as part of the above packages, it can be used to quickly and easily test the installation. Here I connect to the Express Edition database as the HR sample schema user using the EZConnect syntax (host:port/Service_Name):

[markwill@oel02 ~]$ sqlplus hr/hr@oel02:1521/XE

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 26 13:27:59 2009

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

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select user from dual;

USER
------------------------------
HR

SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
[markwill@oel02 ~]$

Create a Test Application

Now that I have tested that the Oracle software is working correctly, I create a simple test application. I call the test application "proctest" and create a new directory to hold the files:

[markwill@oel02 ~]$ mkdir -p Projects/proc/proctest
[markwill@oel02 ~]$ cd Projects/proc/proctest

Here's the content of the proctest.pc source file:

[markwill@oel02 proctest]$ cat proctest.pc
/*
** standard include files for Pro*C application
*/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include <sqlda.h>
#include <sqlcpr.h>

#define NAME_LEN 30
#define ERR_LEN 512

VARCHAR username[NAME_LEN];
VARCHAR password[NAME_LEN];
VARCHAR database[NAME_LEN];

/*
** host variables to hold results of query
*/
int     dept;
VARCHAR fname[NAME_LEN];
VARCHAR lname[NAME_LEN];
int     sal;
int     rn;

/*
** indicator variables used to determine null-ness
*/
short dept_ind;
short fname_ind;
short lname_ind;
short sal_ind;
short rn_ind;

/*
** standard Pro*C error handler function
*/
void sql_error(char *msg)
{
  char err_msg[ERR_LEN];
  size_t buf_len, msg_len;

  EXEC SQL WHENEVER SQLERROR CONTINUE;

  printf("\n");

  if (msg)
  {
    printf("%s\n", msg);
  }

  buf_len = sizeof (err_msg);
  sqlglm(err_msg, &buf_len, &msg_len);
  printf("%.*s", msg_len, err_msg);

  EXEC SQL ROLLBACK RELEASE;

  exit(EXIT_FAILURE);
}

int main(int argc, char *argv[])
{
  /*
  ** setup username, password and database (ezconnect format)
  */
  strncpy((char *) username.arr, "hr", NAME_LEN);
  username.len = (unsigned short) strlen((char *) username.arr);

  strncpy((char *) password.arr, "hr", NAME_LEN);
  password.len = (unsigned short) strlen((char *) password.arr);

  strncpy((char *) database.arr, "oel02:1521/XE", NAME_LEN);
  database.len = (unsigned short) strlen((char *) database.arr);

  /*
  ** register the error handler function
  */
  EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error: \n");

  /*
  ** attempt to connect to the database
  */
  EXEC SQL CONNECT :username IDENTIFIED BY :password USING :database;

  /*
  ** indicate we want to break out of the loop when we get a no data
  ** found message (i.e. at end of fetch)
  */
  EXEC SQL WHENEVER NOT FOUND DO break;

  /*
  ** declare and open cursor using analytic function
  ** to get "top 3" in department by highest salary
  */
  EXEC SQL DECLARE emp_cursor CURSOR FOR
    select   *
    from
    (
      select   department_id,
               first_name,
               last_name,
               salary,
               row_number() over (partition by department_id
                                  order by salary desc) rn
      from     employees
      where    department_id is not null
    )
    where   rn <= 3;

  EXEC SQL OPEN emp_cursor;

  /*
  ** loop through cursor fetching results
  ** and printing as we go
  */
  for (;;)
  {
    EXEC SQL FETCH emp_cursor
      INTO :dept:dept_ind,
           :fname:fname_ind,
           :lname:lname_ind,
           :sal:sal_ind,
           :rn:rn_ind;

    fname.arr[fname.len] = '\0';
    lname.arr[lname.len] = '\0';

    printf("%d, %s %s, %d, %d\n", dept, fname.arr, lname.arr, sal, rn);
  }

  /*
  ** clean-up and exit
  */
  EXEC SQL CLOSE emp_cursor;

  EXEC SQL COMMIT RELEASE;

  return EXIT_SUCCESS;
}
[markwill@oel02 proctest]$

The sample application is pretty bland - the only real point of interest is the use of an analytic function to get the "top 3" ranking of salaries by department. The use of the analytic function will allow us to see how options can be passed to the Pro*C precompiler.

Building the Sample

Rather than craft a custom Makefile or enter the various build/compiler commands by hand, the demonstration Makefile that is provided as part of the precompiler package installed earlier can be used with only small modification. To use this Makefile, copy it into the directory with the source file. On my system this was done as follows:

[markwill@oel02 proctest]$ cp $ORACLE_HOME/sdk/demo/demo_proc_ic.mk .

I use VIM to edit the file. Of course you can use whatever editor you wish.

[markwill@oel02 proctest]$ vi demo_proc_ic.mk

The important part is listed in the "NOTES" section in the Makefile:

# NOTES:
#    1. Please change "cc/CC" and the "InstantClient directories" to point to
#       appropiate locations on your machine before using this makefile.

Because the CC and cc entries are already correct, I did not alter them. I did, however, change the Instant Client directory entries as follows:

# InstantClient Directories.
ICSDKHOME=$(ORACLE_HOME)/sdk/
ICLIBHOME=$(ORACLE_HOME)/

By using the ORACLE_HOME environment variable (which is set in my oic11.env file) in the Makefile I do not need to hard-code the actual path.

Before building the sample, take a minute or two to review the Makefile comments. I build the sample using the following command-line:

[markwill@oel02 proctest]$ make -f demo_proc_ic.mk build PROCFLAGS="common_parser=yes" \
> EXE=proctest OBJS="proctest.o"

Notice how PROCFLAGS is used to pass the "common_parser=yes" to the proc binary (i.e the Pro*C program itself). The EXE option determines the name of the binary executable produced and the OBJS option determines what object files are needed. In this case the options are simple, but larger and more complex projects likely use more than a single object file and possibly other Pro*C options.

Once the build has completed test the application:

[markwill@oel02 proctest]$ ./proctest
10, Jennifer Whalen, 4400, 1
20, Michael Hartstein, 13000, 1
20, Pat Fay, 6000, 2

[ snip ]

100, Nancy Greenberg, 12000, 1
100, Daniel Faviet, 9000, 2
100, John Chen, 8200, 3
110, Shelley Higgins, 12000, 1
110, William Gietz, 8300, 2
[markwill@oel02 proctest]$

One final addition I make to the Makefile is creating a new target called "dust". A Makefile traditionally has a "clean" target which can be used to remove various (non-source!) files. These files are generally used during the build process, but are not needed by the final executable. The "clean" target also removes the executable, however. Since I like the idea of being able to clean the no longer necessary files, but want to keep the executable, I add a target called "dust". Here's the "end" of the Makefile with the "dust" target added:

# Clean up all executables, *.o and generated *.c files
clean: $(CLNCACHE)
        $(REMOVE) $(PROCDEMO) $(PROCDEMO).o $(PROCDEMO).c $(PROCDEMO).lis

dust: $(CLNCACHE)
        $(REMOVE) $(PROCDEMO).o $(PROCDEMO).c $(PROCDEMO).lis

cleancache:
        $(REMOVE) $(CACHEDIR)
        $(REMOVE) $(ICLIBHOME)libclntsh$(SO_EXT)

Of course, adding this target is purely optional; however, after building the sample, I then "dust" the directory:

[markwill@oel02 proctest]$ make -f demo_proc_ic.mk dust PROCDEMO=proctest
rm -rf SunWS_cachea
rm -rf /home/markwill/instantclient_11_2/libclntsh.so
rm -rf proctest.o proctest.c proctest.lis
[markwill@oel02 proctest]$

This then leaves the directory a bit cleaner with only the Makefile, source file, and executable:

[markwill@oel02 proctest]$ ls -al
total 28
drwxrwxr-x  2 markwill markwill 4096 Sep 26 14:31 .
drwxrwxr-x  3 markwill markwill 4096 Sep 26 13:40 ..
-rw-rw-r--  1 markwill markwill 3617 Sep 26 14:31 demo_proc_ic.mk
-rwxrwxr-x  1 markwill markwill 9798 Sep 26 14:29 proctest
-rw-rw-r--  1 markwill markwill 2900 Sep 26 14:14 proctest.pc
[markwill@oel02 proctest]$

Wednesday, September 16, 2009

ODAC 11.1.0.7.20 Production Release Available

The new ODAC 11.1.0.7.20 Production release is now available. See these links for more information and to download.

- Download
- New Features List
- ODT SQL Tuning Advisor Viewlet
- ODT Oracle Performance Analyzer Viewlet
- ODP.NET 11.1.07.20 White Paper

Happy coding.

Friday, September 04, 2009

ODP.NET: The provider is not compatible with the version of Oracle client

One potentially perplexing error that may be raised when using Oracle Data Provider for .NET (ODP.NET) is "The provider is not compatible with the version of Oracle client". The reason I say "potentially perplexing" is that the error can be raised in a situation that doesn't necessarily seem to agree with the wording of the message. More on that later.

ODP.NET consists of both managed and unmanaged components. The managed component is the Oracle.DataAccess.dll and one of the key unmanaged components is the OraOpsXX.dll which I refer to as the bridge dll. The exact name of OraOpsXX.dll depends on the ODP.NET version as well as the .NET Framework version. In this post I am using ODAC 11.1.0.6.21 which includes ODP.NET versions targeted to the .NET Framework 1.x and 2.x versions. Beginning with the 10.2 versions of ODP.NET the .NET Framework major version is pre-pended to the ODP.NET version to differentiate between 1.x and 2.x of the .NET Framework. Therefore, the Oracle.DataAccess.dll I am using will report 2.111.6.20 as its version number. The corresponding OraOpsXX.dll will be named OraOps11w.dll and is found in the %ORACLE_HOME%\bin directory if using a full install or (typically) in the root folder of an Instant Client install.

I'll show what I think are the three most common reasons for this error. In order to do so, I use a (very) simple C# console application:

using System;
using System.Data;
using Oracle.DataAccess.Types;
using Oracle.DataAccess.Client;

namespace NotCompatibleTest
{
  class Program
  {
    static void Main(string[] args)
    {
      /*
       * connection string using EZCONNECT format
       * be sure to change for your environment
       */
      string constr = "user id=hr;" +
                      "password=hr;" +
                      "data source=liverpool:1521/V112;" +
                      "enlist=false;" +
                      "pooling=false";

      /*
       * create and open connection
       */
      OracleConnection con = new OracleConnection(constr);
      con.Open();

      /*
       * write server version to console
       */
      Console.WriteLine("Connected to Oracle version {0}",
                        con.ServerVersion);

      /*
       * explicit clean-up
       */
      con.Dispose();
    }
  }
}

As you can see this simply connects to a database, writes the server version to the console window, and exits. However, it is sufficient to use ODP.NET and for the purposes here. I simply execute this sample in debug mode from within Visual Studio for each of the "tests".

Cause 1: OraOpsXX.dll is Wrong Version (and Hence the Client is Too)

This is a typical case and the message text makes the most sense for this case. Here, the correct version can not be found. How might this occur? One easy way is when you develop your application using 11.1.0.6 of Oracle Client and ODP.NET and then deploy to a machine that has a lower version of Oracle Client and ODP.NET installed. This is what the error looks like in debug mode with an unhandled TypeInitializationException when instantiating the OracleConnection object in the sample code:

PNC_Cause1

 

Cause 2: OraOpsXX.dll is Missing

In order to simulate OraOpsXX.dll missing, I rename my OraOps11w.dll in %ORACLE_HOME%\bin to OraOps11wXX.dll and execute the sample. Sure enough, I get the same error as above. Here the message may not make as much sense. Instead of "The provider is not compatible with the version of Oracle client" it might be better if the message indicated the real issue is that OraOpsXX.dll can't be located.

Cause 3: The tricky one

This cause is certainly less intuitive than either Cause 1 or Cause 2. As mentioned earlier, OraOpsXX.dll is unmanaged code. It also has a dependency on the Microsoft C runtime, in particular version 7 of the C runtime which lives in msvcr71.dll and which many systems have on the system path. However, if that file is not on the system path or in the same directory as OraOpsXX.dll you will receive the "The provider is not compatible with the version of Oracle client" message!

If you are receiving "The provider is not compatible with the version of Oracle client" messages in your environment perhaps it is due to one of the three causes here.

Sunday, August 16, 2009

Getting Started with OCCI (Linux Version with RPM Instant Client)

This is a follow-on post to the "Getting Started with OCCI (Linux Version)" and "Getting Started with OCCI (Windows Version)" posts. In the previous post on getting started with OCCI on Linux, I illustrated using the .zip file method of installing the Oracle Instant Client software. In this post I will cover using the .rpm file downloads rather than the .zip files. Much of the content is intentionally the same, however. This means I will need to use the "root" user to install the .rpm Instant Client packages. With the .zip file method this is not required.

The steps should be general enough to easily mould them to your environment and/or needs.

The Oracle C++ Call Interface, also known as OCCI, is an application programming interface (API) built upon the Oracle Call Interface (OCI - another lower level API from Oracle). One of the goals of OCCI is to offer C++ programmers easy access to Oracle Database in a fashion similar to what Java Database Connectivity (JDBC) affords Java developers. If you would like to learn more about what OCCI is (and isn't), pay a visit to the OCCI documentation on Oracle Technology Network (OTN) here:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28390/toc.htm

My goal with this "Getting Started" post is to give you one method of setting up an environment in which you can use OCCI to develop C++ applications under Linux that access Oracle Database. I am not in any way covering all possible scenarios or delving deep into OCCI itself. Please note that the database itself can be on any supported host.

The Environment

Your environment is likely to differ from mine; however, it is important to be familiar with the various components in the environment used here so that you can make adaptations as necessary for your specific environment.

  • Oracle Database Server/Host: oel01 (Oracle Enterprise Linux 32-bit server)
  • Oracle Database: SID value of OEL11GR1, Service Name value of OEL11GR1.SAND, version 11.1.0.7
  • Development Machine: Hostname of "gerrard", Oracle Enterprise Linux 32-bit (2.6.9 kernel)
  • Development IDE: VIM (any text editor or IDE you can configure should work)
  • Oracle Client: Oracle Instant Client with OCCI

Important Information

One of the most crucial attributes of working with OCCI is that you must ensure that all of the components of the development environment and the runtime environment are supported combinations and correct versions. I can not emphasize this enough. If you deviate from this, you will almost certainly find trouble! In order to find the correct combinations of products and versions, see the following links on OTN:

Download the Correct Packages

The Linux packages come in two varieties: a .zip file or a .rpm file. For this install I have used the .rpm files.

From the download links above, you should download the following components to your development machine.

  • Instant Client Package – Basic: oracle-instantclient11.1-basic-11.1.0.7.0-1.i386.rpm
  • Instant Client Package - SDK: oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm
  • Instant Client Package - SQL*Plus: oracle-instantclient11.1-sqlplus-11.1.0.7.0-1.i386.rpm  (optional, but I always install it)

NOTE: I am using the "Basic" version of the main Instant Client packages and not the "Basic Lite" version for this install. Whilst the "Basic Lite" version is a smaller download, the oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm package wants the full "Basic" rather than the "Basic Lite" package as a prerequisite. You could probably force the install anyway by using "--nodeps" but using the full package is not any more difficult. It also provides additional language support not present in the "lite" package.

Install the Instant Client Packages

Installing the Instant Client packages provided as .rpm files is simply a matter of downloading them and then using rpm in the normal fashion – there's really not too much to go wrong here! I downloaded each of them (3 total) into a directory called "/downloads" on "gerrard". Each of the packages will provide the following files:

[root@gerrard downloads]# rpm -qlp oracle-instantclient11.1-basic-11.1.0.7.0-1.i386.rpm
/usr/lib/oracle/11.1/client/bin/adrci
/usr/lib/oracle/11.1/client/bin/genezi
/usr/lib/oracle/11.1/client/lib/libclntsh.so.11.1
/usr/lib/oracle/11.1/client/lib/libnnz11.so
/usr/lib/oracle/11.1/client/lib/libocci.so.11.1
/usr/lib/oracle/11.1/client/lib/libociei.so
/usr/lib/oracle/11.1/client/lib/libocijdbc11.so
/usr/lib/oracle/11.1/client/lib/ojdbc5.jar
/usr/lib/oracle/11.1/client/lib/ojdbc6.jar

[root@gerrard downloads]# rpm -qlp oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm
/usr/include/oracle/11.1/client/nzerror.h
/usr/include/oracle/11.1/client/nzt.h
/usr/include/oracle/11.1/client/occi.h
/usr/include/oracle/11.1/client/occiAQ.h
/usr/include/oracle/11.1/client/occiCommon.h
/usr/include/oracle/11.1/client/occiControl.h
/usr/include/oracle/11.1/client/occiData.h
/usr/include/oracle/11.1/client/occiObjects.h
/usr/include/oracle/11.1/client/oci.h
/usr/include/oracle/11.1/client/oci1.h
/usr/include/oracle/11.1/client/oci8dp.h
/usr/include/oracle/11.1/client/ociap.h
/usr/include/oracle/11.1/client/ociapr.h
/usr/include/oracle/11.1/client/ocidef.h
/usr/include/oracle/11.1/client/ocidem.h
/usr/include/oracle/11.1/client/ocidfn.h
/usr/include/oracle/11.1/client/ociextp.h
/usr/include/oracle/11.1/client/ocikpr.h
/usr/include/oracle/11.1/client/ocixmldb.h
/usr/include/oracle/11.1/client/odci.h
/usr/include/oracle/11.1/client/oratypes.h
/usr/include/oracle/11.1/client/ori.h
/usr/include/oracle/11.1/client/orid.h
/usr/include/oracle/11.1/client/orl.h
/usr/include/oracle/11.1/client/oro.h
/usr/include/oracle/11.1/client/ort.h
/usr/include/oracle/11.1/client/xa.h
/usr/lib/oracle/11.1/client/lib/libclntsh.so
/usr/lib/oracle/11.1/client/lib/libocci.so
/usr/lib/oracle/11.1/client/lib/ottclasses.zip
/usr/share/oracle/11.1/client/cdemo81.c
/usr/share/oracle/11.1/client/demo.mk
/usr/share/oracle/11.1/client/occidemo.sql
/usr/share/oracle/11.1/client/occidemod.sql
/usr/share/oracle/11.1/client/occidml.cpp
/usr/share/oracle/11.1/client/occiobj.cpp
/usr/share/oracle/11.1/client/occiobj.typ
/usr/share/oracle/11.1/client/ott

[root@gerrard downloads]# rpm -qlp oracle-instantclient11.1-sqlplus-11.1.0.7.0-1.i386.rpm
/usr/bin/sqlplus
/usr/lib/oracle/11.1/client/bin/sqlplus
/usr/lib/oracle/11.1/client/lib/glogin.sql
/usr/lib/oracle/11.1/client/lib/libsqlplus.so
/usr/lib/oracle/11.1/client/lib/libsqlplusic.so

I then installed each of them as follows:

rpm -ivh oracle-instantclient11.1-basic-11.1.0.7.0-1.i386.rpm
rpm -ivh oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm
rpm -ivh oracle-instantclient11.1-sqlplus-11.1.0.7.0-1.i386.rpm

Configure The Environment

To facilitate easily using the new installation I create a file I can source to set my environment correctly. I use the csh (well, tcsh really) as my primary shell and I created a file called "oic11.csh" in my home directory to setup the environment for me:

[markwill@gerrard ~]$ cat oic11.csh
#
setenv ORACLE_BASE /usr/lib/oracle
setenv ORACLE_HOME ${ORACLE_BASE}/11.1/client
setenv LD_LIBRARY_PATH ${ORACLE_HOME}/lib
set path = (${ORACLE_HOME}/bin /usr/local/bin /bin /usr/bin /usr/X11R6/bin ~/bin)

If you are using the bash shell, you may find something like the following helpful:

[markwill@gerrard ~]> cat oic11.env
export ORACLE_BASE=/usr/lib/oracle
export ORACLE_HOME=$ORACLE_BASE/11.1/client
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:~/bin

I can then set my environment to use the new install as follows (in csh):

[markwill@gerrard ~]> source ./oic11.csh

This could be done as follows for bash:

[markwill@gerrard ~]> . ./oic11.env

I also created a configuration file for the dynamic linker and updated the links/cache as follows:

[root@gerrard ~]# echo "/usr/lib/oracle/11.1/client/lib" > /etc/ld.so.conf.d/oic.conf
[root@gerrard ~]# /sbin/ldconfig


Create a Simple Test Project

All the setup work is now complete and the environment is configured! If needed, you can use the following (very!) basic application as a simple test to verify things are working as expected. Again, this is a simple example only to verify things are setup correctly. It is not intended to be a complete template for "proper" code development, etc. Be sure to set the environment correctly!

I created a "Projects" directory under my home directory and then a directory called "Employees" under the "Projects" directory. I then used VIM to create the Employees.h and Employees.cpp files.

Here's the content of the Employees.h file on my system:


/*
 * A simple OCCI test application
 * This file contains the Employees class declaration
 */

#include <occi.h>
#include <iostream>
#include <iomanip>

using namespace oracle::occi;
using namespace std;

class Employees {
public:
  Employees();
  virtual ~Employees();

  void List();

private:
  Environment *env;
  Connection  *con;

  string user;
  string passwd;
  string db;
};

Here's the content of the Employees.cpp file on my system:


/*
 * A simple OCCI test application
 * This file contains the Employees class implementation
 */

#include "Employees.h"

using namespace std;
using namespace oracle::occi;

int main (void)
{
  /*
   * create an instance of the Employees class,
   * invoke the List member, delete the instance,
   * and prompt to continue...
   */

  Employees *pEmployees = new Employees();

  pEmployees->List();

  delete pEmployees;

  cout << "ENTER to continue...";

  cin.get();

  return 0;
}

Employees::Employees()
{
  /*
   * connect to the test database as the HR
   * sample user and use the EZCONNECT method
   * of specifying the connect string. Be sure
   * to adjust for your environment! The format
   * of the string is host:port/service_name

   */

  user = "hr";
  passwd = "hr";
  db = "oel01:1521/OEL11GR1.SAND";

  env = Environment::createEnvironment(Environment::DEFAULT);

  try
  {
    con = env->createConnection(user, passwd, db);
  }
  catch (SQLException& ex)
  {
    cout << ex.getMessage();

    exit(EXIT_FAILURE);
  }
}

Employees::~Employees()
{
  env->terminateConnection (con);

  Environment::terminateEnvironment (env);
}

void Employees::List()
{
  /*
   * simple test method to select data from
   * the employees table and display the results
   */

  Statement *stmt = NULL;
  ResultSet *rs = NULL;
  string sql = "select employee_id, first_name, last_name " \
               "from employees order by last_name, first_name";

  try
  {
    stmt = con->createStatement(sql);
  }
  catch (SQLException& ex)
  {
    cout << ex.getMessage();
  }

  if (stmt)
  {
    try
    {
      stmt->setPrefetchRowCount(32);

      rs = stmt->executeQuery();
    }
    catch (SQLException& ex)
    {
      cout << ex.getMessage();
    }

    if (rs)
    {
      cout << endl << setw(8) << left << "ID"
           << setw(22) << left << "FIRST NAME"
           << setw(27) << left << "LAST NAME"
           << endl;
      cout << setw(8) << left << "======"
           << setw(22) << left << "===================="
           << setw(27) << left << "========================="
           << endl;

      while (rs->next()) {
        cout << setw(8) << left << rs->getString(1)
             << setw(22) << left << (rs->isNull(2) ? "n/a" : rs->getString(2))
             << setw(27) << left << rs->getString(3)
             << endl;
      }

      cout << endl;

      stmt->closeResultSet(rs);
    }

    con->terminateStatement(stmt);
  }
}

To build the simple test I created a simplistic Makefile:

[markwill@gerrard Employees]> cat Makefile
Employees: Employees.cpp
  g++ -o Employees Employees.cpp \
  -I/usr/include/oracle/11.1/client \
  -L$(ORACLE_HOME)/lib -lclntsh -locci

debug: Employees.cpp
  g++ -ggdb3 -o Employees Employees.cpp \
  -I/usr/include/oracle/11.1/client \
  -L$(ORACLE_HOME)/lib -lclntsh -locci

clean:
  rm -f Employees

NOTE: The indented lines are tabs and not spaces in the Makefile

Whilst certainly not destined to win any awards for Makefile creativity it suffices for the purpose at hand.

I then built the application in debug mode by typing "make debug".

Executing the sample should result in output as follows:

[markwill@gerrard Employees]> ./Employees

ID      FIRST NAME            LAST NAME
======  ====================  =========================
174     Ellen                 Abel
166     Sundar                Ande
130     Mozhe                 Atkinson
105     David                 Austin
204     Hermann               Baer
116     Shelli                Baida
167     Amit                  Banda
172     Elizabeth             Bates

[ snip ]

120     Matthew               Weiss
200     Jennifer              Whalen
149     Eleni                 Zlotkey

ENTER to continue...

If you are new to using OCCI on Linux and have used the .rpm file method of installing the Instant Client components, perhaps the above will be helpful in getting started!

Saturday, August 15, 2009

Getting Started with OCCI (Linux Version)

This is a follow-on post to the "Getting Started with OCCI (Windows Version)" with the not too minor change of swapping out Windows for Linux as the development machine. Much of the content is intentionally the same, however. For this particular "walkthrough" (for lack of a better term) I illustrate creating a "private" installation of the Oracle software. That is, I extract the software under my user's home directory. This has the advantage that it is completely separate from any other user's software. Of course, it also has the disadvantage that if many users have the same software installed there will be duplication. As this machine has no other "normal" user accounts (i.e. non-system accounts such as "root") other than my account this is not an issue for me.

The steps should be general enough to easily mould them to your environment and/or needs.

The Oracle C++ Call Interface, also known as OCCI, is an application programming interface (API) built upon the Oracle Call Interface (OCI - another lower level API from Oracle). One of the goals of OCCI is to offer C++ programmers easy access to Oracle Database in a fashion similar to what Java Database Connectivity (JDBC) affords Java developers. If you would like to learn more about what OCCI is (and isn't), pay a visit to the OCCI documentation on Oracle Technology Network (OTN) here:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28390/toc.htm

My goal with this "Getting Started" post is to give you one method of setting up an environment in which you can use OCCI to develop C++ applications under Linux that access Oracle Database. I am not in any way covering all possible scenarios or delving deep into OCCI itself. Please note that the database itself can be on any supported host.

The Environment

Your environment is likely to differ from mine; however, it is important to be familiar with the various components in the environment used here so that you can make adaptations as necessary for your specific environment.

  • Oracle Database Server/Host: oel01 (Oracle Enterprise Linux 32-bit server)
  • Oracle Database: SID value of OEL11GR1, Service Name value of OEL11GR1.SAND, version 11.1.0.7
  • Development Machine: Hostname of "gerrard", Oracle Enterprise Linux 32-bit (2.6.9 kernel)
  • Development IDE: VIM (any text editor or IDE you can configure should work)
  • Oracle Client: Oracle Instant Client with OCCI

Important Information

One of the most crucial attributes of working with OCCI is that you must ensure that all of the components of the development environment and the runtime environment are supported combinations and correct versions. I can not emphasize this enough. If you deviate from this, you will almost certainly find trouble! In order to find the correct combinations of products and versions, see the following links on OTN:

Download the Correct Packages

The Linux packages come in two varieties: a .zip file or a .rpm file. I have used the .zip files since I will be creating a private install under my user's home directory. The .rpm files require a user with the ability to install them (such as "root") and use directories such as "/usr/lib" and "/usr/bin". I want to keep this install as simple as possible and the .zip files work well for this.

From the download links above, you should download the following components to your development machine. I downloaded them to my home directory.

  • Instant Client Package – Basic Lite: instantclient-basiclite-linux32-11.1.0.7.zip
  • Instant Client Package - SDK: instantclient-sdk-linux32-11.1.0.7.zip
  • Instant Client Package - SQL*Plus: instantclient-sqlplus-linux32-11.1.0.7.zip  (optional, but I always install it)

NOTE: I am using the "Basic Lite" version of the main Instant Client packages. This is a smaller download and meets my needs; however, if you require character set or language support not provided by the "Basic Lite" version of Instant Client you should use the "basic" version instead. See the Instant Client home page (link above) for additional information.

Install the Instant Client Packages

Installing the Instant Client packages provided as .zip files is simply a matter of unzipping them – not much to go wrong here! I unzipped each of them (3 total) in my user's home directory on "gerrard". A new directory (instantclient_11_1) will be created and all the necessary files/directories will be under this single directory. This machine has no other Oracle software installed. Unzipping the files should result in the following:

[markwill@gerrard ~/instantclient_11_1]> find .
.
./libsqlplusic.so
./libclntsh.so.11.1
./ojdbc6.jar
./sqlplus
./adrci
./libnnz11.so
./SQLPLUS_README
./libociicus.so
./ojdbc5.jar
./sdk
./sdk/demo
./sdk/demo/occiobj.cpp
./sdk/demo/occidemo.sql
./sdk/demo/occiobj.typ
./sdk/demo/occidemod.sql
./sdk/demo/demo.mk
./sdk/demo/occidml.cpp
./sdk/demo/cdemo81.c
./sdk/SDK_README
./sdk/ott
./sdk/ottclasses.zip
./sdk/include
./sdk/include/orid.h
./sdk/include/occiAQ.h
./sdk/include/oratypes.h
./sdk/include/nzerror.h
./sdk/include/oci1.h
./sdk/include/ocikpr.h
./sdk/include/ocidef.h
./sdk/include/oci.h
./sdk/include/ort.h
./sdk/include/ocixmldb.h
./sdk/include/ocidfn.h
./sdk/include/odci.h
./sdk/include/ori.h
./sdk/include/occiCommon.h
./sdk/include/occi.h
./sdk/include/ociapr.h
./sdk/include/occiObjects.h
./sdk/include/occiControl.h
./sdk/include/xa.h
./sdk/include/ocidem.h
./sdk/include/oci8dp.h
./sdk/include/ociextp.h
./sdk/include/occiData.h
./sdk/include/ociap.h
./sdk/include/nzt.h
./sdk/include/orl.h
./sdk/include/oro.h
./libocci.so.11.1
./libocijdbc11.so
./genezi
./BASIC_LITE_README
./libsqlplus.so
./glogin.sql
[markwill@gerrard ~/instantclient_11_1]>


Create Links

To ensure proper operation of SQL*Plus and linking executables, two links should be created in the "instantclient_11_1" directory. The first is the Oracle Client Shared Library and the second is the OCCI Library:

ln -s ./libclntsh.so.11.1 ./libclntsh.so
ln -s ./libocci.so.11.1  ./libocci.so

After creating the links you should see the following when performing an "ls" on them:

[markwill@gerrard ~/instantclient_11_1]> ls -l libclntsh.so
lrwxrwxrwx  1 markwill markwill 19 Aug 15 19:17 libclntsh.so -> ./libclntsh.so.11.1

[markwill@gerrard ~/instantclient_11_1]> ls -l libocci.so
lrwxrwxrwx  1 markwill markwill 17 Aug 15 19:17 libocci.so -> ./libocci.so.11.1


Configure The Environment

To facilitate easily using the new installation I create a file I can source to set my environment correctly. I use the csh (well, tcsh really) as my primary shell and I created a file called "oic11.csh" in my home directory to setup the environment for me:

[markwill@gerrard ~]> cat oic11.csh
#
setenv ORACLE_BASE /home/markwill
setenv ORACLE_HOME ${ORACLE_BASE}/instantclient_11_1
setenv LD_LIBRARY_PATH ${ORACLE_HOME}
set path = (${ORACLE_HOME} /usr/local/bin /bin /usr/bin /usr/X11R6/bin ~/bin)

If you are using the bash shell, you may find something like the following helpful:

[markwill@gerrard ~]> cat oic11.env
export ORACLE_BASE=/home/markwill
export ORACLE_HOME=$ORACLE_BASE/instantclient_11_1
export LD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$ORACLE_HOME:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:~/bin

I can then set my environment to use the new install as follows (in csh):

[markwill@gerrard ~]> source ./oic11.csh

This could be done as follows for bash:

[markwill@gerrard ~]> . ./oic11.env

Create a Simple Test Project

All the setup work is now complete and the environment is configured! If needed, you can use the following (very!) basic application as a simple test to verify things are working as expected. Again, this is a simple example only to verify things are setup correctly. It is not intended to be a complete template for "proper" code development, etc. Be sure to set the environment correctly!

I created a "Projects" directory under my home directory and then a directory called "Employees" under the "Projects" directory. I then used VIM to create the Employees.h and Employees.cpp files.

Here's the content of the Employees.h file on my system:

/*
* A simple OCCI test application
* This file contains the Employees class declaration
*/

#include <occi.h>
#include <iostream>
#include <iomanip>

using namespace oracle::occi;
using namespace std;

class Employees {
public:
  Employees();
  virtual ~Employees();

  void List();

private:
  Environment *env;
  Connection  *con;

  string user;
  string passwd;
  string db;
};

Here's the content of the Employees.cpp file on my system:

/*
* A simple OCCI test application
* This file contains the Employees class implementation
*/

#include "Employees.h"

using namespace std;
using namespace oracle::occi;

int main (void)
{
  /*
   * create an instance of the Employees class,
   * invoke the List member, delete the instance,
   * and prompt to continue...
   */

  Employees *pEmployees = new Employees();

  pEmployees->List();

  delete pEmployees;

  cout << "ENTER to continue...";

  cin.get();

  return 0;
}

Employees::Employees()
{
  /*
   * connect to the test database as the HR
   * sample user and use the EZCONNECT method
   * of specifying the connect string. Be sure
   * to adjust for your environment! The format
   * of the string is host:port/service_name

   */

  user = "hr";
  passwd = "hr";
  db = "oel01:1521/OEL11GR1.SAND";

  env = Environment::createEnvironment(Environment::DEFAULT);

  try
  {
    con = env->createConnection(user, passwd, db);
  }
  catch (SQLException& ex)
  {
    cout << ex.getMessage();

    exit(EXIT_FAILURE);
  }
}

Employees::~Employees()
{
  env->terminateConnection (con);

  Environment::terminateEnvironment (env);
}

void Employees::List()
{
  /*
   * simple test method to select data from
   * the employees table and display the results
   */

  Statement *stmt = NULL;
  ResultSet *rs = NULL;
  string sql = "select employee_id, first_name, last_name " \
               "from employees order by last_name, first_name";

  try
  {
    stmt = con->createStatement(sql);
  }
  catch (SQLException& ex)
  {
    cout << ex.getMessage();
  }

  if (stmt)
  {
    try
    {
      stmt->setPrefetchRowCount(32);

      rs = stmt->executeQuery();
    }
    catch (SQLException& ex)
    {
      cout << ex.getMessage();
    }

    if (rs)
    {
      cout << endl << setw(8) << left << "ID"
           << setw(22) << left << "FIRST NAME"
           << setw(27) << left << "LAST NAME"
           << endl;
      cout << setw(8) << left << "======"
           << setw(22) << left << "===================="
           << setw(27) << left << "========================="
           << endl;

      while (rs->next()) {
        cout << setw(8) << left << rs->getString(1)
             << setw(22) << left << (rs->isNull(2) ? "n/a" : rs->getString(2))
             << setw(27) << left << rs->getString(3)
             << endl;
      }

      cout << endl;

      stmt->closeResultSet(rs);
    }

    con->terminateStatement(stmt);
  }
}

To build the simple test I created a simplistic Makefile:

[markwill@gerrard Employees]> cat Makefile
Employees: Employees.cpp
  g++ -o Employees Employees.cpp \
  -I$(ORACLE_HOME)/sdk/include \
  -L$(ORACLE_HOME) -lclntsh -locci

debug: Employees.cpp
  g++ -ggdb3 -o Employees Employees.cpp \
  -I$(ORACLE_HOME)/sdk/include \
  -L$(ORACLE_HOME) -lclntsh -locci

clean:
  rm -f Employees

NOTE: The indented lines are tabs and not spaces in the Makefile

Whilst certainly not destined to win any awards for Makefile creativity it suffices for the purpose at hand.

I then built the application in debug mode by typing "make debug".

Executing the sample should result in output as follows:

[markwill@gerrard Employees]> ./Employees

ID      FIRST NAME            LAST NAME
======  ====================  =========================
174     Ellen                 Abel
166     Sundar                Ande
130     Mozhe                 Atkinson
105     David                 Austin
204     Hermann               Baer
116     Shelli                Baida
167     Amit                  Banda
172     Elizabeth             Bates

[ snip ]

120     Matthew               Weiss
200     Jennifer              Whalen
149     Eleni                 Zlotkey

ENTER to continue...

If you are new to using OCCI on Linux, perhaps the above will be helpful in getting started!

Friday, August 14, 2009

ODP.NET, VB, and "Conversion from type 'OracleDecimal' to type 'Integer' is not valid."

It starts innocently enough: you have a PL/SQL procedure or function that returns a number (either as an OUT parameter or a return value respectively) to your VB client application. You know for a fact that the returned value will be less than the possible maximum value for the CLR type Int32 (represented as Integer in your VB code). It's dead easy and nothing can go wrong here, right? Except that from the title of this post you already know something is going to go unexpectedly. But let's not get too far ahead.

In order to understand what may (or may not) go wrong here, let's create a PL/SQL function that is guaranteed to return a valid Int32 value:

create or replace function getone return number
as
begin
  return 1;
end;
/

This function does nothing but return the value "1" (as a number) to the caller and that number is clearly an acceptable Int32 value – there's no trickery here.

We can use trusty old SQL*Plus to verify that it does what it says it does:

SQL> select getone from dual;

    GETONE
----------
         1

1 row selected.

SQL>

It sure does return the expected value, so let's create a simple VB project to test this out. I'm using ODP.NET version 10.2.0.4 for this experiment and here's the first version of the VB code:

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types

Module Module1
  Sub Main()
    ' change connection string for your environment
    Dim constr As String = "User Id=hr;" & _
                           "Password=hr;" & _
                           "Data Source=oel11gr1;" & _
                           "Enlist=false;" & _
                           "Pooling=false"

    ' create and open connection
    Dim con As OracleConnection = New OracleConnection(constr)
    con.Open()

    ' create command and setup to call test function
    Dim cmd As OracleCommand = con.CreateCommand()
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "getone"

    ' create parameter for the test function return value
    Dim retval As OracleParameter = New OracleParameter()
    retval.Direction = ParameterDirection.ReturnValue
    retval.OracleDbType = OracleDbType.Int32

    ' add the parameter to the parameters collection
    cmd.Parameters.Add(retval)

    ' execute the function
    cmd.ExecuteNonQuery()

    ' get the return value in a local variable
    Dim my_one As Integer = retval.Value

    ' basic cleanup
    retval.Dispose()
    cmd.Dispose()
    con.Dispose()
  End Sub
End Module

NOTE: I added a reference to the ODP.NET assembly to the project.

After entering the above code I built the project and got the all-important "success" message:

[ snip ]
ConversionTest -> C:\My Projects\Test\VB\ConversionTest\bin\Debug\ConversionTest.exe
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

So, let's go ahead and run this in debug mode from the IDE and see what happens…

Oops. It stops on the "Dim my_one As Integer = retval.Value" source line and pops up a dialog indicating that "System.InvalidCastException was unhandled". Digging a bit deeper we see "Conversion from type 'OracleDecimal' to type 'Integer' is not valid."

What? The code compiled with no problem, so what does this mean? Quickly looking at the code again we see that we declared the parameter as "OracleDbType.Int32" which should be able to handle the function return value with no issues, and we also see "Integer" which is the VB flavor of Int32! But why are we seeing "OracleDecimal" in the output and why is that being converted to Integer? We didn't declare an OracleDecimal in our code.

One thing we might try is going to the project properties, clicking the Compile tab, and setting "Option strict:" to "On". OK, done. Now, let's recompile. Hmm, now the project does not compile due to the error: Option Strict On disallows implicit conversions from 'Object' to 'Integer'.

Yuck. There was an implicit conversion that wasn't reported during the first compile due to the default of "Option strict" being "Off". OK, so now we have determined that there is a conversion problem happening, but that doesn't really explain why. For that we can consult the ODP.NET documentation and in particular the section that discusses Parameter Binding. In summary, that part of the documentation details how setting the OracleDbType or the DbType property of an OracleParameter object determines the data type of the output parameter. As stated in the documentation, "ODP.NET allows applications to obtain an output parameter as either a .NET Framework type or an ODP.NET type."

Ahhh, so we got an OracleDecimal as the return value because we specified OracleDbType.Int32 as the parameter type. Since we want an Int32 (or Integer) as the return value we can change the code to set the DbType instead of the OracleDbType:

retval.DbType = DbType.Int32

Now recompile and everything will work great. Except that you may get the following error: Option Strict On disallows implicit conversions from 'Object' to 'Integer'.

If "Option strict" is left "On" you will receive the above message. This is because the OracleParameter.Value is returned as an Object. An easy way out is to set "Option strict" back to "Off" and the implicit conversion will be done behind the scenes and the code will work as expected albeit because of a hidden conversion. To take the explicit route you can change the declaration of "my_one" as follows:

Dim my_one As Integer = DirectCast(retval.Value, Int32)

Now you are getting the value back as an Int32/Integer (wrapped in an object) and explicitly (rather than implicitly) extracting the value via DirectCast as an Int32/Integer. With the implicit conversion the compiler will inject a call to a conversion function into the code and with the DirectCast method an "unbox" operation will take place.

Incidentally, the same sort of thing would need to happen with C#; however, the compiler would warn you of this during the initial compile run since it doesn't do the implicit conversion that VB would try if "Option strict" is "Off".

If you are having conversion problems in your code with "output" or "return values" keep in mind that setting OracleDbType vs. DbType will determine whether a .NET Framework type or an Oracle provider type is returned. Also, the current ODP.NET Beta (11.1.7.10) exposes a new property for the OracleParameter class: OracleDbTypeEx  This property allows you to bind values using the OracleDbType but will return values as .NET types.