Mark Williams :: Blog

SQL*Plus, EZCONNECT, Password Prompt, and ORA-12504

2009-07-11T11:18:00.016-04:00

I use SQL*Plus a great deal and I find the "Easy Connect Naming Method" a convenient option.

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)

2009-07-06T23:46:00.001-04:00

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 Environment

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

Oracle Database Server/Host: oel01 (Oracle Enterprise Linux 32-bit server)
Oracle Database: SID value of OEL11GR1, Service Name value of OEL11GR1.SAND, version 11.1.0.7
Development Machine: Hostname of 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

Important Information

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

Download the Correct Packages

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)

Install the Instant Client Packages

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 Package

Much 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 Studio

The 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

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.

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!

Microsoft To Deprecate System.Data.OracleClient

2009-06-18T18:10:00.005-04:00

I found the following to be an interesting announcement:

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).

Oracle Pro*C on Windows with Express Edition Products

2009-05-30T22:25:00.002-04:00

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 Packages

It 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 File

I 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 Edition

Next 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

Create a New Project

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

Create the Pro*C Source File

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);
}

Add a Reference to the Generated C Source File

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

Add the Pro*C Library File to the Project

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

Add the Custom Build Step

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 Verify

All 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>

Conclusion

Whew! 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)

jmp'ing around Win64 with ml64.exe and Assembly Language

2009-03-30T20:51:00.001-04:00

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 Everything

NOTE: 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

sub rsp, 28h ; adjust stack for local storage if needed
; 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

2009-03-24T21:16:00.002-04:00

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

2009-03-14T14:14:00.001-04:00

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

2009-01-20T17:16:00.001-05:00

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?

2009-01-10T20:09:00.001-05:00

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

2008-11-07T20:12:00.001-05:00

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?

2008-10-16T20:27:00.001-04:00

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 Procedure

create or replace procedure ptest (p_vc out varchar2) as
begin
p_vc := rpad ('a', 31999, 'a');
end;
/

C# Sample Console

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

2008-09-25T15:40:00.004-04:00

I've never really been a prolific blogger and the "interruption" of OOW 2008 has definitely impacted this. However, my podcast with Tom Haunert of Oracle Magazine is now available at the following location:

Oracle Magazine Feature Casts

The title of the podcast is:

Origins of a .NET Developer

My Current Development Environment

2008-08-27T21:54:00.001-04:00

Whilst I am primarily a DBA during the day, one of the things that I really enjoy about my current engagement is that it has given me the opportunity to dip my toes back in the development pool a bit. I was describing my current development environment to a friend recently and this person was shocked by the details. I realize that in this day and age of code spitters (term coined by Jeff Richter?) and "click through the wizard for instant coding success" sorts of activities that this environment is something of an anachronism.

Without further ado, the o/s:

$ uname -a
HP-UX zzz B.11.31 U ia64 4131905082 unlimited-user license

My "IDE":

$ which vi
/usr/bin/vi

And the compiler:

$ gcc -v
Using built-in specs.
Target: ia64-hp-hpux11.31
Configured with: ../gcc/configure
Thread model: posix
gcc version 4.2.1

Yes, I actually have to do things like create a Makefile in such an environment!

Joking aside though, one of the things that I firmly believe is that instrumented code is better code. One of the characteristics of this kind of development environment, I feel, is that it really encourages strongly instrumenting the code. Yes, in some circumstances I could use the gdb debugger, but building proper instrumentation into the code and offering a simple switch to enable it (yes, it is in the release build) works very nicely.

Another thing that some people might find "weird" about this application is that it is a database application (Oracle of course) that contains absolutely no SQL statements. Really. It is a "thick database" application (Toon Koppelaars term?). That is, there are many statements like "begin package.procedure...; end;" in the code. All the real work is done using PL/SQL packages (and bodies) in the database.

As a .NET developer it is often easy to lean heavily on Visual Studio for debugging, but I think Microsoft also agrees with the "instrumented code is better code" philosophy - check out the System.Diagnostics.Trace class in the Visual Studio Combined Help Collection for more information.

Lastly, if I've misappropriated any of the terms above, apologies!

Oracle OpenWorld 2008 - Just Say Hi!

2008-08-22T21:35:00.001-04:00

Just a quick note... if you happen to be coming to Oracle OpenWorld 2008 in San Francisco, I will be helping out in the .NET Hands-On Labs Monday (22 Sep) afternoon. I'll also be at the "OTN ACE Office Hours" on Monday (22 Sep) as well as Thursday (25 Sep). See the link for schedules as well as the great folks who will be available. I've also been known to be around the demo grounds booth as well. Please say "hi" if you get the chance!

Creating a Windows Service that uses ODP.NET, Oracle Database, a Custom Event Log, and Local Service

2008-07-24T19:41:00.001-04:00

The steps necessary to create a Windows Service that accesses Oracle Database via ODP.NET and uses the "NT AUTHORITY\LOCAL SERVICE" low-privileged operating system account are not substantially different from the steps necessary to create a service that does not access Oracle Database. As such, the steps below are substantially similar to the steps in the "Walkthrough: Creating a Windows Service Application in the Component Designer" topic available in the Visual Studio Programmer's Guide for the .NET Framework. You may wish to review this walkthrough in addition to the steps I provide below.

My goal is to provide the steps necessary to create a (very) simplistic Windows Service that uses the "NT AUTHORITY\LOCAL SERVICE" account to "talk" to Oracle Database. This is by no means an all-encompassing treatment of the topic of using a Windows Service in conjunction with ODP.NET, etc. However, it should provide the minimum amount of information to get started down this path.

So, without further ado, let's get started...

The Setup

