Mark A. Williams
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.
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 PackagesI 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]$
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 InstallationSince 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 ~]$
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 SampleRather 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]$
ODAC 11.1.0.7.20 Production Release Available
- Download
- New Features List
- ODT SQL Tuning Advisor Viewlet
- ODT Oracle Performance Analyzer Viewlet
- ODP.NET 11.1.07.20 White Paper
Happy coding.
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:
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 oneThis 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.
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 EnvironmentYour 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
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:
- Oracle C++ Call Interface
- OCCI Downloads page (NOTE: you do not need to download any OCCI components for this walkthrough)
- Oracle Database Instant Client
- Instant Client Downloads
- Instant Client Downloads for Linux 32-bit (NOTE: the necessary OCCI components are included in this package)
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 PackagesInstalling 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
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!
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 EnvironmentYour 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
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:
- Oracle C++ Call Interface
- OCCI Downloads page (NOTE: you do not need to download any OCCI components for this walkthrough)
- Oracle Database Instant Client
- Instant Client Downloads
- Instant Client Downloads for Linux 32-bit (NOTE: the necessary OCCI components are included in this package)
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 PackagesInstalling 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
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 ProjectAll 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!
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.
Empezando con OCCI
Luis Neri was kind enough to translate my "Getting Started with OCCI (Windows Version)" post into Spanish. Below is his translation of the original post. Thanks very much to Luis for this. However, please note that I do not speak Spanish, so I won't be able to respond to any comments in that language.
- Mark
La Oracle C++ Call Interface, también conocida como OCCI, es una API construida sobre otras API's de bajo nivel de Oracle. Uno de los objetivos de OCCI es ofrecer a los programadores de C++ una forma de acceso fácil a las bases de datos de Oracle en una forma similar a la que tienen los programadores de Java con "Java Database Connectivity (JDBC) ". Este documento trata de dar una vista rápida para empezar con esta tecnología, la cual puede ser incorporada a los desarrollos y aplicaciones GIS del SIRAN; si se desea, existe más información en la documentación en línea de Oracle.
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28390/toc.htm
Este "Empezando con OCCI" tiene el objeto de dar un método para la puesta del ambiente el cual usa OCCI en los desarrollos con C++ bajo Windows para acceder a las bases de datos de Oracle.
El ambiente
El ambiente en que se realizarón las pruebas para la puesta en funcionamiento de esta tecnología es el que abajo se detalla, tenga en cuenta que a este ambiente se pueden hacer pequeñas adaptaciones para que funcione en el suyo en particular.
- Oracle Database Client: Oracle 11.0.2
- Oracle Database: Oracle 10.2
- Development Machine: Usser3 Geoware, Windows Vista Home Premium 32 bits
- Development IDE: Microsoft Visual C++ 2008 Profesional (Windows SDK also installed) SP1
- Oracle Client: Oracle Instant Client with OCCI
Información importante
Uno de las características más importantes al trabajar con OCCI es que se debe asegurar que todos los componentes del ambiente de desarrollo y de runtime están soportados en las combinaciones y tener las versiones correctas. Se hace un fuerte énfasis en esto, si usted no observa con atención esta recomendación seguramente encontrara problemas. Para lograr esto por favor encuentre las combinaciones correctas de las versiones en la pagina de Oracle Technology Network (OTN).
- Oracle C++ Call Interface
- OCCI Downloads page
- Oracle Database Instant Client
- Instant Client Downloads
- Instant Client Downloads for Windows 32-bit
Descargue los componentes correctos
Al momento de escribir este documento se encontraron para el ambiente descrito arriba los siguientes componentes
- OCCI 11.1.0.6 (Visual C++9 (VS 2008)[Windows 32-bit])
- Instant Client Version 11.1.0.6
Estos componentes se deben descargar en la máquina de desarrollo. Yo lo hice en el escritorio.
- OCCI 11.1.0.6 (Visual C++9 (VS 2008)[Windows 32-bit]) - occivc9win32_111060.zip
- Instant Client Package - Basic: instantclient-basic-win32-11.1.0.6.0.zip
- Instant Client Package - SDK: instantclient-sdk-win32-11.1.0.6.0.zip
- Instant Client Package - SQL*Plus: instantclient-sqlplus-win32-11.1.0.6.0.zip (Opcional, pero se recomienda instalarlo)
Instalación de Instant Client
La instalación es tan simple como descomprimir el archivo, descomprímalo sobre C:\ , el resultado será una carpeta C:\instantclient_11_1 con las subcarpetas "sdk", "vc8" y "vc7", las cuales no serán utilizadas en el desarrollo sobre nuestro ambiente.
Instalación del OCCI Package
Al igual que la instalación de Instant Client, los paquetes Occi pueden ser descomprimidos, pero sin embargo en vez de crear un directorio sobre c:\, los descomprimí sobre el escritorio, una vez descomprimidos, me desvié un poco de lo quel archivo occivc9_111060_readme.txt dice, y realice los siguiente:
Cree un directorio "vc9" dentro del directorio "sdk" como sigue:
C:\instantclient_11_1\sdk\lib\msvc\vc9
Cree una carpeta "vc9" dentro de "instantclient_11_1" como sigue:
C:\instantclient_11_1\vc9
Borre el archivo oraocci11.dll y oraocci11.sym del directorio C:\instantclient_11_1. Estos archives no están hechos para construir o compilar con Visual Studio 2008 y como se dijo anteriormente es importante que se empaten las versiones.
Se extrajeron los archivos OCCI en la carpeta del escritorio moviendo los dos archivos a la carpeta C:\instantclient_11_1\sdk\lib\msvc\vc9 antes creada:
- oraocci11.lib
- oraocci11d.lib
de la misma carpeta en el escritorio, se mueven los siguientes archivos a la carpeta C:\instantclient_11_1\vc9 antes creada:
- oraocci11.dll
- oraocci11.dll.manifest
- oraocci11d.dll
- oraocci11d.dll.manifest
Finalmente se borra el archivo oraocci11.lib de:
C:\instantclient_11_1\sdk\lib\msvc
Otra vez este archivo no es compatible con nuestro ambiente.
Despues de realizar estos pasos, los archivos .lib deben estar bajo la carpeta C:\instantclient_11_1\sdk\lib\msvc y los archivos .dll y .manifest deben estar bajo la carpeta C:\instantclient_11_1. Esto pasos parecen trabajo extra, pero resultan de una separación completa de las varias versiones OCCI y lo hace más fácil y explicito.
Para especificar que versión de librerías OCCI usar, añada estas carpetas al path del sistema. Estas dos carpetas deben estar al inicio del path:
C:\instantclient_11_1\vc9;C:\instantclient_11_1;{y lo demás del path…}
Configuración de Visual Studio
El ambiente Windows ha sido configurado para el uso de los nuevos paquetes OCCI y Instan Client (los adecuados para nuestro ambiente), pero antes de empezar un desarrollo en Visual Studio, es necesario establecer algunas opciones. Sin estas opciones Visual Studio es incapaz de encontrar los archivos correctos y construir aplicaciones. Hay dos opciones las cuales necesitan ser especificadas:
- Include files – permite a Visual Studio encontrar los header files para OCCI
- Library files – permite a Visual Studio encontrar los library files para OCCI
UsandoVisual C++ 2008, las rutas de menu donde se especifican estas opciones son:
- Tools –> Options… expanda el nodo "Projects and Solutions", seleccione "VC++ Directories", bajo "Show directories for:" seleccione "Include files", doble-click bajo la ultima entrada para abrir una nueva pantalla e introduzca la ruta "C:\instantclient_11_1\sdk\include" presione enter
- Bajo "Show directories for:" seleccione"Library files", doble-click bajo la ultima entrada para abrir una nueva pantalla e introduzca la ruta "C:\instantclient_11_1\sdk\lib\msvc\vc9" and presione enter
- Presione OK para guardar las opciones.
Cree un proyecto simple de prueba
Ya que se ha hecho todo la puesta en funcionamiento el ambiente está configurado!, Utilice la siguiente proyecto como prueba para verificar que todo funciona como se espera. Otra vez, este es un simple ejemplo para verificar que las cosas funcionan correctamente. No es un templeate de desarrollo.
Cree en Visual C++ 2008 un proyecto seleccionando File –> New –> Project… del menú principal, seleccione "Win32" como tipo de proyecto, seleccione "Win32 Console Application", dele un nombre al proyecto (yo usé prueba_occi), seleccione un carpeta donde guardar deseleccione "Create directory for solution", y presione OK.
Presione Next en el Wizard, deseleccione Precompiled header, presione Empty project, y presione Finish.
En el explorador de la solucion, click-izquierdo en Header Files, seleccione Add, seleccione New Item…
En Add New Item, seleccione Header File (.h), introduzca Employees.h (o cualquier otro nombre) en nombre y presione Add.
/*
* 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;
};
en Solution Explorer, click-izquierdo en Source Files, seleccione Add, seleccione New Item…
en Add New Item, seleccione C++ File (.cpp), introduzca Employees.cpp ((o cualquier otro nombre) en nombre y presione Add.
Este es el contenido de mi archivo en el sistema:
/*
* 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);
}
}
Antes de construir el ejemplo (build), se necesita añadir la librería OCCI a la lista de entradas del linker:
Seleccione Project –> prueba_occi Properties... del menu (Sustituya el nombre por el proio si es necesario)
Expanda el nodo Configuration Properties, expanda el nodo Linker, seleccione Input item, introduzca "oraocci11d.lib" para debug build o "oraocci11.lib" para release build.
Seleccione Build –> Build Solution del menú para construir la solución. Si todo está puesto correctamente no debería haber errores. Si existen error busque donde pueden estar y corrija. La pantalla de ejecución seria como esta:
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...
Si eres nuevo en el uso de OCCI sobre Windows con Visual Studio 2008, quizá el ejemplo de arriba puede ser de ayuda al inicio.
SQL*Plus, EZCONNECT, Password Prompt, and ORA-12504
Before you get any further in this post, let me be perfectly clear that this post only addresses ORA-12504 in the context of using the "Easy Connect Naming Method" with SQL*Plus and password prompting. It does not address ORA-12504 outside of this context. The documentation (see the link above) has several examples of using and configuring this method, so I won't go into them all here. This method is also known as "EZCONNECT" presumably because "Z" in the American dialect is pronounced like "zee" rather than "zed".
You are likely already familiar with the password prompting behavior of SQL*Plus. For example, when using a tnsnames.ora file alias to connect to a database, if the password is not specified, SQL*Plus will prompt you for it (i.e. "Enter password:"):
[oracle@liverpool ~]$ sqlplus hr@orademo
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 11 11:42:08 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Nice and easy.
So, let's try the equivalent using EZCONNECT:
[oracle@liverpool ~]$ sqlplus hr@liverpool:1521/DEMO
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 11 11:43:20 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
Enter user-name:
Hmm... well, that's interesting. Rather than getting prompted for the password we get an error (ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA) and then we get presented with the "Enter user-name:" prompt again. Funny that the error is saying the SERVICE_NAME was not specified, but it sure is there - DEMO in this case. This is giving us a clue though...
OK, so let's go ahead and specify the password:
[oracle@liverpool ~]$ sqlplus hr/hr@liverpool:1521/DEMO
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 11 11:44:02 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Much better, it works, but what if you (reasonably) don't want to specify the password on the command-line?
Taking a look at the documentation (really - that stuff does come in handy!) for the CONNECT SQL*Plus command we can see this:
username[/password] [@connect_identifier]
Pay particular attention that the "/" precedes the password. In the case of using EZCONNECT we have:
sqlplus hr@liverpool:1521/DEMO
So, in this case the "/" precedes the SERVICE_NAME and not the password. How should we tell SQL*Plus that the "/" is not preceding the password? We quote the connection string!
[oracle@liverpool ~]$ sqlplus hr@\"liverpool:1521/DEMO\"
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 11 11:45:43 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Now that's more like it! Notice that I used "\" (backslash) to escape the double-quote characters on the command-line (I'm using the bash shell for this).
If you are already in SQL*Plus you do not need to use the backslash to escape the double-quote characters:
[oracle@liverpool ~]$ sqlplus /nolog
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 11 11:46:31 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
SQL> connect hr@"liverpool:1521/DEMO"
Enter password:
Connected.
SQL>
This same technique can also be used for privileged connections:
[oracle@liverpool ~]$ sqlplus sys@\"liverpool:1521/DEMO\" as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 11 11:47:23 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
If you use SQL*Plus as much as I do, perhaps this will be a handy tip.
Getting Started with OCCI (Windows Version)
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 Windows 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 EnvironmentYour 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 CHEPSTOW, Windows XP Professional 32-bit
- Development IDE: Microsoft Visual C++ 2008 Express Edition (Windows SDK also installed)
- Oracle Client: Oracle Instant Client with OCCI
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:
- Oracle C++ Call Interface
- OCCI Downloads page
- Oracle Database Instant Client
- Instant Client Downloads
- Instant Client Downloads for Windows 32-bit
At the time of this writing, the following are the component versions supported for the environment listed above:
- OCCI 11.1.0.6 (Visual C++9 (VS 2008)[Windows 32-bit])
- Instant Client Version 11.1.0.6
From the download links above, you should download the following components to your development machine. I downloaded them to the C:\Temp directory.
- OCCI 11.1.0.6 (Visual C++9 (VS 2008)[Windows 32-bit]) - occivc9win32_111060.zip
- Instant Client Package - Basic: instantclient-basic-win32-11.1.0.6.0.zip
- Instant Client Package - SDK: instantclient-sdk-win32-11.1.0.6.0.zip
- Instant Client Package - SQL*Plus: instantclient-sqlplus-win32-11.1.0.6.0.zip (optional, but I always install it)
Installing the Instant Client packages is simply a matter of unzipping them – not much to wrong here! I unzipped them all to the C:\ directory on Chepstow. This resulted in a new top-level directory - C:\instantclient_11_1 with "sdk", "vc8", and "vc7" directories underneath. The "vc8" and "vc7" directories should be ignored in the context of the environment created here.
Install the OCCI PackageMuch like the Instant Client packages, the OCCI package should be unzipped; however, rather than unzipping it to the C:\ directory, I unzipped it to the C:\Temp directory. Once unzipped, review the occivc9_111060_readme.txt file for information; however, I deviate from the directories listed in the file.
I create a "vc9" directory under the "sdk" directory as follows:
C:\instantclient_11_1\sdk\lib\msvc\vc9
I create a "vc9" directory under the "instantclient_11_1" directory as follows:
C:\instantclient_11_1\vc9
I delete the oraocci11.dll and oraocci11.sym files from the C:\instantclient_11_1 directory. These files are not built/linked with the runtime libraries used by Visual Studio 2008 and, as mentioned above, it is critical that component versions match!
From the extracted OCCI files in the C:\Temp directory, move the following two files to the C:\instantclient_11_1\sdk\lib\msvc\vc9 directory previously created:
- oraocci11.lib
- oraocci11d.lib
From the extracted OCCI files in the C:\Temp directory, move the following four files to the C:\instantclient_11_1\vc9 directory previously created:
- oraocci11.dll
- oraocci11.dll.manifest
- oraocci11d.dll
- oraocci11d.dll.manifest
Finally, delete the oraocci11.lib file from the C:\instantclient_11_1\sdk\lib\msvc directory. Again, this file is not compatible with the environment created here.
After performing these steps, the .lib files should only be in directories under C:\instantclient_11_1\sdk\lib\msvc and the .dll and .manifest files should only be in directories under the C:\instantclient_11_1 directory. While this may seem like extra unneeded work, it results in complete separation of the various versions of the OCCI components making it easier (and explicit) which version is to be used.
To specify which version of the OCCI libraries are used, add the directory to the system path. You also add the the Instant Client directory to the path. Both of these directories should be added to the beginning of the system path:
C:\instantclient_11_1\vc9;C:\instantclient_11_1;{rest of path follows…}
Configure Visual StudioThe Windows environment has been configured to use the new OCCI and Instant Client packages but before you can begin developing in Visual Studio, you need to set a few options. Without these options Visual Studio will be unable to find the correct files and build your applications. There are two options that need to be specified:
- Include files – allows Visual Studio to find the header files for OCCI
- Library files – allows Visual Studio to find the library files for OCCI
Using Visual C++ 2008 Express Edition, the menu paths to specify these options are as follows:
- Tools –> Options… Expand "Projects and Solutions" node, select "VC++ Directories", under "Show directories for:" select "Include files", double-click under the last entry to open a new box to enter a path, enter "C:\instantclient_11_1\sdk\include" and press enter
- Under "Show directories for:" select "Library files", double-click under the last entry to open a new box to enter a path, enter "C:\instantclient_11_1\sdk\lib\msvc\vc9" and press enter
- Click OK to save the settings
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.
Create the Visual C++ 2008 Express Edition project by selecting File –> New –> Project… from the main menu, select "Win32" as the project type, select "Win32 Console Application", give the project a name (I used OCCITest), select a location (I used C:\Projects), I unchecked "Create directory for solution", and then click OK.
Click Next in the Application Wizard, uncheck Precompiled header, click Empty project, and click Finish.
In Solution Explorer, right-click Header Files, select Add, select New Item…
In Add New Item, select Header File (.h), enter Employees.h (or any name you prefer) in Name, and click Add.
Here's the content of the 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;
};
In Solution Explorer, right-click Source Files, select Add, select New Item…
In Add New Item, select C++ File (.cpp), enter Employees.cpp (or any name you prefer) in Name, and click Add.
Here's the content of the 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);
}
}
Before you can build the sample, you need to add the OCCI library to the input list for the linker:
Select Project –> OCCITest Properties... from the menu (substitute your project name if different)
Expand Configuration Properties node, expand Linker node, select Input item, enter "oraocci11d.lib" for a debug build or "oraocci11.lib" for a release build.
Select Build –> Build Solution from the menu to build the solution. If everything is setup correctly, there should be no errors during the build. If you receive errors, investigate and correct them.
Executing the sample should result in output as follows:
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 Windows with Visual Studio, perhaps the above will be helpful in getting started!
Oracle Pro*C on Windows with Express Edition Products
NOTE: I have edited the intro text here from the original source as a result of some discussions I've had. These discussions are ongoing so I can't post the results as of yet. (3-June-2009 approximately 5:00 PM).
I thought I would take an introductory look at using the Oracle Pro*C precompiler using Express Edition products. Here are the components I will use for this purpose (links valid at time of posting):
- Oracle Database 10g Express Edition (available here)
- Oracle Enterprise Linux (available here)
- Oracle Instant Client 11.1.0.7 Packages for Microsoft Windows 32-bit (available here)
- Instant Client Package – Basic
- Instant Client Package – SDK
- Instant Client Package – Precompiler
- Instant Client Package - SQL*Plus
- Microsoft Windows XP Professional 32-bit with Service Pack 3
- Microsoft Visual C++ 2008 Express Edition (available here)
- Windows SDK for Windows Server 2008 and .NET Framework 3.5 (available here)
For the purposes of this discussion you will need to have already installed (or have access to) Oracle Database with the HR sample schema. You will also need to have installed Visual C++ 2008 Express Edition and the Windows SDK on the machine you will use as your development machine. For a walkthrough of installing Visual C++ 2008 Express Edition and the Windows SDK, please see this link. Note that even though the SDK seems to be only for Windows Server 2008 (based on the name) it is supported on XP, Vista, and Windows Server.
In my environment I have installed Oracle Database 10g Express Edition on a host running Oracle Enterprise Linux. The host name is "oel02" (not especially clever, I realize). The Windows XP machine that I will use as the development machine is named "chepstow" (perhaps marginally more clever) and Visual C++ Express Edition and the Windows SDK are already installed. I have downloaded the four Instant Client packages listed above to the "c:\temp" directory on chepstow. The SQL*Plus package is not required; however, I find it convenient so I always install it. So, since I already have a database server and the Microsoft tools are installed, all that remains is to install the Instant Client packages.
Installing the Instant Client PackagesIt is incredibly easy to install the Instant Client packages – simply unzip them! I chose to unzip them (on chepstow, my development machine) to the "c:\" directory and this created a new "c:\instantclient_11_1" directory and various sub-directories. I then added the following two directories to the system path:
- C:\instantclient_11_1
- C:\instantclient_11_1\sdk
NOTE: I added the two directories to the beginning of the system path and had no other Oracle products installed. See comments for more information about this. (Added 29 June 2009 approximately 11:30 AM)
Setting up the Pro*C Configuration FileI know it is easy to skip reading a README file, but it is important that you do read the PRECOMP_README file in the Instant Client root directory. Pro*C will, by default, look for a configuration file named "pcscfg.cfg" when it is invoked. In the environment that I have created (default installs of all products) Pro*C will want to find this file in the "c:\instantclient_11_1\precomp\admin" directory. However, if you look at your install (if you have done the same as me) you will notice there is no such directory! Therefore you should create this directory ("c:\instantclient_11_1\precomp\admin"). You should then copy the "pcscfg.cfg" file from the "c:\instantclient_11_1\sdk\demo" directory to the "c:\instantclient_11_1\precomp\admin" directory.
The "pcscfg.cfg" file will initially contain the following single line:
define=(WIN32_LEAN_AND_MEAN)
Below this line you add the following four lines:
sys_include=C:\PROGRA~1\MICROS~1.0\VC\include\sys
include=C:\PROGRA~1\MICROS~3\Windows\v6.1\Include
include=C:\PROGRA~1\MICROS~1.0\VC\include
include=C:\instantclient_11_1\sdk\include
Save the file and exit your editor.
Be sure to note that the directory names above are the "short" names to ensure they do not contain spaces. If the directory names contain spaces this will cause problems with the Pro*C precompiler. To help "translate" the directories above, here are the long versions (be sure you do not enter these):
sys_include=C:\Program Files\Microsoft Visual Studio 9.0\VC\include\sys
include=C:\Program Files\Microsoft SDKs\Windows\v6.1\Include
include=C:\Program Files\Microsoft Visual Studio 9.0\VC\include
include=C:\instantclient_11_1\sdk\include
You can find the short names by using "dir /x" in a command-prompt window.
Adding Directories to Visual Studio C++ 2008 Express EditionNext you should add the Oracle Instant Client include and library directories to Visual Studio. To do this, simply perform the following steps:
- Select Tools –> Options to open the Options dialog
- Expand the "Projects and Solutions" node
- Click the "VC++ Directories" item
- Under "Show directories for:" select "Include files"
- Click underneath the last entry in the list (you should get a highlighted line with no text)
- Click the folder button to create a new line
- Enter "c:\instantclient_11_1\sdk\include" and press Enter
- Under "Show directories for:" select "Library files"
- Click underneath the last entry in the list (you should get a highlighted line with no text)
- Click the folder button to create a new line
- Enter "c:\instantclient_11_1\sdk\lib\msvc" and press Enter
- Click the OK button to save the changes
WARNING: You should create your project in a directory (and path) that has no spaces in it. If you create the project in a directory or path with spaces you will receive errors during the precompile phase. I used "c:\temp" for this example.
Now create a new project in Visual Studio:
- Select File –> New Project to open the New Project dialog
- Select "Win32" as the project type
- Select "Win32 Console Application" under "Templates"
- Give the project a name (I used "proctest" in keeping with my clever naming tradition)
- I always choose to de-select "Create directory for solution" and click OK
- Click the "Next" button in the application wizard
- Click the "Empty project" checkbox under "Additional options"
- Click the "Finish" button
To create the Pro*C source file, perform the following steps:
- Right-click "Source Files" and select Add –> New Item… from the context menu
- Select "Code" under "Visual C++"
- Select "C++ File (.cpp)" under "Visual Studio installed templates" (note that you will not actually create C++ code in this example)
- Give the file a name such as "proctest.pc" and click "Add"
Here's the Pro*C source I used for this example (this is clearly sample code and lots is left out!):
/*
** suppress certain warnings
*/
#ifdef WIN32
#define _CRT_SECURE_NO_DEPRECATE 1
#endif
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include <sqlda.h>
#include <sqlcpr.h>
EXEC SQL BEGIN DECLARE SECTION;
/*
** defines for VARCHAR lengths.
*/
#define UNAME_LEN 30
#define PWD_LEN 30
#define DB_LEN 48
#define FNAME_LEN 32
#define LNAME_LEN 32
/*
** variables for the connection
*/
VARCHAR username[UNAME_LEN];
VARCHAR password[PWD_LEN];
VARCHAR dbname[DB_LEN];
/*
** variables to hold the results
*/
int ctr;
int empid;
VARCHAR fname[FNAME_LEN];
VARCHAR lname[LNAME_LEN];
EXEC SQL END DECLARE SECTION;
/*
** declare error handling function
*/
void sql_error(char *msg)
{
char err_msg[128];
size_t buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n%s\n", msg);
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);
EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}
void main()
{
/*
** Copy the username into the VARCHAR.
*/
strncpy((char *) username.arr, "hr", UNAME_LEN);
username.len = strlen("hr");
username.arr[username.len] = '\0';
/*
** Copy the password.
*/
strncpy((char *) password.arr, "hr", PWD_LEN);
password.len = strlen("hr");
password.arr[password.len] = '\0';
/*
** copy the dbname (using EZCONNECT syntax)
*/
strncpy((char *) dbname.arr, "oel02/XE", DB_LEN);
dbname.len = strlen("oel02/XE");
dbname.arr[dbname.len] = '\0';
/*
** register sql_error() as the error handler.
*/
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
/*
** Connect to database. Will call sql_error()
** if an error occurs when connecting.
*/
EXEC SQL CONNECT :username IDENTIFIED BY :password USING :dbname;
printf("\nConnected to ORACLE as user: %s\n\n", username.arr);
/*
** simple select statement
*/
EXEC SQL DECLARE emps CURSOR FOR
SELECT employee_id,
first_name,
last_name
FROM employees
ORDER BY last_name,
first_name;
/*
** open the cursor
*/
EXEC SQL OPEN emps;
/*
** when done fetching break out of the for loop
*/
EXEC SQL WHENEVER NOT FOUND DO break;
/*
** simple counter variable
*/
ctr = 0;
/*
** print a little header
*/
printf("Employee ID First Name Last Name\n");
printf("=========== ==================== =========================\n");
/*
** fetch all the rows
*/
for (;;)
{
EXEC SQL FETCH emps into :empid, :fname, :lname;
/*
** null-terminate the string values
*/
fname.arr[fname.len] = '\0';
lname.arr[lname.len] = '\0';
/*
** print the current values
*/
printf("%-13d%-22s%-25s\n", empid, fname.arr, lname.arr);
ctr++;
}
/*
** close the cursor
*/
EXEC SQL CLOSE emps;
/*
** provide simple feedback on how many rows fetched
*/
printf("\nFetched %d employees.\n", ctr);
/*
** disconnect from database
*/
EXEC SQL ROLLBACK WORK RELEASE;
/*
** have a nice day
*/
exit(EXIT_SUCCESS);
}
The output of the Pro*C precompiler is either C or C++ source code (C in this case). However, because we are working with only a Pro*C source file we need to tell Visual Studio about the file that will be generated. To do this we add a reference to the not yet generated file:
- Select Project –> Add New Item to open the Add New Item dialog
- Select "Code" under "Visual C++"
- Select "C++ File (.cpp)" under "Visual Studio installed templates"
- Type "proctest.c" in the "Name" textbox and click "Add"
- Next close the (empty) file after it is created
- Select Project –> <project name> Properties… to open the Property Pages dialog
- Expand the "Configuration Properties" node
- Expand the "Linker" node
- Click the "Input" item
- In the "Additional Dependencies" type "orasql11.lib" and click "OK" to save the changes
In order for Visual Studio to be able to invoke the Pro*C executable (proc.exe) to create the C source code file, a custom build step needs to be created:
- Right-click "proctest.pc" in the Solution Explorer and select "Properties" from the context menu
- Select "Custom Build Step"
- For "Command Line" type "proc.exe $(ProjectDir)$(InputName).pc"
- For "Outputs" type "$(ProjectDir)$(InputName).c"
- Click "OK" to save the custom build step
This step will cause Visual Studio to invoke proc.exe on the input file (proctest.pc) and create an output file called "proctest.c" which will then be compiled as normal. This is really the key step in the whole process I suppose. This custom build step is the "integration" of Pro*C into Visual Studio.
Build the Sample and VerifyAll the hard work is now done and it is time to build the sample!
- Select Build –> Build Solution
If all has gone well you should see output similar to the following in the output window:
proctest - 0 error(s), 0 warning(s)
========== Build: 1 succeeded, 0 failed, 0 up-to-date, 0 skipped ==========
If there are errors reported you will need to investigate and correct the cause.
Upon completion of a successful build, you can execute the program and verify the results:
C:\temp\proctest\Debug>proctest
Connected to ORACLE as user: hr
Employee ID First Name Last Name
=========== ==================== =========================
174 Ellen Abel
166 Sundar Ande
130 Mozhe Atkinson
[ snip ]
120 Matthew Weiss
200 Jennifer Whalen
149 Eleni Zlotkey
Fetched 107 employees.
C:\temp\proctest\Debug>
ConclusionWhew! That's a lot of work! As I mentioned at the beginning of this post, this is intended to be an introductory look at using Pro*C and Visual C++ 2008 Express Edition. There is, of course, much more that Pro*C can do and this simple example of selecting from the "employees" table in the "hr" schema is exactly that: a simple example. It is not intended to be a complete tutorial but perhaps it will be helpful in working with Pro*C and Visual Studio if you choose to do so. You should be able to adapt the steps here to using the "full" version of Visual Studio or Oracle Client.
If you made it this far, thanks for stopping by. I hope this was helpful in some regard.
NOTE: Some comments below were recently deleted by me at the request of the poster. I have, therefore, deleted my responses to those comments as they made no sense on their own. (1-June-2009 approximately 1:10 PM)
Microsoft To Deprecate System.Data.OracleClient
System.Data.OracleClient Update
It looks like Microsoft have decided to deprecate System.Data.OracleClient beginning with the .NET 4.0 release.
Of course, I'm more than a little biased when it comes to anything related to Oracle.
For more information and to download ODP.NET, please see the Oracle Data Provider for .NET center on Oracle Technology Network (OTN).
jmp'ing around Win64 with ml64.exe and Assembly Language
I've been fascinated by (and interested in) assembly language for a number of years. I fondly remember my "Peter Norton Assembly" book and wish I hadn't sent it to a better home several years ago. But, hey, it was a major moving of house and I had to have as few items as possible. The sacrifices we make…
Anyway, I had some spare time this past weekend and decided to try and see if I could get a simple 64-bit assembly language program working under Win64. My goal was to write some text to a console window and exit without destroying my entire system. I managed to succeed in that endeavor and had a bit of fun along the way. My wife thinks I have a bizarre definition of fun. If you continue to read the rest of this post keep in mind I said I was "interested" in assembly. By no means is this intended to be a tutorial or guide in writing efficient and professional assembly! As the comments in the code indicate, there was no attempt at optimization, etc!
A couple of links that I ran across and found helpful (Links accurate as of today. Who knows what the future may bring?):
- Matt Pietrek's X64 Primer column from the May 2006 MSDN Magazine found here.
- Raymond Chen's Blog (The Old New Thing) posting entitled "The History of Calling Conventions, Part 5: AMD64" found here.
In order to make the code just a tiny bit more interesting I decided to call the "cpuid" function to get the Vendor ID from the processor and write that out as well as some static text. Information on the "cpuid" function is available in pdf format on both the Intel and AMD websites. One thing that I found funny is that the 4 byte chunks (of which there are 3) that make up the output of the Vendor ID request from the "cpuid" function are stored in the registers in an "out of order" fashion. Look at the code below that gets the chunks from the registers and you'll see what I mean (I hope).
My First-Ever 64-bit Assembly Language Program With Comments and EverythingNOTE: Edits made per comment(s)
; a (very) simple x64 masm (ml64.exe) demo that calls the cpuid function
; and writes the results to a console window.
; this is just an experiment and is likely not very good assembly code
; for one thing there is no attempt at optimization (like removing duplicate
; sections of code).
; args are passed in registers as follows:
; RCX: 1st integer argument
; RDX: 2nd integer argument
; R8: 3rd integer argument
; R9: 4th integer argument
; the stack is used for args beyond 4 (after space reserved for first 4)
; this reserved space on the stack for first 4 arguments is the
; "register parameter stack area"
; unlike win32 the caller is responsible for cleaning the stack rather than
; the callee
; win api functions that are called below
extrn GetStdHandle : proc
extrn WriteFile : proc
extrn lstrlen : proc
extrn ExitProcess : proc
; the variables used in the main code below such as the text to write
.data
hFile qword 0
msglen dword 0
BytesWritten dword 0
supp byte 13, 10, 'CPUID instruction supported!', 13, 10, 0
nosupp byte 13, 10, 'CPUID instruction NOT supported!', 13, 10, 0
vndrid byte 13, 10, "The processor Vendor ID is 'xxxxxxxxxxxx'.", 13, 10, 0
; and we're off...
.code
main proc
; edited Tuesday, 31 March 2009
; registers that should be preserved (non-volatile)
push rbx ; save (preserve) register
push rbp ; save (preserve) register
push rsi ; save (preserve) register
push rdi ; save (preserve) register
push r12 ; save (preserve) register
push r13 ; save (preserve) register
push r14 ; save (preserve) register
push r15 ; save (preserve) register
; and align on a 16 byte boundary
; get the handle for the console window to use when writing text
; this is used by WriteFile win api function below
mov ecx, -11 ; put -11 (STD_OUTPUT_HANDLE) into ecx (dword)
call GetStdHandle ; call win api to get console handle
mov hFile, rax ; move returned handle to hFile
; check that the cpuid instruction is supported
; this is very likely supported, but here for completeness
; taken from the AMD64 Architecture Programmer’s Manual
; Volume 3: General-Purpose and System Instructions
pushfq ; save EFLAGS
pop rax ; store EFLAGS in RAX
mov rbx, rax ; save in RBX for test
xor rax, 200000h ; toggle bit #21 (i.e. 22nd bit)
push rax ; put result on stack
popfq ; save changed RAX to EFLAGS
pushfq ; push EFLAGS onto stack
pop rax ; store EFLAGS in RAX
cmp rax, rbx ; determine if bit #21 changed
je no_cpuid ; cpuid not supported (bit did not change)
; cpuid is supported if we did not jump...
; so print the message that it is supported and then
; jump to location where it is actually performed
lea rcx, supp ; load address of supp text into rcx
call lstrlen ; call win api function to get length of text
mov msglen, eax ; move length of string from eax to msglen
lea r9, BytesWritten ; LPDWORD lpNumberOfBytesWritten
mov r8d, msglen ; DWORD nNumberOfBytesToWrite
lea rdx, supp ; LPCVOID lpBuffer
mov rcx, hFile ; HANDLE hFile
call WriteFile ; call win api to write text to console
jmp do_cpuid ; jump over the no_cpuid code path
no_cpuid:
lea rcx, nosupp ; load address of nosupp text into rcx
call lstrlen ; call win api function to get length of text
mov msglen, eax ; move length of string from eax to msglen
lea r9, BytesWritten ; LPDWORD lpNumberOfBytesWritten
mov r8d, msglen ; DWORD nNumberOfBytesToWrite
lea rdx, nosupp ; LPCVOID lpBuffer
mov rcx, hFile ; HANDLE hFile
call WriteFile ; call win api to write text to console
jmp exit ; the no support message was written, so exit
do_cpuid:
lea rcx, vndrid ; load address of vndrid text into rcx
call lstrlen ; call win api function to get length of text
mov msglen, eax ; move length of string from eax to msglen
xor eax, eax ; 0 indicates we want the vendor id string
cpuid ; invoke the cpuid instruction to get the string
; the results are now in ebx, edx, and ecx
; replace the 'x' placeholders with the vendor id
lea r9, vndrid
mov [r9+30], ebx ; the first 4 bytes of the vendor id
mov [r9+34], edx ; the next 4 bytes of the vendor id
mov [r9+38], ecx ; the final 4 bytes of the vendor id
lea r9, BytesWritten ; LPDWORD lpNumberOfBytesWritten
mov r8d, msglen ; DWORD nNumberOfBytesToWrite
lea rdx, vndrid ; LPCVOID lpBuffer
mov rcx, hFile ; HANDLE hFile
call WriteFile ; call win api to write text to console
; edited Tuesday, 31 March 2009
exit:
add rsp, 28h ; clean up the stack
pop r15 ; restore preserved register
pop r14 ; restore preserved register
pop r13 ; restore preserved register
pop r12 ; restore preserved register
pop rdi ; restore preserved register
pop rsi ; restore preserved register
pop rbp ; restore preserved register
pop rbx ; restore preserved register
xor ecx, ecx ; xor ecx (set to 0) for return value
call ExitProcess ; call win api to exit
main endp
end
The command I used to compile the above is (apologies if wrapping make it difficult to read):
ml64 test64.asm /link /subsystem:console /defaultlib:"C:\Program Files\Microsoft SDKs\Windows\v6.0A\Lib\x64\kernel32.lib" /entry:main
And for the moment of truth…
C:\My Projects\Test\ASM\test>test64
CPUID instruction supported!
The processor Vendor ID is 'GenuineIntel'.
Hurray! Unless you sell AMD chips then you might think 'AuthenticAMD' would look better…
If you see something profoundly wrong in the above, please let me know. It's not like I am going to be churning out assembly code all of a sudden, but I would still be interested in knowing…
ODP.NET Tip: Bind Variables, the BindByName Property, and PL/SQL Function Return Values
A question was posed as a follow-up to a previous post here:
http://oradim.blogspot.com/2007/08/odpnet-tip-bind-variables-and.html
The poster wanted to know if you have set BindByName to "true" for an OracleCommand object and the command text for that object calls a PL/SQL function (which of course has a return value but no name), what name should you give to the parameter object that will receive the return value? It's a good question since BindByName set to "true" implies that you've given names to your bind variables!
The short answer is: It doesn't matter as it will be ignored anyway.
As in The Highlander movies, with return values, there can be only one. You can set ParameterDirection to "ReturnValue" for multiple parameters, but it is an error to do so. Try it and see what errors are raised.
Here's a quick little demo showing that it does not matter what name you give to the parameter that you declare as the return value.
First create a simple PL/SQL function (so that there is a return value!) like this:
create or replace function subtract_numbers(p1 in number, p2 in number) return number as
begin
return p1 - p2;
end;
/
I've elected to do a simple bit of subtraction and this is for a reason. Since in subtraction the order of the parameters matters, it is easy to show that the parameters are being handled in the correct order. As you'll see in the sample code below, I use the values "48" and "2" so it would be obvious if the ordering were reversed.
Now, here's some simple (and normal caveats about no error handling, etc. apply) bit of C# to illustrate the point:
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
class Program
{
static void Main(string[] args)
{
// adjust connect string to suit your environment
string constr = "user id=hr;" +
"password=hr;" +
"data source=orademo;" +
"pooling=false;" +
"enlist=false";
// create and open connection
OracleConnection con = new OracleConnection(constr);
con.Open();
// create command object
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "subtract_numbers";
cmd.CommandType = CommandType.StoredProcedure;
// set bindbyname to true so parameters can be added
// in any order and to illustrate what happens with a
// return value
cmd.BindByName = true;
// the first input parameter to the pl/sql function
OracleParameter p1 = new OracleParameter();
p1.Direction = ParameterDirection.Input;
p1.OracleDbType = OracleDbType.Int64;
p1.ParameterName = "p1";
p1.Value = 48;
// the second input parameter to the pl/sql function
OracleParameter p2 = new OracleParameter();
p2.Direction = ParameterDirection.Input;
p2.OracleDbType = OracleDbType.Int64;
p2.ParameterName = "p2";
p2.Value = 2;
// the return parameter from the pl/sql function
OracleParameter p3 = new OracleParameter();
p3.Direction = ParameterDirection.ReturnValue;
p3.OracleDbType = OracleDbType.Int64;
p3.ParameterName = "ignored";
// add the parameters in a "wrong" order since
// bindbyname is true -- this is key
cmd.Parameters.Add(p1); // input #1
cmd.Parameters.Add(p3); // return value
cmd.Parameters.Add(p2); // input #2
// execute the stored pl/sql code
cmd.ExecuteNonQuery();
// write the result to the console window
Console.WriteLine("The difference of {0} and {1} is {2}",
p1.Value, p2.Value, p3.Value);
// prevent console window from closing when run from VS
Console.WriteLine();
Console.Write("ENTER to continue...");
Console.ReadLine();
// clean up
p3.Dispose();
p2.Dispose();
p1.Dispose();
cmd.Dispose();
con.Dispose();
}
}
Notice how the parameters are added to the parameter collection "out of order". This is OK since BindByName is set to "true". You can comment out the line that sets BindByName and see an exception raised if you want. Anyway, when I execute this code, I get the expected results as follows:
The difference of 48 and 2 is 46
ENTER to continue...
So, even though the return value parameter was added to the collection in the second position everything works as desired. Yes, but I gave it a name of "ignored" so how do we know that name is not really being used? Well there are two easy ways to find out. One, just don't give the parameter a name at all and everything should continue to execute just fine. It's easy to ignore a name that's not there! Or, two, if you are an inquisitive type, enable SQL tracing (search the web for 10046 trace -- you'll find a million hits) and see what ODP.NET actually sent to the database. Here's a snippet of a trace file from the above code (with the parameter name of "ignored" in place):
PARSING IN CURSOR #2 len=54 dep=0 uid=82 oct=47 lid=82 tim=10601980885 hv=1771638930 ad='7ff39775518' sqlid='gx0kvypntk44k'
Begin :ret := subtract_numbers(p1=>:v0, p2=>:v2); End;
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=10601980885
BINDS #2:
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=00 csi=00 siz=72 off=0
kxsbbbfp=1bb2dd60 bln=22 avl=00 flg=05
Bind#1
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=24
kxsbbbfp=1bb2dd78 bln=22 avl=02 flg=01
value=48
Bind#2
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=48
kxsbbbfp=1bb2dd90 bln=22 avl=02 flg=01
value=2
See how ODP.NET generated a little anonymous block and substituted "ret" for the return value parameter? You should see the same thing even if there is no name given to the return parameter. You can also clearly see how the input parameter names are associated with the input values.
HotSos Symposium 2009 Materials
The paper and all sample code from my HotSos Symposium 2009 presentation entitled "Instrumented Code Is Better Code" is available as a single .zip file. To download the .zip file, click here.
Oracle ACE Director No More
Today marks my first day back at Oracle Corp. and I find myself in the same boat as Kevin Closson. I'm not saying I'm sitting in the same section mind you, just that I am in the same boat as regards to no longer being eligible for the Oracle ACE program.
I think I have removed the ACE and Director logos as well as text from the appropriate spots, but if I missed a place or two, well, I'll try to get them removed too!
As Kevin says, at least there is that vest thing...
So, back to Oracle then?
Next week will mark the last week at my current location as I have recently decided to return to Oracle Corp.
I will be going back to the world of C and UNIX derivatives having accepted a position on the non-Windows PLBDE team.
It's hard to say what that will mean for the blog here, which has been pretty quiet from my side as of late to be sure, but perhaps I will branch out from my typical topics of ODP.NET and/or Oracle on Windows. We'll see.
HotSos Symposium 2009
The HotSos Symposium 2009 is coming up soon. For 2009, the symposium runs from 8 March through to 12 March. This is always one of my favorite conferences of the year, and I will be presenting on "Instrumented Code is Better Code".
The conference, as always, features a fantastic line-up this year. I'm particularly interested in the keynote being delivered by Chris Date. In addition, Jonathan Lewis will be delivering the training day material. If you have the opportunity to attend this conference, I highly recommend it.
Now, I'd better get to work!
Does ODP.NET Send Data to the Server for Out PL/SQL Parameters?
I was recently discussing (and by that I mean trading emails) with a friend about an interesting behavior observed when using PL/SQL "out" VARCHAR2 variables with ODP.NET - in this case it looks like ODP.NET is sending data from the client and to the server for a parameter that is defined as "out" in a PL/SQL procedure. This seems like strange behavior and I don't have a good answer as to why data would be sent from the client to the server for a parameter declared as an "out" parameter.
In any case here's a quick and dirty sample that illustrates this (on my test system anyway):
PL/SQL Procedurecreate or replace procedure ptest (p_vc out varchar2) as
begin
p_vc := rpad ('a', 31999, 'a');
end;
/
using System;
using System.Data;
using System.Data.OracleClient;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace Miscellaneous {
class Program {
static void Main(string[] args) {
string constr = "User Id=markwill;" +
"Password=oracle;" +
"Data Source=orademo;" +
"Pooling=false;" +
"Enlist=false";
TestODP(constr);
TestMS(constr);
Console.WriteLine();
Console.Write("ENTER to continue...");
Console.ReadLine();
}
static void TestODP(string constr) {
Oracle.DataAccess.Client.OracleConnection con = new Oracle.DataAccess.Client.OracleConnection(constr);
con.Open();
Oracle.DataAccess.Client.OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "alter session set events '10046 trace name context forever, level 12'";
cmd.ExecuteNonQuery();
cmd.CommandText = "ptest";
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
Oracle.DataAccess.Client.OracleParameter p_vc = new Oracle.DataAccess.Client.OracleParameter();
p_vc.ParameterName = "p_vc";
p_vc.OracleDbType = OracleDbType.Varchar2;
p_vc.Size = 32000;
p_vc.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p_vc);
cmd.ExecuteNonQuery();
Console.WriteLine("p_vc = {0}", p_vc.Value);
p_vc.Dispose();
cmd.Dispose();
con.Dispose();
}
static void TestMS(string constr) {
System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection(constr);
con.Open();
System.Data.OracleClient.OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "alter session set events '10046 trace name context forever, level 12'";
cmd.ExecuteNonQuery();
cmd.CommandText = "ptest";
cmd.CommandType = CommandType.StoredProcedure;
System.Data.OracleClient.OracleParameter p_vc = new System.Data.OracleClient.OracleParameter();
p_vc.ParameterName = "p_vc";
p_vc.OracleType = OracleType.LongVarChar;
p_vc.Size = 32000;
p_vc.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p_vc);
cmd.ExecuteNonQuery();
Console.WriteLine("p_vc = {0}", p_vc.Value);
cmd.Dispose();
con.Dispose();
}
}
}
This is pretty straight-forward and simply opens a connection, enables Extended SQL Tracing via setting event 10046, creates a parameter for the output value, and calls the stored procedure.
Looking at the respective trace files I find this for the ODP.NET test:
PARSING IN CURSOR #4 len=28 dep=0 uid=88 oct=47 lid=88 tim=9306513611 hv=1721325005 ad='7ff2eca8c38' sqlid='dk08s2dm9kpfd'
Begin ptest(p_vc=>:v0); End;
END OF STMT
PARSE #4:c=0,e=798,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=9306513607
WAIT #4: nam='SQL*Net more data from client' ela= 48 driver id=1413697536 #bytes=44 p3=0 obj#=-1 tim=9306513786
WAIT #4: nam='SQL*Net more data from client' ela= 7 driver id=1413697536 #bytes=24 p3=0 obj#=-1 tim=9306513827
WAIT #4: nam='SQL*Net more data from client' ela= 171 driver id=1413697536 #bytes=4 p3=0 obj#=-1 tim=9306514027
WAIT #4: nam='SQL*Net more data from client' ela= 28 driver id=1413697536 #bytes=49 p3=0 obj#=-1 tim=9306514085
BINDS #4:
Bind#0
oacdty=01 mxl=32767(32000) mxlc=32000 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=178 siz=32767 off=0
kxsbbbfp=1c3d8000 bln=32767 avl=32000 flg=05
value="Ä6¿¿"...
WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9306514373
WAIT #4: nam='SQL*Net more data to client' ela= 49 driver id=1413697536 #bytes=8216 p3=0 obj#=-1 tim=9306514456
WAIT #4: nam='SQL*Net more data to client' ela= 23 driver id=1413697536 #bytes=8192 p3=0 obj#=-1 tim=9306514507
WAIT #4: nam='SQL*Net more data to client' ela= 271 driver id=1413697536 #bytes=8192 p3=0 obj#=-1 tim=9306514805
EXEC #4:c=0,e=1139,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=9306514839
XCTEND rlbk=0, rd_only=1
WAIT #4: nam='SQL*Net message from client' ela= 69517 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9306584449
XCTEND rlbk=0, rd_only=1
As you can see there are several "SQL*Net more data from client" messages. Of course, these are followed by the sending of data to the client.
Now here is the relevant portion of the trace file for the MS provider test:
PARSING IN CURSOR #3 len=30 dep=0 uid=88 oct=47 lid=88 tim=9306615423 hv=1003497284 ad='7ff27721950' sqlid='42zzxzhxx09u4'
begin ptest(p_vc=>:p_vc); end;
END OF STMT
PARSE #3:c=0,e=1507,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=9306615420
BINDS #3:
Bind#0
oacdty=01 mxl=32767(32000) mxlc=32000 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=178 siz=32767 off=0
kxsbbbfp=1c3d8000 bln=32767 avl=00 flg=05
WAIT #3: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9306615726
WAIT #3: nam='SQL*Net more data to client' ela= 69 driver id=1413697536 #bytes=8216 p3=0 obj#=-1 tim=9306615822
WAIT #3: nam='SQL*Net more data to client' ela= 18 driver id=1413697536 #bytes=8192 p3=0 obj#=-1 tim=9306615861
WAIT #3: nam='SQL*Net more data to client' ela= 200 driver id=1413697536 #bytes=8192 p3=0 obj#=-1 tim=9306616081
EXEC #3:c=0,e=612,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=9306616112
XCTEND rlbk=0, rd_only=1
WAIT #3: nam='SQL*Net message from client' ela= 60351 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9306676628
XCTEND rlbk=0, rd_only=1
In this case there are no messages from the client and in the bind section there is no "value=" element.
An interesting wrinkle is that if I set the parameter direction to "ParameterDirection.InputOutput" using ODP.NET I do not observe the sending of data from the client.
Why, I wonder, does ODP.NET send data to the server for an output parameter? Ideas? Similar behavior observed?
Oracle Open World 2008 Podcast
Oracle Magazine Feature Casts
The title of the podcast is:
Origins of a .NET Developer