Re: PL/SQL error

Im guessing your coming from an object oriented background right? You are trying to create a cursor 'object' and use it later?

dont work that way in oracle.

Syntax is..

decare

cursor cur IS
Select col
from table;

rec cur%ROWTYPE (create a dynamic record to hold what your cursor retrieves)

begin

for rec in cur;
  loop
   do stuff with rec -- to access individual columns inside of the record do rec.<column_name>
 end loop;

end;

been working with alot of OO guys lately.

"shiva" <shivas_bhat_at_yahoo.co.in> wrote in message news:fe32bedd.0301280757.63d0befa_at_posting.google.com...
> 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
Received on Wed Jan 29 2003 - 15:28:37 CST