When Oracle OLEDB Provider Hangs Visual Studio

I’ve been playing around with SSRS in SQL Server 2005 recently, and have been particularly encouraged by its potential as a central reporting tool. As part of this  I have been demonstrating how we can SSRS to produce reports from numerous different data sources and providers.

In this particular instance I have been testing its ability to produce reports from the output of an Oracle Package/Procedure and a SQL Server Stored Procedure.  Using the built-in .NET Client I was able to do this quite successfully with little fuss.

It was then brought to my attention that Microsoft are deprecating Oracle Client from ADO.NET 4. This means that, while the code may continue working for some considerable time, going forward it is a better strategy to use an alternative. So, I switched to using Oracle 11g Release 2 ODAC 11.2.0.1.2.

It’s worth noting that for those wanting to utilise an Oracle Stored procedure in SSRS, it must return its data in an OUT REF CURSOR. There are also a few other conditions which you must abide by.  For further info see: Tap into Oracle Databases with SQL Server Reporting Services.

Anyway, on to the problem…

So, as already intimated, all was fine and dandy, until I switched provider. I had set up the dataset on my report, entered the Stored Procedure name, and clicked ‘Ok’. Nothing happened; the IDE just hung.

At this point, I should probably reproduce the procedure in question:

PROCEDURE pck_mypackage.pr_myproc(L_CURSOR out SYS_REFCURSOR)
is
   BEGIN
     OPEN L_CURSOR FOR
       SELECT * FROM MyTable;
   END;

And here the connection string (missing the credentials).

Provider=OraOLEDB.Oracle.1;Data Source=DBSOURCE

Nothing shocking whatsoever, right?

For the first day or so, I took to killing Visual Studio, tweaking a few settings and then trying again. No-one in the websphere seemed to have encountered this; I was baffled. I even resorted to Window’s Process Monitor and trawling through the 1 million+ events it had logged.

So, I decided to confide in my old friend, the bullet-point list. I regaled it with the details of what was working, and what was not. Here it is, replete with the pertinent information:

What Works?

  • The stored procedure executes correctly in PL/SQL Developer
  • The ‘test connect’ in works fine in SSRS
  • A query string of SELECT * FROM MyTable; with Command Type of ‘text’ produces the correct fields in the SSRS report.
  • .NET Oracle Provider instead of Oracle OLE DB Provider

What Fails:

  • If i change the Command Type to ‘Stored Procedure’ and enter ‘pr_myproc’, when I click ‘OK’ Visual Studio 2005 (service pack 2) simply hangs/crashes.

My attention was drawn to the last three words, “simply hangs/crashes.” It was then I noticed that Visual Studio was not actually hanging completely. I could still click the top-right window buttons; it just seemed to be waiting.

I confided in a friendly DBA, who suggested I looked at the Oracle sessions in an attempt to determine:

  1. Whether or not a connection was actually being made, and,
  2. What it was doing if the connection was successful

Something must have piqued his interest, because he decided to do it there for me himself.

“Ahhh,” he said, “you’re querying sys.obj$.” He then proceeded to explain to me why exactly this was an issue. The schema we were searching had substantially large data dictionary. This dictionary has to be queried in order to bring back the procedure parameters and the REFCURSOR output.  When looking up the information, the two providers (.NET and Oracle) used two different queries that had a dramatic relative difference in their performances.

Here is the one the Oracle Provider used, taking 10+ minutes:

select * from (select null  PROCEDURE_CATALOG
                      , owner PROCEDURE_SCHEMA
                      , object_name PROCEDURE_NAME
                      , decode (object_type, 'PROCEDURE', 2, 'FUNCTION',  3, 1) PROCEDURE_TYPE
                      , null PROCEDURE_DEFINITION
                      , null DESCRIPTION
                      , created DATE_CREATED
                      , last_ddl_time DATE_MODIFIED
              from all_objects where object_type in ('PROCEDURE','FUNCTION')
              union all
              select null PROCEDURE_CATALOG
                          , arg.owner PROCEDURE_SCHEMA
                          , arg.package_name||'.'||arg.object_name PROCEDURE_NAME
                          , decode(min(arg.position), 0, 3, 2) PROCEDURE_TYPE
                          , null PROCEDURE_DEFINITION
                          , decode(arg.overload, '', '', 'OVERLOAD') DESCRIPTION
                          , min(obj.created) DATE_CREATED
                          , max(obj.last_ddl_time) DATE_MODIFIED
              from all_objects obj, all_arguments arg
              where arg.package_name is not null
              and   arg.owner = obj.owner
              and   arg.object_id = obj.object_id
              group by arg.owner, arg.package_name, arg.object_name, arg.overload ) PROCEDURES
WHERE PROCEDURE_NAME = 'pck_my_package.pr_myproc' order by 2, 3

The second SQL Statement in the union is a particular killer. Here, it joins all_objects and all_arguments tables! I actually ran this query in PL/SQL developer, but gave up after it failed to finish in thirty minutes.

This a particular problem of those schemas with large data dictionaries. I tried this in a smaller one, further up our shipping chain, and it returned in an acceptable amount of time.

One thought on “When Oracle OLEDB Provider Hangs Visual Studio

  1. This post was very useful to me. I was trying to figure out where this SQL was coming from. The app vendor said it wasn’t their code. In our case, the SQL was looking for stored code with the names of tables!? This was repeated for each table accessed hundreds of times a day.
    Oracle support basically told me that the solution for was to fix the application:
    “The query isn’t pointless; it is the only way to interface to stored procedures that return ref cursors (and N-types and LOBs). The Microsoft OLE DB specification does not allow for these types, so when you call a stored procedure, the provider must first query the data dictionary to find out if it has a REF CURSOR, N-type or LOB parameter so that it can handle it. It will take a lot more than a patch to fix this; the Microsoft OLE DB specification must first be extended to allow for more than just SQL Server data types, then we can implement the changes in the driver and remove this query.

    This behaviour can be disabled by setting PLSQLRSet=FALSE in the connection string, then if you do want to call stored procedures that return ref cursors you can enable it on a per-query basis; this is documented with an example on page 2-11 of the Oracle Provider for OLE DB Developer’s Guide 11.1 (B28431-01) – if you have a different version of the doc, just search for PLSQLRSet, and you’re looking for objCmd.Properties(“PLSQLRSet”) = TRUE. If you’re using IIS you might not have that level of control, but you could have two connections, one with PLSQLRSet=TRUE and the other FALSE, then use one connection for calling procedures with ref cursors and the other for the rest.

    ODP.Net doesn’t have this problem because ref cursors can be bound directly; when binding you just add an OracleDbType.RefCursor and so no data dictionary query is necessary.”

    I’m going to pass this on to the developers.
    I hope this info helps someone else because I couldn’t find much about this issue on the net.

Leave a Reply