<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.3243" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=734433017-19122007><FONT face=Arial
color=#0000ff size=2>Here's an example from our QTP test
package:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=734433017-19122007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=734433017-19122007>
<DIV dir=ltr align=left><SPAN class=870172317-19122007><FONT face=Arial
color=#0000ff size=2>Here's an example:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=870172317-19122007><FONT face=Arial
color=#0000ff size=2>QTP:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=870172317-19122007><FONT face=Arial
color=#0000ff size=2>sql declare ccminfo cursor for &<BR> call
RETURN_RESULT_SET &<BR> result set
tbranch char(2),
&<BR>
tbranch_name char(20),
&<BR>
tbranch_manager varchar(20)</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV dir=ltr align=left><SPAN class=870172317-19122007><FONT face=Arial
color=#0000ff size=2>ACCESS ccminfo<BR>SUBFILE stposy02 KEEP INCLUDE
ccminfo<BR>---------</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=870172317-19122007><FONT face=Arial
color=#0000ff size=2>Stored procedures:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=870172317-19122007><FONT face=Arial
color=#0000ff size=2>CREATE PROCEDURE RETURN_RESULT_SET<BR> (oCursor IN OUT
RS_PKG.ResultCursor) AS<BR>BEGIN<BR> open oCursor for SELECT * from
BRANCHES;<BR>END;<BR>/</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=870172317-19122007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=870172317-19122007><FONT face=Arial
color=#0000ff size=2>CREATE PACKAGE RS_PKG<BR>IS<BR> TYPE ResultCursor IS
REF CURSOR;<BR>END RS_PKG;<BR>/<BR></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=870172317-19122007><SPAN
class=734433017-19122007><FONT face=Arial color=#0000ff
size=2>Bob</FONT></SPAN></DIV></SPAN></SPAN></DIV><BR>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
powerh-l-bounces+bob.deskin=cognos.com@lists.sowder.com
[mailto:powerh-l-bounces+bob.deskin=cognos.com@lists.sowder.com] <B>On Behalf
Of </B>Adrian.Hudson@eu.averydennison.com<BR><B>Sent:</B> December 18, 2007
10:58 am<BR><B>To:</B> powerh-l@lists.sowder.com<BR><B>Subject:</B> Powerhouse
and Result Sets/Cursors<BR></FONT><BR></DIV>
<DIV></DIV><BR><FONT face=sans-serif size=2>Hi,</FONT> <BR><BR><FONT
face=sans-serif size=2>This is my first post, so I hope I manage to follow all
the necessary etiquette!</FONT> <BR><BR><FONT face=sans-serif size=2>Has
anyone managed to use a cursor with a result set in Powerhouse and Oracle
(This was introduced in 8.4D)?</FONT> <BR><BR><FONT face=sans-serif size=2>I'm
struggling with how the Cursor/Result Set is passed back (and indeed defined)
in the Powerhouse code.</FONT> <BR><BR><FONT face=sans-serif size=2>We are
running Oracle 10g with Powerhouse 8.43D1 on AIX</FONT> <BR><BR><FONT
face=sans-serif size=2>The text below illustrates the problem.</FONT>
<BR><BR><FONT size=2><TT>Regards,</TT></FONT> <BR><BR><BR><FONT
size=2><TT>Adrian</TT></FONT> <BR><BR><FONT size=2><TT>Create package and
procedure </TT></FONT><BR><FONT
size=2><TT>----------------------------------------------</TT></FONT>
<BR><BR><FONT size=2><TT>create package powpkg</TT></FONT> <BR><FONT
size=2><TT>as</TT></FONT> <BR><FONT size=2><TT>type CursorType is REF
CURSOR;</TT></FONT> <BR><FONT size=2><TT>end powpkg;</TT></FONT> <BR><BR><FONT
size=2><TT>create or replace procedure return_wishlist (ocursor in out
powpkg.CursorType) </TT></FONT><BR><FONT size=2><TT>as</TT></FONT> <BR><FONT
size=2><TT>begin</TT></FONT> <BR><FONT size=2><TT>open ocursor for select
description from hudsona.boys_wishlist;</TT></FONT> <BR><FONT
size=2><TT>end;</TT></FONT> <BR><FONT size=2><TT>/</TT></FONT> <BR><BR><FONT
size=2><TT>To demo it works in SQLPLUS</TT></FONT> <BR><FONT
size=2><TT>--------------------------------------------</TT></FONT>
<BR><BR><FONT size=2><TT>SQL hudsona@DVXD> variable c refcursor</TT></FONT>
<BR><FONT size=2><TT>SQL hudsona@DVXD> exec return_wishlist
(:c)</TT></FONT> <BR><BR><FONT size=2><TT>PL/SQL procedure successfully
completed.</TT></FONT> <BR><BR><FONT size=2><TT>SQL hudsona@DVXD> print
c</TT></FONT> <BR><BR><FONT size=2><TT>DESCRIPTION</TT></FONT> <BR><FONT
size=2><TT>--------------------------------------------------</TT></FONT>
<BR><FONT size=2><TT>Football Game</TT></FONT> <BR><FONT
size=2><TT>Keyboard</TT></FONT> <BR><FONT size=2><TT>Shrek DVD</TT></FONT>
<BR><BR><FONT size=2><TT>Powerhouse code</TT></FONT> <BR><FONT
size=2><TT>-------------------------- </TT></FONT><BR><BR><FONT size=2><TT>sql
in css2base &</TT></FONT> <BR><FONT size=2><TT>declare c_cursor cursor for
&</TT></FONT> <BR><FONT size=2><TT>call hudsona.return_wishlist
&</TT></FONT> <BR><FONT size=2><TT>result set description
varchar(50)</TT></FONT> <BR><BR><FONT size=2><TT>acc c_cursor</TT></FONT>
<BR><FONT size=2><TT>go</TT></FONT> <BR><BR><FONT
size=2><TT>-------------------------------------------------------------------------------</TT></FONT>
<BR><FONT size=2><TT>DMS-E-GENERAL, A general exception has occurred during
operation 'execute'.</TT></FONT> <BR><FONT size=2><TT> File:
C_CURSOR</TT></FONT> <BR><FONT
size=2><TT>-------------------------------------------------------------------------------</TT></FONT>
<BR><FONT size=2><TT>ORA-06550: line 1, column 7:</TT></FONT> <BR><BR><FONT
size=2><TT>PLS-00306: wrong number or types of arguments in call to
'RETURN_WISHLIST'</TT></FONT> <BR><BR><FONT size=2><TT>ORA-06550: line 1,
column 7:</TT></FONT> <BR><BR><FONT size=2><TT>PL/SQL: Statement
ignored</TT></FONT> <BR><BR><BR><BR><BR><BR>-
-----------------------------------------------------------------<BR>The
information transmitted is intended only for the person or entity to which it
is addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any action
in reliance upon, this information by persons or entities other than the
intended recipient is prohibited. If you received this in error, please
contact the sender and delete the material from any
computer.<BR></BLOCKQUOTE></BODY></HTML>