Powerhouse and Result Sets/Cursors

[Adrian.Hudson at eu.averydennison.com]

Hi Peter,

Thanks a lot for the response.

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 :-(

One thing I do remember is I also tried calling a function with no 
success.

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.

Have a good Christmas and thanks again


Adrian




Peter Bateman <peterbateman808 at hotmail.com> 
18/12/2007 19:19

To
<adrian.hudson at eu.averydennison.com>, <powerh-l at lists.sowder.com>
cc

Subject
RE: Powerhouse and Result Sets/Cursors






        Hi Adrian:
 
             The error indicates you have the wrong number of
             parameters.
 
             I don't have PowerHouse up , right now, But if I did
 
      1)       I would try an empty parameter list

             e.g.
 
          sql in css2base & 
          declare c_cursor cursor for & 
          call hudsona.return_wishlist ( ) & 
          result set description varchar(50) 

           acc c_cursor 
 
    2)      If that didn't work I would add a dummy
             parameter to both PowerHouse code
             and the SQL code
 
        Please let us know how you resolved the issue.
 
        Merry Christmas,
 
        Peter Bateman
To: powerh-l at lists.sowder.com
Subject: Powerhouse and Result Sets/Cursors
From: Adrian.Hudson at eu.averydennison.com
Date: Tue, 18 Dec 2007 16:57:57 +0100


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.

HO HO HO, if you've been naughty this year, email Santa! Visit asksanta.ca 
to learn more!


- -----------------------------------------------------------------
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/38decaa5/attachment.html