Because this solution needs an installer, I am not using an Express Edition of Visual Studio. I'm using Visual Studio 2005 with ODP.NET 11.1.6.20 on a Windows XP Professional machine. This machine will also serve as the deployment target for the installer and is hosting an Oracle 11g Release 1 database.

Create the Visual Studio Project

1. Select File -> New -> Project... from the main menu.

2. In the New Project dialog expand the Visual C# node under Project types, click the Windows option, select Windows Service in the Templates list, enter OraWinService as the Name, and select an appropriate location (or simply accept the suggested location). The New Project dialog should resemble the following:

3. Click OK to create the project.

Add a Reference to the ODP.NET Assembly

1. Select Project -> Add Reference... from the main menu.

2. In the Add Reference dialog, scroll down and select Oracle.DataAccess under the Component Name. The dialog should look similar to the following:

3. Click OK to add the reference.

Set the Service Properties

1. The Service1.cs file should be displayed in the design view. If it is not, right-click the Service1.cs file in the Solution Explorer and select View Designer.

2. Ensure the Service1.cs file is selected in the designer by clicking anywhere on the surface except for on the hyperlinks.

3. In the Properties window for Service1.cs, enter OraWinSvcDemo for the (Name) property. Also enter OraWinSvcDemo for the ServiceName property. Set the CanPauseAndContinue property to True. The AutoLog property should also be set to True, which is the default. When complete, the Properties window should resemble:

Edit the Main Method

1. Double-click the Program.cs file in the Solution Explorer to open it in the editor.

2. Replace the auto-created Main method with the following code:

static void Main() {
System.ServiceProcess.ServiceBase[] ServicesToRun;
ServicesToRun = new System.ServiceProcess.ServiceBase[] { new OraWinSvcDemo() };
System.ServiceProcess.ServiceBase.Run(ServicesToRun);
}

3. Select File -> Save All from the main menu and then select File -> Close from the main menu to close the Program.cs file.

Add an EventLog Component

1. With the Service1.cs file displayed in the design view, expand the Components node in the Toolbox and drag and drop an EventLog component onto the design surface. This will create an instance of the EventLog component named eventLog1.

2. If eventLog1 is not selected, left-click it to select. In the Properties window enter OraWinSvcLog for the Log property and enter OraWinSvcSource for the Source property. The Properties window should now look as follows:

Define the Service Methods

1. Right-click the Service1.cs file in the Solution Explorer and select View Code from the context menu to open the file in the editor.

2. Add the following three lines to the end of the "list of using statements". A timer will be used to periodically trigger an event to write information to the Event Log. The other two lines are boilerplate inclusions for the ODP.NET namespaces.

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

3. At the top of the class definition (but outside of any method definitions) add the following (be sure to adjust the Data Source in the connection string as appropriate for your environment). The user will be created later:

string constr = "User Id=/; Data Source=orademo; Enlist=false";
OracleConnection con = null;
Timer timer1 = null;

At this time the entire Service1.cs file should contain the following:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.ServiceProcess;
using System.Text;
using System.Timers;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace OraWinService {
public partial class OraWinSvcDemo : ServiceBase {
    string constr = "User Id=/; Data Source=orademo; Enlist=false";
    OracleConnection con = null;
    Timer timer1 = null;

    public OraWinSvcDemo() {
      InitializeComponent();
    }

    protected override void OnStart(string[] args) {
      // TODO: Add code here to start your service.
    }

    protected override void OnStop() {
      // TODO: Add code here to perform any tear-down necessary to stop your service.
    }
}
}

4. Change the constructor to the following to create the timer and set the handler function for the timer event:

public OraWinSvcDemo() {
InitializeComponent();

timer1 = new Timer();

timer1.Elapsed += new ElapsedEventHandler(OnTimer);
}

5. Add the OnTimer method below the constructor to handle the timer event:

private void OnTimer(object source, ElapsedEventArgs e) {
if (con != null) {
    if (con.State == ConnectionState.Open) {
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "select user, sysdate from dual";
      OracleDataReader dr = cmd.ExecuteReader();
      while (dr.Read()) {
        eventLog1.WriteEntry(String.Format("User: {0}, Date: {1}", dr[0].ToString(), dr[1].ToString()));
      }

      dr.Dispose();
      cmd.Dispose();
      con.Close();
    }
}
}

6. Replace the generated OnStart method with the following which establishes a connection to the database and sets the timer properties (interval of 3 seconds and enables the timer):

protected override void OnStart(string[] args) {
eventLog1.WriteEntry("In OnStart");

con = new OracleConnection(constr);

try {
    con.Open();
    eventLog1.WriteEntry("Successfully connected to Oracle!");
}
catch (OracleException ex) {
    eventLog1.WriteEntry("OracleException: " + ex.Message);
}

timer1.Interval = 3000;
timer1.Enabled = true;
}

7. Replace the generated OnStop method with the following code:

protected override void OnStop() {
eventLog1.WriteEntry("In OnStop");

timer1.Enabled = false;

if (con != null) {
con.Dispose();
}
}

8. Add a handler for the OnPause event for the service below the OnStop method:

protected override void OnPause() {
eventLog1.WriteEntry("In OnPause.");

timer1.Enabled = false;
}

9. Add a handler for the OnContinue event for the service below the OnPause method:

protected override void OnContinue() {
eventLog1.WriteEntry("In OnContinue.");

timer1.Enabled = true;
}

10. Add a handler for the shutdown event below the handler for the OnContinue event:

protected override void OnShutdown() {
eventLog1.WriteEntry("In OnShutdown");

timer1.Enabled = false;

if (con != null) {
con.Dispose();
}
}

11. Select File -> Save All from the main menu to save all work and File -> Close to close the Service1.cs file in the editor.

