| Groups | Blog | Home |
sql server reporting services : Oracle Stored Procedure Syntax
I'd like to write a report with a data set that calls an Oracle data source
for its result set. I have a package with a procedure that returns a ref cursor as an out variable. What is the syntax for calling this procedure from the Report Designer? Does the out variable with the cursor need to be mapped to anything? Are input parameters on the procedure anything other than regular @parameter references?
John,
The sql syntax is identical to the that used for MS SQL Server, except for the @. Switch it to a : (colon) and the parameters will work just fine, example: Select Blah From tblBlah where Blah.tblBlah = :param I like to use the IN Keyword. If you set your parameters as multi-select, the parameters are passed in as an string seperated by commas, so your Where clause would look like this: Where Blah.tblBlah IN (:Param) Hope this helps, rwiethorn [quoted text, click to view] "John W at Sungard HE" wrote:
> I'd like to write a report with a data set that calls an Oracle data source > for its result set. I have a package with a procedure that returns a ref > cursor as an out variable. What is the syntax for calling this procedure > from the Report Designer? Does the out variable with the cursor need to be > mapped to anything? Are input parameters on the procedure anything other > than regular @parameter references? >
Hi,
Continuing with John's question, I want to know, What is the syntax for calling the oracle stored procedure from the Report Designer? My procedure is returning a ref cursor as an out variable. Regards, Aruna [quoted text, click to view] "rwiethorn" wrote:
> John, > The sql syntax is identical to the that used for MS SQL Server, except for > the @. Switch it to a : (colon) > and the parameters will work just fine, example: > Select Blah > From tblBlah > where Blah.tblBlah = :param > > I like to use the IN Keyword. If you set your parameters as multi-select, > the parameters are passed in as an string seperated by commas, so your Where > clause would look like this: > Where Blah.tblBlah IN (:Param) > > Hope this helps, > rwiethorn > > > "John W at Sungard HE" wrote: > > > I'd like to write a report with a data set that calls an Oracle data source > > for its result set. I have a package with a procedure that returns a ref > > cursor as an out variable. What is the syntax for calling this procedure > > from the Report Designer? Does the out variable with the cursor need to be > > mapped to anything? Are input parameters on the procedure anything other > > than regular @parameter references? > >
Thanks for the response - the bit with the parameters defintely helps. Do
you have an example that references a stored procedure instead of a select statement? For example, do I have to wrap the procedure call in a PL/SQL block like: BEGIN CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm ) END or is it sufficient to call the procedure without the block like: CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm ) And is the above parameter syntax going to work? What about the output parameter that accepts the cursor back from the stored procedure? Is that just set up as a report parameter like any other, even though it's an output parameter? Does it matter where the output parameter is placed in the call list, i.e. does it need to appear either first or last? Thanks! JW [quoted text, click to view] "rwiethorn" wrote:
> John, > The sql syntax is identical to the that used for MS SQL Server, except for > the @. Switch it to a : (colon) > and the parameters will work just fine, example: > Select Blah > From tblBlah > where Blah.tblBlah = :param > > I like to use the IN Keyword. If you set your parameters as multi-select, > the parameters are passed in as an string seperated by commas, so your Where > clause would look like this: > Where Blah.tblBlah IN (:Param) > > Hope this helps, > rwiethorn > > > "John W at Sungard HE" wrote: > > > I'd like to write a report with a data set that calls an Oracle data source > > for its result set. I have a package with a procedure that returns a ref > > cursor as an out variable. What is the syntax for calling this procedure > > from the Report Designer? Does the out variable with the cursor need to be > > mapped to anything? Are input parameters on the procedure anything other > > than regular @parameter references? > >
change the command type to stored procedure.
The syntax then looks like this packagename.procedurename no need to put any parameters in here. [quoted text, click to view] John W at Sungard HE wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do > you have an example that references a stored procedure instead of a select > statement? For example, do I have to wrap the procedure call in a PL/SQL > block like: > > BEGIN > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm ) > END > > or is it sufficient to call the procedure without the block like: > > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm ) > > And is the above parameter syntax going to work? What about the output > parameter that accepts the cursor back from the stored procedure? Is that > just set up as a report parameter like any other, even though it's an output > parameter? Does it matter where the output parameter is placed in the call > list, i.e. does it need to appear either first or last? > > Thanks! > > JW > > "rwiethorn" wrote: > > > John, > > The sql syntax is identical to the that used for MS SQL Server, except for > > the @. Switch it to a : (colon) > > and the parameters will work just fine, example: > > Select Blah > > From tblBlah > > where Blah.tblBlah = :param > > > > I like to use the IN Keyword. If you set your parameters as multi-select, > > the parameters are passed in as an string seperated by commas, so your Where > > clause would look like this: > > Where Blah.tblBlah IN (:Param) > > > > Hope this helps, > > rwiethorn > > > > > > "John W at Sungard HE" wrote: > > > > > I'd like to write a report with a data set that calls an Oracle data source > > > for its result set. I have a package with a procedure that returns a ref > > > cursor as an out variable. What is the syntax for calling this procedure > > > from the Report Designer? Does the out variable with the cursor need to be > > > mapped to anything? Are input parameters on the procedure anything other > > > than regular @parameter references? > > > > > > Thanks!
John,
I'm a MS SQL Server guy myself, so I'm limited on the Oracle. I have a Oracle DBA that makes tables for me when I define my sql statement. I've not had the need to do any processing, I'm been only needing simple selects with groupings. [quoted text, click to view] >What about the output I think you can do with out it, the system may just 'swallow' the return > parameter that accepts the cursor back from the stored procedure? value (ie a 0 or 1) indicating a success or failure. [quoted text, click to view] >Does it matter where the output parameter is placed in the call Yes, parameter order does matter when your calling stored procs, you need to > list, i.e. does it need to appear either first or last? match the order and the data type. I hope this helps, rwiethorn [quoted text, click to view] "John W at Sungard HE" wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do > you have an example that references a stored procedure instead of a select > statement? For example, do I have to wrap the procedure call in a PL/SQL > block like: > > BEGIN > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm ) > END > > or is it sufficient to call the procedure without the block like: > > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm ) > > And is the above parameter syntax going to work? What about the output > parameter that accepts the cursor back from the stored procedure? Is that > just set up as a report parameter like any other, even though it's an output > parameter? Does it matter where the output parameter is placed in the call > list, i.e. does it need to appear either first or last? > > Thanks! > > JW > > "rwiethorn" wrote: > > > John, > > The sql syntax is identical to the that used for MS SQL Server, except for > > the @. Switch it to a : (colon) > > and the parameters will work just fine, example: > > Select Blah > > From tblBlah > > where Blah.tblBlah = :param > > > > I like to use the IN Keyword. If you set your parameters as multi-select, > > the parameters are passed in as an string seperated by commas, so your Where > > clause would look like this: > > Where Blah.tblBlah IN (:Param) > > > > Hope this helps, > > rwiethorn > > > > > > "John W at Sungard HE" wrote: > > > > > I'd like to write a report with a data set that calls an Oracle data source > > > for its result set. I have a package with a procedure that returns a ref > > > cursor as an out variable. What is the syntax for calling this procedure > > > from the Report Designer? Does the out variable with the cursor need to be > > > mapped to anything? Are input parameters on the procedure anything other > > > than regular @parameter references? > > >
Sorry, I did not get it. How I can send an OUT parameter (CURSOR) and use it
to generate report? Thank you [quoted text, click to view] "rwiethorn" wrote:
> John, > The sql syntax is identical to the that used for MS SQL Server, except for > the @. Switch it to a : (colon) > and the parameters will work just fine, example: > Select Blah > From tblBlah > where Blah.tblBlah = :param > > I like to use the IN Keyword. If you set your parameters as multi-select, > the parameters are passed in as an string seperated by commas, so your Where > clause would look like this: > Where Blah.tblBlah IN (:Param) > > Hope this helps, > rwiethorn > > > "John W at Sungard HE" wrote: > > > I'd like to write a report with a data set that calls an Oracle data source > > for its result set. I have a package with a procedure that returns a ref > > cursor as an out variable. What is the syntax for calling this procedure > > from the Report Designer? Does the out variable with the cursor need to be > > mapped to anything? Are input parameters on the procedure anything other > > than regular @parameter references? > >
Sorry, can I have more details, please. Like, what driver did you use, did
you use parameters Tab to set parameter and so on. Thank you [quoted text, click to view] "gene.furibondo@gmail.com" wrote:
> change the command type to stored procedure. > > The syntax then looks like this > > packagename.procedurename > > no need to put any parameters in here. > > John W at Sungard HE wrote: > > Thanks for the response - the bit with the parameters defintely helps. Do > > you have an example that references a stored procedure instead of a select > > statement? For example, do I have to wrap the procedure call in a PL/SQL > > block like: > > > > BEGIN > > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm ) > > END > > > > or is it sufficient to call the procedure without the block like: > > > > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm ) > > > > And is the above parameter syntax going to work? What about the output > > parameter that accepts the cursor back from the stored procedure? Is that > > just set up as a report parameter like any other, even though it's an output > > parameter? Does it matter where the output parameter is placed in the call > > list, i.e. does it need to appear either first or last? > > > > Thanks! > > > > JW > > > > "rwiethorn" wrote: > > > > > John, > > > The sql syntax is identical to the that used for MS SQL Server, except for > > > the @. Switch it to a : (colon) > > > and the parameters will work just fine, example: > > > Select Blah > > > From tblBlah > > > where Blah.tblBlah = :param > > > > > > I like to use the IN Keyword. If you set your parameters as multi-select, > > > the parameters are passed in as an string seperated by commas, so your Where > > > clause would look like this: > > > Where Blah.tblBlah IN (:Param) > > > > > > Hope this helps, > > > rwiethorn > > > > > > > > > "John W at Sungard HE" wrote: > > > > > > > I'd like to write a report with a data set that calls an Oracle data source > > > > for its result set. I have a package with a procedure that returns a ref > > > > cursor as an out variable. What is the syntax for calling this procedure > > > > from the Report Designer? Does the out variable with the cursor need to be > > > > mapped to anything? Are input parameters on the procedure anything other > > > > than regular @parameter references? > > > > > > > > Thanks! >
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 |