M&S Consulting - Solutions Consultants Training

TOAD for Oracle 9 or TOAD 10 - 64-bit Windows - ORA-12154: could not resolve the connect identifier specified or ORA-6413: connection not open

ashok.aggarwal — Sat, 27 Feb 2010 21:48:08 +0000

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.

BPA 10.1.3.4 Windows Install with XE I have just had a heck of a time with...
Oracle Express Edition (XE) 10g Windows Download The following are links to the download page for Oracle...
SQL LIKE - Oracle LIKE Operator - SQL Syntax Examples The reserved word LIKE in Oracle SQL represents an operator...
Oracle Client Download (11g Windows Client) - 32 bit and x64 Following are links to the Oracle 11g Release 1 Client...
MySQL and SQL Server - Oracle CONNECT BY PRIOR for Recursive, Hierarchical Data Recursive queries are something I have used many, many times...

MySQL and SQL Server - Oracle CONNECT BY PRIOR for Recursive, Hierarchical Data

ashok.aggarwal — Wed, 24 Feb 2010 17:37:47 +0000

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.

Oracle Database vs Sun MySQL Well, this is a topic we have reviewed numerous times...
TOAD for Oracle 9 or TOAD 10 - 64-bit Windows - ORA-12154: could not resolve the connect identifier specified or ORA-6413: connection not open I recently downloaded the new 10.x version of TOAD and...
Google Apps Reporting API - Not quite this easy with Exchange I have been somewhat enamored by Google Apps and its...
Oracle IdM - SAML SSO with Google Apps We recently integrated Oracle IdM with the SAML SSO Service...
Oracle Application Server Startup and Shutdown Script Generator - FREE Complete the below to receive scripts to automatically startup and...

XMLFOREST vs XMLELEMENT - Missing vs Empty in SQLX

ashok.aggarwal — Tue, 23 Feb 2010 15:40:37 +0000

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:

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

Oracle SUBSTR Function - SQL Syntax Examples The Oracle SUBSTR SQL Function is very common, and many...
Oracle INSTR Function - SQL Syntax Examples The Oracle INSTR SQL function is popular and performs materially...
Oracle TO_DATE Function - SQL Syntax Examples The Oracle TO_DATE SQL function is used to convert a...
Oracle TO_CHAR Function - SQL Syntax Examples (Most With Dates, TO_DATE) This article provides common examples of TO_CHAR using dates....
Oracle SUBSTR with INTSR Function - SQL Syntax Examples Oracle SUBSTR and INSTR SQL functions are typically...

Flex on Android - Nexus One Mobile Device

ashok.aggarwal — Mon, 22 Feb 2010 21:08:05 +0000

I just can’t wait for Flash on Android…and for Apple’s hand to [hopefully] get forced into supporting Flash as well.

Click here to view the embedded video.

Flash-Based Oracle MetaLink Sucks I am a big fan of RIA, Flex, and Flash...
Flex 3.0 Introspecting Utility: Handling Collections Bug Even Flex Builder with all its awesomeness is not without...

SSL with Oracle Application Server WebCache and iPhone

ashok.aggarwal — Wed, 03 Feb 2010 13:19:59 +0000

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.

OBIEE for the iPhone We recently had a customer request help with configuring OBIEE...
Oracle Application Server Startup and Shutdown Script Generator - FREE Complete the below to receive scripts to automatically startup and...
OracleAS 10.1.2 on 32 bit vs. 64 bit Some people get excited about the prospects of running Oracle...
Oracle Portal Server Dynamic Menu As part of the M&S Dynamic Oracle Portal utility, the...
Oracle Certification Matrix - Online Application Oracle provides a dynamic, web-based application they call "Certify" that...

SQL to Query Oracle, Return XML - with SQLX

ashok.aggarwal — Thu, 07 Jan 2010 07:27:00 +0000

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.

XMLFOREST vs XMLELEMENT - Missing vs Empty in SQLX Like me, some people may have gotten excited about using...
Applications with SQL Maintained Outside of Stored Procedures I have seen multiple custom solutions and packaged applications store...
MySQL and SQL Server - Oracle CONNECT BY PRIOR for Recursive, Hierarchical Data Recursive queries are something I have used many, many times...
Oracle Rename Table - Example Syntax and Dependencies Renaming a table in Oracle is simple. Following is the...
Oracle TO_CHAR Function - SQL Syntax Examples (Most With Dates, TO_DATE) This article provides common examples of TO_CHAR using dates....

Applications with SQL Maintained Outside of Stored Procedures

ashok.aggarwal — Tue, 24 Nov 2009 19:37:04 +0000

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.

MySQL and SQL Server - Oracle CONNECT BY PRIOR for Recursive, Hierarchical Data Recursive queries are something I have used many, many times...
Oracle Rename Table - Example Syntax and Dependencies Renaming a table in Oracle is simple. Following is the...
Discoverer - Public Connections for Oracle Applications with SSO While using SSO, in order to create a public connection...
Oracle APEX Tutorial 8 - Up and Downloading Files - Part 2 - Video Training Reports and data entry constitue the bulk of what most...
SQL to Query Oracle, Return XML - with SQLX Like many days, I had a need to query data...

OID Install in Oracle Enterprise Linux (OEL) 5 - Shared Libraries Error

amit.gupta — Sun, 01 Nov 2009 14:45:44 +0000

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

BPA 10.1.3.4 Windows Install with XE I have just had a heck of a time with...
Invalid Nested Schema Loading Error with Oracle BPEL, AIA/PIP I faced this issue in the context of building an...
Oracle System Administration - Linux Commands (Red Hat) Following is a sortable table of the most common Linux...
Move Oracle Linux VM to New Machine - Static IP I just copied an Oracle Enterprise Linux VM from one...
Oracle VM Templates Firewall Policy Many Oracle VM templates come with a default firewall policy...

Visual Studio 2008 (VS2008) - Common Keyboard Shortcuts

ashok.aggarwal — Sun, 27 Sep 2009 00:48:27 +0000

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

Google Custom Search Engine (CSE) The site search game is changing with Google CSE....
Oracle ADF Faces 10g: Submitting a part of the page Did you know ADF Faces allows you to submit only...
Oracle INSTR Function - SQL Syntax Examples The Oracle INSTR SQL function is popular and performs materially...
Google Apps Reporting API - Not quite this easy with Exchange I have been somewhat enamored by Google Apps and its...
TOAD for Oracle 9 or TOAD 10 - 64-bit Windows - ORA-12154: could not resolve the connect identifier specified or ORA-6413: connection not open I recently downloaded the new 10.x version of TOAD and...

BPEL Not Good For Rapid Changes

ashok.aggarwal — Wed, 16 Sep 2009 15:12:50 +0000

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.

Asynchronous vs Synchronous BPEL Processes Asynchronous and Synchronous BPEL processes, as their names imply, process...
Salesforce.com Integration - Oracle Fusion Middleware SOA and BPEL Oracle Corporation has recently published a white paper written by...
Oracle BPEL Sensors for Real Time Event Publishing from Business Processes - A Design Perspective This article is a look at Oracle’s BPEL Sensor approach...
Deciding where to use BPEL vs ESB I just had a discussion with an M&S SOA Architect...
Invalid Nested Schema Loading Error with Oracle BPEL, AIA/PIP I faced this issue in the context of building an...