Feed aggregator

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.

Agenda

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

Products

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

Can consume and expose webservice endpoints.

Colloboration

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

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

Standards

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

http://www.microsoft.com/web

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

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

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!

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.

An UPSERT or ON DUPLICATE KEY...

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:

INSERT INTO ORDERLINES
       (order_id,sku,qty)
VALUES 
       (1234,'ABC',5)

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()
RETURNS TRIGGER
 AS $BODY$
DECLARE
    result INTEGER; 
BEGIN
    SET SEARCH_PATH TO PUBLIC;
    
    -- 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;

END; $BODY$
LANGUAGE 'plpgsql' SECURITY DEFINER;

-- That extremely annoying second command you always
-- need for Postgres triggers.
CREATE TRIGGER orderlines_insert_before_T
   before insert
   ON ORDERLINES
   FOR EACH ROW
   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
INSTEAD OF insert
AS
BEGIN
    SET NOCOUNT ON;
    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
         ,@new_sku
         ,@new_qty;

    WHILE @@Fetch_status = 0 
    BEGIN
        -- 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 
        BEGIN
            -- 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;
        END
        ELSE
        BEGIN
            -- When there is no row, we insert it
            INSERT INTO orderlines 
                  (order_id,sku,qty)
            VALUES
                  (@new_order_id,@new_sku,@new_qty)
            UPDATE orderlines

        -- Pull the next row
        FETCH NEXT FROM trig_ins_orderlines
         INTO @new_order_id
             ,@new_sku
             ,@new_qty;

    END  -- Cursor iteration

    CLOSE trig_ins_orderlines;
    DEALLOCATE trig_ins_orderlines;

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

INSERT INTO ORDERLINES
       (_upsert,order_id,sku,qty)
VALUES
       ('Y',1234,'ABC',5)

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';
   ELSE
      result = 0;
   END IF;
   
   ...rest of trigger is the same
Conclusion

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

One more date trick

Klein Denkraam - Mon, 2009-06-29 04:11

Tyler Muth has a useful addition to the date functions I have published here before.

If you ever want to know how long ago a date is and you want to display it in ‘human readable’ format you (and I) could use his function.

Like this:

select date_text_format(sysdate - 3/86400) the_date from dual;
select date_text_format(sysdate - 5/1440) the_date from dual;
select date_text_format(sysdate - 1/24) the_date from dual;
select date_text_format(sysdate - 3.141549) the_date from dual;
select date_text_format(sysdate - 15) the_date from dual;
select date_text_format(sysdate - 120) the_date from dual;
select date_text_format(sysdate - 365) the_date from dual;
--------------------------------------------------------------------
3 seconds ago
5 minutes ago
1 hour ago
3 days ago
2 weeks ago
4 months ago
1 year ago

One more for the toolbox.


Are you sure you will be able to activate your standby??

Fairlie Rego - Sun, 2009-06-28 06:50
A couple of weeks I faced a scenario where the standby database crashed

On looking at the alert.log I see the following message in the alert.log of the standby

***********************************************************
Sat Jun 6 06:48:52 2009
Recovery interrupted!
cannot find needed online log for redo thread 1
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Sat Jun 6 06:48:53 2009
Errors in file /u01/app/oracle/admin/TEST/bdump/test1_mrp0_24533.trc:
ORA-10576: Give up restoring recovered datafiles to consistent state: some error occurred
ORA-16037: user requested cancel of managed recovery operation
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Sat Jun 6 06:48:54 2009
Waiting for MRP0 pid 24533 to terminate
************************************************************

Hmmm... this means that if the standby does not have the redo and cannot get it from the primary you will not be able to online media fuzzy files using supported methods

The same issue is explained in Bug 5956646
as an architectural limitation.

This is a very unlikely scenario but a possibility none the less

Concepts Guide: 5/27 - Schema Objects

Charles Schultz - Wed, 2009-06-24 15:16
I found that reading the Guide is quite hard if you are already tired. ;-)

As always, I like pictures. Figure 5-1 on page 5-3 does justice. Although they would make their point more clear by labelling the schemas.

Was not aware of intra-block chaining (pp 5-5) - interesting concept. Especially since it does not affect performance (does not increase the number of IO calls).

Figure 5-3 is good in that it really helps to see the various pieces of a block and the row, including the headers.

As much as I hate how nulls are handled in Oracle, the one good thing is that I like how Oracle just does not even record information for null-trailing rows (ie, column-length not stored in block). Except, of course, if you have LONG data - DO NOT USE LONG! =)

I was not aware how table compression actually worked. Now that I know a little more about it, I am surprised the guide did not mention any limitations. For example, if a block has 100% unique data (uncompressable), would the symbol table still be built and populated? If not, what is the cut-off? At what point does Oracle deem compression worthwhile, pragamatically?

I have never seen a practical use for nested tables, but I'll keep my eyes open. I jumped to 27-7 as referenced just to see what it said. I still have never seen a practical use for nested tables.

The fact that sessions are "bound" to temp tables was new to me; I did not realize you could not do ddl on a temp table if is already bound to a session. Kinda makes sense, though. I wonder why they do not simply call it "locking"? =) Afterall, that is how normal tables are handled.

Ojbect Views really confuse me, not being familiar with the concept. And this being the Concepts Guide, I found that the short little blurb did not really help me much at all.

I also did not realize that one could put declaritive constraints on views; interesting way to propagate metadata information for restricted environments.

The short paragraph on Materialized View Logs did not do the concept any justice. I get the impression that either space and/or time was restrained when this section was done. =)

The intro section to Dimensions left my head whirling. I am not a Warehousing guy by any means; while I appreciate the extra background and the quasi-example, I find that it deep-dives too quick for me. And using an example of a year being tied to a year-row is just the most absurd thing I have ever heard. Why not a practical, real-life example that "anyone" can grasp?

Good discussion for sequences; I like how the good is balanced with the bad - the "Caution" is stronger than I expected, but I think very important and am glad to see that the authors made it stand out.

Nice long section on indexes. *grin* I always find it strange to find in 10g documentation references to "analyze table", when most of the time I believe they really mean collect stats, for which we are told to use dbms_stats instead. The intro to deterministic (user-defined) indexes was quite interesting. I would personally consider this an example of "black magic" in Oracle. Another one of those cases where there is a strong lack of practical examples.

Figure 5-7 starts out looking like a great pictoral example. However I found it to be quite confusing. Actually, what I really want to see is how an index is built, starting with one row. At the very least, it would be helpful to augment the figure with text explaining the function of the values for the branch blocks. However, excellent information on how searches are mathematically bounded (big-O notation).

Excellent piece on bitmap indexes; great examples, wonderful discourse. I appreciate the balanced approach to addressing the pros and cons of bitmap indexes, which may at the outset seem to be the pancea of query performance issues. The sidebar on cardinality was very well done as well.

The section on Index-organized tables was also quite interesting, however I wonder why, if they are so highly recommended for OLTP applications, why are they not more popular?

Application Domain indexes, and the Oracle Data Cartridge in general, are another area of black magic that I fear ever going back to. I dabbled in it once when attempting to define custom statistics for a function and never looked back. =) I am sure they have their place on some "True Expert"'s shelf, but not here....

Like IOTs, the Concepts Guide does a good job selling Clusters and Hash Clusters as beneficial methods, but I do not see many folks using it in Real Life. Is it merely the learning curve that keeps the standard DBA away from these features? We have a lot of third-party apps; shall we assume that the vendors simply do not have the expertise to utilize these fun but advanced toys?

Interesting stuff none-the-less.

Pages

Subscribe to Oracle FAQ aggregator