Eclipse Community Forums: BIRT » Getting output from a Stored Procedure Dataset in BIRT

Help

Home

Home » Archived » BIRT » Getting output from a Stored Procedure Dataset in BIRT

Show: Today's Messages :: Show Polls :: Message Navigator

Getting output from a Stored Procedure Dataset in BIRT [message #193658]

Mon, 02 October 2006 18:03

Eclipse User

Originally posted by: komalta.rajani.accenture.com

Hi,

I am having problems using output parameters from a stored procedure that is
called by a stored procedure dataset in BIRT.

In my stored procedure, I have a few output parameters of type NUMBER and
BIRT automatically creates corresponding BIRT dataset output parameters of
type decimal. These output parameters show up in the data explorer under the
dataset but I am unable to drag and drop into the report mainly because they
show up as, {} param1, {} param2 and not with the icon for output
parameters.

When I click preview results for this dataset, I get the following error:
SQL statement does not return a ResultSet object.

I also tried to return a ref cursor in my procedure so that BIRT gets a
resultset on its end but it does not work.

Thanks,
Komalta

Report message to a moderator

Re: Getting output from a Stored Procedure Dataset in BIRT [message #193667 is a reply to message #193658]

Mon, 02 October 2006 18:25

Scott Rosenbaum

Messages: 425
Registered: July 2009

Senior Member

What database are you using?

Scott Rosenbaum
BIRT PMC

Komalta wrote:
> Hi,
>
> I am having problems using output parameters from a stored procedure that is
> called by a stored procedure dataset in BIRT.
>
> In my stored procedure, I have a few output parameters of type NUMBER and
> BIRT automatically creates corresponding BIRT dataset output parameters of
> type decimal. These output parameters show up in the data explorer under the
> dataset but I am unable to drag and drop into the report mainly because they
> show up as, {} param1, {} param2 and not with the icon for output
> parameters.
>
> When I click preview results for this dataset, I get the following error:
> SQL statement does not return a ResultSet object.
>
> I also tried to return a ref cursor in my procedure so that BIRT gets a
> resultset on its end but it does not work.
>
> Thanks,
> Komalta
>
>

Report message to a moderator

Re: Getting output from a Stored Procedure Dataset in BIRT [message #193675 is a reply to message #193667]

Mon, 02 October 2006 18:49

Eclipse User

Originally posted by: komalta.rajani.accenture.com

I am using Oracle.

"Scott Rosenbaum" <scottr@innoventsolutions.com> wrote in message
news:efrlh5$4sq$1@utils.eclipse.org...
> What database are you using?
>
> Scott Rosenbaum
> BIRT PMC
>
> Komalta wrote:
>> Hi,
>>
>> I am having problems using output parameters from a stored procedure that
>> is called by a stored procedure dataset in BIRT.
>>
>> In my stored procedure, I have a few output parameters of type NUMBER and
>> BIRT automatically creates corresponding BIRT dataset output parameters
>> of type decimal. These output parameters show up in the data explorer
>> under the dataset but I am unable to drag and drop into the report mainly
>> because they show up as, {} param1, {} param2 and not with the icon for
>> output parameters.
>>
>> When I click preview results for this dataset, I get the following error:
>> SQL statement does not return a ResultSet object.
>>
>> I also tried to return a ref cursor in my procedure so that BIRT gets a
>> resultset on its end but it does not work.
>>
>> Thanks,
>> Komalta
>>

Report message to a moderator

Re: Getting output from a Stored Procedure Dataset in BIRT [message #193731 is a reply to message #193675]

Mon, 02 October 2006 22:59

Eclipse User

Originally posted by: komalta.rajani.accenture.com

This is a multi-part message in MIME format.

------=_NextPart_000_0132_01C6E64C.903A2330
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I found this on some BIRT FAQs:

Q: How do I use a stored procedure?

" The JDBC ODA plugin included in BIRT currently supports SQL SELECT =
queries and simple stored procedure queries. That is, those that 1) use =
scalar input parameters only or no parameters, and 2) retrieve a single =
result set directly, like those in SQL Server or Sybase (instead of via =
a cursor output parameter like those in Oracle).=20

You can access a simple stored procedure call statement using a =
statement in the JDBC query editor. Create a JDBC data set with a dummy =
query. Use the Edit data set option to open the data set editor. replace =
the SQL SELECT statement with your stored procedure call.=20

We encourage the BIRT community to provide additional Open Data Access =
(ODA) extensions to handle various types of data sources, including =
complex stored procedures. "

Does this mean that BIRT stored procedure dataset does not support =
oracle cursor output parameters? In that case what should I use in order =
to get output in BIRT from my oracle stored procedure?

Has anyone worked on this or has any ideas??

Thanks!!

"Komalta" <komalta.rajani@accenture.com> wrote in message =
news:efrmrj$e4f$1@utils.eclipse.org...
>I am using Oracle.
>=20
> "Scott Rosenbaum" <scottr@innoventsolutions.com> wrote in message=20
> news:efrlh5$4sq$1@utils.eclipse.org...
>> What database are you using?
>>
>> Scott Rosenbaum
>> BIRT PMC
>>
>> Komalta wrote:
>>> Hi,
>>>
>>> I am having problems using output parameters from a stored procedure =
that=20
>>> is called by a stored procedure dataset in BIRT.
>>>
>>> In my stored procedure, I have a few output parameters of type =
NUMBER and=20
>>> BIRT automatically creates corresponding BIRT dataset output =
parameters=20
>>> of type decimal. These output parameters show up in the data =
explorer=20
>>> under the dataset but I am unable to drag and drop into the report =
mainly=20
>>> because they show up as, {} param1, {} param2 and not with the icon =
for=20
>>> output parameters.
>>>
>>> When I click preview results for this dataset, I get the following =
error:
>>> SQL statement does not return a ResultSet object.
>>>
>>> I also tried to return a ref cursor in my procedure so that BIRT =
gets a=20
>>> resultset on its end but it does not work.
>>>
>>> Thanks,
>>> Komalta
>>>=20
>
------=_NextPart_000_0132_01C6E64C.903A2330
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2627" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV>I found this on some BIRT =
FAQs:</DIV>
<DIV> </DIV>
<DIV>
<SPAN=20
style=3D"FONT-WEIGHT: bold; FONT-SIZE: 18pt; FONT-FAMILY: 'Times New =
Roman'"><FONT=20
face=3DArial>Q: How do I use a stored =
procedure?<?xml:namespace=20
prefix =3D o ns =3D "urn:schemas-microsoft-com:office:office"=20
/><o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 12pt; FONT-FAMILY: 'Times New Roman'"><FONT=20
size=3D2>" The JDBC ODA plugin included in BIRT currently supports SQL =
SELECT=20
queries and simple stored procedure queries. That is, those that 1) use =
scalar=20
input parameters only or no parameters, and 2) retrieve a single result =
set=20
directly, like those in SQL Server or Sybase (instead of via a cursor =
output=20
parameter like those in Oracle). <o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 12pt; FONT-FAMILY: 'Times New Roman'"><FONT=20
size=3D2>You can access a simple stored procedure call statement using a =
statement=20
in the JDBC query editor. Create a JDBC data set with a dummy query. Use =
the=20
Edit data set option to open the data set editor. replace the SQL SELECT =

