<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>
<FONT face="Verdana, Geneva, Arial, Sans-serif"> Thanks for letting us know that you are still having a problem. </FONT><BR>
<BR>
What do you have set in the dictionary with regard to <BR>
NULL VALUES ALLOWED/NULL VALUES NOT ALLOWED for your<BR>
database?<BR>
<BR>
I think most people want NULL VALUES ALLOWED .<BR>
However, the PowerHouse default is NULL VALUES NOT ALLOWED.<BR>
<BR>
I am thinking that Oracle may want to send back a<BR>
a null/not null flag to PowerHouse and there is not place to put it.<BR>
Hence, the Oracle error.<BR>
<FONT face="Verdana, Geneva, Arial, Sans-serif"> </FONT><BR>
<FONT face="Verdana, Geneva, Arial, Sans-serif"> Have you tried compiling you Powerhouse code with the option</FONT><BR>
<FONT face="Verdana, Geneva, Arial, Sans-serif"> DBAUDIT=FULL. You should be able to see the SQL code generated</FONT><BR>
<FONT face="Verdana, Geneva, Arial, Sans-serif"> by PowerHouse for your call to the stored procedure. </FONT><BR>
<FONT face="Verdana, Geneva, Arial, Sans-serif"></FONT> <BR>
<FONT face="Verdana, Geneva, Arial, Sans-serif"> <EM>description</EM> is a PowerHouse keyword. To distinguish between</FONT><BR>
<FONT face="Verdana, Geneva, Arial, Sans-serif"> keyword usage and identifier usage one normally places</FONT><BR>
<FONT face="Verdana, Geneva, Arial, Sans-serif"> a'%' sign in front of the word. i.e. %description. PowerHouse</FONT><BR>
<FONT face="Verdana, Geneva, Arial, Sans-serif"> is usually pretty good at figuring out how the word is being</FONT><BR>
<FONT face="Verdana, Geneva, Arial, Sans-serif"> use but on the slight chance that it might be a factor here</FONT><BR>
<FONT face="Verdana, Geneva, Arial, Sans-serif"> I brought it up.</FONT><BR>
<FONT face="Verdana, Geneva, Arial, Sans-serif"></FONT> <BR>
<FONT face="Verdana, Geneva, Arial, Sans-serif"> sql in css2base &<BR> declare c_cursor cursor for &<BR> call hudsona.return_wishlist &<BR> result set %description varchar(50)</FONT><BR><BR> acc c_cursor<BR> go<BR>
<FONT face="Courier New"> </FONT><BR>
<BR><BR><BR>
<BLOCKQUOTE>
<HR>
To: Doug.Moffatt@cognos.com<BR>Subject: RE: Powerhouse and Result Sets/Cursors<BR>From: Adrian.Hudson@eu.averydennison.com<BR>Date: Thu, 3 Jan 2008 13:03:45 +0100<BR>CC: powerh-l-bounces+adrian.hudson=eu.averydennison.com@lists.sowder.com; powerh-l@lists.sowder.com<BR><BR><BR><FONT face=sans-serif size=2>Hi All,,</FONT> <BR><BR><FONT face=sans-serif size=2>Just for the record, I did not manage to get this working.</FONT> <BR><BR><FONT face=sans-serif size=2>I tried both Bob't example and Doug's suggestion about the environment variable and still got the same error message.</FONT> <BR><BR><FONT face=sans-serif size=2>Happy New Year to All,</FONT> <BR><BR><BR><BR><FONT face=sans-serif size=2>Adrian</FONT> <BR><BR><BR><BR><BR><BR>
<TABLE width="100%">
<TBODY>
<TR vAlign=top>
<TD width="40%"><FONT face=sans-serif size=1><B>"Moffatt, Doug" <Doug.Moffatt@cognos.com></B> </FONT><BR><FONT face=sans-serif size=1>Sent by: powerh-l-bounces+adrian.hudson=eu.averydennison.com@lists.sowder.com</FONT>
<FONT face=sans-serif size=1>19/12/2007 23:05</FONT> <BR>
<TD width="59%">
<TABLE width="100%">
<TBODY>
<TR vAlign=top>
<TD>
<DIV align=right><FONT face=sans-serif size=1>To</FONT></DIV>
<TD><FONT face=sans-serif size=1><powerh-l@lists.sowder.com></FONT>
<TR vAlign=top>
<TD>
<DIV align=right><FONT face=sans-serif size=1>cc</FONT></DIV>
<TD>
<TR vAlign=top>
<TD>
<DIV align=right><FONT face=sans-serif size=1>Subject</FONT></DIV>
<TD><FONT face=sans-serif size=1>RE: Powerhouse and Result Sets/Cursors</FONT></TD></TR></TBODY></TABLE><BR>
<TABLE>
<TBODY>
<TR vAlign=top>
<TD>
<TD></TD></TR></TBODY></TABLE><BR></TD></TR></TBODY></TABLE><BR><BR><BR><FONT size=2><TT>Hi Adrian,<BR><BR>The Powerhouse source is correct. On your server machine, try setting<BR>the environment variable, STRICT_SQL92=yes.<BR><BR>Regards...Doug<BR><BR>-----Original Message-----<BR>From: powerh-l-bounces+doug.moffatt=cognos.com@lists.sowder.com<BR>[mailto:powerh-l-bounces+doug.moffatt=cognos.com@lists.sowder.com] On<BR>Behalf Of powerh-l-request@lists.sowder.com<BR>Sent: Tuesday, December 18, 2007 1:06 PM<BR>To: powerh-l@lists.sowder.com<BR>Subject: powerh-l Digest, Vol 31, Issue 12<BR><BR>Send powerh-l mailing list submissions to<BR> powerh-l@lists.sowder.com<BR><BR>To subscribe or unsubscribe via the World Wide Web, visit<BR> http://lists.sowder.com/mailman/listinfo/powerh-l<BR>or, via email, send a message with subject or body 'help' to<BR> powerh-l-request@lists.sowder.com<BR><BR>You can reach the person managing the list at<BR> powerh-l-owner@lists.sowder.com<BR><BR>When replying, please edit your Subject line so it is more specific than<BR>"Re: Contents of powerh-l digest..."<BR><BR><BR>Today's Topics:<BR><BR> 1. Powerhouse and Result Sets/Cursors<BR> (Adrian.Hudson@eu.averydennison.com)<BR><BR><BR>----------------------------------------------------------------------<BR><BR>Message: 1<BR>Date: Tue, 18 Dec 2007 16:57:57 +0100<BR>From: Adrian.Hudson@eu.averydennison.com<BR>Subject: Powerhouse and Result Sets/Cursors<BR>To: powerh-l@lists.sowder.com<BR>Message-ID:<BR> <BR><OFB361EA54.9B7A67F3-ONC12573B5.0055DD10-C12573B5.00579241@averydennison<BR>.com><BR> <BR>Content-Type: text/plain; charset="us-ascii"<BR><BR>Hi,<BR><BR>This is my first post, so I hope I manage to follow all the necessary<BR>etiquette!<BR><BR>Has anyone managed to use a cursor with a result set in Powerhouse and<BR>Oracle (This was introduced in 8.4D)?<BR><BR>I'm struggling with how the Cursor/Result Set is passed back (and indeed<BR>defined) in the Powerhouse code.<BR><BR>We are running Oracle 10g with Powerhouse 8.43D1 on AIX<BR><BR>The text below illustrates the problem.<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<BR>powpkg.CursorType)<BR>as<BR>begin<BR>open ocursor for select description from hudsona.boys_wishlist; end; /<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>-------<BR>DMS-E-GENERAL, A general exception has occurred during operation<BR>'execute'.<BR> File: C_CURSOR<BR>------------------------------------------------------------------------<BR>-------<BR>ORA-06550: line 1, column 7:<BR><BR>PLS-00306: wrong number or types of arguments in call to<BR>'RETURN_WISHLIST'<BR><BR>ORA-06550: line 1, column 7:<BR><BR>PL/SQL: Statement ignored<BR><BR><BR><BR><BR><BR><BR>- -----------------------------------------------------------------<BR>The information transmitted is intended only for the person or entity to<BR>which it is addressed and may contain confidential and/or privileged<BR>material. Any review, retransmission, dissemination or other use of, or<BR>taking of any action in reliance upon, this information by persons or<BR>entities other than the intended recipient is prohibited. If you<BR>received this in error, please contact the sender and delete the<BR>material from any computer.<BR>-------------- next part --------------<BR>An HTML attachment was scrubbed...<BR>URL:<BR>http://lists.sowder.com/pipermail/powerh-l/attachments/20071218/93863039<BR>/attachment-0001.html<BR><BR>------------------------------<BR><BR>--<BR>= = = = = = = = = = = = = = = = = = = = = = = = = = = = Mailing list:<BR>powerh-l@lists.sowder.com<BR>Subscribe: &quot;subscribe&quot; in message body to<BR>powerh-l-request@lists.sowder.com<BR>Unsubscribe: &quot;unsubscribe &lt;password&gt;&quot; in message body to<BR>powerh-l-request@lists.sowder.com<BR>http://lists.sowder.com/mailman/listinfo/powerh-l<BR>This list is closed, thus to post to the list you must be a subscriber.<BR>Add 'site:lists.sowder.com powerh-l' to your search terms to search the<BR>list archive at Google.<BR><BR>End of powerh-l Digest, Vol 31, Issue 12<BR>****************************************<BR><BR>-- <BR>= = = = = = = = = = = = = = = = = = = = = = = = = = = =<BR>Mailing list: powerh-l@lists.sowder.com<BR>Subscribe: &quot;subscribe&quot; in message body to powerh-l-request@lists.sowder.com<BR>Unsubscribe: &quot;unsubscribe &lt;password&gt;&quot; in message body to powerh-l-request@lists.sowder.com<BR>http://lists.sowder.com/mailman/listinfo/powerh-l<BR>This list is closed, thus to post to the list you must be a subscriber.<BR>Add 'site:lists.sowder.com powerh-l' to your search terms to search the list archive at Google.<BR></TT></FONT><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 nice this year, email Santa! <a href='http://asksanta.ca/?icid=SANTAENCA005' target='_new'>Visit asksanta.ca to learn more!</a></body>
</html>