passing report parameters to an Oracle proc

Hi,
I received an error message after running a report by pushing the Preview
button:
ORA-00972:identifier is too long
ORA-06512:at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1
Here is the Oracle proc:
CREATE OR REPLACE PACKAGE BODY MPC IS
PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
ip_launch_list IN VARCHAR2,
results_cur
OUT T_RESULT_CURSOR)
Here is the Query string:
"MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
At the same time, in the Parameters tab of the Dataset screen, I also create
two parameters:
Name= :ip_response_interval
Value= =Parameters!ip_response_interval.Value
Name= :ip_launch_list
Vaue= =Parameters!ip_launch_list.Value
However, if I click the generic query design button and just type
"MPC.Report_Performance_Main_5" and click the Run button, it pops out the
"Define Query Parameters" screen. After typing the necessary data and hit the
"OK" button, I am able to see the query result in the buttom pane.
I am confused what's wrong when I hit the Preview button.
Any clues will be very appreciated!
JamesNot sure but I think in the generic query window you can do this:
MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
It should create the report parameters for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <James@.discussions.microsoft.com> wrote in message
news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> Hi,
> I received an error message after running a report by pushing the Preview
> button:
> ORA-00972:identifier is too long
> ORA-06512:at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Here is the Oracle proc:
> CREATE OR REPLACE PACKAGE BODY MPC IS
> PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> ip_launch_list IN VARCHAR2,
> results_cur
> OUT T_RESULT_CURSOR)
> Here is the Query string:
> "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> At the same time, in the Parameters tab of the Dataset screen, I also
> create
> two parameters:
> Name= :ip_response_interval
> Value= =Parameters!ip_response_interval.Value
> Name= :ip_launch_list
> Vaue= =Parameters!ip_launch_list.Value
> However, if I click the generic query design button and just type
> "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> "Define Query Parameters" screen. After typing the necessary data and hit
> the
> "OK" button, I am able to see the query result in the buttom pane.
> I am confused what's wrong when I hit the Preview button.
> Any clues will be very appreciated!
> James
>|||I did this in the generic query and got the following error:
An error occurred while retrieving the parameters in the query.
ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1
Is there any document about call a Oracle proc?
Thanks,
James
"Bruce L-C [MVP]" wrote:
> Not sure but I think in the generic query window you can do this:
> MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> It should create the report parameters for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "James" <James@.discussions.microsoft.com> wrote in message
> news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > Hi,
> >
> > I received an error message after running a report by pushing the Preview
> > button:
> >
> > ORA-00972:identifier is too long
> > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > ORA-06512: at line 1
> >
> > Here is the Oracle proc:
> >
> > CREATE OR REPLACE PACKAGE BODY MPC IS
> > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> >
> > ip_launch_list IN VARCHAR2,
> > results_cur
> > OUT T_RESULT_CURSOR)
> >
> > Here is the Query string:
> > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> >
> > At the same time, in the Parameters tab of the Dataset screen, I also
> > create
> > two parameters:
> > Name= :ip_response_interval
> > Value= =Parameters!ip_response_interval.Value
> >
> > Name= :ip_launch_list
> > Vaue= =Parameters!ip_launch_list.Value
> >
> > However, if I click the generic query design button and just type
> > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > "Define Query Parameters" screen. After typing the necessary data and hit
> > the
> > "OK" button, I am able to see the query result in the buttom pane.
> >
> > I am confused what's wrong when I hit the Preview button.
> >
> > Any clues will be very appreciated!
> >
> > James
> >
> >
>
>|||Here is some misc things I've kept around. I haven't used Oracle with RS
(although I did use 8.1.7 extensively in the past).
Oracle has a few unique things going on. First, my recommendation is to use
the generic data designer (2 panes). The button to switch to this is to the
right of the ...
Second, because the development environment was not designed for managed
providers they got tricky with what is used under the covers (hence my
recommendation to use the generic designer). Here is a description from
Robert Bruckner [MSFT].
/Snip
Note: the behavior of PREVIEW in Report Designer is identical to the
ReportServer behavior! However the DATA view in Report Designer is
different for the visual designer: * the visual query designer with 4 panes
will internally always use OleDB providers for verifying and executing
queries directly in "Data" view. (Main reason: the visual query designer
does not work with managed providers). Example: if you choose "Oracle" in
the data source dialog, the Data view has to use the OleDB provider for
Oracle behind the scenes, but Preview and Server will use the managed Oracle
provider. The generic text-based query designer (2 panes) will _always_ use
the data provider you specified.
/End Snip
Just a little background for you. OK, now, from the generic query designer.
He then had this to say about stored procedures:
/Snip
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/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.
/End Snip
And more from Robert:
/Snip
Managed Oracle provider (named parameters):
select * from table where ename = :parameter
OleDB for Oracle (unnamed parameters):
select * from table where ename = ?
The managed Oracle data provider uses a ':' to mark named parameters
(instead of '@.'); the OleDB provider for Oracle only allows unnamed
parameters (using '?'). The following KB article explains more details:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
/End Snip
Hope that helps. Definitely not intuitive but it works.
One last thing. The MS managed provider for Oracle need 8.1.7 or higher (8i)
client installed for it to work.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <James@.discussions.microsoft.com> wrote in message
news:14B75C3F-7959-47B9-A563-EF5436510BFC@.microsoft.com...
>I did this in the generic query and got the following error:
> An error occurred while retrieving the parameters in the query.
> ORA-00911: invalid character
> ORA-06512: at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Is there any document about call a Oracle proc?
> Thanks,
> James
> "Bruce L-C [MVP]" wrote:
>> Not sure but I think in the generic query window you can do this:
>> MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
>> It should create the report parameters for you.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "James" <James@.discussions.microsoft.com> wrote in message
>> news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
>> > Hi,
>> >
>> > I received an error message after running a report by pushing the
>> > Preview
>> > button:
>> >
>> > ORA-00972:identifier is too long
>> > ORA-06512:at "SYS.DBMS_UTILITY", line 114
>> > ORA-06512: at line 1
>> >
>> > Here is the Oracle proc:
>> >
>> > CREATE OR REPLACE PACKAGE BODY MPC IS
>> > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
>> >
>> > ip_launch_list IN VARCHAR2,
>> >
>> > results_cur
>> > OUT T_RESULT_CURSOR)
>> >
>> > Here is the Query string:
>> > "MPC"."Report_Performance_Main_5 :ip_response_interval,
>> > :ip_launch_list"
>> >
>> > At the same time, in the Parameters tab of the Dataset screen, I also
>> > create
>> > two parameters:
>> > Name= :ip_response_interval
>> > Value= =Parameters!ip_response_interval.Value
>> >
>> > Name= :ip_launch_list
>> > Vaue= =Parameters!ip_launch_list.Value
>> >
>> > However, if I click the generic query design button and just type
>> > "MPC.Report_Performance_Main_5" and click the Run button, it pops out
>> > the
>> > "Define Query Parameters" screen. After typing the necessary data and
>> > hit
>> > the
>> > "OK" button, I am able to see the query result in the buttom pane.
>> >
>> > I am confused what's wrong when I hit the Preview button.
>> >
>> > Any clues will be very appreciated!
>> >
>> > James
>> >
>> >
>>|||James:
Any luck making this successful..I am strugling with this issue as well.
Please reply if you have this working,
"James" wrote:
> I did this in the generic query and got the following error:
> An error occurred while retrieving the parameters in the query.
> ORA-00911: invalid character
> ORA-06512: at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Is there any document about call a Oracle proc?
> Thanks,
> James
> "Bruce L-C [MVP]" wrote:
> > Not sure but I think in the generic query window you can do this:
> >
> > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> >
> > It should create the report parameters for you.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "James" <James@.discussions.microsoft.com> wrote in message
> > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > Hi,
> > >
> > > I received an error message after running a report by pushing the Preview
> > > button:
> > >
> > > ORA-00972:identifier is too long
> > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > ORA-06512: at line 1
> > >
> > > Here is the Oracle proc:
> > >
> > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > >
> > > ip_launch_list IN VARCHAR2,
> > > results_cur
> > > OUT T_RESULT_CURSOR)
> > >
> > > Here is the Query string:
> > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > >
> > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > create
> > > two parameters:
> > > Name= :ip_response_interval
> > > Value= =Parameters!ip_response_interval.Value
> > >
> > > Name= :ip_launch_list
> > > Vaue= =Parameters!ip_launch_list.Value
> > >
> > > However, if I click the generic query design button and just type
> > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > the
> > > "OK" button, I am able to see the query result in the buttom pane.
> > >
> > > I am confused what's wrong when I hit the Preview button.
> > >
> > > Any clues will be very appreciated!
> > >
> > > James
> > >
> > >
> >
> >
> >|||anyone lucky enough to make oracle refcursor working under RS? kindly share
with us, thanks...
"MSOracle" wrote:
> James:
> Any luck making this successful..I am strugling with this issue as well.
> Please reply if you have this working,
> "James" wrote:
> > I did this in the generic query and got the following error:
> > An error occurred while retrieving the parameters in the query.
> > ORA-00911: invalid character
> > ORA-06512: at "SYS.DBMS_UTILITY", line 114
> > ORA-06512: at line 1
> >
> > Is there any document about call a Oracle proc?
> >
> > Thanks,
> >
> > James
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Not sure but I think in the generic query window you can do this:
> > >
> > > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> > >
> > > It should create the report parameters for you.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "James" <James@.discussions.microsoft.com> wrote in message
> > > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > > Hi,
> > > >
> > > > I received an error message after running a report by pushing the Preview
> > > > button:
> > > >
> > > > ORA-00972:identifier is too long
> > > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > > ORA-06512: at line 1
> > > >
> > > > Here is the Oracle proc:
> > > >
> > > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > > >
> > > > ip_launch_list IN VARCHAR2,
> > > > results_cur
> > > > OUT T_RESULT_CURSOR)
> > > >
> > > > Here is the Query string:
> > > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > > >
> > > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > > create
> > > > two parameters:
> > > > Name= :ip_response_interval
> > > > Value= =Parameters!ip_response_interval.Value
> > > >
> > > > Name= :ip_launch_list
> > > > Vaue= =Parameters!ip_launch_list.Value
> > > >
> > > > However, if I click the generic query design button and just type
> > > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > > the
> > > > "OK" button, I am able to see the query result in the buttom pane.
> > > >
> > > > I am confused what's wrong when I hit the Preview button.
> > > >
> > > > Any clues will be very appreciated!
> > > >
> > > > James
> > > >
> > > >
> > >
> > >
> > >|||I have been able to do i just recenly but Only after installing the Oracle
10g client. & using the Orcale provider.
"J" wrote:
> anyone lucky enough to make oracle refcursor working under RS? kindly share
> with us, thanks...
> "MSOracle" wrote:
> > James:
> >
> > Any luck making this successful..I am strugling with this issue as well.
> > Please reply if you have this working,
> >
> > "James" wrote:
> >
> > > I did this in the generic query and got the following error:
> > > An error occurred while retrieving the parameters in the query.
> > > ORA-00911: invalid character
> > > ORA-06512: at "SYS.DBMS_UTILITY", line 114
> > > ORA-06512: at line 1
> > >
> > > Is there any document about call a Oracle proc?
> > >
> > > Thanks,
> > >
> > > James
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Not sure but I think in the generic query window you can do this:
> > > >
> > > > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> > > >
> > > > It should create the report parameters for you.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "James" <James@.discussions.microsoft.com> wrote in message
> > > > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I received an error message after running a report by pushing the Preview
> > > > > button:
> > > > >
> > > > > ORA-00972:identifier is too long
> > > > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > > > ORA-06512: at line 1
> > > > >
> > > > > Here is the Oracle proc:
> > > > >
> > > > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > > > >
> > > > > ip_launch_list IN VARCHAR2,
> > > > > results_cur
> > > > > OUT T_RESULT_CURSOR)
> > > > >
> > > > > Here is the Query string:
> > > > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > > > >
> > > > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > > > create
> > > > > two parameters:
> > > > > Name= :ip_response_interval
> > > > > Value= =Parameters!ip_response_interval.Value
> > > > >
> > > > > Name= :ip_launch_list
> > > > > Vaue= =Parameters!ip_launch_list.Value
> > > > >
> > > > > However, if I click the generic query design button and just type
> > > > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > > > the
> > > > > "OK" button, I am able to see the query result in the buttom pane.
> > > > >
> > > > > I am confused what's wrong when I hit the Preview button.
> > > > >
> > > > > Any clues will be very appreciated!
> > > > >
> > > > > James
> > > > >
> > > > >
> > > >
> > > >
> > > >