ORA-06550: declaring cursor inside "begin/end" structure of PL/SQL block

From: <strepxe_at_yahoo.co.uk>
Date: 9 Dec 2005 04:46:26 -0800
Message-ID: <1134132386.423743.275330@z14g2000cwz.googlegroups.com>

This is a newbie question so please don't be too annoyed if the question is trivial or the method I'm using is inefficient. I'm keen to learn!

I've written PL/SQL which gets a lists of values into a cursor. That list of values is then used in a loop to create another cursor containing LONG RAW values which I'm trying to get the size of for some capacity planning activites.

Here's the basic structure:

DECLARE
   CURSOR1
   BEGIN
   LOOP1

      CURSOR2
         LOOP2
         END LOOP2

   END LOOP1
END Here's the PL/SQL itself:

---

set serveroutput on

declare

v_longcol long raw;
v_size number(8,3) not null := 0;
v_name varchar(100);
r_count number not null := 0;

cursor get_str is select name from tbl1;

begin

for v_name in get_str
loop

        cursor get_row is select col1 from tbl2 where stream_name = 'v_name.name';

	open get_row;
	fetch get_row into v_longcol;
	loop
	exit when get_row%notfound;

		v_size := utl_raw.length(v_longcol) / 1024 ;
                ...
                ....
                ...

	fetch get_row into v_longcol;
	end loop;
	close get_row;

end loop;

end;
/

---

When I run the block I get the following error:

---

        cursor get_row is select col1 from tbl2 where stream_name = 'v_name.name';

               *
ERROR at line 23:

ORA-06550: line 23, column 9:
PLS-00103: Encountered the symbol "GET_ROW" when expecting one of the
following:

:= . ( @ % ;

---

I'm aware of the limitations of cusors from a basic point of view. I guess I need some way to generate cusors in another way inside the loop.

Any help very much appreciated! Received on Fri Dec 09 2005 - 06:46:26 CST