Re: Error in Stored Procedure [message #36747] |
Mon, 17 December 2001 07:35 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
Has it ever worked?? This proc depends on a Package which contains the definition of the cursor variable (t_cursor). If it was worked before, see what needs compiling and just compile it. Note, that you should compile the specs first then the bodies to avoid dependency issues. Avoid re compiling specs as the code which depends on the often needs re-compiling them. Try something like this:
set heading off
set feedback off
set term off
spool s.sql
SELECT
'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)||' '||object_name||' '||decode(object_type,'PACKAGE BODY','COMPILE
BODY','COMPILE')||';'
FROM user_objects
WHERE object_type IN
('PACKAGE','PACKAGE BODY','VIEW','PROCEDURE','TRIGGER','FUNCTION')
AND status='INVALID';
spool off
set term on
set heading on
set feedback on
@s.sql
Below is an example of a function with a ref cursor and a test to call it.
SQL> create or replace package types as
2 type sqlcur is REF cursor;
3 end;
4 /
Package created.
SQL>
SQL>
SQL> create or replace function test return types.sqlcur as
2 c1 types.sqlcur;
3 begin
4 open c1 for select table_name from cat where rownum < 5;
5 return c1;
6 end;
7 /
Function created.
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 c1 types.sqlcur;
3 v_tab_name varchar2(30);
4 begin
5 c1 := test;
6 loop
7 fetch c1 into v_tab_name;
8 exit when c1%NOTFOUND;
9 dbms_output.put_line(v_tab_name);
10 end loop;
11 end;
12 /
ABC
ABC_2
ABC_SEQ
DEPT
PL/SQL procedure successfully completed.
----------------------------------------------------------------------
|
|
|