PL/SQL: cursor variables can point to cursors?

From: Alexander Zimmer <zimmer_at_hollomey.com>
Date: Tue, 3 Dec 2002 12:34:54 +0100
Message-ID: <MPG.1856b5341ce65c79896ce@news.cis.dfn.de>

I have a PL/SQL-Code fragement that does some deeds on data which are fetched via cursors. The deeds are identical to all cursors, so it would be nice if I could open "the right" cursor and then proceed with code that processes the data from just this opened cursor.

To illustrate this: I would need a construct like this:

---

  cursor cu1
    is select text from textblocks;     

  cursor cu2
    is select text from other_textblocks;     

  x_which_cursor := 1;
  if x_which_cursor = 1 then
    cuText := cu1;
  else
    cuText := cu1;
  end if;   

  open cuText;
  loop
    fetch cuText into x_text;
    exit when cuText%NOTFOUND;
  end loop;
  close cuText;

---

So, depending on an if-construct, I want to open either Cursor cu1 or Cursor cu2. Can I do something like this in a syntactically "nice-to- look-at" way?

One (not so beautiful) way I can think of is this:

---

  cursor cu1
    is select text from textblocks;     

  cursor cu2
    is select text from other_textblocks;     

  x_which_cursor := 1;
  if x_which_cursor = 1 then
    open cu1;
  else
    open cu2;
  end if;   

  loop
  if x_which_cursor = 1 then
    fetch cu1 into x_text;
    exit when cu1%NOTFOUND;
  else
    fetch cu2 into x_text;
    exit when cu2%NOTFOUND;
  end if;
  end loop;   

  if x_which_cursor = 1 then
    close cu1;
  else
    close cu2;
  end if;

---

But this seems to me quite ugly - the many IF's make the code somewhat obfuscated.

I would need something like a cursor variable, but instead of assigning a SQL-Select to a cursor variable, I would need to assign an explicit cursor to such a cursor variable. This seems not to be possible, according to my available documentation.

BTW:

• I want to use explicit definitions for cu1 and cu2.
• I can tackle this problem with some tricks, but I don't want to. I'm looking for a "clean" solution

Thanx a lot for all useful hints!
Alex Received on Tue Dec 03 2002 - 05:34:54 CST