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

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

• Fizgig

  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

  
  

• BMF

  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.

  
  

• er1067

  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.

   

   

  
  
  

• Newbie2007

  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

  
  
  

• Yaron Lavi

  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.

  
  

• Peter B.L. Rasmussen

  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

  
  

• gb99

  (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 )}
  

  
  
  

• DylanG3893

  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

  
  
  

• ricoxxx

  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

  
  
  

• Delamater

  Thank you so much for the replies. I appreciate that very much. :-) I'll try that one.
  

• bobe59

  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.

  
  
  

• Maju V Poulose

  Ok. I'll take note of the things u've said. Thanks a lot.
  
• How To Execute an Oracle 10g Stored Proc thru ODBC using VS 2005, for Reporting Services