Hi,
I want to get data from Oracle 10g Stored procedure to Reporting Services
2005. I could pass a SQL text and get a record set, but I want to execute a
store proc and get the record set.
1. Add New Data Source
2. Choose Type : Oracle and connection tested OK
3. { call Test_Package.Test_Procedure(?) } is it wrong... how to write?
There is an error in the query. ORA-00911: invalid character
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.
Question : I tried many methods but unable to solve it...
create or replace
PACKAGE Test_Package
AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
END Test_Package;
create or replace PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Package.Test_Type,
Test_Parameter IN Test_Table.ID%TYPE
)
AS
BEGIN
OPEN Test_Cursor FOR
SELECT *
FROM Test_Table WHERE Test_Table.ID >= Test_Parameter;
END Test_Procedure;
The below site gave some example but i could not solve it... any suggestions
greatly appreciated...
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=357121&SiteID=17
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/bbc613c4529ed3cd/696624ec4ba70937?q=oracle+stored+procedures
I want to know HOW to call Oracle stored procedure with parameter using
Reporting Services (VS 2005)... that's all...
thanks
ramaYour issue is the difference between OLEDB provider (4 pane) versus the
dotnet provider (2 pane). The below explains what is happening in detail:
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
"rama2007" <rama2007@.discussions.microsoft.com> wrote in message
news:E3A84017-C16A-4AF2-8409-BB2D050D7BDD@.microsoft.com...
> Hi,
> I want to get data from Oracle 10g Stored procedure to Reporting Services
> 2005. I could pass a SQL text and get a record set, but I want to execute
> a
> store proc and get the record set.
> 1. Add New Data Source
> 2. Choose Type : Oracle and connection tested OK
> 3. { call Test_Package.Test_Procedure(?) } is it wrong... how to
> write?
> There is an error in the query. ORA-00911: invalid character
>
> 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.
> Question : I tried many methods but unable to solve it...
> create or replace
> PACKAGE Test_Package
> AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
> END Test_Package;
> create or replace PROCEDURE Test_Procedure (
> Test_Cursor IN OUT Test_Package.Test_Type,
> Test_Parameter IN Test_Table.ID%TYPE
> )
> AS
> BEGIN
> OPEN Test_Cursor FOR
> SELECT *
> FROM Test_Table WHERE Test_Table.ID >= Test_Parameter;
> END Test_Procedure;
> The below site gave some example but i could not solve it... any
> suggestions
> greatly appreciated...
> http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=357121&SiteID=17
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/bbc613c4529ed3cd/696624ec4ba70937?q=oracle+stored+procedures
> I want to know HOW to call Oracle stored procedure with parameter using
> Reporting Services (VS 2005)... that's all...
> thanks
> rama|||Thanks Bruce...
Thank you for detailed explanation. Appreciate it..
Somehow, i tried to use the text-based query designer (2 panes) to settle
the problem, i 'm still having the difficulty.. i will try again and get back
to you.. any other suggestions'
thanks.
rama arumugam
"Bruce L-C [MVP]" wrote:
> Your issue is the difference between OLEDB provider (4 pane) versus the
> dotnet provider (2 pane). The below explains what is happening in detail:
> 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
> "rama2007" <rama2007@.discussions.microsoft.com> wrote in message
> news:E3A84017-C16A-4AF2-8409-BB2D050D7BDD@.microsoft.com...
> > Hi,
> >
> > I want to get data from Oracle 10g Stored procedure to Reporting Services
> > 2005. I could pass a SQL text and get a record set, but I want to execute
> > a
> > store proc and get the record set.
> >
> > 1. Add New Data Source
> > 2. Choose Type : Oracle and connection tested OK
> > 3. { call Test_Package.Test_Procedure(?) } is it wrong... how to
> > write?
> > There is an error in the query. ORA-00911: invalid character
> >
> >
> > 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.
> > Question : I tried many methods but unable to solve it...
> >
> > create or replace
> > PACKAGE Test_Package
> > AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
> > END Test_Package;
> >
> > create or replace PROCEDURE Test_Procedure (
> > Test_Cursor IN OUT Test_Package.Test_Type,
> > Test_Parameter IN Test_Table.ID%TYPE
> > )
> > AS
> > BEGIN
> > OPEN Test_Cursor FOR
> > SELECT *
> > FROM Test_Table WHERE Test_Table.ID >= Test_Parameter;
> > END Test_Procedure;
> >
> > The below site gave some example but i could not solve it... any
> > suggestions
> > greatly appreciated...
> >
> > http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=357121&SiteID=17
> >
> > http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/bbc613c4529ed3cd/696624ec4ba70937?q=oracle+stored+procedures
> >
> > I want to know HOW to call Oracle stored procedure with parameter using
> > Reporting Services (VS 2005)... that's all...
> >
> > thanks
> > rama
>
>|||All I can tell you is that I know it is possible to get this to work. The
stored proc has to be written correctly. You need to configure the oracle
client properly. One suggestion, using the two pane generic query designer
make sure that you can query the Oracle database. Then you know that part of
the setup is working. Deploy it and make sure that for a very simple query
that you have the client and oracle.net provider all working. Then get it
working with the stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"rama2007" <rama2007@.discussions.microsoft.com> wrote in message
news:CE9BE8A3-47E0-4739-BC31-E7F80B4E460E@.microsoft.com...
> Thanks Bruce...
> Thank you for detailed explanation. Appreciate it..
> Somehow, i tried to use the text-based query designer (2 panes) to settle
> the problem, i 'm still having the difficulty.. i will try again and get
> back
> to you.. any other suggestions'
> thanks.
> rama arumugam
>
> "Bruce L-C [MVP]" wrote:
>> Your issue is the difference between OLEDB provider (4 pane) versus the
>> dotnet provider (2 pane). The below explains what is happening in detail:
>> 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
>> "rama2007" <rama2007@.discussions.microsoft.com> wrote in message
>> news:E3A84017-C16A-4AF2-8409-BB2D050D7BDD@.microsoft.com...
>> > Hi,
>> >
>> > I want to get data from Oracle 10g Stored procedure to Reporting
>> > Services
>> > 2005. I could pass a SQL text and get a record set, but I want to
>> > execute
>> > a
>> > store proc and get the record set.
>> >
>> > 1. Add New Data Source
>> > 2. Choose Type : Oracle and connection tested OK
>> > 3. { call Test_Package.Test_Procedure(?) } is it wrong... how to
>> > write?
>> > There is an error in the query. ORA-00911: invalid character
>> >
>> >
>> > 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.
>> > Question : I tried many methods but unable to solve it...
>> >
>> > create or replace
>> > PACKAGE Test_Package
>> > AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
>> > END Test_Package;
>> >
>> > create or replace PROCEDURE Test_Procedure (
>> > Test_Cursor IN OUT Test_Package.Test_Type,
>> > Test_Parameter IN Test_Table.ID%TYPE
>> > )
>> > AS
>> > BEGIN
>> > OPEN Test_Cursor FOR
>> > SELECT *
>> > FROM Test_Table WHERE Test_Table.ID >= Test_Parameter;
>> > END Test_Procedure;
>> >
>> > The below site gave some example but i could not solve it... any
>> > suggestions
>> > greatly appreciated...
>> >
>> > http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=357121&SiteID=17
>> >
>> > http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/bbc613c4529ed3cd/696624ec4ba70937?q=oracle+stored+procedures
>> >
>> > I want to know HOW to call Oracle stored procedure with parameter using
>> > Reporting Services (VS 2005)... that's all...
>> >
>> > thanks
>> > rama
>>|||Hi i have a sp which returns Ref cursr.
var x refcursor;
exec am.GET_test(1,146,14,51,285, :x);
print x;
I tried this and its working form sql plus. What thst syntax for the the
same from SSRS
| |
No comments:
Post a Comment