Powerhouse and Result Sets/Cursors

Thu Jan 3 15:14:10 CST 2008

        Hi Adrian:

            Thanks for letting us know that you are still having a problem. 

            What do you have set in the dictionary with regard to 
            NULL VALUES ALLOWED/NULL VALUES NOT ALLOWED for your
            database?

            I think most people want NULL VALUES ALLOWED .
            However, the PowerHouse default is NULL VALUES NOT ALLOWED.

             I am thinking that Oracle may want to send back a
             a null/not null flag to PowerHouse and there is not place to put it.
             Hence, the Oracle error.

             Have you tried compiling you Powerhouse code with the option
             DBAUDIT=FULL. You should be able to see the SQL code generated
             by PowerHouse for your call to the stored procedure. 

             description is a PowerHouse keyword. To distinguish between
             keyword usage and identifier usage one normally places
             a'%' sign in front of the word. i.e. %description. PowerHouse
             is usually pretty good at figuring out how the word is being
             use but on the slight chance that it might be a factor here
             I brought it up.

             sql in css2base &             declare c_cursor cursor for &             call hudsona.return_wishlist &             result set %description varchar(50)            acc c_cursor            go

To: Doug.Moffatt at cognos.comSubject: RE: Powerhouse and Result Sets/CursorsFrom: Adrian.Hudson at eu.averydennison.comDate: Thu, 3 Jan 2008 13:03:45 +0100CC: powerh-l-bounces+adrian.hudson=eu.averydennison.com at lists.sowder.com; powerh-l at lists.sowder.comHi All,, Just for the record, I did not manage to get this working. I tried both Bob't example and Doug's suggestion about the environment variable and still got the same error message. Happy New Year to All, Adrian 

"Moffatt, Doug" <Doug.Moffatt at cognos.com> Sent by: powerh-l-bounces+adrian.hudson=eu.averydennison.com at lists.sowder.com 
19/12/2007 23:05 

To
<powerh-l at lists.sowder.com> 

cc

Subject
RE: Powerhouse and Result Sets/Cursors

Hi Adrian,The Powerhouse source is correct.  On your server machine, try settingthe environment variable,  STRICT_SQL92=yes.Regards...Doug-----Original Message-----From: powerh-l-bounces+doug.moffatt=cognos.com at lists.sowder.com[mailto:powerh-l-bounces+doug.moffatt=cognos.com at lists.sowder.com] OnBehalf Of powerh-l-request at lists.sowder.comSent: Tuesday, December 18, 2007 1:06 PMTo: powerh-l at lists.sowder.comSubject: powerh-l Digest, Vol 31, Issue 12Send powerh-l mailing list submissions to                powerh-l at lists.sowder.comTo subscribe or unsubscribe via the World Wide Web, visit                http://lists.sowder.com/mailman/listinfo/powerh-lor, via email, send a message with subject or body 'help' to                powerh-l-request at lists.sowder.comYou can reach the person managing the list at                powerh-l-owner at lists.sowder.comWhen replying, please edit your Subject line so it is more specific than"Re: Contents of powerh-l digest..."Today's Topics:  1. Powerhouse and Result Sets/Cursors     (Adrian.Hudson at eu.averydennison.com)----------------------------------------------------------------------Message: 1Date: Tue, 18 Dec 2007 16:57:57 +0100From: Adrian.Hudson at eu.averydennison.comSubject: Powerhouse and Result Sets/CursorsTo: powerh-l at lists.sowder.comMessage-ID:                <OFB361EA54.9B7A67F3-ONC12573B5.0055DD10-C12573B5.00579241 at averydennison.com>                Content-Type: text/plain; charset="us-ascii"Hi,This is my first post, so I hope I manage to follow all the necessaryetiquette!Has anyone managed to use a cursor with a result set in Powerhouse andOracle (This was introduced in 8.4D)?I'm struggling with how the Cursor/Result Set is passed back (and indeeddefined) in the Powerhouse code.We are running Oracle 10g with Powerhouse 8.43D1 on AIXThe text below illustrates the problem.Regards,AdrianCreate package and procedure----------------------------------------------create package powpkgastype CursorType is REF CURSOR;end powpkg;create or replace procedure return_wishlist (ocursor in outpowpkg.CursorType)asbeginopen ocursor for select description from hudsona.boys_wishlist; end; /To demo it works in SQLPLUS--------------------------------------------SQL hudsona at DVXD> variable c refcursorSQL hudsona at DVXD> exec return_wishlist (:c)PL/SQL procedure successfully completed.SQL hudsona at DVXD> print cDESCRIPTION--------------------------------------------------Football GameKeyboardShrek DVDPowerhouse code-------------------------- sql in css2base &declare c_cursor cursor for &call hudsona.return_wishlist &result set description varchar(50)acc c_cursorgo-------------------------------------------------------------------------------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 towhich it is addressed and may contain confidential and/or privilegedmaterial. Any review, retransmission, dissemination or other use of, ortaking of any action in reliance upon, this information by persons orentities other than the intended recipient is prohibited. If youreceived this in error, please contact the sender and delete thematerial from any computer.-------------- next part --------------An HTML attachment was scrubbed...URL:http://lists.sowder.com/pipermail/powerh-l/attachments/20071218/93863039/attachment-0001.html--------------------------------= = = = = = = = = = = = = = = = = = = = = = = = = = = = Mailing list:powerh-l at lists.sowder.comSubscribe: &quot;subscribe&quot; in message body topowerh-l-request at lists.sowder.comUnsubscribe: &quot;unsubscribe &lt;password&gt;&quot; in message body topowerh-l-request at lists.sowder.comhttp://lists.sowder.com/mailman/listinfo/powerh-lThis list is closed, thus to post to the list you must be a subscriber.Add 'site:lists.sowder.com powerh-l' to your search terms to search thelist archive at Google.End of powerh-l Digest, Vol 31, Issue 12****************************************-- = = = = = = = = = = = = = = = = = = = = = = = = = = = =Mailing list: powerh-l at lists.sowder.comSubscribe: &quot;subscribe&quot; in message body to powerh-l-request at lists.sowder.comUnsubscribe: &quot;unsubscribe &lt;password&gt;&quot; in message body to powerh-l-request at lists.sowder.comhttp://lists.sowder.com/mailman/listinfo/powerh-lThis list is closed, thus to post to the list you must be a subscriber.Add 'site:lists.sowder.com powerh-l' to your search terms to search the list archive at Google.- -----------------------------------------------------------------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.
_________________________________________________________________
Use fowl language with Chicktionary. Click here to start playing!
http://puzzles.sympatico.msn.ca/chicktionary/index.html?icid=htmlsig
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20080103/b31966ab/attachment.htm