Feed aggregator

Patterns in eclipse

Venkat Viswa - Fri, 2009-07-17 01:57
Three rules of eclipse

plugin may not change screen,task and create objects unless user asks for it

1) User owns the screen

2) User owns the CPU

3) User owns the memory

--> Do not use singleton pattern.Memory allocation never goes away

Building the UI withoug loading plugins

Decoupling using adapters
--> dynamic implementation of an interface
--> class can be adapted to any interface
--> best example is the properties view

4) most of the items are implemented as services
Located,Scoped and Destroyed

5) Separation of concerns
same class should not do a,b,c,d,e
use handlers
6) scalable UI
browse instead of combo
filters instead of presenting a large set of info
always use group data in relevant sections
provide deselect /select all buttons
differentiate and place common buttons appropriately

Eclipse and Building Data Centric RIA

Venkat Viswa - Fri, 2009-07-17 00:50
Demo of portal type application using flex
Interactive charts --> looked really good with nice drilldowns

Rich Internet Applicaions
1) REal time data push
2) resizable
3) Rich Data entry
4) Chat
5) Data Synchronization
6) Audio and Video
7) Offline

Open Screen platform : Adbobe flash platform

clients --> AIR and flash player

Servers/services --> Blazeds,data services

Flex framework

tools to design and develop : flex builder

Understanding flex

written in mxml markup

flex sdk is free and opensources

flash builder : eclipse based professional IDE

benefits :

UI goes to client only once . after that only data changes..

Approach for Developing RIAs

Design focused
Data focused


Testing and Deployment


Model Driven development


1) Create a new project

2) Create a data model (fml file)

3) Deploy model to LCDS (Live cycle data services)

4) import fml file

5) create mxml and add datagrid.

6) Dnd datamodel to data grid

7) Run application.

Business logic can be written in custom assembler. Normal one is fiber assembler.

Even tomcat is fine. Internally fml creates java classes at runtime during startup.

Data Centric Development using Flash Builder

1) Define Service (CF,php,java,soap,rest)

2) Model Service (flash builder examines service, builds design-time model)

3) Bind Operations to Flex UI Components (data binding,UI generation,Paging,Data-management)

AMF : Action script messaging format used for sending and reciving data , more efficient than json and xml.

Testing and deployment

Network monitor
unit testing framework
Command line builds ( coming soon)

Demo of Ruby service plugin

Data Centric Development Extensibility

Extension points
Key interfaces and classes for custom service
Key interfaces and classes for componentConfigurator

Flash Catalyst

Imports photoshop files and analyzes it. Based on eclipse platform. takes mockups and creates flex application based on them. Generates flex code behind the scenes.

Wow.. thats too good

Require flash builder license and run time license for LCDS


Tour de flex : www.adobe.com/devnet/flex/tourdeflex/

EclipseLink : High Performance Persistence

Venkat Viswa - Thu, 2009-07-16 23:48
I have used eclipselink on one of the key projects lastyear. It was donated by Oracle to Eclipse foundation. Toplink became eclipselink. I was really impressed by its implementation of JPA and customizations such as Criteria Queries. Hoping to learn more from this session.

EclipseLink Architecture

Supports JavaSE,EE,OSGi and Spring

Eclipselink solution comprises JPA implentation,Moxy,EIS,SDO(service data objects),DBWS (database webservice --> want xml from relational data ).

--> supports JPA 1.0 with many advanced features
--> simplified configuration of using annotations and/or xml.
--> Best ORM for Oracle database

Tool Support
-->EclipseLink is a runtime project supported by IDEs.
-->Eclipselink supported by Dali
-->Oracle Jdeveloper 11g,Enterprise Pack for eclipse
--> Netbeans
--> Standalone workbench(Swing project)

Eclipselink distributions
--> Eclipse,Oracle toplink,weblogic,glassfish,spring framework,JOnAS

JPA persistence Provider(eclipselink) sits between Java SE/EE and rdbms

Core JPA Mappings

ID (primary key)
Basic (field mappings)

Mapping Annotations
Query hints
Advanced Mappings
Eclipselink orm.xml

Examples of Advanced Mappings
class level --> @Converter(name="",converterClass="")
field level --> @Convert(name="")

@PrivateOwned (coming in JPA 2.0) : doesnt exist without parent

Caching :

Optimistic locking :

Custom Data type conversions:

Query Framework

5 different ways

Entity Model : JPQL
Native : SQL
Stored Procedures

Customizations in queries
--> Locking,Cache usage
--> Optimizations (batching,joining)
--> Result shaping/conversions
--> Stored Proc support

SQL and Stored Procedure directly hit db.

For other 3 query framework is used. Checks for Cache hit,Cache result is used using Object builder)

Annotations for stored procedures
@NamedStoredProcedureQuery and @NamedStoredProcedureQueries

e.g. @NamedStoredProcedureQuery(name="",procedureName="",parameters={})

eclipselink.read-only : useful in read only screens

Lazy loading & Fetch Groups
Two fetch groups defined automatically --> eagerly loaded fields and lazily loaded fields.Enabled by byte code weaving

Create your own fetch groups and added to query as hint. Overrides default fetch groups.

Caching Advantage :

Shared L2 and persistence context caching
--> Entity cache and not data cache like ehacache that comes with hibernate

Cache Invalidation --> time to live,fixed times, programmable

Cache Coordination --> Messaging,Type specific configuration (invalidate,sync,sync + new,none)

Invalidate is the most used and cheapest one

Annotations for caching : @Cache,@TimeofDay,@ExistenceChecking

Concurrency Protection :
@OptimisticLocking : Numeric,Timestamp,All Fields,Selected fields,Changed field

Pessimistic locking : using hit eclipselink.pessimistic-lock

Dealing with DB Triggers

@ReturnInsert and @ReturnUpdate : efficiently reread modified data in Oracle db.

Change Tracking
@ChangeTracking annotation to configure the way changes to Entities are computed
Attribute level ,object level, deferred ,auto

Performance Options summary
--> ChangeTracking
--> Read only queries
--> parameter binding
--> joining
--> inheritance
--> concurrency
--> dynamic expressions

Moxy : Object XML Binding

JavaApp --> Objects --> EclipselinkOXM --> xml

Rich set of mappings provides complete control to map objects to any XSD.

Eclipselink JAXB2 Annotations , Moxy XML

twice as fast as sun ri and xml beans.

API steps
1) Create factory,context
2) Marshal and UnMarshal

SDO : Xml Centric

Wow.. too fast session .. but left me lots to explore in JPA.

Eclipselink 1.1.2 released as part of Galileo

Eclipselink 2.0 --> Fall 2009 ; implements JPA 2.0

DBWS : Generated JAX-WS from DB

Keynote address - Day 1

Venkat Viswa - Thu, 2009-07-16 23:06
First PR on Saltmarch going on :).

Sponsors : Platinum --> Oracle,IBM and Microsoft
Gold : Actuate,Adobe etc

Key note speaker will be Ramkumar Kothandaram from Microsoft

What is microsoft doing in an eclipse conference !!!

Topic will be interoperability.


--> Microsoft's approach to interoperability
--> Open source & Microsoft platform

Need for interoperability

We have diversified client applications (firefox,ms office,Open office , IE8) and server applications (Jboss,.NEt etc..) , storage(EMC2,netapp) , multiple databases, processors(intel,ibm,sun).

Customers buy heterogeneous hardware/sofware. Painful EAI projects used to take a long time. This resulted in interoperability evolution.

Microsofts approach to Interoperatbility

--> Products : All products natively interoperability
--> Colloboration : with Partners,competitors and open source community
--> Standards : Promote interoperability through new and existing standards
--> Developer resources : msdn,codeplex etc


All products like Windows Vista,.net ,Win server 2008,office 2007 support interoperability.

Can consume and expose webservice endpoints.


Apache stonehenge : Project to test for interoperability between various vendors.

MS part of interoperability alliance. Took leadership in webservices interop.


Participates in over 150 standards bodies

Developer Perspective

PHP on Windows --> PHP FastCGI on IIS

Seems that PHP on windows scales as fast as Apache ..

Then there was introduction to Microsoft Azure which is microsofts cloud computing platform.

Eclipse tools for sliverlight (eclipse4SL) : eclipse plugin for silverlight from soyatech. Looks good to me :). Can build RIA and embed in Html.

Java API for open XML : standard for storing documents. Create a document for server side. http://poi.apache.org

Links to remember


Codeplex : 80000 open source apps on MIcrosoft application.

On the whole it was a good eye opener on microsoft and its contribution to interoperability.

Live Blogging from Eclipse summit - Bangalore

Venkat Viswa - Thu, 2009-07-16 22:57
Here I am back again with live blogging from Eclipse summit Bangalore.

We are minutes away from launch and there seems to be a lot of Java/Eclipse lovers. The ballroom is really full.

The keynote address is set to begin

Connecting with Team Productivity Center

Susan Duncan - Thu, 2009-07-16 04:05
Well, it's been quite a couple of weeks. TPC was featured in the developer tools section of the Fusion Middleware 11g launch and got some good press coverage (for instance eWeek) and the bloggers were our in force. You can watch the launch and see Duncan Mills use TPC in his demo. He has also recorded an Oracle Videocast and Podcast introducing TPC.

My aim is to get as many users to download and use it as possible (actually, that is every Product Manager's aim for their product!) That is going well, but I could do with your help to get the news out there. The ALM repositories we integrate with currently are all leaders in their field. The JIRA integration is a great productivity booster and the more I work with JIRA the more I realise how flexible it is. Microsoft Project Server is widely used in enterprise organisations and Rally Software is a world leader in agile development.

We are working on additional connectors (more news on that when I can divulge more) but there is always room for more. I'd love to hear from you on what ALM repositories you would like to see TPC integrate with. I'd love to hear from you if you have an internal system you would like to write a connector to integrate it with TPC. In fact, I'd love to hear from you in any capacity concerning TPC - and that, of course, includes those of you who have reported bugs too ;-)

John Stegeman, an Oracle Ace Director, is interested in creating a connector to TRAC. He announced this with a JDeveloper forum post saying, " I've been thinking about writing a connector for an issue management system that I use (of course, there had to be a selfish reason), TRAC. I think an ideal way to do this would be with a small team of developers working together on the new sample code site hosted by Oracle (http://www.samplecode.oracle.com). "

I think this is a great idea. It would be about a month of effort and you would get the opportunity not only to get to know our connector framework but to work as part of a small team and gain experience to perhaps go on to creating a connector for something within your own organisation. You can contact John through the forum post. I would highly recommend it (well, I would, wouldn't I?)

So, download, use, report, connect, and code - yep, that about covers it!

2 members JHeadstart Team in Oracle Innovation Showcase

JHeadstart - Thu, 2009-07-16 00:07

We are honored that 2 of our JHeadstart Team members are included in the Conversations with Oracle Innovators of the Oracle Innovation Showcase.

Full credit to Steven, who is the driving power behind the JHeadstart innovations!

Categories: Development

When Reality Does Not Meet Hype: AT&T + iPhone Probably the Worst Customer Experience I've Ever Had

Ken Pulverman - Sat, 2009-07-11 15:33
I know we are all supposed to love these little slippery shiny plastic boxes specifically engineered to drop out of you hand, with batteries and memory that can't be upgraded giving you yet another reason to pay Apple more money.

I've resisted for some time. I was actually given one that I gave away without using it as it didn't have the features I use most often, notably MMS and Bluetooth Stereo. When the newest one launched, I did a bit of research online and decided I'd take the plunge despite my issues with its purposely designed obsolescence.

Unfortunately the experience of trying to get one ordered and working has probably been the worst consumer experience I have ever suffered through. The commercials make it look so easy, and I was actually looking forward to the experience. I had no idea how far from reality the actual consumer experience is.

When the 3Gs came out, I didn't try to rush out and get one. I waited a couple of weeks for the early hiccups to work themselves out and inventory to be replenished. I guess I didn't wait long enough.

About a week ago, I went to an AT&T store in the heart of the financial district of San Francisco. They didn't have any and directed me to the Apple store, but said that only a couple of people they sent there had been successful in getting one.

I decided to order one online through my company. The first order failed for some strange reason after I submitted it through the AT&T Premier site. After calling my bank to make sure I wasn't charged, I processed the order again. It launched a workflow inside my company that showed that service for the year would be almost $1,000. That really is a lot of money, but I use a cell phone a lot for work and also need to demo some of the applications the development group I work with has built for the iPhone.

The phone arrived on Friday. I've been an AT&T customer for many years so this was an upgrade. I was supposed to call a number to process the order. The recording told me that the automatic processing system was down. I waited on hold 30 minutes, and hour, and 30 minutes again and never got through. The last time, AT&T simply hung up. The next time I called they said the "Premier" support would not be open until Monday. One more call dumped me into a voicemail system. I gave up and decided to go to the Apple store.

A portly guy in a giant orange T-shirt with those oversized earings that permanently distort your ear lobes insists that to activate the phone, I just needed to plug it into iTunes. I knew this was rubbish so I pressed some more. "Oh activation, for that you need to go to the AT&T store. I don't know what their hours are though."

I trundle down to the AT&T Store and of course it is closed. I give up and decide I will return the next morning to see what they can do.

At the AT&T store, another forcefully insistent but clearly ignorant employee tells me I need to take the sim card out of my existing phone and put it in the new iPhone. He is adamant that there is no sim card in the new phone. I tell him that certainly AT&T ships a sim card in a phone they are upgrading. He pops it open and out drops a sim card. Having now splayed my phone open, he reassembles it and attempts to activate on his system. He then tells me this is a "Premier" account and thus he can't do anything. I ask him if he can return the phone. Yes, as it turns out, that is the one thing he can do and in this matter he actually seemed eager to help.

I left with my now 24 hour old brick resolving to give up until Monday for another hour or more on the phone.

It is very clear that this undynamic duo needs a little remedial marketing.....

EXPECTATION - Don't set the bar ridiculously high in your ads and commercials if your process is shit. Under promise and try to over deliver.

ALERT SALES - It's in excusable after 3 releases of the product that probably one of the most heavily trafficked Apple stores in the heart of San Francisco doesn't know the process for activating a phone or the hours of the AT&T Store.

SEGMENTATION - "Premier" should not mean an invitation to traipse around San Francisco or climb through the phone tree to get crapped on. I work at one of the largest companies in the world. We spend a lot on voice and data communications because these services are vital to our business. We do have choices though at the employee level and guess what we talk...and blog. We want our company to get good value and we want good service ourselves. It's not a surprise that Apple is blowing it in the corporate market, but AT&T? You should really be ashamed of yourselves. I suggest you work on making Premier actually Premier. And Apple, come on. Even if you think it is cool to ignore the corporate market, at least you could recognize that if the iPhone is the supposed gateway drug to your other products, and someone buys the most expensive version of that product, they probably expect a decent introduction to your world.

EAT HUMBLE PIE - At both stores I sensed a great degree of smugness. Employees who insisted they knew what they were doing when they didn't have a clue. It's very clear this partnership on its third try still has many issues. When I tell you I've been on hold for more than two hours don't brashly insist that you know how to fix my problem only to send me away with nothing. Perhaps this goes back to expectation, but you can't just hire a guy with a bone through his earlobe to show the world you are different you actually have to be different.

It's takes a lot for me to sprint home two miles from the center of San Francisco to crank out such a blog entry. I am way beyond irritated with the myriad of incompetent processes and people I have encountered at this point.

I can assure you, that the only thing that will keep me from talking about this pathetic display forever will be an act of service recovery befitting the crime of the time I've been forced to waste. Apple & AT&T, if someone in your headquarters is listening, unlike your employees in your stores, I plan to waste just one more hour on Monday trying to get this phone to work. After which I will take advantage of evidently the only thing you have collectively (and oddly) mastered - the process of giving my money back.

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

Mark A. Williams - Sat, 2009-07-11 12:06
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 - 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Nice and easy.

So, let's try the equivalent using EZCONNECT:

[oracle@liverpool ~]$ sqlplus hr@liverpool:1521/DEMO

SQL*Plus: Release - Production on Sat Jul 11 11:43:20 2009

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

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 - 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


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 - 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


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

This same technique can also be used for privileged connections:

[oracle@liverpool ~]$ sqlplus sys@\"liverpool:1521/DEMO\" as sysdba

SQL*Plus: Release - 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


If you use SQL*Plus as much as I do, perhaps this will be a handy tip.

vi, Still Relevant

Tahiti Views - Thu, 2009-07-09 23:50
I thought this was a good summary of why vi (or more accurately vim) is still a good choice for editing today:Why, oh WHY, do those #?@! nutheads use vi?One trick I learned from this article that I hadn't known: keep the cursor on the same line, but position that line at the top, middle, or bottom of the script via 'zt', 'zz', and 'zb' respectively. I am always ending up with the cursor at the John Russellhttp://www.blogger.com/profile/17089970732272081637noreply@blogger.com5

Classic MetaLink vs. My Oracle Support

Jared Still - Thu, 2009-07-09 18:01
If you are in any way involved with supporting Oracle products, then you know that the death knell for Classic MetaLink has sounded. MetaLink will be unplugged at the end of July 2009.

The new support site, My Oracle Support, seems to be causing some pain for quite a few people in the Oracle user community.

Some of the complaints regard limited platform support due to the Adobe Flash 9 requirements, navigation and response times.

On the other hand there are some cool new features such as Power View, Configuration Manager and the new Advanced Search options.

How do you feel about it?

Here's a chance to let your opinion be know as a poll has been created where you can vote on it.

At this time 637 voters have voiced their opinion about MetaLink and My Oracle Support.

Current Poll results can be found in this Excel File: MetaLink vs My Oracle Support Results

Categories: DBA Blogs

Microsoft Deprecates OracleClient: Time to Consider Moving to ODP.NET

Christian Shay - Mon, 2009-07-06 22:52
Microsoft recently announced that it will deprecate Microsoft System.Data.OracleClient. For existing Microsoft OracleClient developers, especially those that haven't taken a look at the Oracle Data Provider for .NET (ODP.NET) in some time, this is a good time to look at ODP.NET again for new development or to migrate existing Oracle .NET applications. In recent years, ODP.NET has added lots of new features -- such as performance tuning, user-defined types, advanced queuing, RAC connection pooling, and supporting multiple ODP.NET client versions simultaneously on the same machine.

Alex Keh, who is the product manager for ODP.NET, has put together a special new webhome for developers using OracleClient...its called ODP.NET for Microsoft OracleClient Developers. This web page provides good information about why developers choose to migrate from ODP.NET from Microsoft OracleClient. Alex told me that the page will also provide a step-by-step Microsoft OracleClient to ODP.NET migration tutorial in the near future, which should be extremely useful. If you have questions about what this deprecation means for your company, please contact Alex over at alex.keh [at] oracle [dot] com or post to the ODP.NET Forum (OTN registration required).

In reading various comments on the MSDN post and elsewhere, I noticed a few misconceptions about ODP.NET that I would like to clarify:

Misconception: "This deprecation means I have to pay for an Oracle provider now!"
Fact: ODP.NET is free! You don't need to pay for a third party provider if you don't want to

Misconception: "I have to download a particular version of ODP.NET depending on the version of my database."
Fact: Any version of ODP.NET works with any version of Oracle Database. So you can use the latest version (currently and use it against a 9.2, 10, or 11g database!

Misconception: "I'll have to use the Oracle installer when I deploy my app. Argh!!!"
Fact: At deployment time, you don't have to use the Oracle installer to install ODP.NET if you don't want to. If you so choose you can write your own installer, using scripting or Installshield or whatever you want. All you need to do is download the XCOPY version of ODP.NET. As a bonus, it has a smaller footprint too!

Misconception: "If I need to standardize on/test different apps with different versions of ODP.NET I'm in big trouble!"
Fact:Multiple versions of ODP.NET can live on the same box and your application can target whatever specific one it was tested with. Not everyone has multiple apps that have been tested with different versions of ODP.NET but it happens.

Misconception: "I hit a bug in ODP.NET 9/ODP.NET 10! I can't use ODP.NET because of it!"
Fact: If you hit some nasty bug of some sort with ODP.NET 9 or even 10, make sure to download the 11.1 version and try it as the bug is likely fixed in the years that passed. Again, it does not matter what version of the database you are using, the 11g ODP.NET version will work against them all and over the years we have added tons of new features and bug fixes.

So again, please check out the ODP.NET for Microsoft OracleClient Developers webpage. I'll blog again when the migration step-by-step guide is posted there, so feel free to subscribe to my blog to get an alert when that happens!

Happy coding :)

Getting Started with OCCI (Windows Version)

Mark A. Williams - Mon, 2009-07-06 22:49

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:


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
  • 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 (Visual C++9 (VS 2008)[Windows 32-bit])
  • Instant Client Version

From the download links above, you should download the following components to your development machine. I downloaded them to the C:\Temp directory.

  • OCCI (Visual C++9 (VS 2008)[Windows 32-bit]) - occivc9win32_111060.zip
  • Instant Client Package - Basic: instantclient-basic-win32-
  • Instant Client Package - SDK: instantclient-sdk-win32-
  • Instant Client Package - SQL*Plus: instantclient-sqlplus-win32-  (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:


I create a "vc9" directory under the "instantclient_11_1" directory as follows:


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 {
  virtual ~Employees();

  void List();

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


  delete pEmployees;

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


  return 0;

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

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


  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";

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

  if (stmt)

      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;



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!

ETL patent case: Constellar and DataMirror let off off the hook; DataStage still in dock

Nigel Thomas - Mon, 2009-07-06 14:27
Once again Vincent McBurney delivers a fantastic summary of the latest state of the Juxtacomm ETL patent case: SQL Server, DB2 and DataStage will fight out Data Integration Patent Infringement.

I'm most interested from the Constellar point of view - I first came across Constellar (then Information Junction) as a product on sale in late 1993 / early 1994 (before joining the company from Oracle in 1995), so it always seemed clear to me that it would qualify as prior art to Juxtacomm's 1998 patent. Oddly, it seems that the parties to the trial have agreed that Constellar Hub (and DataMirror Transformation Server) can be dropped from consideration; they won't be subject to damages - but equally they won't be considered as prior art. I don't understand that, but I guess the IBM lawyers must know what they are doing.

So, the case rumbles on, serving (if nothing else) to show how broken the US software patent system is.

SQLstream delivers instant data stream analysis of Mozilla 3.5 downloads

Nigel Thomas - Wed, 2009-07-01 13:52
Here are a couple of posts that describe the download monitor/dashboard which is giving up-to-the-second statistics for downloads by country of the latest Mozilla release 3.5 (just about to top 5.5 million downloads since yesterday's launch). The dashboard has been put together with the help of my friends at SQLstream. Just don't try looking at this with Internet Explorer, as it doesn't support HTML5.

Julian Hyde on Open Source OLAP. And stuff.: SQLstream powers ...
By Julian Hyde
SQLstream gathers data from Mozilla's download centers around the world, assigns each record a latitude and longitude, and summarizes the information in a continuously executing SQL query. Data is read with sub-second latencies, ...
Julian Hyde on Open Source OLAP.... - http://julianhyde.blogspot.com/
SQLstream the Sequel - RealTime Intelligence for Mozilla BI in Action
From ebizQ Presents BI in Action Virtual Conference ...

Introducing Oracle Team Productivity Center

Susan Duncan - Wed, 2009-07-01 09:22
Today is an exciting day for me. It's the launch of Oracle Fusion Middleware 11g in the USA and tomorrow the launch event comes to London. Included in this launch is, of course, Oracle JDeveloper 11gR1.

For some time I've been working on a new aspect of JDeveloper - Oracle Team Productivity Center. It is our first release of functionality to enable better Application Lifecycle Management for JDeveloper users and it is included in Oracle JDeveloper 11gR1.

TPC introduces the Team Navigator to JDeveloper. Through this navigator I can set up my team and user structure, applying team roles to users in teams/projects. I can connect to my existing ALM repositories and query/update artifacts in those repositories while working in JDeveloper.

In addition I can contextually link artifacts from different ALM repositories together - so I can create a relationship between a requirement defined in JIRA and a task in MS Project Server and I can tag items (needs a use case, ready for code review etc.) that are either visible to me or to all the members of my team. And I can be a member of multiple teams too. If I'm working on one task and am asked to switch to some other piece of code I can save the state of my development files open in the IDE against a specific work item. Work item is the generic term we give to any ALM artifact queried from an integrated ALM repository. So in the example below, I am working on a JIRA issue - SSTORE-23. If I Save Context I will save the Business Component files open in the editor along with the position and sizing of all the other JDeveloper windows. Then when I come back to this piece of work I can re-open SSTORE and Restore Context - to return my IDE to the saved state. Another great productivity booster!

A database is used to store queries, relationships, tags etc and managed by a small JEE application. This is set up by the installer and the client-side workings are downloaded through the normal Check For Updates Center in JDeveloper.

In this first release, in addition to the adaptors developed by us to connect to JIRA and MS Project Server I'm really pleased that we have partnered with Rally Software.

Rally is the leader in Agile application lifecycle management (ALM) dedicated to making distributed development organizations faster and leaner by dramatically cutting the time, cost and effort needed to deliver high quality applications. Rally's products were honored with four consecutive Jolt awards (the software industry's equivalent of the Oscar® award) in 2006, 2007, 2008 and 2009. The company's end-to-end solutions for Agile development also include Agile University, the largest source for Agile training, and Agile Commons, the largest collaborative Web 2.0 community dedicated to advancing software agility. Using the Rally Connector JDeveloper users can view and update their Rally tasks and defects directly from JDeveloper.

This release of TPC concentrates on enabling JDeveloper users, but Application Lifecycle Management is about more than just developers - it has a role in breaking down functional silos (development, QA, Doc, PM....) and it's our aim to push TPC out to more than developers going forward - both in terms of increased services provided by TPC and increasing the number of connectors available to differing ALM repositories (requirements, task, defects, testing etc)

One step towards that goal is the provision of a Connector Developers Guide and a sample connector to allow other third parties to create connectors to their existing ALM tools - be those commercial products or in-house systems.

But that's not all - JDeveloper users can also integrate their XMPP chat system into JDeveloper - even more productivity for developers without the need to leave their IDE! I can who of my team mates is connected to chat and also chat with all my buddies - whether they are working with TPC or not.

This is just a very brief introduction to Team Productivity Center. Browse the link above for more information, download, install and try it out - and let me know what you think ;-)

OTNs APEX Developer Competition 2009

Anthony Rayner - Wed, 2009-07-01 05:22
Are you the...

  ...travelling type?  Fancy winning a free ticket for Oracle OpenWorld in San Francisco (October 11 - 15) to meet with like minded APEX enthusiasts and learn more about APEX and other Oracle technology?

Or maybe more the...

  ...bragging type?  How would the words 'Oracle Application Express Developer Competition Winner 2009' look on your CV? It does have a certain ring to it, don't you think?

Or even the...

  ...academic type?   What about the prospect of furthering your understanding of APEX by paging through your winning copy of 'Pro Oracle Application Express'?

Whatever your reasons, enter the OTN 'Oracle Application Express Developer Competition 2009' by submitting an APEX application that stands out from the crowd and you could be in with the opportunity of winning one of these great prizes or accolades!

For more information, including submission guidelines, all important judging criteria and registration details, please visit the OTN page and David Peake's related post. Entries close 24 August, 2009.

Good luck!
Categories: Development

Approaches to "UPSERT"

Kenneth Downs - Mon, 2009-06-29 20:33

This week in the Database Programmer we look at something called an "UPSERT", the strange trick where an insert command may magically convert itself into an update if a row already exists with the provided key. This trick is very useful in a variety of cases. This week we will see its basic use, and next week we will see how the same idea can be used to materialize summary tables efficiently.


The idea behind an UPSERT is simple. The client issues an INSERT command. If a row already exists with the given primary key, then instead of throwing a key violation error, it takes the non-key values and updates the row.

This is one of those strange (and very unusual) cases where MySQL actually supports something you will not find in all of the other more mature databases. So if you are using MySQL, you do not need to do anything special to make an UPSERT. You just add the term "ON DUPLICATE KEY UPDATE" to the INSERT statement:

insert into table (a,c,b) values (1,2,3)
    on duplicate key update
     b = 2,
     c = 3

The MySQL command gives you the flexibility to specify different operation on UPDATE versus INSERT, but with that flexibility comes the requirement that the UPDATE clause completely restates the operation.

With the MySQL command there are also various considerations for AUTO_INCREMENT columns and multiple unique keys. You can read more at the MySQL page for the INSERT ... ON DUPLICATE KEY UPDATE feature.

A Note About MS SQL Server 2008

MS SQL Server introduced something like UPSERT in SQL Server 2008. It uses the MERGE command, which is a bit hairy, check it out in this nice tutorial.

Coding a Simpler UPSERT

Let us say that we want a simpler UPSERT, where you do not have to mess with SQL Server's MERGE or rewrite the entire command as in MySQL. This can be done with triggers.

To illustrate, consider a shopping cart with a natural key of ORDER_ID and SKU. I want simple application code that does not have to figure out if it needs to do an INSERT or UPDATE, and can always happily do INSERTs, knowing they will be converted to updates if the line is already there. In other words, I want simple application code that just keeps issuing commands like this:


We can accomplish this by a trigger. The trigger must occur before the action, and it must redirect the action to an UPDATE if necessary. Let us look at examples for MySQL, Postgres, and SQL Server.

A MySQL Trigger

Alas, MySQL giveth, and MySQL taketh away. You cannot code your own UPSERT in MySQL because of an extremely severe limitation in MySQL trigger rules. A MySQL trigger may not affect a row in a table different from the row originally affected by the command that fired the trigger. A MySQL trigger attempting to create a new row may not affect a different row.

Note: I may be wrong about this. This limitation has bitten me on several features that I would like to provide for MySQL. I am actually hoping this limitation will not apply for UPSERTs because the new row does not yet exist, but I have not had a chance yet to try.

A Postgres Trigger

The Postgres trigger example is pretty simple, hopefully the logic is self-explanatory. As with all code samples, I did this off the top of my head, you may need to fix a syntax error or two.

CREATE OR REPLACE FUNCTION orderlines_insert_before_F()
    result INTEGER; 
    -- Find out if there is a row
    result = (select count(*) from orderlines
                where order_id = new.order_id
                  and sku      = new.sku

    -- On the update branch, perform the update
    -- and then return NULL to prevent the 
    -- original insert from occurring
    IF result = 1 THEN
        UPDATE orderlines 
           SET qty = new.qty
         WHERE order_id = new.order_id
           AND sku      = new.sku;
        RETURN null;
    END IF;
    -- The default branch is to return "NEW" which
    -- causes the original INSERT to go forward
    RETURN new;


-- That extremely annoying second command you always
-- need for Postgres triggers.
CREATE TRIGGER orderlines_insert_before_T
   before insert
   EXECUTE PROCEDURE orderlines_insert_before_F();
A SQL Server Trigger

SQL Server BEFORE INSERT triggers are significantly different from Postgres triggers. First of all, they operate at the statement level, so that you have a set of new rows instead of just one. Secondly, the trigger must itself contain an explicit INSERT command, or the INSERT never happens. All of this means our SQL Server example is quite a bit more verbose.

The basic logic of the SQL Server example is the same as the Postgres, with two additional complications. First, we must use a CURSOR to loop through the incoming rows. Second, we must explicitly code the INSERT operation for the case where it occurs. But if you can see past the cruft we get for all of that, the SQL Server exmple is doing the same thing:

CREATE TRIGGER upsource_insert_before
ON orderlines
    DECLARE @new_order_id int;
    DECLARE @new_sku      varchar(15);
    DECLARE @new_qty      int;
    DECLARE @result       int;

    DECLARE trig_ins_orderlines CURSOR FOR 
            SELECT * FROM inserted;
    OPEN trig_ins_orderlines;

    FETCH NEXT FROM trig_ins_orderlines
     INTO @new_order_id

    WHILE @@Fetch_status = 0 
        -- Find out if there is a row now
        SET @result = (SELECT count(*) from orderlines
                        WHERE order_id = @new_order_id
                          AND sku      = @new_sku
        IF @result = 1 
            -- Since there is already a row, do an
            -- update
            UPDATE orderlines
               SET qty = @new_qty
             WHERE order_id = @new_order_id
               AND sku      = @new_sku;
            -- When there is no row, we insert it
            INSERT INTO orderlines 
            UPDATE orderlines

        -- Pull the next row
        FETCH NEXT FROM trig_ins_orderlines
         INTO @new_order_id

    END  -- Cursor iteration

    CLOSE trig_ins_orderlines;
    DEALLOCATE trig_ins_orderlines;

A Vague Uneasy Feeling

While the examples above are definitely cool and nifty, they ought to leave a certain nagging doubt in many programmers' minds. This doubt comes from the fact that an insert is not necessarily an insert anymore, which can lead to confusion. Just imagine the new programmer who has joined the team an is banging his head on his desk because he cannot figure out why his INSERTS are not working!

We can add a refinement to the process by making the function optional. Here is how we do it.

First, add a column to the ORDERLINES table called _UPSERT that is a char(1). Then modify the trigger so that the UPSERT behavior only occurs if the this column holds 'Y'. It is also extremely import to always set this value back to 'N' or NULL in the trigger, otherwise it will appear as 'Y' on subsequent INSERTS and it won't work properly.

So our new modified explicit upsert requires a SQL statement like this:


Our trigger code needs only a very slight modification. Here is the Postgres example, the SQL Server example should be very easy to update as well:

   ...trigger declration and definition above
   IF new._upsert = 'Y'
      result = (SELECT.....);
      _upsert = 'N';
      result = 0;
   END IF;
   ...rest of trigger is the same

The UPSERT feature gives us simplified code and fewer round trips to the server. Without the UPSERT there are times when the application may have to query the server to find out if a row exists, and then issue either an UPDATE or an INSERT. With the UPSERT, one round trip is eliminated, and the check occurs much more efficiently inside of the server itself.

The downside to UPSERTs is that they can be confusing if some type of explicit control is not put onto them such as the _UPSERT column.

Next week we will see a concept similar to UPSERT used to efficiently create summary tables.

Categories: Development

Debugging root cause of MQ related Errors

Ramkumar Menon - Mon, 2009-06-29 16:25

I ran into a few MQ errors. It helped me to take a look at $MQ_INSTALL_DIR\WebSphereMQ\Qmgrs\<QueueManagerName>\errors directory to see whats going on!

Oracle Pro*C on Windows with Express Edition Products

Mark A. Williams - Mon, 2009-06-29 10:34

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


Below this line you add the following four lines:


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

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

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include <sqlda.h>
#include <sqlcpr.h>


** 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 password[PWD_LEN];

** variables to hold the results
int ctr;
int empid;


** declare error handling function
void sql_error(char *msg)
  char err_msg[128];
  size_t buf_len, msg_len;


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



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
    SELECT   employee_id,
    FROM     employees
    ORDER BY last_name,

  ** open the cursor

  ** when done fetching break out of the for loop

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


  ** close the cursor

  ** provide simple feedback on how many rows fetched
  printf("\nFetched %d employees.\n", ctr);

  ** disconnect from database

  ** have a nice day

  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:


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.



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)


Subscribe to Oracle FAQ aggregator