Create Installers for the Components

1. Service1.cs should in the design view mode within the editor. If it is not, right-click it in the Solution Explorer and select View Designer from the context menu.

2. Create an installer by right-clicking on the design surface and selecting Add Installer from the context menu:

3. After clicking Add Installer, ProjectInstaller.cs will be created and opened in the editor. Click the Service1.cs [Design] tab to switch back to Service1.cs in the editor. Add an installer for the eventLog1 component by right-clicking it and selecting Add Installer from the context menu:

4. After adding the eventLog1 installer, left-click the serviceProcessInstaller1 component to select it. Next select LocalService from the drop-down list:

5. Select File -> Save All from the main menu to save all files.

6. Select Build -> Build OraWinService from the main menu to build the project, then select File -> Close twice to close the ProjectInstaller.cs and Service1.cs files.

Create a Setup Project for the Service

1. Select File -> Add -> New Project... from the main menu, in the Project types pane expand the Other Project Types node, select Setup and Deployment, select Setup Project in the Templates list, enter OraWinServiceSetup for the Name, and select an appropriate location (or accept the default supplied value). The Add New Project dialog should look as follows:

2. Click OK to add the setup project to the solution.

3. In Solution Explorer, right-click OraWinServiceSetup then select Add -> Project Output... from the context menu. The Add Project Output Group dialog will be displayed:

4. Ensure Primary Output is selected and click OK.

5. In Solution Explorer, right-click OraWinServiceSetup then select View -> Custom Actions from the context menu to open the custom actions in the editor.

6. In the Custom Action editor, right-click Custom Actions and select Add Custom Action... from the context menu:

7. In the Select Item in Project dialog, double-click the Application Folder item, then select Primary output from OraWinService (Active), and click OK to add the output to each of the Install, Commit, Rollback, and Uninstall nodes:

8. Select File -> Save All from the main menu and then File -> Close for each opened file in the editor.

9. Build the setup program by right-clicking OraWinServiceSetup in Solution Explorer and selecting Build from the context menu:

Install the Windows Service

1. Using Windows Explorer navigate to the directory where the setup project was built. For example, on my system this is in C:\My Projects\Test\C#\OraWinServiceSetup\Debug directory.

2. Install the service by right-clicking the OraWinServiceSetup.msi file and selecting Install from the context menu. Step through the installation wizard to complete the installation.

Create the Database User

1. Connect to the database that the service will use as a DBA user and determine the value of the os_authent_prefix parameter:

SQL> show parameter os_authent_prefix

NAME TYPE VALUE
----------------- ----------- -----
os_authent_prefix string

2. As you can see here I do not have a value for this parameter; therefore, when I create the user I do not use a prefix:

SQL> create user "NT AUTHORITY\LOCAL SERVICE" identified externally;

User created.

SQL> grant create session to "NT AUTHORITY\LOCAL SERVICE";

Grant succeeded.

3. For more information on creating an Operating System authenticated user, see my earlier post on Configuring Windows Authentication.

Running the Service and Viewing Results

1. Open the Services management console by clicking Start -> Administrative Tools -> Services. You can also right-click the My Computer icon on the desktop and select Manage from the context menu.

2. Locate the OraWinSvcDemo service in the list of services and click the start button:

3. Open the Event Viewer by clicking Start -> Administrative Tools -> Event viewer. Or right-click the My Computer icon on the desktop and select Manage from the context menu (if you have not already done so).

4. In the Event Viewer, select OraWinSvcLog by left-clicking it. As the service writes entries to the log every 3 seconds while it is executing, you should see several entries after a 10 seconds or so:

5. Experiment with the different service states (paused, resume, restart, etc) and verify the entries in the Event Viewer.

Oracle OpenWorld 2008 San Francisco

2008-06-28T17:21:00.001-04:00

It's hard to believe, but Oracle OpenWorld 2008 in San Francisco is coming round fast! I will be attending this year and here's my Oracle Mix profile link (free account required):

I've been really busy with the day job though I plan to have some more technical content in the near future! I've a couple of ideas swimming around that may be interesting as they're based on real questions.

Recommended by Joe

2008-05-27T19:21:00.001-04:00

A friend of mine at Oracle (that is to say Greg is still at Oracle whilst I am not) pointed out to me that Microsoft's Joe Stagner has Pro .NET Oracle Programming as a recommended book on the Oracle - ASP.NET forum. Currently the recommended books list looks like this (subject to change naturally):

That got me to thinking a bit. It has been just over 4 years since I started writing that book. (I started the first chapter in March 2004). Certainly a lot has changed since then with several releases of the Oracle Data Provider for .NET, Oracle Database, Visual Studio, the Oracle Developer Tools for Visual Studio .NET, etc. I was just at one of the major booksellers here locally over the weekend and the "computer section" has dwindled immensely. I wonder if it would be worthwhile to update the book? There are several things I would want to change to be sure, but would it be worth it? Do people get their technical information from OTN and MSDN mostly now?

ODTUG Kaleidoscope 2008

2008-05-09T17:17:00.001-04:00

It's ODTUG time again! It is a great opportunity to see lots of Oracle folks like Tom Kyte, Sue Harper, Joel Kallman, David Peake, Carl Backstrom, as well as a ton of other great people!

June 15-19, 2008
New Orleans, Louisiana

ODTUG Kaleidoscope 2008 – the annual conference of the Oracle Development Tools User Group, will be held this June 15-19, 2008 in New Orleans, Louisiana. Packed with technical sessions, four individual keynotes, three symposiums, a dedicated Hyperion track crafted by the masters, and a special gathering of Oracle Ace Directors, ODTUG Kaleidoscope offers the most comprehensive in-depth technical conference available for Oracle professionals. Unlike other conferences in this category, ODTUG Kaleidoscope strives to provide real world knowledge by real world technologists and provide a conference experience that will truly help Oracle developers take their role to the next level. For additional information and a complete agenda, visit www.odtugkaleidoscope.com.

