none
How To Execute an Oracle 10g Stored Proc thru ODBC using VS 2005, for Reporting Services

    Question

  • Good day! :-)

    Anybody who knows the syntax for executing an Oracle Stored Proc thru ODBC connection using VS 2005 for Reporting Services? I am having a problem about this.

    I am using the ODBC connection because VS 2005 does not support Oracle Database 10g in its Oracle Database Connection feature (only 7.3, 8i, and 9i), that's y.

    While using MS SQL Server 2000, I never encountered any problem. But using Oracle Database 10g, I find it hard to call the stored procedures, an error is generated everytime.

    Please click the link below for the screenshots:

    http://www.geocities.com/vrcode2/oraError.JPG 

    http://www.geocities.com/vrcode2/storedProc.JPG

    Please help...thank you so much.

     

    Francis

     

     

     

     

     

     

    Wednesday, April 19, 2006 6:14 AM

Answers

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

    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 do not use "ODBC" (it won't work due to cursors used by Oracle stored procedures) - but rather "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 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

     

    Wednesday, April 19, 2006 4:09 PM
    Owner
  • You should still be able to use "Oracle" as data source type with Oracle 10g - but you may have problems getting the data correctly if you use any of the Oracle 10g specific data types in your tables. 

     -- Robert

    Thursday, April 20, 2006 1:32 AM
    Owner
  • Thanks for getting back.

    Just to confirm - you got it working by using the "Oracle" data extension and following the advice regarding OUT REF cursors, right?

    As mentioned previously, it won't work through ODBC. Unless you write a custom data extension that wraps the Oracle ODBC provider or any other Oracle provider and does the necessary cursor parameter handling in the data extension.

    -- Robert

     

    Wednesday, May 03, 2006 5:04 AM
    Owner
  • Problems regarding OUT REF cursors never emerged when I used the "Oracle (client)" connection using VS 2005 for Reporting Services. The way I invoke Oracle stored procs (with or without input parameters) is the same as invoking MS SQL Server stored procs. I was able to retrieve the data smoothly.

    You're right, it won't work through ODBC.

     

    Friday, May 05, 2006 12:36 AM

All replies

  • (I have moved this thread to the RS forum, from SSIS)

    Try using the ODBC canonical syntax-

    {call schema.proc}
    {call schema.proc()}
    {call schema.proc( 1, 2 )}

    Wednesday, April 19, 2006 7:34 AM
  • Thank you so much for the reply.:-) I would like to clarify something about the "schema" you have mentioned. Am I going to create it on the Oracle side? If yes, how?

    As what I know, Oracle throws a "cursor" everytime you retrieve data using stored procedures, and somebody mentioned that we need to catch it. I am using VS 2005. Were you able to view the screenshots?

    Thanks again.

     

    Francis

     

    Wednesday, April 19, 2006 8:48 AM
  • If you don't have a schema (you must have really) or it is not required then forget about that, just use the proc name directly.

    {call proc}

    No idea about cursors, and yes the screenshots were fine.

    Wednesday, April 19, 2006 9:18 AM
  • First, make sure that you are running at least RS 2000 with SP1 installed on report designer and report server.

    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 do not use "ODBC" (it won't work due to cursors used by Oracle stored procedures) - but rather "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 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

     

    Wednesday, April 19, 2006 4:09 PM
    Owner
  • Thank you so much for the replies. I appreciate that very much. :-) I'll try that one.
    Thursday, April 20, 2006 12:11 AM
  • By the way, I am using VS 2005. But VS 2005 given your solution "Oracle sa data source" supports only 7.3, 8i and 9i only as indicated there. 

     Robert Bruckner MSFT wrote:

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

    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 do not use "ODBC" (it won't work due to cursors used by Oracle stored procedures) - but rather "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 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, April 20, 2006 12:22 AM
  • You should still be able to use "Oracle" as data source type with Oracle 10g - but you may have problems getting the data correctly if you use any of the Oracle 10g specific data types in your tables. 

     -- Robert

    Thursday, April 20, 2006 1:32 AM
    Owner
  • Ok. I'll take note of the things u've said. Thanks a lot.
    Friday, April 21, 2006 5:56 AM
  • I'll give you updates about the use of the MS SQL Server Reporting Services with VS 2005 and Oracle, this might be helpful for those who will encounter this same ctuation in the future.

    I have discovered that if you're going to connect thru ODBC, even if the connection is successful, the user is still prompted to input a password, which should not be the case when using the Reporting Services. Another thing to take note is the issue about REF CURSOR, we're going to have problems with this. And also the syntax on how you're going to call the stored procedures (with or without input parameters).

    With modifications made through Oracle Net Manager, and I tried AGAIN connecting through "Oracle (client)" feature of VS 2005 (even if the list only include Oracle 7.3, 8i, and 9i that are supported) with some changes also, you'd still be able to generate  correct data with that solution. If the configuration is correct, you just need to call the stored procedure (with or without input parameter), you won't have any problems about the correct syntax.

    I suppose VS 2005 was released earlier than Oracle 10g, that's why 10g wasn't included  If ever u'd encounter the same ctuation like mine, and u need the details on how to connect, do not hesitate to ask me.

     

     


    Wednesday, May 03, 2006 12:43 AM
  • Thanks for getting back.

    Just to confirm - you got it working by using the "Oracle" data extension and following the advice regarding OUT REF cursors, right?

    As mentioned previously, it won't work through ODBC. Unless you write a custom data extension that wraps the Oracle ODBC provider or any other Oracle provider and does the necessary cursor parameter handling in the data extension.

    -- Robert

     

    Wednesday, May 03, 2006 5:04 AM
    Owner
  • Problems regarding OUT REF cursors never emerged when I used the "Oracle (client)" connection using VS 2005 for Reporting Services. The way I invoke Oracle stored procs (with or without input parameters) is the same as invoking MS SQL Server stored procs. I was able to retrieve the data smoothly.

    You're right, it won't work through ODBC.

     

    Friday, May 05, 2006 12:36 AM
  • We are having the same issue for executing a SP using parameters from SQL reporting services project.

    Ih the data view of the RDL, with the command type as text  and dataset as Dataset1, I wrote this in the generic query designer

    Call DSNP002.SPODS001(?,?)  //the SP takes 2 integer in params

    click the execute button. It asks for parameter values.Put in the parameter values and OK. Error.

    Error[HY000][DataDirect][ODBC DB2 Wire protocol Driver][UDB DB2 for OS/390 and z/OS]Value of input host variable Num 001 not used; Wrong data type.

    _____________________________________________

    I used the same stuff from ASP.net program. It worked.

    OdbcConnection myConn = new OdbcConnection("DSN=D2AD0;IpAddress=bevmvs.beverlycorp.com;UID=xxxxxx;PWD=yyyyyy;TcpPort=44605;Location=USSILR_91090I01");

    string strSPName="Call DSNP002.SPODS001(?,?)";

    OdbcCommand spcmd = new OdbcCommand(strSPName,myConn);

    spcmd.CommandType = CommandType.StoredProcedure;

    OdbcParameter param = new OdbcParameter();

    param.DbType = DbType.Int32;

    param.Value = pValue1;

    spcmd.Parameters.Add(param);

    param = new OdbcParameter();

    param.DbType = DbType.Int32;

    param.Value = pValue2;

    spcmd.Parameters.Add(param);

    Please help by replying a plausible resolution.

    Wednesday, June 07, 2006 5:52 PM
  • Hi all,

    I am having the Same problem ,  I am using the SSRS 2005 to connect to Oracale 10g database , the database provider i used is Oracle (ODP) .

    I want to excute the Oracle package  in dataset1  "call eec_sys.eec_pck_logins.login('ABS', 'ASC', 'XYZ')"   which sets up some environment variables and immediately after this  i am going to excute the sql  query

    package does not return any ref cursor.

    "select *  from   temp"  but not working  .

    if  i  runs  the  package   first  "call eec_sys.eec_pck_logins.login('ABS', 'ASC', 'XYZ')"

    after that i comment package  and excutes the query    "select *  from   temp"  within  the same dataset  it works .

    If i executes both the queries  at once as below

     

    call eec_sys.eec_pck_logins.login('ABS', 'ASC', 'XYZ')

    select *  from   temp

     

     it gives me an error "SQL command not properly ended
     (Microsoft Report Designer)"

    if i puts an semicolon at the end of the each query it also gives an error

    " invalid character
     (Microsoft Report Designer)"

     

    Please help

    Sushil

     

     

     

    Monday, October 20, 2008 11:20 AM