Powerhouse and Result Sets/Cursors

[Deskin, Bob]

Here's an example from our QTP test package:
 
Here's an example:
QTP:
sql declare ccminfo cursor for &
   call RETURN_RESULT_SET &
      result set  tbranch char(2), &
                  tbranch_name char(20), &
                  tbranch_manager varchar(20)
 
ACCESS ccminfo
SUBFILE stposy02 KEEP INCLUDE ccminfo
---------
Stored procedures:
CREATE PROCEDURE RETURN_RESULT_SET
 (oCursor IN OUT RS_PKG.ResultCursor) AS
BEGIN
 open oCursor for SELECT * from BRANCHES;
END;
/
 
CREATE PACKAGE RS_PKG
IS
 TYPE ResultCursor IS REF CURSOR;
END RS_PKG;
/

Bob


________________________________

	From: powerh-l-bounces+bob.deskin=cognos.com at lists.sowder.com
[mailto:powerh-l-bounces+bob.deskin=cognos.com at lists.sowder.com] On
Behalf Of Adrian.Hudson at eu.averydennison.com
	Sent: December 18, 2007 10:58 am
	To: powerh-l at lists.sowder.com
	Subject: Powerhouse and Result Sets/Cursors
	
	

	Hi, 
	
	This is my first post, so I hope I manage to follow all the
necessary etiquette! 
	
	Has anyone managed to use a cursor with a result set in
Powerhouse and Oracle (This was introduced in 8.4D)? 
	
	I'm struggling with how the Cursor/Result Set is passed back
(and indeed defined) in the Powerhouse code. 
	
	We are running Oracle 10g with Powerhouse 8.43D1 on AIX 
	
	The text below illustrates the problem. 
	
	Regards, 
	
	
	Adrian 
	
	Create package and procedure 
	---------------------------------------------- 
	
	create package powpkg 
	as 
	type CursorType is REF CURSOR; 
	end powpkg; 
	
	create or replace procedure return_wishlist (ocursor in out
powpkg.CursorType) 
	as 
	begin 
	open ocursor for select description from hudsona.boys_wishlist; 
	end; 
	/ 
	
	To demo it works in SQLPLUS 
	-------------------------------------------- 
	
	SQL hudsona at DVXD> variable c refcursor 
	SQL hudsona at DVXD> exec return_wishlist (:c) 
	
	PL/SQL procedure successfully completed. 
	
	SQL hudsona at DVXD> print c 
	
	DESCRIPTION 
	-------------------------------------------------- 
	Football Game 
	Keyboard 
	Shrek DVD 
	
	Powerhouse code 
	-------------------------- 
	
	sql in css2base & 
	declare c_cursor cursor for & 
	call hudsona.return_wishlist & 
	result set description varchar(50) 
	
	acc c_cursor 
	go 
	
	
------------------------------------------------------------------------
------- 
	DMS-E-GENERAL, A general exception has occurred during operation
'execute'. 
	   File:  C_CURSOR 
	
------------------------------------------------------------------------
------- 
	ORA-06550: line 1, column 7: 
	
	PLS-00306: wrong number or types of arguments in call to
'RETURN_WISHLIST' 
	
	ORA-06550: line 1, column 7: 
	
	PL/SQL: Statement ignored 
	
	
	
	
	
	-
-----------------------------------------------------------------
	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.
	

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20071219/b195bd1c/attachment-0001.htm