Using Delegates to make a Synchronous Database Call Asynchronously

2008-05-03T11:15:00.001-04:00

There are times when a tight coupling exists between user interaction with an application and database calls. That is, a user initiates an action that requires a database call, the database call executes, the call to the database returns, the user responds, and the cycle continues in fairly rapid succession. However, there are also times when a user may initiate an action that requires a lengthy database call. Of course, lengthy is a relative term and would be defined within your own environment. What is lengthy to one may not be lengthy to another. Something of a judgement call on that one.

If a lengthy database call is initiated from an application you may desire the capability to continue to do other work within that application while the database call executes. However, as current production versions of the Oracle Data Provider for .NET (ODP.NET) do not offer BeginXXX and EndXXX methods (such as BeginExecuteNonQuery() for example), you may think offering this functionality in your ODP.NET application is not possible or is difficult. Again, depending on your application specifics, offering such asynchronous behavior may give your application better responsiveness and increase the end-user experience positively.

One method of achieving this goal (calling a synchronous method asynchronously) is by using delegates. If you are not familiar with delegates, I suggest reviewing the Delegates topic in the online version of the C# Programming Guide on Microsoft's MSDN site. In addition to the standard reference information, there is also a tutorial (again located on MSDN) which can be found here. Of course these links are subject to change in the future.

In order to implement this technique you will need two methods (other than the Main method of course). One method will perform the synchronous database call and the other method will be invoked when that call is complete. A delegate is created for the method that performs the database call. This is the key to the technique. By creating a delegate for this synchronous method it becomes possible to invoke it asynchronously via the BeginInvoke() method exposed by the delegate. The delegate keyword actually causes the compiler to expand the declaration into a class which contains a BeginInvoke() method (among others).

The sample code below is a simple implementation of this technique using a console application. In this application a "do nothing" call to the database occurs. This call does nothing more than sleep inside the database for a specified period of time. As noted in the source code comments, the database user must have the "execute" privilege on the dbms_lock PL/SQL package for the code to work. Of course a real application would not normally connect to the database only to sleep for a period of time and return; however, this is a sufficient method for illustrating that the call is asynchronous in relation to the main thread.

The basic flow is as follows:

Create the delegate
Invoke the delegate to execute asynchronously from the main thread
Continue processing in the main thread while the database call is in progress
When database call is complete, invoke the OnCallComplete() method
Work in the main thread continues for a short period of time
Along the way prompt information is displayed and a final prompt is provided

It is necessary to cause the main thread to have a longer duration than the thread performing the database call because if the main thread ended before the database call thread the entire console application would terminate.

When the application is executed you should see output similar to the following:

Asynchronous call in progress.
Will sleep in database for 10 seconds.
    Doing work in Main thread...
Enter LongDatabaseCall
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
Exit LongDatabaseCall
Enter OnCallComplete
    Perform post-database call actions...
Exit OnCallComplete
    Doing work in Main thread...
    Doing work in Main thread...
    Doing work in Main thread...
Press ENTER to continue...

As you can see, the basic flow above is represented here and work in the main thread continues after the LongDatabaseCall method has been invoked via the delegate.

Here's the (hopefully) well-commented code to produce the above:

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

namespace AsynchronousDemo
{
class Program
{
    // change as necessary for your environment
    // the user must have execute privilege on dbms_lock
    const string constr = "User Id=hr;" +
                          "Password=hr;" +
                          "Data Source=orademo;" +
                          "Enlist=false";

// the delegate for the function that makes the db call
internal delegate void LongCallDelegate(int sleepSeconds);

    static void Main(string[] args)
    {
      // the amount of time the call to the database will sleep
      int sleepSeconds = 10;

      // write marker text to console to indicate beginning of process
      Console.WriteLine("Asynchronous call in progress.");
      Console.WriteLine(" Will sleep in database for {0} seconds.", sleepSeconds.ToString());

// used to call the function
LongCallDelegate ldc = new LongCallDelegate(LongDatabaseCall);

      // call BeginInvoke to make call asynchronous
      // the method to invoke when the call has completed is passed
      // as the second parameter
      ldc.BeginInvoke(sleepSeconds, OnCallComplete, ldc);

      // cause main thread to "pause" while the database call is in progress
      // add an arbitrary number (5) to the sleepSeconds to help show that
      // the database call is "independent" of the main thread
      for (int i = 1; i < sleepSeconds + 5; i++)
      {
        Console.WriteLine("    Doing work in Main thread...");
        Thread.Sleep(1000);
      }

      // prevent console from automatically closing as when run from VS in debug mode
      Console.WriteLine("Press ENTER to continue...");
      Console.ReadLine();
    }

    static void LongDatabaseCall(int sleepSeconds)
    {
      // this is the function that makes the database call
      // it will be called from the delegate to make the call
      // asynchronous in relation to the main thread

// marker text
Console.WriteLine(" Enter LongDatabaseCall");

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

      // create command object
      // will call dbms_lock to sleep for x seconds in database
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "begin dbms_lock.sleep(:1); end;";

      // parameter for the sleep duration
      OracleParameter p_sleepSeconds = new OracleParameter("1",
                                                           OracleDbType.Decimal,
                                                           sleepSeconds,
                                                           ParameterDirection.Input);

// add parameter to the collection for the command object
cmd.Parameters.Add(p_sleepSeconds);

// make the database call
cmd.ExecuteNonQuery();

      // clean up
      p_sleepSeconds.Dispose();
      cmd.Dispose();
      con.Dispose();

      // marker text
      Console.WriteLine(" Exit LongDatabaseCall");
    }

