none
Passing parameters to Oracle stored procedure

    Question

  • Hi,

    How can I pass input parameters to the Oracle storeprocedure from Reporting services.

    Can any one help me in this?

    Regards,

    RajeshJV

    Thursday, May 04, 2006 12:05 AM

Answers

  • There have been a few related threads on this forum recently, but here is my summary of advice:

    First, make sure that you are running at least RS 2000 with SP1 installed on report designer and report server. Or use RS 2005.

    Check the following article for general information about how to connect to Oracle from RS: http://support.microsoft.com/default.aspx?scid=kb;en-us;834305


    For stored procedures use "Oracle" as data source type which will give you the managed provider for Oracle. Also make sure that you use the text-based generic query designer (2 panes !) instead of the visual query designer (4 panes) - you can switch between them through an icon on the toolbar in the data view of report designer.

    In addition, how do you return the data from your stored procedure? Note: only an out ref cursor is supported (but not Output parameters!). Please follow the guidelines in the following article on MSDN (scroll down to the section where it talks about "Oracle REF CURSORs") on how to design the Oracle stored procedure:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp

    To use a stored procedure with regular out parameters, you should either remove the parameter (if it is possible) or write a little wrapper around the original stored procedure which checks the result of the out parameter and just returns the out ref cursor but no out parameter.

    Finally, in the generic query designer, just specify the name of the stored procedure without arguments and the input parameters should get detected automatically.

    Once you have it running successfully in report designer and deploy the report to a report server, make sure the Oracle Client software is correctly installed on the report server machine: check this KB article for more details: http://support.microsoft.com/?kbid=870668

    -- Robert

    Thursday, May 04, 2006 4:44 AM
    Owner
  • What happens if you add a wrapper stored procedure and call the wrapper instead of the original. The wrapper stored procedure would be defined without default values:

    PROCEDURE Wrapper
    (
        pResultSet out sys_refcursor,
        param1 char,
        param2 varchar2
    )
    IS
    BEGIN
        StoredProcedure_Code(pResultSet, param1, param2);
    END;

     

    -- Robert

    Friday, May 05, 2006 4:49 AM
    Owner
  •  

    Hi,

    I tired using the above suggested procedure and its working fine now.

    Thanks a lot for your suggestion.

    -- Rajesh

    Tuesday, May 09, 2006 3:07 PM

All replies

  • There have been a few related threads on this forum recently, but here is my summary of advice:

    First, make sure that you are running at least RS 2000 with SP1 installed on report designer and report server. Or use RS 2005.

    Check the following article for general information about how to connect to Oracle from RS: http://support.microsoft.com/default.aspx?scid=kb;en-us;834305


    For stored procedures use "Oracle" as data source type which will give you the managed provider for Oracle. Also make sure that you use the text-based generic query designer (2 panes !) instead of the visual query designer (4 panes) - you can switch between them through an icon on the toolbar in the data view of report designer.

    In addition, how do you return the data from your stored procedure? Note: only an out ref cursor is supported (but not Output parameters!). Please follow the guidelines in the following article on MSDN (scroll down to the section where it talks about "Oracle REF CURSORs") on how to design the Oracle stored procedure:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp

    To use a stored procedure with regular out parameters, you should either remove the parameter (if it is possible) or write a little wrapper around the original stored procedure which checks the result of the out parameter and just returns the out ref cursor but no out parameter.

    Finally, in the generic query designer, just specify the name of the stored procedure without arguments and the input parameters should get detected automatically.

    Once you have it running successfully in report designer and deploy the report to a report server, make sure the Oracle Client software is correctly installed on the report server machine: check this KB article for more details: http://support.microsoft.com/?kbid=870668

    -- Robert

    Thursday, May 04, 2006 4:44 AM
    Owner
  • Robert , Thanks for the Info.

    I have RS 2000 SP 1 installed.

    But still I have the problem.

    Here is an example of the Oracle PL/SQL code I am running.

    PROCEDURE StoredProcedure_Code
    (
        pResultSet out sys_refcursor,
        param1 char := 'A',
        param2 varchar2 := '1M'
    )
    IS
    BEGIN

        OPEN pResultSet FOR
            SELECT Code, Description FROM Catalog
            WHERE Type=param1 AND Size=param2 AND Expression IS NOT NULL
    END;

    In DataSet I am passing the parameters as:

    param1 =parameters!param1.value

    param2 =parameters!param2.value

    when I use the above PL/SQL code it is not taking the parameters that I am passing but is using the default parmeters.

    I am using "Oracle" as data source option. And jst calling the storedprocedure name.

    Could you please help me in this.

     

    Thursday, May 04, 2006 7:10 PM
  • What happens if you add a wrapper stored procedure and call the wrapper instead of the original. The wrapper stored procedure would be defined without default values:

    PROCEDURE Wrapper
    (
        pResultSet out sys_refcursor,
        param1 char,
        param2 varchar2
    )
    IS
    BEGIN
        StoredProcedure_Code(pResultSet, param1, param2);
    END;

     

    -- Robert

    Friday, May 05, 2006 4:49 AM
    Owner
  •  

    Hi,

    I tired using the above suggested procedure and its working fine now.

    Thanks a lot for your suggestion.

    -- Rajesh

    Tuesday, May 09, 2006 3:07 PM