<br><font size=2 face="sans-serif">Hi Peter,</font>
<br>
<br><font size=2 face="sans-serif">Thanks a lot for the response.</font>
<br>
<br><font size=2 face="sans-serif">I've already tried the things you mention.
In fact I've tried so many different ways, I think I've tied myself in
knots and can now hardly remember what I've tried :-(</font>
<br>
<br><font size=2 face="sans-serif">One thing I do remember is I also tried
calling a function with no success.</font>
<br>
<br><font size=2 face="sans-serif">An alternative for me is to populate
a table within the procedure and get around it that way, but it would still
be nice to get the result set/ref cursor functionality working correctly.</font>
<br>
<br><font size=2 face="sans-serif">Have a good Christmas and thanks again</font>
<br>
<br>
<br><font size=2 face="sans-serif">Adrian</font>
<br>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td width=40%><font size=1 face="sans-serif"><b>Peter Bateman <peterbateman808@hotmail.com></b>
</font>
<p><font size=1 face="sans-serif">18/12/2007 19:19</font>
<td width=59%>
<table width=100%>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">To</font></div>
<td><font size=1 face="sans-serif"><adrian.hudson@eu.averydennison.com>,
<powerh-l@lists.sowder.com></font>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">cc</font></div>
<td>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">Subject</font></div>
<td><font size=1 face="sans-serif">RE: Powerhouse and Result Sets/Cursors</font></table>
<br>
<table>
<tr valign=top>
<td>
<td></table>
<br></table>
<br>
<br>
<br><font size=2 face="Tahoma"> 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>
sql in css2base & <br>
declare c_cursor cursor for & <br>
call hudsona.return_wishlist ( ) &
<br>
result set description varchar(50) <br>
<br>
acc c_cursor <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</font>
<br>
<hr><font size=2 face="Tahoma">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>
</font><font size=2 face="sans-serif"><br>
Hi,</font><font size=2 face="Tahoma"> <br>
</font><font size=2 face="sans-serif"><br>
This is my first post, so I hope I manage to follow all the necessary etiquette!</font><font size=2 face="Tahoma">
<br>
</font><font size=2 face="sans-serif"><br>
Has anyone managed to use a cursor with a result set in Powerhouse and
Oracle (This was introduced in 8.4D)?</font><font size=2 face="Tahoma">
<br>
</font><font size=2 face="sans-serif"><br>
I'm struggling with how the Cursor/Result Set is passed back (and indeed
defined) in the Powerhouse code.</font><font size=2 face="Tahoma"> <br>
</font><font size=2 face="sans-serif"><br>
We are running Oracle 10g with Powerhouse 8.43D1 on AIX</font><font size=2 face="Tahoma">
<br>
</font><font size=2 face="sans-serif"><br>
The text below illustrates the problem.</font><font size=2 face="Tahoma">
<br>
<br>
Regards, <br>
<br>
<br>
Adrian <br>
<br>
Create package and procedure <br>
---------------------------------------------- <br>
<br>
create package powpkg <br>
as <br>
type CursorType is REF CURSOR; <br>
end powpkg; <br>
<br>
create or replace procedure return_wishlist (ocursor in out powpkg.CursorType)
<br>
as <br>
begin <br>
open ocursor for select description from hudsona.boys_wishlist; <br>
end; <br>
/ <br>
<br>
To demo it works in SQLPLUS <br>
-------------------------------------------- <br>
<br>
SQL hudsona@DVXD> variable c refcursor <br>
SQL hudsona@DVXD> exec return_wishlist (:c) <br>
<br>
PL/SQL procedure successfully completed. <br>
<br>
SQL hudsona@DVXD> print c <br>
<br>
DESCRIPTION <br>
-------------------------------------------------- <br>
Football Game <br>
Keyboard <br>
Shrek DVD <br>
<br>
Powerhouse code <br>
-------------------------- <br>
<br>
sql in css2base & <br>
declare c_cursor cursor for & <br>
call hudsona.return_wishlist & <br>
result set description varchar(50) <br>
<br>
acc c_cursor <br>
go <br>
<br>
-------------------------------------------------------------------------------
<br>
DMS-E-GENERAL, A general exception has occurred during operation 'execute'.
<br>
File: C_CURSOR <br>
-------------------------------------------------------------------------------
<br>
ORA-06550: line 1, column 7: <br>
<br>
PLS-00306: wrong number or types of arguments in call to 'RETURN_WISHLIST'
<br>
<br>
ORA-06550: line 1, column 7: <br>
<br>
PL/SQL: Statement ignored <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.</font>
<br><font size=2 face="Tahoma"><br>
</font>
<hr><font size=2 face="Tahoma">HO HO HO, if you've been naughty this year,
email Santa! </font><a href="http://asksanta.ca/?icid=SANTAENCA005" target=_new><font size=2 color=blue face="Tahoma"><u>Visit
asksanta.ca to learn more!</u></font></a>
<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>