Re: PL/SQL error

Shiva

Additionally to the solutions already pointed out, you could also use a ref cursor. First, you'd have to create a ref cursor type (type cur_t is ref cursor) and then declare a variable of that type (cur cur_t).

Also, first fetch from a cursor, then check for NOTFOUND. I changed

    exit when cur%NOTFOUND;
    fetch cur into v_a, v_b;
to

    fetch cur into v_a, v_b;
    exit when cur%NOTFOUND;
.

Try, what happens, if you check for NOTFOUND first and fetch then.

--------------->8-------------->8----------------------

set serveroutput on size 1000000

create table x_ (a number, b varchar2(10));

insert into x_ Values (3,'three');
insert into x_ Values (4,'four');
insert into x_ Values (2,'two');
insert into x_ Values (5,'five');
insert into x_ Values (1,'one');

create or replace procedure plan(sys_ in varchar2) as   iKeyVal integer;

  type cur_t is ref cursor;
  cur cur_t;

  v_a x_.a%type;
  v_b x_.b%type;

begin
  open cur for 'select a,b from x_ where a > :1' using sys_;   loop
    fetch cur into v_a, v_b;
    exit when cur%NOTFOUND;
    dbms_output.put_line('found a: ' || v_a || ', b: ' || v_b);   end loop;
  close cur;
end;
/

exec plan(3);

drop table x_;

--------------->8-------------->8----------------------

Hope, this helps

Rene Nyffenegger

> the following procedure give errors:
>
> create or replace procedure plan(sys in varchar2) as
> iTmp integer;
> iKeyVal integer;
> cursor cur;
> ctname varchar2(40);
> begin
> open cur for 'select table_name from user_tab_columns where
> substr(table_name,1,2) = :1' using sys;
> loop
> exit when cur%NOTFOUND;
> fetch cur into ctname;
> execute immediate('select nvl(max(contkey),0) from '||ctname) into
> iTmp;
> if iTmp > iKeyVal then iKeyVal := iTemp; end if;
> end loop;
> close cur;
> execute immediate('drop sequence seq'||sys||'cntkeycontkey');
> dbms_output.put_line('create sequence seq'||sys||'cntkeycontkey
> cache 10 start with '||to_char(iKeyVal+1));
> execute immediate('create sequence seq'||sys||'cntkeycontkey cache
> 10 start with '||to_char(iKeyVal+1));
>
> execute immediate('select nvl(max(actionkey),0) from
> '||sys||'actns') into iKeyVal;
> execute immediate('drop sequence seq'||sys||'actkeyactionkey');
> execute immediate('create sequence seq'||sys||'actkeyactionkey cache
> 10 start with '||to_char(iKeyVal+1));
> dbms_output.put_line('create sequence seq'||sys||'actkeyactionkey
> cache 10 start with '||to_char(iKeyVal+1));
>
> end;
> /
>
> The error is:
> PLS-00360: cursor declaration without body needs return type
>
> Can anyone help.
> Thanks
> Shiva

-- 
  no sig today