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

Choose "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) and not 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. 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/cp
contheadonetdatareader.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.

There also have been several discussion threads about Oracle stored
procedures on this newsgroup. You may want to search for these in case you
are running into issues.

-- Robert