[Q] pl/sql cusror state using Oracle web agent, web server & database server

Apologies for the broad ng hit, but it could count in any of them.

I am trying to use Oracle web server 1.0, Oracle web agent and Oracle Server 7.3.3 to simply return a list of items from a table in groups of ten as a web page allowing the user to click "next ten" when required.

To do this I have written a couple of pl/sql procedures which are encapsulated in a pl/sql package. The package contains a public declaration of a cursor which is used to fetch each row from the table for display. According to the documentation a public cursor in a package should have its state preserved between calls to the package.

When i test these procedures from SQLWorksheet, I execute pack.getfirstten which opens the cursor and fetches the first ten rows. I then execute pack.getnextten which fetches the next ten rows as expected.

However, when I call the procedures through the use of Oracle Web Agent as a URL (eg http://machine/test/owa/pack.getfirstten), the first procedure correctly returns the first ten rows, but a call to the next procedure in the form of a URL
(http://machine/test/owa/pack.getnextten) fails with an (ORA-1001 [i think]) unidentified cursor error written to the OWA service log.

Has anyone any ideas why this doesn't work, how it can be made to work, and if it wont ever work what is an alternative approach based in pl/sql with OWA as Im on tight timescales?

Many thanks in advance. Received on Tue Jan 27 1998 - 00:00:00 CST