    static void OnCallComplete(IAsyncResult ar)
    {
      // marker text
      Console.WriteLine(" Enter OnCallComplete");

// get the delegate from the IAsyncResult object
LongCallDelegate ldc = (LongCallDelegate)ar.AsyncState;

// Must call EndInvoke to pair with BeginInvoke
ldc.EndInvoke(ar);

      // marker text
      Console.WriteLine("    Perform post-database call actions...");
      Console.WriteLine(" Exit OnCallComplete");
    }
}
}

Generating Test Data

2008-04-08T19:54:00.001-04:00

I'm an advocate of testing things - it can be a great way to discover different behaviors and learn how things work (or perhaps why they didn't work the way you thought they might!). When it comes to testing database applications, having some test data is certainly helpful. Ideally, I like to have a fully-loaded production database from which to draw this data into a test environment. Sometimes, however, this is not possible. In cases like these I use the "trick" described below to create my own test data.

When I need to generate test data I frequently call upon a seemingly little-known PL/SQL Package supplied with Oracle Database called "dbms_random". As you may guess this package can be used to generated random data. Rather than explain the package details (they are short and you can read them using the link), I'll just present a quick way to generate some data using SQL*Plus and the dbms_random package.

First, I'll need a table:

create table test_data
(
id number,
first_name varchar2(16),
last_name varchar2(24),
amount number(6,2),
purchase_date date
);

As you can see, this table is nothing to write home about, but it does mirror what a real-world table would look like in a lot of cases.

When I am creating test data I generally prefer the data to be reproducible. That is, if I execute the process more than once I like to get the same data each time. To accomplish this with the dbms_random package I call the "seed" procedure and provide an initial seed value:

exec dbms_random.seed(42);

Let's say I wanted to populate this table with 10,000 rows of data. I use the following to do this:

insert into test_data
select
rownum,
initcap(dbms_random.string('l',dbms_random.value(2,16))),
initcap(dbms_random.string('l',dbms_random.value(2,24))),
round(dbms_random.value(1,1000),2),
to_date('01-JAN-2008', 'DD-MON-YYYY') + dbms_random.value(-100,100)
from
(select level from dual connect by level <= 10000);

Starting at the bottom of the SQL text is a neat trick using "dual" and "connect by" to essentially create an "unlimited dual" for generating lots of rows. This trick was (I believe) originally put forth by Mikito Harakiri and I discovered it via an Ask Tom thread.

The remainder of the text is fairly straight-forward in its use of the dbms_random package. I use the "string" function to generate a random, lowercase value (which is subsequently passed to "initcap" to capitalize the first character of each string), the "value" function is used to create a random numeric value (which is passed to "round" to make it look like a purchase amount), and then I use a fixed-date to which I add (possibly a negative value) to create a set of valid dates within a range.

The first 10 rows of this data when selected from the table look like:

ID FIRST_NAME       LAST_NAME                    AMOUNT PURCHASE_DATE
--- ---------------- ------------------------ ---------- --------------------
1 Oqq              Mxsazbwyx                    521.33 22-MAR-2008 16:49:40
2 Jjgqrywtxbdn     Fwwbzshhkbqzb                921.47 04-OCT-2007 09:10:00
3 Zxflhufls        Mstwydowbaogeyyjiles         172.34 20-MAR-2008 10:22:05
4 Zjjxtyysitsog    Zxrzqeflxgo                  882.16 26-DEC-2007 18:56:44
5 Kjmuvbrqx        Hfu                          742.61 16-OCT-2007 14:35:27
6 Oywaibiyp        Angvlehlmeujfdlhdmtt          664.5 29-FEB-2008 12:50:40
7 Uhwyvla          Nhbwcv                       168.99 27-DEC-2007 22:29:59
8 Kpdiqafanbvzt    Phjeqwelyugrmahybocwbhvp     813.81 01-MAR-2008 09:15:59
9 Tvezuvrgnzzqkpq Pjyygoqx                     880.09 21-NOV-2007 00:42:07
10 Olchylbeft       Nflaxjqfkmkgt                847.71 07-DEC-2007 16:53:23

10 rows selected.

Can this technique always be used? No, probably not. For example, the names are not exactly what you might call "proper" names. However, I do find that this technique can be useful when I need to create some data to perform some testing with. Perhaps it will be helpful to you too if you experiment with it and find the right combination of values to use in your tests.

Applications and Data == Importance Envy?

2008-04-06T12:34:00.001-04:00

I was recently reading an interesting posting (and comments) over on Doug Burns' Blog:

http://oracledoug.com/serendipity/index.php?/archives/1394-Theres-Hope-for-us-all.html

Doug's blog is a great resource and I encourage you to check it out if you have a few spare minutes.

I found this interesting because the "debate" around which is more important -- the application or the data -- raises its head every now and then. Now, my being a DBA in my "day job" may automatically bias me in this context; however, I was a developer before I was a DBA. This, of course, does not mean I am bias-free, but I do hope that I am not so closed-minded that I may see both sides of a discussion.

When I hear this question (which is more important? the application? or the data? OK, so that is three questions, but you understand.) I immediately think of a question I was posed long ago in school - which is more important, the heart or the blood? Of course there is not a perfect correlation between biology and applications and data, but I think that is not an unreasonable analogy. While not exactly easy, it is possible to find other mechanisms to transport the blood, but it is not so easy to replace the blood itself.

