sql-serversql-server-2005reporting-servicesvisual-studio-2005oracle-cursor

Selecting Oracle Stored Procedure in SSRS Crashes Visual Studio 2005 IDE


Problem Conditions

I have a very simple Oracle (11g) Stored Procedure that is declared like so:

CREATE OR REPLACE PROCEDURE pr_myproc(L_CURSOR out SYS_REFCURSOR)
is
   BEGIN
     OPEN L_CURSOR FOR
       SELECT * FROM MyTable;
   END;

This compiles correctly. The cursor contains col1, col2 and col3.

In SSRS, i have a Shared Data Source that uses Oracle OLEDB Provider for Oracle 11g:

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

(Plus the user credentials).

What Works OK:

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.

Does anyone have any knowledge/experience of this?

Any help would be most appreciated. Thanks.

FURTHER INFORMATION

I've modified the provider from the Oracle OLE DB Provider to the .NET Oracle Provider, and, magically, it works.

This would seem to indicate an issue with the Oracle provider.

Any more thoughts?


Solution

  • We got to the bottom of this.

    On the environment where the procedure resided, we have a substantial data dictionary. The two providers when looking up information use two different queries.

    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 = '[MY_PROCEDURE_NAME]' order by 2, 3
    

    More info can be found here