statement with your stored procedure call. =
<o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 12pt; FONT-FAMILY: 'Times New Roman'"><FONT=20
size=3D2>We encourage the BIRT community to provide additional Open Data =
Access=20
(ODA) extensions to handle various types of data sources, including =
complex=20
stored procedures. "
<SPAN=20
style=3D"FONT-SIZE: 12pt; FONT-FAMILY: 'Times New Roman'"><SPAN=20
style=3D"FONT-SIZE: 12pt; FONT-FAMILY: 'Times New Roman'"><FONT=20
size=3D2><o:p></o:p> 
Does this=20
mean that BIRT stored procedure dataset does not support oracle cursor =
output=20
parameters? In that case what should I use in order to get =
output in=20
BIRT from my oracle stored procedure?
Has anyone=20
worked on this or has any ideas??
<SPAN=20
style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">Thanks!!</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Komalta" <<A=20
href=3D"mailto:komalta.rajani@accenture.com">komalta.rajani@accenture.com</A>> wrote=20
in message <A href=3D"news:efrmrj$e4f$1@utils.eclipse.org">news:efrmrj$e4f$1@utils.eclipse.org</A>...</DIV>>I am using =
Oracle. >=20
 > "Scott Rosenbaum" <<A=20
href=3D"mailto:scottr@innoventsolutions.com">scottr@innoventsolutions.com</A>> wrote=20
in message > <A =
href=3D"news:efrlh5$4sq$1@utils.eclipse.org"><FONT=20
face=3DArial =
size=3D2>news:efrlh5$4sq$1@utils.eclipse.org</A>... >> What database are you =
using? >> >>=20
Scott Rosenbaum >> BIRT PMC >> >> Komalta=20
wrote: >>> Hi, >>> >>> I am having =
problems=20
using output parameters from a stored procedure that >>> is =
called=20
by a stored procedure dataset in BIRT. >>> >>> =
In my=20
stored procedure, I have a few output parameters of type NUMBER and=20
 >>> BIRT automatically creates corresponding BIRT dataset =
