Re: PL/SQL error

shiva wrote:

> 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

What is: cursor cur; ? This is not how you define a static cursor.

Try:

CURSOR cur IS

   SELECT *
   FROM ... And a parameter named "sys" surely you jest or just want to live dangerously. Time to explore the contents of v_$reserved_words methinks.

Daniel Morgan Received on Tue Jan 28 2003 - 10:49:19 CST