Re: ORA-06550: declaring cursor inside "begin/end" structure of PL/SQL block
- From: fitzjarrell@xxxxxxx
- Date: 9 Dec 2005 18:36:00 -0800
strepxe@xxxxxxxxxxx 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
.
- Follow-Ups:
- References:
- Prev by Date: Re: ORA-29516: Aurora assertion failure: Assertion failure at eox.c:317
- Next by Date: Re: ORA-29516: Aurora assertion failure: Assertion failure at eox.c:317
- Previous by thread: Re: ORA-06550: declaring cursor inside "begin/end" structure of PL/SQL block
- Next by thread: Re: ORA-06550: declaring cursor inside "begin/end" structure of PL/SQL block
- Index(es):
Relevant Pages
|
|