Clearly both applications and data are important. However, rather than necessarily declare one to be more important than the other, my inclination is to view them in terms of life span. Often I hear something along the lines of: An application is used to access the data. To me this implies that there may be more than one application or a series of applications over time whereas the data itself is not really viewed as having multiple incarnations. If I may borrow a quote from The Kinks: "Rocks bands will come and rock bands will go, but Rock 'n Roll will go on forever!" (From a live performance of "All Day and All of the Night" as I recall).

Experimenting with Connection Pooling

2008-02-28T19:17:00.001-05:00

Connection pooling can be a great way to increase performance and scalability in your ODP.NET applications; however, it is also a feature that can be confusing to figure out as there are several parameters used in the connection string that control the behavior of the feature. These parameters are all fully documented in the documentation that ships with the data provider. In brief, the primary parameters used to control connection pooling are (see documentation for additional parameters):

Pooling - Enables or disables the connection pooling feature
Min Pool Size - Specifies the minimum number of connections that can be in the pool
Max Pool Size - Specifies the maximum number of connections that can be in the pool
Incr Pool Size - Specifies the increment value (the number of connections) to be added if the connection pool needs to grow in size
Decr Pool Size - Specifies the maximum number of connections that may be removed from the pool in a single "downsizing" operation
Connection Lifetime - Specifies the amount of time that a connection must be active after which the connection will not be returned to the pool (i.e. it will be disposed)
Connection Timeout - Specifies the amount of time that the provider will wait for an available connection from the pool

In ODP.NET each connection in the pool is represented by a physical connection to the database. There is a one-to-one relationship. If there are 4 connections in the pool there will be 4 corresponding connections in the database. This is another way of saying that ODP.NET connection pooling does not implement multiplexing (or sharing) of connections. Another important characteristic is that a connection pool is associated with a client process. This means that if you implement connection pooling in an application and there are 8 copies of that application simultaneously running at any given time, there will be 8 connections pools - one for each instance of the application process.

Because a connection pool is associated with a specific instance of an application it can possibly be a bit difficult to test the impact and operation of the various connection string parameters related to connection pooling. I've put together a simple project that uses threads to test connection pooling. This allows the single application process to create multiple connections (and thus multiple entries in the connection pool) to the database.

The sample code invokes a supplied PL/SQL procedure in the DBMS_LOCK package to sleep for a period of time (hard-coded as 30 seconds in the below code). The allows the connection to be "held active" and helps with testing the impact of the Connection Lifetime parameter. In order for this to work, the database user must be granted execution permission on the PL/SQL package. For example (as a DBA user via SQL*Plus):

grant execute on dbms_lock to orademo;

After starting the specified number of threads (and connections) the application holds the connection as active for the determined period of time, and then disposes of the connection object placing it back into the connection pool (depending on how you set the parameters!). In order to monitor the connections in the database, I executed the following query as a DBA user via SQL*Plus:

SELECT   SID,
         SERIAL#,
         USERNAME,
         STATUS,
         to_char(LOGON_TIME, 'DD-MON-YY HH24:MI') LOGON_TIME
FROM     V$SESSION
WHERE    LENGTH(USERNAME) > 1
ORDER BY 3, 4, 1;

The output from the application is as follows:

Thread 1 started...
Thread 2 started...
Thread 3 started...
Thread 4 started...
Thread 5 started...
Thread 6 started...
Thread 7 started...
Thread 8 started...
Thread 1 completing...
Thread 2 completing...
Thread 3 completing...
Thread 4 completing...
Thread 5 completing...
Thread 6 completing...
Thread 7 completing...
Thread 8 completing...

Paused after threads complete.
Monitor connections using tool of choice.
Be sure to wait several minutes (approx. 6) for clean-up to occur.

Press Enter when finished.

It is important to execute the monitoring query while the application is "paused" otherwise the application will terminate and the connection pool will be destroyed.

Here is the output of the monitoring query after the application has reached the "pause point":

SQL> SELECT   SID,
2           SERIAL#,
3           USERNAME,
4           STATUS,
5           to_char(LOGON_TIME, 'DD-MON-YY HH24:MI') LOGON_TIME
6 FROM     V$SESSION
7 WHERE    LENGTH(USERNAME) > 1
8 ORDER BY 3, 4, 1;

       SID    SERIAL# USERNAME                       STATUS   LOGON_TIME
---------- ---------- ------------------------------ -------- ---------------
       135          2 ORADEMO                        INACTIVE 28-FEB-08 18:22
       136          2 ORADEMO                        INACTIVE 28-FEB-08 18:22
       137          2 ORADEMO                        INACTIVE 28-FEB-08 18:22
       138          4 ORADEMO                        INACTIVE 28-FEB-08 18:22
       139          6 ORADEMO                        INACTIVE 28-FEB-08 18:22
       142          3 ORADEMO                        INACTIVE 28-FEB-08 18:22
       143          3 ORADEMO                        INACTIVE 28-FEB-08 18:22
       164          6 ORADEMO                        INACTIVE 28-FEB-08 18:22
       134          2 SYSTEM                         ACTIVE   28-FEB-08 18:22

9 rows selected.

This shows the 8 connections from the 8 threads (adjustable) in the application and my SQL*Plus connection.

If you simply let the application sit paused for a period of time, the connections will automatically be cleaned up by ODP.NET (again, depending on the parameter values, but the values as provided in the below code allow this to occur). On my system it took approximately 6 minutes for the connection pool to be "cleaned" (i.e. reduced to the minimum of one connection based on the values I used in the connection string).

[ after waiting for about 6 minutes ]

SQL> /

       SID    SERIAL# USERNAME                       STATUS   LOGON_TIME
---------- ---------- ------------------------------ -------- ---------------
       164          6 ORADEMO                        INACTIVE 28-FEB-08 18:22
       134          2 SYSTEM                         ACTIVE   28-FEB-08 18:22

