<html>
<head>
<style>
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
FONT-SIZE: 10pt;
FONT-FAMILY:Tahoma
}
</style>
</head>
<body class='hmmessage'>
Hi Adrian:<BR>
<BR>
The error indicates you have the wrong number of<BR>
parameters.<BR>
<BR>
I don't have PowerHouse up , right now, But if I did<BR>
<BR>
1) I would try an empty parameter list<BR><BR> e.g.<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>
<BR>
2) If that didn't work I would add a dummy<BR>
parameter to both PowerHouse code<BR>
and the SQL code<BR>
<BR>
Please let us know how you resolved the issue.<BR>
<BR>
Merry Christmas,<BR>
<BR>
Peter Bateman<BR>
<BLOCKQUOTE>
<HR>
To: powerh-l@lists.sowder.com<BR>Subject: Powerhouse and Result Sets/Cursors<BR>From: Adrian.Hudson@eu.averydennison.com<BR>Date: Tue, 18 Dec 2007 16:57:57 +0100<BR><BR><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><br /><hr />HO HO HO, if you've been naughty this year, email Santa! <a href='http://asksanta.ca/?icid=SANTAENCA005' target='_new'>Visit asksanta.ca to learn more!</a></body>
</html>