|
|
You're in the
sql server reporting services
group:passing report parameters to an Oracle proc
sql server reporting services:
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
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 [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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, [quoted text, click to view] "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.... [quoted text, click to view] "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. [quoted text, click to view] "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 > > > > > > > > > > > > > > > > > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |