Calling Oracle Procedures

I've seen some threads on this issue, but no clear resolution. Here is my
procedure declare:
PROCEDURE get_releases (
p_CNUMBER IN VARCHAR2,
p_HIDDENSTATE IN NUMBER,
p_COMPLIANCE IN NUMBER,
p_OBSOLETE IN NUMBER,
p_CORE IN NUMBER,
p_REL_CUR OUT RelCurTyp);
RelCurTyp is defined as an out cursor in the package definition.
I've created the report and data source and I'm trying to execute this text
command type:
get_releases :cnumber, :hiddenState, :compliance, :obsolete
It then asks me to enter the values for the parameters, but I then get
ORA-00900: invalid SQL statement.
What is the correct way to call this procedure? Will the oledb automatically
handle the returned cursor?I wanted to know how can we call Oracle stored procedures from Microsoft
Reporting Services. I could pass a sql text and get a record set, but I want
to execute a store proc and get the record set, and kindly let me know how to
pass a refcursor to get the record set into the reporting services.
Tom, please let me know when you have your answer. Thanks!
"Tom" wrote:
> I've seen some threads on this issue, but no clear resolution. Here is my
> procedure declare:
> PROCEDURE get_releases (
> p_CNUMBER IN VARCHAR2,
> p_HIDDENSTATE IN NUMBER,
> p_COMPLIANCE IN NUMBER,
> p_OBSOLETE IN NUMBER,
> p_CORE IN NUMBER,
> p_REL_CUR OUT RelCurTyp);
> RelCurTyp is defined as an out cursor in the package definition.
> I've created the report and data source and I'm trying to execute this text
> command type:
> get_releases :cnumber, :hiddenState, :compliance, :obsolete
> It then asks me to enter the values for the parameters, but I then get
> ORA-00900: invalid SQL statement.
> What is the correct way to call this procedure? Will the oledb automatically
> handle the returned cursor?|||I tried everything I could think of, and just before the laptop was about to
take a flying leap out of the window, I decided to re-do the report from
scratch.
I added a new report in the solution, copied all of the controls from the
old report to the new one and re-created the Data object as follows:
- Command type = Storedprocedure
- The only text to be added is : COMPLIANCE_PKG.get_releases
- Added the parameters by clicking the "..." next to the Dataset, and set
the parameters in the parameters tab(the out param is not required).
The DataSource is pointing to MS OLE DB Provider for Oracle and the Type of
the data source is set to Oracle, so the connection string should only have
"data source={your DS}"
It now works like a charm.|||Hi Tom,
I am working on the similar situation and call the following Oracle proc
from a MSRS report in MS Visual Studio:
MPC.Report_Performance_Main_5 :Response_Interval, :Launch_List
Response_Interval and Launch_List are both query parameters and report
parameters. The proc is:
Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
ip_launch_list IN VARCHAR2,
results_cur OUT T_RESULT_CURSOR)
When I pushed the preview button, I got the following error:
ORA-00972:identifier is too long
ORA-06512:at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1
The stored proc looks fine. It seems there is a problem from MSRS to pass
the input parameters.
Any clues would be very appreciated!
James
"Tom" wrote:
> I tried everything I could think of, and just before the laptop was about to
> take a flying leap out of the window, I decided to re-do the report from
> scratch.
> I added a new report in the solution, copied all of the controls from the
> old report to the new one and re-created the Data object as follows:
> - Command type = Storedprocedure
> - The only text to be added is : COMPLIANCE_PKG.get_releases
> - Added the parameters by clicking the "..." next to the Dataset, and set
> the parameters in the parameters tab(the out param is not required).
> The DataSource is pointing to MS OLE DB Provider for Oracle and the Type of
> the data source is set to Oracle, so the connection string should only have
> "data source={your DS}"
> It now works like a charm.|||TOM!!
you are great man!! thank you soooooo much,,, i have been trying to do that
for 3 !! and your posting solved my problem...
thaaaaaaaaaaaaaaank you
"Tom" wrote:
> I tried everything I could think of, and just before the laptop was about to
> take a flying leap out of the window, I decided to re-do the report from
> scratch.
> I added a new report in the solution, copied all of the controls from the
> old report to the new one and re-created the Data object as follows:
> - Command type = Storedprocedure
> - The only text to be added is : COMPLIANCE_PKG.get_releases
> - Added the parameters by clicking the "..." next to the Dataset, and set
> the parameters in the parameters tab(the out param is not required).
> The DataSource is pointing to MS OLE DB Provider for Oracle and the Type of
> the data source is set to Oracle, so the connection string should only have
> "data source={your DS}"
> It now works like a charm.|||Where do I set 'Command type = Storedprocedure'? or How do I recreate the
dataset?
"Tom" wrote:
> I tried everything I could think of, and just before the laptop was about to
> take a flying leap out of the window, I decided to re-do the report from
> scratch.
> I added a new report in the solution, copied all of the controls from the
> old report to the new one and re-created the Data object as follows:
> - Command type = Storedprocedure
> - The only text to be added is : COMPLIANCE_PKG.get_releases
> - Added the parameters by clicking the "..." next to the Dataset, and set
> the parameters in the parameters tab(the out param is not required).
> The DataSource is pointing to MS OLE DB Provider for Oracle and the Type of
> the data source is set to Oracle, so the connection string should only have
> "data source={your DS}"
> It now works like a charm.