2 rows selected.

This shows the connection pool has been "downsized" to the minimum number (one) I specified.

I encourage you to experiment with the different parameters and values to see how they operate and what impact they have on your system.

The Code

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

namespace Miscellaneous
{
class Program
{
    static void Main(string[] args)
    {
      // change connection string as appropriate and experiment with different values
      const string constr = "User Id=orademo; " +
                            "Password=oracle; " +
                            "Data Source=orademo; " +
                            "Enlist=false;" +
                            "Pooling=true;" +
                            "Min Pool Size=1;" +
                            "Max Pool Size=8;" +
                            "Incr Pool Size=1;" +
                            "Decr Pool Size=8;" +
                            "Connection Lifetime=60;" +
                            "Connection Timeout=5";

// set to number of threads / connections to use
const int numberOfThreads = 8;

      // create arrays for class instances and events
      ConnectionThread[] threadArray = new ConnectionThread[numberOfThreads];
      ManualResetEvent[] doneEvents = new ManualResetEvent[numberOfThreads];

      // populate arrays and start threads
      for (int i = 0; i < numberOfThreads; i++)
      {
        // initialize each event object in the array
        doneEvents[i] = new ManualResetEvent(false);
        // create a new instance of the ConnectionThread class
        ConnectionThread ct = new ConnectionThread(i + 1, constr, doneEvents[i]);
        // assign the new instance to array element
        threadArray[i] = ct;
        // Queue the thread for execution and specify the method to execute
        // when thread becomes available from the thread pool
        ThreadPool.QueueUserWorkItem(ct.ThreadPoolCallback);
      }

// wait until all threads have completed
WaitHandle.WaitAll(doneEvents);

      // keep application from terminating while monitoring connections in database
      // if the application / process terminates all connections will be removed
      Console.WriteLine();
      Console.WriteLine("Paused after threads complete.");
      Console.WriteLine("Monitor connections using tool of choice.");
      Console.WriteLine("Be sure to wait several minutes (approx. 6) for clean-up to occur.");
      Console.WriteLine();
      Console.Write("Press Enter when finished.");
      Console.ReadLine();
    }
}

public class ConnectionThread
{
    // private class members
    private int _threadNumber;
    private string _constr;
    private ManualResetEvent _doneEvent;

    // parameterized constructor
    public ConnectionThread(int threadNumber, string connectionString, ManualResetEvent doneEvent)
    {
      _threadNumber = threadNumber;
      _constr = connectionString;
      _doneEvent = doneEvent;
    }

    // this will be called when the thread starts
    public void ThreadPoolCallback(Object threadContext)
    {
      Console.WriteLine("Thread {0} started...", _threadNumber);

// do some database work that holds the connection open
DoWork();

Console.WriteLine("Thread {0} completing...", _threadNumber);

      // signal that this thread is done
      _doneEvent.Set();
    }

    public void DoWork()
    {
      // create and open connection
      OracleConnection con = new OracleConnection(_constr);
      con.Open();

      // command to do the database work (simply hold connection open for 30 seconds)
      // NOTE: execute privilege must be granted to user on the dbms_lock package
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "begin dbms_lock.sleep(30); end;";

// execute the anonymous pl/sql block to does nothing but sleep to hold the connection
cmd.ExecuteNonQuery();

      // clean up and return connection to pool (depending on connection string settings)
      cmd.Dispose();
      con.Dispose();
    }
}
}

VB, OracleCommandBuilder, and What's Wrong With This Code?

2008-01-31T20:13:00.001-05:00

Below is some sample code. Create a new VB console application, add a reference to the Oracle.DataAccess.dll assembly, and add Oracle.DataAccess.Client to the Imported namespaces using the properties page for the project. Copy and paste the code into the .vb source file. Do you get any errors? Does it compile? Is there anything wrong with this code?

Module Module1
Sub Main()
    ' connection string -- change as necessary
    Dim constr As String = "User Id=scott; " & _
                           "Password=tiger; " & _
                           "Data Source=orademo; " & _
                           "Enlist=false; " & _
                           "Pooling=false"

    ' will use below
    Dim ds As New DataSet
    Dim da As OracleDataAdapter
    Dim con As OracleConnection

    ' open connection
    con = New OracleConnection(constr)
    con.Open()

' get a data adapter for the emp table
da = New OracleDataAdapter("select * from emp", con)

' get the schema information
da.FillSchema(ds, SchemaType.Source)

' get command builder from data adapter
Dim cb As New OracleCommandBuilder(da)

' set the insert command from the command builder
da.InsertCommand = cb.GetInsertCommand(True)

    ' simple prompt to keep console from closing when
    ' run from within the Visual Studio environment
    Console.WriteLine("ENTER to continue...")
    Console.ReadLine()
End Sub
End Module

If the code compiles, when you try to run it, do you get an error? If you get an error, does it indicate: "The DataAdapter.SelectCommand property needs to be initialized."

Hmm. Strange. It looks like the SelectCommand is being initialized right there in the OracleDataAdapter constructor: da = New OracleDataAdapter("select * from emp", con)

A quick check in the Oracle Data Provider for .NET documentation shows that the constructor for OracleDataAdapter can definitely take a string which represents the select command text and a connection object. Yet you may get an error stating that the SelectCommand property needs to be initialized.

If this code compiled fine and yet you get a runtime error, here's a pointer - add the following to the top of the source code file:

' Enable Option Strict checking
Option Strict On

(You can also change this via the project property page on the Compile tab)

Did anything change in the source code? In particular this part of the source code:

' set the insert command from the command builder
da.InsertCommand = cb.GetInsertCommand(True)

