<br><font size=2 face="sans-serif">Hi,</font>
<br>
<br><font size=2 face="sans-serif">This is my first post, so I hope I manage
to follow all the necessary etiquette!</font>
<br>
<br><font size=2 face="sans-serif">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 size=2 face="sans-serif">I'm struggling with how the Cursor/Result
Set is passed back (and indeed defined) in the Powerhouse code.</font>
<br>
<br><font size=2 face="sans-serif">We are running Oracle 10g with Powerhouse
8.43D1 on AIX</font>
<br>
<br><font size=2 face="sans-serif">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>