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

<strepxe_at_yahoo.co.uk> wrote in message
news:1134132386.423743.275330_at_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!
>

declarations, including cursor declarations, belong in a declare section of a block

you can next blocks with a declare section if required, but in this case, you probably just need to move the cursor declaration up into the declare section

also, cursor for loops return records, not scalars, and the record is implicitly declared in the for loop... you have two v_names, the varchar2 explicitly declared variable, and the v_name RECORD variable implicitly created for you for loop

i'm sure others will have additional observations about best practices...

++ mcs Received on Fri Dec 09 2005 - 06:55:21 CST