If Option Strict is not enabled, which happens to be the default, VB will try to perform an implicit conversion from one data type to another data type with no compile-time warning. However, at runtime the conversion may fail. In addition, if you consult the ODP.NET documentation you will see that there is no OracleCommandBuilder.GetInsertCommand that takes a Boolean. However, OracleCommandBuilder inherits from System.Data.Common.DbCommandBuilder which does have an overloaded GetInsertCommand that takes a Boolean.

So, in the case of the (incorrect) code above, DbCommandBuilder.GetInsertCommand(Boolean) is the method that was to be invoked not the OracleCommandBuilder.GetInsertCommand. This is the cause of the runtime error and explains the complaint about the select command not being initialized. If you try to compile the code with Option Strict enabled you should receive: "Option Strict On disallows implicit conversions from 'System.Data.Common.DbCommand' to 'Oracle.DataAccess.Client.OracleCommand'."

From my perspective it seems like a good idea to always enable Option Strict.

ODAC 11g Released

2007-12-27T21:49:00.001-05:00

ODAC 11g Version 11.1.0.6.20 (including the Oracle Developer Tools for Visual Studio .NET and ODP.NET) has now been released to production.

Get your download here and check out the new features list!

The Bulk Copy and User-Defined Types (UDT) features are likely to be well received along with the Instant Client support.

Dynamically Creating a Variable In-List

2007-12-12T16:52:00.001-05:00

Frequently SQL statements in code use an "in-list". That is, SQL that resembles the following:

select   employee_id,
         first_name,
         last_name
from     employees
where    employee_id in (102,127,206)

Here I am providing an in-list for the employee_id column. Using the HR sample that ships with Oracle Database, the above query returns the following results:

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        102 Lex                  De Haan
        206 William              Gietz
        127 James                Landry

Of course you may wish to vary the number of items in the in-list as well as the actual values. When the value of an item in the list changes, you may wish to use a bind variable for that item. See my Oracle Magazine column here for more details on bind variables and why you might want to use them.

The above SQL statement re-factored to use bind variables would look like:

select   employee_id,
         first_name,
         last_name
from     employees
where    employee_id in (:0,:1,:2)

In this re-factored statement I am still using 3 values in the in-list. This is definitely not a requirement: the in-list can contain anywhere from 1 to 1000 items. See the official Oracle documentation here for more details.

When attempting to use an in-list with bind variables you may be tempted to supply the comma-delimited values as a single string argument to the in-list. This may resemble:

select   employee_id,
         first_name,
         last_name
from     employees
where    employee_id in (:0)

In this case you may try to pass the string "102,127,206" as the value for the bind variable. However, this will not work as you might expect. You will receive "ORA-1722: invalid number" if you try this because the string "102,127,206" can not be converted to a numeric value. Instead you must create the statement with a separate bind variable for each item you wish to pass in the in-list.

One technique to do this uses a StringBuilder object to create the SQL statement and dynamically build the in-list items. Here is some sample code that illustrates this technique:

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

namespace VariableInListDemo
{
class Program
{
    static void Main(string[] args)
    {
      // change connection string for your environment
      string constr = "User Id=hr;" +
                      "Password=hr;" +
                      "Data Source=orademo;" +
                      "Pooling=false;" +
                      "Enlist=false";

      // the "base" sql statement to which the in-list will be appended
      StringBuilder sbSQL = new StringBuilder("select employee_id, " +
                                              "first_name, " +
                                              "last_name " +
                                              "from employees " +
                                              "where employee_id in (");

      // if no command-line arguments specified, simply return
      if (args.Length == 0)
      {
        Console.WriteLine("No employee_id values specified...");
        Console.WriteLine("Please specify values such as: 102,127,206");
        return;
      }
      // create and open connection
      OracleConnection con = new OracleConnection(constr);
      con.Open();

      // split the command-line argument into separate tokens
      // the command-line argument should be similar to:
      // 102,127,206
      string[] EmpNos = args[0].Split(',');

      // build the in-list part of the sql
      for (int i = 0; i < EmpNos.Length; i++)
      {
        // append the bind variables into the sql
        sbSQL.Append(":" + i.ToString());

        // add a "," after all but the last bind variable
        if (i < EmpNos.Length - 1)
        {
          sbSQL.Append(",");
        }
      }

// close the in-list with the trailing ")"
sbSQL.Append(")");

      // create the command object
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = sbSQL.ToString();

      // create an array of OracleParameter objects for each
      // empno specified on the command-line
      OracleParameter[] EmpNoParams = new OracleParameter[EmpNos.Length];

      // set the properties for each parameter
      // and add to parameters collection
      for (int i = 0; i < EmpNos.Length; i++)
      {
        EmpNoParams[i] = new OracleParameter();
        EmpNoParams[i].OracleDbType = OracleDbType.Decimal;
        EmpNoParams[i].Value = Convert.ToDecimal(EmpNos[i]);

cmd.Parameters.Add(EmpNoParams[i]);
}

// create a data reader
OracleDataReader dr = cmd.ExecuteReader();

      // iterate over the reader and display to console
      while (dr.Read())
      {
        Console.WriteLine(dr[0].ToString() + ", " +
                          dr[1].ToString() + ", " +
                          dr[2].ToString());
      }

// add clean up code as necessary for your environment

      // prevent console from closing when run from VS environment
      Console.WriteLine();
      Console.WriteLine("ENTER to continue...");
      Console.ReadLine();
    }
}
}

Running the above sample from a console window using the values "102,127,206" produces the following results:

C:\>VariableInListDemo.exe 102,127,206
102, Lex, De Haan
206, William, Gietz
127, James, Landry

ENTER to continue...

If you need to use a variable in-list in your application, I encourage you to experiment with this technique using different values and numbers of items as a possible solution.