Home » SQL & PL/SQL » SQL & PL/SQL » Re: Error in Stored Procedure
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.

----------------------------------------------------------------------
Previous Topic: Re: pl/sql-urgent
Next Topic: move a tablespace of table
Goto Forum:
  


Current Time: Thu Mar 28 20:28:54 CDT 2024