Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Oracle Stored Procedure Result Set

4 posts in DataWindow Last posting was on 2009-01-10 01:37:57.0Z
L. Davidson Posted on 2009-01-09 00:08:15.0Z
Sender: 1eda.496690c2.1804289383@sybase.com
From: L. Davidson
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Oracle Stored Procedure Result Set
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4966956f.1f59.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Jan 2009 16:08:15 -0800
X-Trace: forums-1-dub 1231459695 10.22.241.41 (8 Jan 2009 16:08:15 -0800)
X-Original-Trace: 8 Jan 2009 16:08:15 -0800, 10.22.241.41
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88522
Article PK: 417778

PB 11 using Oracle 10g v2.

I have a stored procedure that contains a select-into
statement that selects a field in a table and stores the
data into a variable.

Next there is an insert statement that inserts data into a
temp table based off user-selected criteria.

Next a ref cursor selects data from temp table joined with
other tables. This data will be used as the result set for a
datawindow.

The stored proc was successfully tested in PL/SQL. However,
when I go to build a datawindow using the stored procedure I
get an error. I discovered that the datawindow wants to use
the result set from the select-into statement (first select
statement in the procedure) which creates an error. I want
to use the data in the ref cursor (last select statement in
stored proc) as the result set. Is there a way to do this
using the datawindow wizard? My workaround is to comment-out
the select-into statement; compile the stored proc; create
the datawindow; then un-comment the select-into statement
and re-compile.

I hope this makes sense.


Bruce Armstrong [TeamSybase] Posted on 2009-01-09 17:37:39.0Z
From: "Bruce Armstrong [TeamSybase]" <NOCANSPAM_bruce.armstrong@teamsybase.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4966956f.1f59.1681692777@sybase.com>
Subject: Re: Oracle Stored Procedure Result Set
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49678b63$1@forums-1-dub>
Date: 9 Jan 2009 09:37:39 -0800
X-Trace: forums-1-dub 1231522659 10.22.241.152 (9 Jan 2009 09:37:39 -0800)
X-Original-Trace: 9 Jan 2009 09:37:39 -0800, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88548
Article PK: 417805

I didn't follow it.

The way you get a result set back to PB from Oracle is to make an IN OUT ref
cursor argument to the procedure. Whatever you open into that ref cursor is
what PB will see.

--
-----------------------------------

My Web 2.0 Stuff

Blog: http://bruce.pbdjmagazine.com/
Facebook: http://www.facebook.com/people/Bruce-Armstrong/1600223798
Fotki: http://public.fotki.com/brucearmstrong/
LinkedIn: http://www.linkedin.com/in/bruceaarmstrong
Twitter: http://twitter.com/bruce_armstrong
YouTube: http://www.youtube.com/user/brucearmstrong

<L. Davidson> wrote in message news:4966956f.1f59.1681692777@sybase.com...
> PB 11 using Oracle 10g v2.
>
> I have a stored procedure that contains a select-into
> statement that selects a field in a table and stores the
> data into a variable.
>
> Next there is an insert statement that inserts data into a
> temp table based off user-selected criteria.
>
> Next a ref cursor selects data from temp table joined with
> other tables. This data will be used as the result set for a
> datawindow.
>
> The stored proc was successfully tested in PL/SQL. However,
> when I go to build a datawindow using the stored procedure I
> get an error. I discovered that the datawindow wants to use
> the result set from the select-into statement (first select
> statement in the procedure) which creates an error. I want
> to use the data in the ref cursor (last select statement in
> stored proc) as the result set. Is there a way to do this
> using the datawindow wizard? My workaround is to comment-out
> the select-into statement; compile the stored proc; create
> the datawindow; then un-comment the select-into statement
> and re-compile.
>
> I hope this makes sense.


L. Davidson Posted on 2009-01-09 23:29:12.0Z
Sender: 5b34.4967ddbc.1804289383@sybase.com
From: L. Davidson
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Oracle Stored Procedure Result Set
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4967ddc7.5b36.1681692777@sybase.com>
References: <49678b63$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 9 Jan 2009 15:29:12 -0800
X-Trace: forums-1-dub 1231543752 10.22.241.41 (9 Jan 2009 15:29:12 -0800)
X-Original-Trace: 9 Jan 2009 15:29:12 -0800, 10.22.241.41
Lines: 56
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88555
Article PK: 417809

That was my assumption also. However, the error message when
I am trying to create the DataWindow is on a line where I am
doing a select-into a variable in the stored proc. When I
comment-out the select-into, I can create the DataWindow
using the result set in the ref cursor. After the DataWindow
is created I un-comment the select-into statement.

