Fog Creek Software
Discussion Board




Resetting Oracle Cursors

Is there a way to get an oracle cursor pointer pointed at the top of the cursor without closing and re-opening the cursor?

Due to the urban legened around here that 'dynamic sql causes performance problems' (which may be true but has never been tested in our production environment) I get to do some rework...

Kevin

Kero
Wednesday, September 17, 2003

To the best of my knowledge OCI supports scrollable cursors in Oracle 9i.

So if you are using native OCI to talk to the database you can do things like this. However it is not available when using PL/SQL stored procedures.

Here is a link to some info:

http://www.csis.gvsu.edu/GeneralInfo/Oracle/appdev.920/a96584/toc.htm

Patrik
Wednesday, September 17, 2003

Kevin,

I have thought about how you can work around the lack of scrollable cursrors in stored procedures - depending on your data volumes you need to be able to access randomly this might work.

Here is a PL/SQL code example:


CREATE OR REPLACE procedure dummy_test is

cursor cUserCur is select username from all_users;

type UserRec is
        table of cUserCur%rowtype
        index by binary_integer;

AUser cUserCur%rowtype;
UserList Userrec;
counter integer;

begin
counter:=0;
open cUserCur;
loop
  fetch cUserCur into AUser;
  if cUserCur%found then
    counter:=counter+1;
  end if;
  UserList(Counter):=AUser;
  if cUserCur%notfound then
      exit;
  end if;
  end loop;
  close cUserCur;

  -- Access the data in random order.
  dbms_output.put_line(UserList(1).username);
  dbms_output.put_line(UserList(Counter/2).username);
  dbms_output.put_line(UserList(Counter-1).username);
  dbms_output.put_line(UserList(Counter).username);

end;
/

Patrik
Wednesday, September 17, 2003

Dynamic SQL causes performance problems only if you do not use bind variables in repetive SQL - then you just flood SQL cache with new queries and SQL cache noble features never kicks in.

Dig AskTom http://asktom.oracle.com/ for further.

msc
Wednesday, September 17, 2003

> open cUserCur;
> loop
>  fetch cUserCur into AUser;

If you fetch data into PLSQL table (array) then always use bulk fetches (a lot faster). In 9i you can bulk-bind into record based array but in 8i you have to use separate array for each columns.

Bulk fetch (collect into) example:

declare
  -- define type for array
  type TStringTable is table of varchar2(50) index by binary_integer;
  -- declare array for result
  tabObject TStringTable;
begin
  -- bulk fetch
  select OBJECT_NAME bulk collect into tabObject
  from ALL_OBJECTS
  where OBJECT_NAME like 'A%';
  -- check that at least one row was fetched
  -- (otherwise you get index out of bounds error)
  if tabObject is not NULL then
    -- show count
    dbms_output.put_line( 'There is ' || to_char( tabObject.count ) || ' accessible objects' );
    -- loop over... and print out name
    for i in tabObject.first .. tabObject.last loop
      dbms_output.put_line(tabObject(i));
    end loop;
  end if;
end;

msc
Wednesday, September 17, 2003

Thanks everyone.

The dynamic sql argument is a tad on the religous side so it isn't really winable in this situation.  I'm pretty sure collections fall in the same category...it's almost like the guy in charge didn't get that far in his self-education.

What I ended up doing is creating 2 tables mimicing the cursors and walking the tables doing insert, update, delete stuff using the 'first' pointer to the head of the tables.  It is is basically a cobol program written in PL/SQL...

Kevin

Kero
Wednesday, September 17, 2003

I am sure there is way to do it with SQL. Maybe you post procedure outline here - maybe someone can see the light?

msc
Thursday, September 18, 2003

*  Recent Topics

*  Fog Creek Home