output=20
parameters >>> of type decimal. These output parameters =
show up in=20
the data explorer >>> under the dataset but I am unable to =
drag and=20
drop into the report mainly >>> because they show up =
as,  {}=20
param1, {} param2 and not with the icon for >>> output=20
parameters. >>> >>> When I click preview results =
for=20
this dataset, I get the following error: >>> SQL statement =
does not=20
return a ResultSet object. >>> >>> I also tried =
to=20
return a ref cursor in my procedure so that BIRT gets a >>> =

resultset on its end but it does not =
work. >>> >>>=20
Thanks, >>> Komalta >>> =
 ></BODY></HTML>

------=_NextPart_000_0132_01C6E64C.903A2330--

Report message to a moderator

Re: Getting output from a Stored Procedure Dataset in BIRT [message #194120 is a reply to message #193731]

Wed, 04 October 2006 14:40

Eclipse User

Originally posted by: raison.fermat.eu

There is some thread on the newsgroup

http://dev.eclipse.org/newslists/news.eclipse.birt/msg13055. html

I don't think that 2.1.1 has corrected this for Oracle.

No general SYS_CURSOR, only cursor based on types (and also piped ...)

Komalta wrote:
> I found this on some BIRT FAQs:
>
>
> Q: How do I use a stored procedure?
>
> " The JDBC ODA plugin included in BIRT currently supports SQL SELECT
> queries and simple stored procedure queries. That is, those that 1) use
> scalar input parameters only or no parameters, and 2) retrieve a single
> result set directly, like those in SQL Server or Sybase (instead of via
> a cursor output parameter like those in Oracle).
>
> You can access a simple stored procedure call statement using a
> statement in the JDBC query editor. Create a JDBC data set with a dummy
> query. Use the Edit data set option to open the data set editor. replace
> the SQL SELECT statement with your stored procedure call.
>
> We encourage the BIRT community to provide additional Open Data Access
> (ODA) extensions to handle various types of data sources, including
> complex stored procedures. "
>
>
>
> Does this mean that BIRT stored procedure dataset does not support
> oracle cursor output parameters? In that case what should I use in order
> to get output in BIRT from my oracle stored procedure?
>
> Has anyone worked on this or has any ideas??
>
> Thanks!!
>
>
>
> "Komalta" <komalta.rajani@accenture.com
> <mailto:komalta.rajani@accenture.com>> wrote in message
> news:efrmrj$e4f$1@utils.eclipse.org...
> >I am using Oracle.
> >
> > "Scott Rosenbaum" <scottr@innoventsolutions.com
> <mailto:scottr@innoventsolutions.com>> wrote in message
> > news:efrlh5$4sq$1@utils.eclipse.org...
> >> What database are you using?
> >>
> >> Scott Rosenbaum
> >> BIRT PMC
> >>
> >> Komalta wrote:
> >>> Hi,
> >>>
> >>> I am having problems using output parameters from a stored
> procedure that
> >>> is called by a stored procedure dataset in BIRT.
> >>>
> >>> In my stored procedure, I have a few output parameters of type
> NUMBER and
> >>> BIRT automatically creates corresponding BIRT dataset output
> parameters
> >>> of type decimal. These output parameters show up in the data explorer
> >>> under the dataset but I am unable to drag and drop into the report
> mainly
> >>> because they show up as, {} param1, {} param2 and not with the
> icon for
> >>> output parameters.
> >>>
> >>> When I click preview results for this dataset, I get the following
> error:
> >>> SQL statement does not return a ResultSet object.
> >>>
> >>> I also tried to return a ref cursor in my procedure so that BIRT
> gets a
> >>> resultset on its end but it does not work.
> >>>
> >>> Thanks,
> >>> Komalta
> >>>
> >

Report message to a moderator

Previous Topic:	Birt 2.2 Project Plan
Next Topic:	Text Wrapping in BIRT

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 18:19:38 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter