I recently downloaded the new 10.x version of TOAD and could not connect to my databases. Still running TOAD 9.x, no problem. This really threw me off for a minute, so I thought I would share the resolution.

You are likely running a 64-bit Windows OS and have installed TOAD into C:\Program Files (x86)\....

I simply copied over my entire Quest Software directory from C:\Program Files (x86)\... to C:\Program Files\..., and restarted the TOAD.exe. Everything is working great, without having to reinstall anything.

PS: Not sure who to blame here. Vendors (like Oracle and TOAD) should know Microsoft is capable of doing strange things with important directory names (using spaces and, in this case, “strange” characters like parentheses)…and of course, Microsoft should simple stop perpetuating the use of these characters in important directories.

Recursive queries are something I have used many, many times over the years to build dynamic, n-tiered hierarchies. Oracle’s CONNECT BY PRIOR is awesome.

Does anyone know if this is possible in SQL Server or MySQL?

From what I understand, stored procedures would need to be used in MySQL and SQL Server to accomplish what Oracle does in one SQL statement with CONNECT BY PRIOR. I need to dig into the other databases a bit further to verify, but I am interested in any elegant solutions people have come up with if you would like to share.

Like me, some people may have gotten excited about using XMLFOREST in SQLX, only to find that NULL values result in altogether MISSING elements, as opposed to simply EMPTY elements. Many times avoiding printing elements with null values is desired functionality, but in some cases the consumer of the XML might require those elements to generated anyways, with a null value (i.e. [tagname][/tagname]).

Thus, I have found it is easiest to use XMLELEMENT instead of XMLFOREST for these cases. Note: IBM offers a directive to print empty elements for null values, but until Oracle implements such a feature, your best best is probably to leverage XMLELEMENT:

1
2
3
4
5
6
7
8
9
10
11

SELECT xmlforest(a).getstringval() FROM (SELECT 1 a FROM dual);
--returns: <A>1</A>
 
SELECT xmlforest(a).getstringval() FROM (SELECT NULL a FROM dual);
--returns: null
 
SELECT xmlelement("A", a).getstringval() FROM (SELECT 1 a FROM dual);
--returns: <A>1</A>
 
SELECT xmlelement("A", a).getstringval() FROM (SELECT NULL a FROM dual);
--returns: <A></A>

The latest iPhone update appears to have broken some SSL-enabled OracleAS sites. We have been working through finding a solution for an Oracle Portal customer of ours and it has been fairly hairy. On at least one of the environments, we have found a solution. We can likely help if you have been having an issue with your SSL handshakes.

Like many days, I had a need to query data stored in Oracle. But today was different. I needed to return the data in an XML format. I started with a very simple need and generated the XML brute-force while traversing my cursor. However, I realized the data would not be escaped properly and knew/assumed Oracle has created built-in functionality to accomplish what I was trying to do.

I then recreated my logic using DBMS_XMLGEN. This worked well, in that it created the desired result set, but I hate the idea of storing SQL in a string to be passed into a function, which is precisely what DBMS_XMLGEN was having me do. I had a hard time figuring out the precise technique recommended by Oracle, or even the “latest” technique. My client’s environment was 9i, so I knew I wasn’t going to get to play with anything interesting 11g might have to offer.

My brief research showed that SQL/XML (SQLX) appeared to be the best option. A few hours later, it really has been nice. I have been able to create both simple and complex XML results. Once you have the syntax down, you can generate just about any XML structure you desire.

Let me know if you come across anything more interesting/useful than SQLX for querying Oracle RDBMS tables (not XML data stored natively in the DB), and I’ll be interested in checking it out.

I have seen multiple custom solutions and packaged applications store their SQL outside of stored procedures. I am focusing this article on custom enterprise solutions, which I feel is one of the fastest growing areas in development. Some people who choose to store SQL outside of their database have touted database independence/abstraction, others easier maintenance, and I’m sure many other “excuses” have been communicated. In my opinion, if you have 1) a custom enterprise application and 2) an Oracle database(s) as the backend, all of your SQL — and associated DB logic — should be written, stored, maintained, etc. within Oracle stored procedures. I can dream up very unique scenarios where a valid argument could possibly begin to be made to keep SQL outside — but I think 99% of places that have done this, or will be considering this, really should have kept SQL inside their DB.

I’d like to hear your reasons for maintaining your Oracle SQL outside of Stored Procedures.

When installing 10g OID on OEL5, if you run into the following error, you can run the below commands to address the issue:

Error:

OPMN HTTP process fails to start.  When you look in the log file, it says:
/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file:  No such file or directory

To fix, run the following commands:

# ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2
# chmod 755 /usr/lib/libgdbm.so.2.0.0
# chmod 755 /usr/lib/libdb.so.2

When I did a quick search online, I found some keyboard shortcuts for Visual Studio 2008 that didn’t work for me, so thought I would get a post going.

Comment and Uncomment Code Blocks Quickly

I comment blocks of code in VS2008 with:

CTRL-E, C

…and I uncomment blocks of code with:

CRTL-E, U

I am excited about BPEL’s proliferation. But for some organizations (specifically, IT shops), BPEL is being used in areas it is simple not well-suited. For example, some places where IT has gotten ahead of the business, it is used as a process modeling tool. True, BPEL “represents” a process, it is my opinion that proper business modeling tools be used prior to orchestration being defined in BPEL.

Another scenario I continue to find is that people are using BPEL for just about every piece of logic that goes into a business process being executed. The fact is, BPEL does very well at integrating with other services where complex — and more importantly, “fast-changing” — logic is executed. So, my advise is to not throw away every other tool in the belt in the face of BPEL. Fast-changing decisions are better handled outside of BPEL, especially as you consider the deployment process for BPEL, and most especially if you have long-running processes. Business rules engines, databases, and external services that perform specific logic/functions should continue to be leveraged in these cases. I’d be interested in hearing from others who are concerned about this challenge or maybe have already faced it.

I have been somewhat enamored by Google Apps and its potential. Many enterprises have been happy with Exchange for mail, calendaring, contacts, and more. I have been one of those people. However, I have found Google Apps to be getting better and better for use in the enterprise and I will not be surprised to see it begin to take significantly larger chunk of market share from Microsoft over time.