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


<strepxe@xxxxxxxxxxx> wrote in message
news:1134132386.423743.275330@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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


.

Relevant Pages

  • Re: Cursor loop
    ... I've created a stored procedure that loops through a cursor, ... DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods ... The problem is that this loop only executes one time, ...
    (comp.databases.ms-sqlserver)
  • Opening cursor after closing does not seem to requery
    ... I declare a cursor, then enter a loop. ... opens at the same row as the initial opening, ...
    (microsoft.public.sqlserver.programming)
  • Re: ORA-06550: declaring cursor inside "begin/end" structure of PL/SQL block
    ... > This is a newbie question so please don't be too annoyed if the ... > I've written PL/SQL which gets a lists of values into a cursor. ... > list of values is then used in a loop to create another cursor ... > DECLARE ...
    (comp.databases.oracle.server)
  • Loop with Read Only cursor?
    ... loop, a few days ago. ... but I don't know how to declare a read only cursor. ... I had 2 columns - a RowNum col and ...
    (microsoft.public.inetserver.asp.general)
  • ORA-06550: declaring cursor inside "begin/end" structure of PL/SQL block
    ... This is a newbie question so please don't be too annoyed if the ... I've written PL/SQL which gets a lists of values into a cursor. ... list of values is then used in a loop to create another cursor ... I'm aware of the limitations of cusors from a basic point of view. ...
    (comp.databases.oracle.server)