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

strepxe_at_yahoo.co.uk wrote:
> 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!

This is how you should have written it:

declare

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

cursor get_str is select name from tbl1; cursor get_row (v_nme in varchar2) is select col1 from tbl2 where stream_name =
v_nme;

begin

for v_name in get_str
loop
open get_row(v_name.name);
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;
/

Note the cursor declaration is in the 'declare' section, where it should be, and note also you've coded the second cursor to accept a parameter, to pass the value from the first cursor to the second.

David Fitzjarrell Received on Fri Dec 09 2005 - 20:36:00 CST