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

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

Post by Quentin Nor » Wed, 28 Jan 1998 04:00:00



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.