> I didn't follow it.
>
> The way you get a result set back to PB from Oracle is to
> make an IN OUT ref cursor argument to the procedure.
> Whatever you open into that ref cursor is what PB will
> see.
>
> --
> -----------------------------------
>
> My Web 2.0 Stuff
>
> Blog: http://bruce.pbdjmagazine.com/
> Facebook:
> http://www.facebook.com/people/Bruce-Armstrong/1600223798
> Fotki: http://public.fotki.com/brucearmstrong/
> LinkedIn: http://www.linkedin.com/in/bruceaarmstrong
> Twitter: http://twitter.com/bruce_armstrong
> YouTube: http://www.youtube.com/user/brucearmstrong <L.
> Davidson> wrote in message
> > news:4966956f.1f59.1681692777@sybase.com... PB 11 using
> Oracle 10g v2. >
> > I have a stored procedure that contains a select-into
> > statement that selects a field in a table and stores the
> > data into a variable.
> >
> > Next there is an insert statement that inserts data into
> > a temp table based off user-selected criteria.
> >
> > Next a ref cursor selects data from temp table joined
> > with other tables. This data will be used as the result
> > set for a datawindow.
> >
> > The stored proc was successfully tested in PL/SQL.
> > However, when I go to build a datawindow using the
> > stored procedure I get an error. I discovered that the
> > datawindow wants to use the result set from the
> > select-into statement (first select statement in the
> > procedure) which creates an error. I want to use the
> > data in the ref cursor (last select statement in stored
> > proc) as the result set. Is there a way to do this using
> > the datawindow wizard? My workaround is to comment-out
> > the select-into statement; compile the stored proc;
> create the datawindow; then un-comment the select-into
> > statement and re-compile.
> >
> > I hope this makes sense.
>
>


Bruce Armstrong [TeamSybase] Posted on 2009-01-10 01:37:57.0Z
From: "Bruce Armstrong [TeamSybase]" <NOCANSPAM_bruce.armstrong@teamsybase.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Oracle Stored Procedure Result Set
Organization: TeamSybase
Reply-To: NOCANSPAM_bruce.armstrong@teamsybase.com
Message-ID: <bsufm4hiq3img4hga85u07spvfhaokh0oq@4ax.com>
References: <49678b63$1@forums-1-dub> <4967ddc7.5b36.1681692777@sybase.com>
X-Newsreader: Forte Agent 5.00/32.1171
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 9 Jan 2009 17:37:57 -0800
X-Trace: forums-1-dub 1231551477 10.22.241.152 (9 Jan 2009 17:37:57 -0800)
X-Original-Trace: 9 Jan 2009 17:37:57 -0800, vip152.sybase.com
Lines: 79
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88556
Article PK: 417810

Well, remember that if you use an unconstrained ref cursor you're
forcing PB to execute the underlying procedure with dummy arguments in
order to try to figure out what the result set looks like. Its
probably that the arguments that PB is passing is resulting in a data
not found or similar error.

You might try using a constrained ref cursor and/or ensuring that the
procedure will return something (at least temporarily) when dummy data
is sent it.

On 9 Jan 2009 15:29:12 -0800, L. Davidson wrote:

>That was my assumption also. However, the error message when
>I am trying to create the DataWindow is on a line where I am
>doing a select-into a variable in the stored proc. When I
>comment-out the select-into, I can create the DataWindow
>using the result set in the ref cursor. After the DataWindow
>is created I un-comment the select-into statement.
>
>> I didn't follow it.
>>
>> The way you get a result set back to PB from Oracle is to
>> make an IN OUT ref cursor argument to the procedure.
>> Whatever you open into that ref cursor is what PB will
>> see.
>>
>> --
>> -----------------------------------
>>
>> My Web 2.0 Stuff
>>
>> Blog: http://bruce.pbdjmagazine.com/
>> Facebook:
>> http://www.facebook.com/people/Bruce-Armstrong/1600223798
>> Fotki: http://public.fotki.com/brucearmstrong/
>> LinkedIn: http://www.linkedin.com/in/bruceaarmstrong
>> Twitter: http://twitter.com/bruce_armstrong
>> YouTube: http://www.youtube.com/user/brucearmstrong <L.
>> Davidson> wrote in message
>> > news:4966956f.1f59.1681692777@sybase.com... PB 11 using
>> Oracle 10g v2. >
>> > I have a stored procedure that contains a select-into
>> > statement that selects a field in a table and stores the
>> > data into a variable.
>> >
>> > Next there is an insert statement that inserts data into
>> > a temp table based off user-selected criteria.
>> >
>> > Next a ref cursor selects data from temp table joined
>> > with other tables. This data will be used as the result
>> > set for a datawindow.
>> >
>> > The stored proc was successfully tested in PL/SQL.
>> > However, when I go to build a datawindow using the
>> > stored procedure I get an error. I discovered that the
>> > datawindow wants to use the result set from the
>> > select-into statement (first select statement in the
>> > procedure) which creates an error. I want to use the
>> > data in the ref cursor (last select statement in stored
>> > proc) as the result set. Is there a way to do this using
>> > the datawindow wizard? My workaround is to comment-out
>> > the select-into statement; compile the stored proc;
>> create the datawindow; then un-comment the select-into
>> > statement and re-compile.
>> >
>> > I hope this makes sense.
>>
>>

-----------------------------------

My Web 2.0 Stuff

Blog: http://bruce.pbdjmagazine.com/
Facebook: http://www.facebook.com/people/Bruce-Armstrong/1600223798
Fotki: http://public.fotki.com/brucearmstrong/
LinkedIn: http://www.linkedin.com/in/bruceaarmstrong
Twitter: http://twitter.com/bruce_armstrong
YouTube: http://www.youtube.com/user/brucearmstrong