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

'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)||' '||object_name||' '||decode(object_type,'PACKAGE BODY','COMPILE
FROM user_objects
WHERE object_type IN
AND status='INVALID';

spool off
set term on
set heading on
set feedback on

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> 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> set serveroutput on
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 /

PL/SQL procedure successfully completed.

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

Current Time: Tue May 28 05:50:13 CDT 2024